Find Function does not find

[access345]

I have created a button that I want to fine a specific record based on its serial number. After the record is found I want a query to be executed based on the serial number that would record what time the button was pushed.

What is currently happening is when I click on the button. I get a message that I am going to run the query. But I never get to enter the serial number so the time stamp gets recorded on the first record.

Here is my code:

Private Sub btnFindRecord_Click()
On Error GoTo Err_btnFindRecord_Click

Dim stDocName As String


Screen.PreviousControl.SetFocus
DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70

'Start counting Time
'Dim stDocName As String

stDocName = "qryFunctionalTestTI"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_btnFindRecord_Click:
Exit Sub

Err_btnFindRecord_Click:
MsgBox Err.Description
Resume Exit_btnFindRecord_Click

End Sub

 

[Remou]

How about this?

Private Sub btnFindRecord_Click()
On Error GoTo Err_btnFindRecord_Click

    Dim stDocName As String
    Dim intSNo

    intSNo = InputBox("Enter Serial No: ")
    Me.Recordset.findfirst "SerialNo=" & intSNo

    [s]Screen.PreviousControl.SetFocus
    DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70[/s]
    
    'Start counting Time
    'Dim stDocName As String
    
    stDocName = "qryFunctionalTestTI"
    DoCmd.OpenQuery stDocName, acNormal, acEdit
    
Exit_btnFindRecord_Click:
    Exit Sub

Err_btnFindRecord_Click:
    MsgBox Err.Description
    Resume Exit_btnFindRecord_Click
    
End Sub

Where SerialNo is the name of the field in the table. This snippet assumes that serial number is numeric. It is more usual to use CurrentDB.Execute, which does not give a warning, or DoCmd.Execute, which can give a warning, to run action queries. If the time field is added to a control on your form, either hidden of visible, you can simply set the value:
[tt]Me.txtDateTime=Now()[/tt]

PS Have a look at the Process TGML link at the bottom of the Tek-Tips message window.

 

[access345]

Now you have me intrigued.

Your part on finding the correct record works. But I can't get the time function to work.

Here is what I have:

Private Sub btnFindRecord_Click()
On Error GoTo Err_btnFindRecord_Click

Dim stDocName As String
Dim intSNo
Dim LaborCount As Double

intSNo = InputBox("Enter Serial No: ")
Me.Recordset.FindFirst "SerialNumber=" & intSNo

Call LaborCnt

'Me.LaborTI & "LaborCount" = Now()



'Count Amt of Times Labor was used


'stDocName = "qryFunctionalTestTI"
'DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_btnFindRecord_Click:
Exit Sub

Err_btnFindRecord_Click:
MsgBox Err.Description
Resume Exit_btnFindRecord_Click

End Sub

Module LaborCnt

Public Sub LaborCnt()

On Error GoTo ERR_LaborCnt_1


Dim SerialNumber As Long
Dim Counter As Double
Dim SQL As String
Dim LaborCount As Double
Dim strQueryName As String

If IsNull(DLookup("LaborCount", "TblPAQ3280Process", "SerialNumber =" & SerialNumber)) Then
Counter = 1
Else
Counter = DLookup("LaborCount", "TblPAQ3280Process", "SerialNumber =" & SerialNumber) + 1
End If

'strQueryName = "qryupdateLabor" & Counter
DoCmd.OpenQuery strQueryName, acViewNormal, acEdit
SQL = "UPDATE TblPAQ3280Process " & _
"SET LaborCount = " & Counter & " " & _
"WHERE SerialNumber= " & SerialNumber & " "

CurrentDb.Execute SQL, dbFailOnError
Exit Sub

ERR_LaborCnt_1:
MsgBox "Information not available. Contact Administrator x412"
Exit Sub

End Sub

 

[Remou]

If DlookUp is returning Null, you need an Append Query so:

If IsNull(DLookup("LaborCount", "TblPAQ3280Process", "SerialNumber =" & SerialNumber)) Then
SQL = "Insert Into TblPAQ3280Process (SerialNumber, LaborCount) Values ( " _
      & SerialNumber & ", 1 )"    

Else
 strSQL = "UPDATE TblPAQ3280Process " & _
      "SET   LaborCount = LaborCount + 1 " & _
      "WHERE SerialNumber= " & SerialNumber
End If

[s]'strQueryName = "qryupdateLabor" & Counter
DoCmd.OpenQuery strQueryName, acViewNormal, acEdit
 strSQL = "UPDATE TblPAQ3280Process " & _
      "SET   LaborCount = " & Counter & " " & _
      "WHERE SerialNumber= " & SerialNumber & " "[/s]

CurrentDb.Execute strSQL, dbFailOnError

Or such like.

 

[access345]

I commented out the code you lined. Now I get the error message "Call administrtor

 

[access345]

Your code does create a new record. The problem is I have to update the existing record not create a new record.

 

[Remou]

If [tt](DLookup("LaborCount", "TblPAQ3280Process", "SerialNumber =" & SerialNumber))[/tt] is returning nothing, the code will create a new entry, as there is nothing to update. If you expect something to be returned, you need to check that SerialNumber contains something and that it is numeric. To check that it is not null or empty, you can Debug.Print or step through the code.