Difficulty with creating a search button that will retrieve data from table and populate form

[chloe]

Hello. I'm pretty new to Access. I've been able to navigate a lot on my own, but there is one issue I cannot seem to resolve. I have 1 form and 1 table. I have the form set up so that you can enter data, and then press a button, and it will "save" and refresh the form for a new entry. However, I want to be able to pull that entry back up in the form, and fill out additional fields later on.

The form is set to data entry = yes because I do want the form to open up as brand new each time.

To sum up my question. I want a text box and search button at the top of my form. When you type an ID number in the text box, and then press search, I want access to populate my form with the information in my data table associated with that ID number.

Example:
If I type in the number 1234 and hit search. I want my form to autopopulate with the data in the row for ID number 1234 (all the fields I have already populated). So by searching 1234, the name, phone, background info, etc that is populated in the row will appear.

Get it? I have done basic coding, but I can't seem to figure this one out.



Any help would very appreciated!!!

[orange]

How about showing us the code you have written and tried?

Try DataEntry = NO and see what happens.

[chloe]

This was my attempt at coding the search. No idea how right it is or what is missing. For ease of reference, the text box you are typing your id number in is titled searchcommandtext, the search button your clicking is searchcommandbutton, my table is titled Table Test, my form titled is 11-14 Test1, and my Id column in my table is titled ID.

Private Sub SearchCommandButton_Click()


If IsNull(SearchCommandText) = False Then
Me.Recordset.FindFirst "[Table Test], [ID]=" & SearchCommandText
Me!SearchCommandText = Null
If Me.Recordset.NoMatch Then
MsgBox "No record found", vbOKOnly + vbInformation, "Sorry"
Me!SearchCommandText = Null
End If
End If

[chloe]

I just changed the data entry to no and it worked (had to change the Me.Recordset.FindFirst "[ID]=" & SearchCommandText as well). But I want the form to open as a blank slate ready to start a new entry. Any suggestions on how to have both?

[chloe]

I got it! Nevermind! Thank yoU!!!

[orange]

Good stuff!!!

Also, for your info, there is a free product called smart indenter which when run against your code produces this:

Private Sub SearchCommandButton_Click()


    If IsNull(SearchCommandText) = False Then
        Me.Recordset.FindFirst "[Table Test], [ID]=" & SearchCommandText
        Me!SearchCommandText = Null
        If Me.Recordset.NoMatch Then
            MsgBox "No record found", vbOKOnly + vbInformation, "Sorry"
            Me!SearchCommandText = Null
        End If
    End If
 End Sub

I have used it with Acc2000, 2003 and 2010. Very useful for formatting vba code for readability.