I'm working on a final for my VB 6.0 course and I am having one problem after another.

I'm also having trouble getting a timely response from my instructor and nobody else will help since it's the final.

Ok, here's my problem now.  For some reason, when I select add from the menu, it's overwriting the record I select add on instead of adding the record to the end of the database.  My guess is that it's something to do with my SQL, but I have triple checked it and I can't find where it's
incorrect.  I tried taking out the clear record sub procedure, however, my last assignment I used that during the add process and it didn't cause a problem, but when I remove that procedure, it just unlocks the
textboxes for editing.  Once again, overwriting the current record.  Also, my validation, I took the books suggestion and put my validation in the data control validate event, however, that's not working either.  I tried using requery, but I get an error stating that I can't use for this type of object.  When I enter text in a numeric
field, instead of getting the appropriate message, I'm getting a data conversion error.

Here's my code:

Public gdbCurrent As Database
Private mrstCurrent As Recordset
Private psngProfit As Single
Private psngSalesPrice As Single
Private psngCost As Single
Private fldID As Long
Private fldCategory As String
Private fldDescription As String
Private fldSalesUnit As String
Private fldCost As Single
Private fldSalesPrice As Single
Private fldLastInventoried As Date

Private Sub CalculateProfit()
    psngProfit = Val(txtSalesPrice - txtCost)
    lblProfit.Caption = Format$(psngProfit, "currency")
End Sub


Private Sub SetEditState(pblnEditing As Boolean)
    If pblnEditing Then
        mnuFind.Enabled = False
        txtProductID.Locked = False
        txtCategory.Locked = False
        txtProductDscrp.Locked = False
        txtSalesUnit.Locked = False
        txtCost.Locked = False
        txtSalesPrice.Locked = False
        txtLastInv.Locked = False
        mnuEditAdd.Enabled = False
        mnuEditRecord.Enabled = False
        mnuEditUpdate.Enabled = True
        mnuEditDelete.Enabled = False
        mnuEditCancel.Enabled = True
    Else
        mnuFind.Enabled = True
        txtProductID.Locked = True
        txtCategory.Locked = True
        txtProductDscrp.Locked = True
        txtSalesUnit.Locked = True
        txtCost.Locked = True
        txtSalesPrice.Locked = True
        txtLastInv.Locked = True
        mnuEditAdd.Enabled = True
        mnuEditRecord.Enabled = True
        mnuEditUpdate.Enabled = False
        mnuEditDelete.Enabled = True
        mnuEditCancel.Enabled = False
    End If
End Sub

Private Sub LoadCurrentRecord()
    txtProductID = mrstCurrent![fldID]
    txtCategory = mrstCurrent![fldCategory]
    txtProductDscrp = mrstCurrent![fldDescription]
    txtSalesUnit = mrstCurrent![fldSalesUnit]
    txtCost = mrstCurrent![fldCost]
    txtSalesPrice = mrstCurrent![fldSalesPrice]
    txtLastInv = mrstCurrent![fldLastInventoried]
End Sub

Private Sub ClearCurrentRecord()
    txtProductID = vbNullString
    txtCategory = vbNullString
    txtProductDscrp = vbNullString
    txtSalesUnit = vbNullString
    txtCost = vbNullString
    txtSalesPrice = vbNullString
    txtLastInv = vbNullString
    lblProfit = vbNullString
End Sub

Private Sub SaveCurrentRecord()
    mrstCurrent![fldID] = txtProductID
    mrstCurrent![fldCategory] = txtCategory
    mrstCurrent![fldDescription] = txtProductDscrp
    mrstCurrent![fldSalesUnit] = txtSalesUnit
    mrstCurrent![fldCost] = txtCost
    mrstCurrent![fldSalesPrice] = txtSalesPrice
    mrstCurrent![fldLastInventoried] = txtLastInv
End Sub

Private Sub DatInventory_Reposition()
    Call CalculateProfit
End Sub

Private Sub DatInventory_Validate(Action As Integer, Save As Integer)
    Dim pstrMessage As String
    Dim pintReturn As Integer
    If Action = vbDataActionUpdate Then
        If IsNumeric(txtProductID) = False Then
            pstrMessage = pstrMessage & Chr(vbKeyReturn) & _
            txtProductID & " is not a number."
            Action = vbDataActionCancel
        End If
    
        If txtProductDscrp > 50 Then
            pstrMessage = "Product Description must be less than 50 characters."
            Action = vbDataActionCancel
        End If
    
        If IsNumeric(txtCost) = False Then
            pstrMessage = pstrMessage & Chr(vbKeyReturn) & _
            txtCost & " is not a number."
            Action = vbDataActionCancel
        End If
    
        If IsNumeric(txtSalesPrice) = False Then
            pstrMessage = pstrMessage & Chr(vbKeyReturn) & _
            txtSalesPrice & " is not a number."
            Action = vbDataActionCancel
        End If
        
        If IsDate(txtLastInv) = False Then
            pstrMessage = pstrMessage & Chr(vbKeyReturn) & _
            "The date " & txtLastInv & _
            " is not a date."
            Action = vbDataActionCancel
        End If
    End If
   End Sub

Private Sub Form_Load()
    Set gdbCurrent = OpenDatabase(App.Path & "IP.mdb")
    Set mrstCurrent = gdbCurrent.OpenRecordset("tblInventory")
    Call LoadCurrentRecord
    Call SetEditState(False)
End Sub

Private Sub mnuEditAdd_Click()
    mrstCurrent.AddNew
    Dim pstrSQL As String
    Dim plngProductID As Long
    Dim pstrCategory As String
    Dim pstrProductDscrp As String
    Dim pstrSalesUnit As String
    Dim psngCost As Single
    Dim psngSalesPrice As Single
    Dim pdatLastinv As Date
        Open (App.Path & "IP.txt") For Input As #1
        Do Until EOF(1)
            Input #1, plngProductID, pstrCategory, pstrSalesUnit, psngCost, psngSalesPrice, pdatLastinv
            pstrSQL = "INSERT INTO tblInventory " & _
            "(fldID,fldCategory,fldDescription,fldSalesUnit,fldCost,fldSalesPrice,fldLastInventoried)" & _
            " VALUES ('" & plngProductID & "'" & "," & _
            "'" & pstrCategory & "'" & "," & _
            "'" & pstrProductDscrp & "'" & "," & _
            "'" & pstrSalesUnit & "'" & "," & _
            psngCost & "'" & _
            psngSalesPrice & "'" & _
            "#" & pdatLastinv & "#" & ")"
            gdbCurrent.Execute pstrSQL
        Loop
        Close #1
    Call ClearCurrentRecord
    Call SetEditState(True)
End Sub

Private Sub mnuEditCancel_Click()
    mrstCurrent.CancelUpdate
    Call LoadCurrentRecord
    Call CalculateProfit
    Call SetEditState(False)
End Sub

Private Sub mnuEditDelete_Click()
    mrstCurrent.Delete
    Dim pstrSQL As String
    Dim pstrProductID As String
    pstrProductID = InputBox("Enter Product ID", "Industrial Paper")
        pstrSQL = "DELETE * FROM tblInventory " & _
        " WHERE fldID = " & pstrProductID
        gdbCurrent.Execute pstrSQL
    Call ClearCurrentRecord
    mrstCurrent.MoveNext
    Call CalculateProfit
    Call SetEditState(False)
End Sub

Private Sub mnuEditRecord_Click()
    mrstCurrent.Edit
    Call SetEditState(True)
End Sub

Private Sub mnuEditUpdate_Click()
    mrstCurrent.Update
    Dim pstrSQL As String
    Dim pstrProductID As String
    pstrProductID = InputBox("Enter Product ID", "Industrial Paper")
        pstrSQL = "UPDATE tblInventory " & _
            "SET (fldID, fldCategory, fldDescription, fldSalesUnit, fldCost, fldSalesPrice, fldLastInventoried)" & _
            " WHERE fldID = " & pstrProductID
    Call CalculateProfit
    Call SetEditState(False)
End Sub

Private Sub mnuFileExit_Click()
    Dim pintresult As Integer
    pintresult = MsgBox("Are You Sure You Want To Exit?", vbQuestion + vbYesNo, "Exit?")
    If pintresult = vbYes Then
    Unload Me
    End If
End Sub

Private Sub mnuFindFirst_Click()
    mrstCurrent.MoveFirst
    Call LoadCurrentRecord
    Call CalculateProfit
End Sub

Private Sub mnuFindLast_Click()
    mrstCurrent.MoveLast
    Call LoadCurrentRecord
    Call CalculateProfit
End Sub

Private Sub mnuFindNext_Click()
    mrstCurrent.MoveNext
    If mrstCurrent.EOF Then
        mrstCurrent.MoveLast
    End If
    Call LoadCurrentRecord
    Call CalculateProfit
End Sub

Private Sub mnuFindPrevious_Click()
    mrstCurrent.MovePrevious
    If mrstCurrent.BOF Then
        mrstCurrent.MoveFirst
    End If
    Call LoadCurrentRecord
    Call CalculateProfit
End Sub



Please help if you can, I have pasted all my code and if necessary, I will be happy to send you my program to play around with.  I want to do well on this final, but as I said, the book is almost useless and I can't get help anywhere else!!  Thank you in advance!
kje424         

[Edited by KJE424 on 01-12-2002 at 11:22 PM GMT]

...id like 2 help,send me your database so i that i don't have to make my own database

you can send it in
noycez@hotmail.com

don't wory, your program is safe with me



____________________________
Funny thought:

Practice makes perfect.....
But nobody's perfect......
so why practice?

Just by looking at the code... Why are you using a recordset variable? With the exception of displaying info in text boxes at the form_load, everything else is done with the connection variable bypassing the rcordset variable.

From what it looks like it's doing is this:
1) load the first record and display into the textboxes
2) perform all calculations without using the recordset variable
3) display information from the recordset variable (that hasn't been moved, but may show the last info displayed)

Check the database table itself, you may find that all the records are actually there... I'll post minor modifications to your code later that will show what I'm meaning better.

Public gdbCurrent As Database     Private mrstCurrent As Recordset     Private psngProfit As Single     Private psngSalesPrice As Single     Private psngCost As Single     Private fldID As Long     Private fldCategory As String     Private fldDescription As String     Private fldSalesUnit As String     Private fldCost As Single     Private fldSalesPrice As Single     Private fldLastInventoried As Date Private Sub CalculateProfit() ' the following would be better, less error prone     psngProfit = Val(txtSalesPrice.Text) - Val(txtCost.Text)     lblProfit.Caption = Format$(psngProfit, "currency") End Sub Private Sub SetEditState(pblnEditing As Boolean) ' Since pblnEditing is True/False, and enabled is True/False ' If pblnEditing=True then Not pblnEditing=False     mnuFind.Enabled = Not pblnEditing     txtProductID.Locked = Not pblnEditing     txtCategory.Locked = Not pblnEditing     txtProductDscrp.Locked = Not pblnEditing     txtSalesUnit.Locked = Not pblnEditing     txtCost.Locked = Not pblnEditing     txtSalesPrice.Locked = Not pblnEditing     txtLastInv.Locked = Not pblnEditing     mnueditadd.Enabled = Not pblnEditing     mnuEditRecord.Enabled = Not pblnEditing     mnuEditUpdate.Enabled = pblnEditing     mnuEditDelete.Enabled = Not pblnEditing     mnuEditCancel.Enabled = pblnEditing End Sub Private Sub LoadCurrentRecord() ' Better not to use defaults for items ' .Text is the default for textboxes     txtProductID.Text = mrstCurrent![fldID]     txtCategory.Text = mrstCurrent![fldCategory]     txtProductDscrp.Text = mrstCurrent![fldDescription]     txtSalesUnit.Text = mrstCurrent![fldSalesUnit]     txtCost.Text = mrstCurrent![fldCost]     txtSalesPrice.Text = mrstCurrent![fldSalesPrice]     txtLastInv.Text = mrstCurrent![fldLastInventoried] End Sub Private Sub ClearCurrentRecord() ' "" can be used and is easier then vbNullString ' not really clearing the record, just the display     txtProductID.Text = ""     txtCategory.Text = ""     txtProductDscrp.Text = ""     txtSalesUnit.Text = ""     txtCost.Text = ""     txtSalesPrice.Text = ""     txtLastInv.Text = ""     lblProfit.Text = "" End Sub Private Sub SaveCurrentRecord() ' Umm... Isn't called, and no Update so doesn't save     mrstCurrent![fldID] = txtProductID.Text     mrstCurrent![fldCategory] = txtCategory.Text     mrstCurrent![fldDescription] = txtProductDscrp.Text     mrstCurrent![fldSalesUnit] = txtSalesUnit.Text     mrstCurrent![fldCost] = txtCost.Text     mrstCurrent![fldSalesPrice] = txtSalesPrice.Text     mrstCurrent![fldLastInventoried] = txtLastInv.Text ' If really going to use this, then the below is needed! '    mrstCurrent.Update End Sub ' For the DatInventory's... This would make an unused control... Why is it there? Private Sub DatInventory_Reposition() ' Don't know what control is using this, so didn't change     Call CalculateProfit End Sub Private Sub DatInventory_Validate(Action As Integer, Save As Integer) ' Don't know what control is using this, so didn't change     Dim pstrMessage As String     Dim pintReturn As Integer     If Action = vbDataActionUpdate Then         If IsNumeric(txtProductID) = False Then             pstrMessage = pstrMessage & Chr(vbKeyReturn) & _             txtProductID & " is not a number."             Action = vbDataActionCancel         End If              If txtProductDscrp > 50 Then             pstrMessage = "Product Description must be less than 50 characters."             Action = vbDataActionCancel         End If              If IsNumeric(txtCost) = False Then             pstrMessage = pstrMessage & Chr(vbKeyReturn) & _             txtCost & " is not a number."             Action = vbDataActionCancel         End If              If IsNumeric(txtSalesPrice) = False Then             pstrMessage = pstrMessage & Chr(vbKeyReturn) & _             txtSalesPrice & " is not a number."             Action = vbDataActionCancel         End If                  If IsDate(txtLastInv) = False Then             pstrMessage = pstrMessage & Chr(vbKeyReturn) & _             "The date " & txtLastInv & _             " is not a date."             Action = vbDataActionCancel         End If     End If End Sub Private Sub Form_Load()     Set gdbCurrent = OpenDatabase(App.Path & "IP.mdb")     Set mrstCurrent = gdbCurrent.OpenRecordset("tblInventory")     Call LoadCurrentRecord     Call SetEditState(False) End Sub Private Sub mnuEditAdd_Click() ' Following would add a new record... ' However, no data's inserted, so useless (line below). '    mrstCurrent.AddNew     Dim pstrSQL As String     Dim plngProductID As Long     Dim pstrCategory As String     Dim pstrProductDscrp As String     Dim pstrSalesUnit As String     Dim psngCost As Single     Dim psngSalesPrice As Single     Dim pdatLastinv As Date         Open (App.Path & "IP.txt") For Input As #1         Do Until EOF(1)             Input #1, plngProductID, pstrCategory, pstrSalesUnit, psngCost, psngSalesPrice, pdatLastinv             pstrSQL = "INSERT INTO tblInventory " & _             "(fldID,fldCategory,fldDescription,fldSalesUnit,fldCost,fldSalesPrice,fldLastInventoried)" & _             " VALUES ('" & plngProductID & "'" & "," & _             "'" & pstrCategory & "'" & "," & _             "'" & pstrProductDscrp & "'" & "," & _             "'" & pstrSalesUnit & "'" & "," & _             psngCost & "'" & _             psngSalesPrice & "'" & _             "#" & pdatLastinv & "#" & ")"         ' Bypassing the mrstCurrent variable!             gdbCurrent.Execute pstrSQL         Loop         Close #1     Call ClearCurrentRecord     Call SetEditState(True) End Sub Private Sub mnuEditCancel_Click() ' There's nothing going on with the mrstCurrent, so this does nothing '    mrstCurrent.CancelUpdate     Call LoadCurrentRecord     Call CalculateProfit     Call SetEditState(False) End Sub Private Sub mnuEditDelete_Click() ' Umm... You're deleting the current record, which hasn't been moved ' Then deleting the record inputted by the user (2 deletes, not 1) '    mrstCurrent.Delete     Dim pstrSQL As String     Dim pstrProductID As String     pstrProductID = InputBox("Enter Product ID", "Industrial Paper")     pstrSQL = "DELETE * FROM tblInventory " & " WHERE fldID = " & pstrProductID     ' bypassing the mrstCurrent again!         gdbCurrent.Execute pstrSQL     Call ClearCurrentRecord     ' Basically useless... Since not being used for the delete     ' and the above can delete ANY record position, not just the current     'mrstCurrent.MoveNext     Call CalculateProfit     Call SetEditState(False) End Sub Private Sub mnuEditRecord_Click() ' Useless, not being used '    mrstCurrent.Edit     Call SetEditState(True) End Sub Private Sub mnuEditUpdate_Click() '... If you're going to use the recordset variable and not the connection '    like you've been doing... put the update where I did above ' Otherwise you're not saving until here     mrstCurrent.Update ' The below is pointless if you use the above ' And is so anyway since nothing's updated or executed using the following     Dim pstrSQL As String     Dim pstrProductID As String     pstrProductID = InputBox("Enter Product ID", "Industrial Paper")     pstrSQL = "UPDATE tblInventory " & _         "SET (fldID, fldCategory, fldDescription, fldSalesUnit, fldCost, fldSalesPrice, fldLastInventoried)" & _         " WHERE fldID = " & pstrProductID ' Below is useful     Call CalculateProfit     Call SetEditState(False) End Sub Private Sub mnuFileExit_Click() ' See Form_Unload for details     Unload Me End Sub Private Sub mnuFindFirst_Click()     mrstCurrent.MoveFirst     Call LoadCurrentRecord     Call CalculateProfit End Sub Private Sub mnuFindLast_Click()     mrstCurrent.MoveLast     Call LoadCurrentRecord     Call CalculateProfit End Sub Private Sub mnuFindNext_Click()     mrstCurrent.MoveNext     If mrstCurrent.EOF Then         mrstCurrent.MoveLast     End If     Call LoadCurrentRecord     Call CalculateProfit End Sub Private Sub mnuFindPrevious_Click()     mrstCurrent.MovePrevious     If mrstCurrent.BOF Then         mrstCurrent.MoveFirst     End If     Call LoadCurrentRecord     Call CalculateProfit End Sub Private Sub Form_QueryUnload(Cancel As Integer, UnloadMode As Integer) ' the followign code with prompt no matter how it's closed! (X, menu, ...)     If MsgBox("Are You Sure You Want To Exit?", vbQuestion + vbYesNo, "Exit?") = vbNo Then         Cancel = 1 ' or cancel=true     End If End Sub

Oh, sending noycez the database would be useful... My comments in the code are suggestions based entirely on the code (not running it, or using a database)



[Edited by JLRodgers on 02-12-2002 at 07:43 PM GMT]