Fill in Text boxes based on Query results?

[slenish]

Hello,

Im having trouble getting a form to fill in mulitple text boxes based on a query.

What I have is a form where I can enter the Criteria information to look up a record. You have to enter the information in to 3 text boxes on the form. Then after your done a query will run pulling up the results. That part seems to work with no problem but what i cant get to work is instead of the query window popping up, have all of the the other text boxes on the form fill in with the information.

I was thinking I might have to change all of my text boxes to list boxes but not sure if that would be the best approach?? Also after the information fills in i need to be able to edit the information, if i can do that with a list box then maybe that is the best approach.

Any help would be great!

Thanks in advance :D

[MOCaseA]

Let me see if I have this straight. You are trying to create a record look up based off the information typed into a series of text boxes. You also want to be able to edit the information on the record that you look up. Correct?

[slenish]

yes you are correct, but the information from the query I want to populate a form. Then you can edit the information on the form and then re-save it.

[MOCaseA]

Try something like this:

Option Compare Database

Private Sub Field1_AfterUpdate()
    SetFilter
End Sub

Private Sub Field2_AfterUpdate()
    SetFilter
End Sub

Private Sub Field3_AfterUpdate()
    SetFilter
End Sub

Private Sub SetFilter()
    Dim FilterCriteria As String
    If Field1 & "" <> "" Then FilterCriteria = FilterCriteria & " AND [Field1]='" & Field1 & "'"
    If Field2 & "" <> "" Then FilterCriteria = FilterCriteria & " AND [Field2]='" & Field2 & "'"
    If Field3 & "" <> "" Then FilterCriteria = FilterCriteria & " AND [Field3]='" & Field3 & "'" 
    If FilterCriteria = "" Then
            Me.FilterOn = False
    Else
        FilterCriteria = Mid(FilterCriteria, 6) 'REMOVE THE LEADING " AND "
        Me.Filter = FilterCriteria
        Me.FilterOn = True
    End If
End Sub

Do this by creating 3 Combo Boxes somewhere on your form. Relabel the "Field#" with the appropriate combo box name, then set your row source to the appropriate field on the associated table (you can use the Query Builder for this to make it easier). Make sure your control source properties are blank. Finally if you find you are generating huge lists of duplicate data in the combo boxes, or wish to avoid having this happen in the future change "SELECT" to "SELECT DISTINCT" in the row source. Also if you wish to avoid typos you can change the Limit to List property to "Yes."

This will create a series of 3 combo boxes that allow you to look up information. It's quick and dirty, and still allows for user error (Like if you select a name then a company which that name is not associated with in any record) but if you add it to your main data form, then you can filter your records down. Attached is a screen shot showing an example of how I implemented this.