Find Record Button on Form and return data to the form

[ballj_35]

I have been using Combo Boxes and now I need to understand how to create a Find Button on the same form. What I would like to do is allow the user to enter a value to get a Member Bond Number (format: double). They would enter a value and press the FIND button. If the record is found move the MBR_Last_Name, etc.. to the form. If the record is not found, display an error message.


Then if the record is found, allow the user to enter a Term_Date value on the form. When they click on the SAVE button, then move the form fields to another tables field and do an INSERT.
Can someone plz give me some ideas, since I am confused on all of this. I have used Combo Boxes before to get data and update, but now I have to try this without those functions.
Thanx in advance.

Jerry

[June7]

One way is a simple DLookup function.

If Not IsNull(DLookup("[Member Bond Number]", "table name", "[field name]='" & Me.textboxname & "'")) Then
'do something
End If

Example assumes text datatype. If not, remove the apostrophe delimiters. Dates use #, numbers nothing.

[ballj_35]

I am not having success with this command. Found documentation where it stated to put this in the Control Source, which is what I am doing and I am just getting errors. I have the command as follows:

=DLookUp("[MBR_Last_Name]","Mbr_Tbl","[MBR_Bond_Nbr]= Form![txtBndNbr]")

Also I need to return 3 fields from the Mbr_Tbl to fields on the form. From what I have read, this is command is only going to return the MBR_LastName. Am I understanding this correctly?

[ballj_35]

I am attaching the DB for review. I have created a query FindBondNumber, that prompts the user for a value and get's it from the DB. If I could figure out how to do error handling for not found and move the data returned to the form fields, then I may be okay.
Once the user clicks on SAVE, then I will try to figure out how to execute an INSERT into the Transfer table. Any ideas, suggestions or places I can read to get this done?
Thanx in advance.

Jerry

[June7]

Look at my example again. The variable must be concatenated. Reference to control on form is a variable.

=DLookUp("[MBR_Last_Name]", "Mbr_Tbl", "[MBR_Bond_Nbr]='" & [txtBndNbr] & "'")

You can use it in the ControlSource or in VBA code as in my suggestion.

[ballj_35]

June7,

I have it coded like you have stated and I am still getitng an error. Coded as follows in the Control SOurce:

=DLookUp("[MBR_Last_Name]","Mbr_Tbl","[ MBR_Bond_Number]= " & [Form]![txtBndNbr] & "")

When I switch to Form View to test, in the Find Bnd Nbr field, it has "#Type!". The MBR_Bond_Number is Data Type: Number, Field Size: Double. The field txtBndNbr has a Format of General Number. DO not see what I am doing wrong here. I am confused.

[orange]

If you look carefully at June7's code, you'll see single quotes enclose the Form!txtBndNbr

'" & [txtBndNbr] & "'") change your code to match June7's post

[ballj_35]

I thank everying so much for the assistance. This is great to have a forum to reach out to others with the knowledge to help get through these challenges!

Jerry