dlookup for autonumber field

[Rizvi]

Hello Everyone
Just stuck in below query, i know that it is simple but could not figuring it out. i have a text box "txnsearch" in a form in which user will input a value. i need to search this value in a table field "Txn_number" which is an autonumber field to see if the search record is there in table "tbl_fxmain" or not. i have put code to check the null value in the text box but am trying to check the text box value in the table as per below code but it is giving me error. if any one can help me out keeping in view the Txn_number is an autonumber type field in table.

Thanks in advance.


Private Sub Command5_Click()
Dim txnsearch As Long
txnsearch = (DLookup("Txn_number", "tbl_fxmain", "Txn_number= " & "txtsearch"))




If IsNull(txtsearch) Or IsNull(txnsearch) Then
MsgBox (" Please enter Transaction Number "), vbOKOnly
txtsearch.SetFocus

Else

DoCmd.OpenForm "frm_user_edit", , , "[Txn_number]=" & txtsearch
End If


End Sub

[Bulzie]

If it is numeric(autonumber), then you don't need the 2nd quote.

txnsearch = DLookup("Txn_number", "tbl_fxmain", "Txn_number= " & txtsearch)

[June7]

You say textbox is txnsearch but then your code uses txtsearch and declares txnsearch as a variable.

The txnsearch variable can never be Null. Only Variant type variable can hold Null. Code will have runtime error if the DLookup returns Null. But if txtsearch is null, then certainly the DLookup will return Null.

Don't put reference to textbox within quote marks.

Private Sub Command5_Click()
If IsNull(DLookup("Txn_number", "tbl_fxmain", "Txn_number= " & Me.txtsearch)) Then
   MsgBox (" Please enter Transaction Number "), vbOKOnly
   Me.txtsearch.SetFocus
Else
    DoCmd.OpenForm "frm_user_edit", , , "[Txn_number]=" & Me.txtsearch
End If
End Sub

However, use a LimitToList combobox instead of textbox and users can only enter valid txn number and DLookup would not be necessary.

Suggest you give button more meaningful name than Command5.

[Rizvi]

You say textbox is txnsearch but then your code uses txtsearch and declares txnsearch as a variable.

The txnsearch variable can never be Null. Only Variant type variable can hold Null. Code will have runtime error if the DLookup returns Null. But if txtsearch is null, then certainly the DLookup will return Null.

Don't put reference to textbox within quote marks.

Private Sub Command5_Click()
If IsNull(DLookup("Txn_number", "tbl_fxmain", "Txn_number= " & Me.txtsearch)) Then
   MsgBox (" Please enter Transaction Number "), vbOKOnly
   Me.txtsearch.SetFocus
Else
    DoCmd.OpenForm "frm_user_edit", , , "[Txn_number]=" & Me.txtsearch
End If
End Sub

However, use a LimitToList combobox instead of textbox and users can only enter valid txn number and DLookup would not be necessary.

Suggest you give button more meaningful name than Command5.

Thanks for the reply
i have tried the above code, it is working fine if user is inputs any value but when the txtsearch is blank it is giving me below error, if you can help me out on this.
"run-time error 3075 syntax error (missing operator) in query expression 'Txn_number='.

[orange]

You could check and see if "txtsearch is blank" and prevent the error/ask a question/give a message.

[June7]

Sorry, should have suggested:

If IsNull(DLookup("Txn_number", "tbl_fxmain", "Txn_number= " & Nz(Me.txtsearch,0))) Then