Seach Query in a form

I

iLevy

I want to make a form that i can chose an account name from a combo box and
all the info on that account shows up. How do i make the combo box search for
the info based on what i choose?
 
C

C Hayes

just make a query that uses two fields: primary key and the account name
save it, call it "qrySearchAccounts".

add a combo box with the wizard and select that query you just made as the
data source. Make sure to hide the primary key field when it asks you so.

give the combobox a name like "cmbSearchAccounts"

Then I would add this code to the 'after update' event

Private Sub cmbSearchAccounts_AfterUpdate()
On Error GoTo Err_cmbSearchAccounts_AfterUpdate
Me.FilterOn = False

Dim rst As Recordset
Dim strSearchName As String
Set rst = Me.RecordsetClone
strSearchName = str(Me!cmbSearchAccounts)
rst.FindFirst "key = " & strSearchName
If rst.NoMatch Then
MsgBox "Record not Found"
Else
Me.Bookmark = rst.Bookmark
End If
rst.Close
Err_cmbSearchAccounts_AfterUpdate:
Resume Exit_MyComboBox_AfterUpdate
Exit_cmbSearchAccounts_AfterUpdate:
Exit Sub
End Sub
 
C

C Hayes

C Hayes said:
just make a query that uses two fields: primary key and the account name
save it, call it "qrySearchAccounts".

add a combo box with the wizard and select that query you just made as the
data source. Make sure to hide the primary key field when it asks you so.

give the combobox a name like "cmbSearchAccounts"

Then I would add this code to the 'after update' event

Private Sub cmbSearchAccounts_AfterUpdate()
On Error GoTo Err_cmbSearchAccounts_AfterUpdate
Me.FilterOn = False

Dim rst As Recordset
Dim strSearchName As String
Set rst = Me.RecordsetClone
strSearchName = str(Me!cmbSearchAccounts)
Click to expand...
******* rst.FindFirst "key = " & strSearchName *******

******** make sure this word 'key' matches the name of your primary key in
your table!!!!! *********

******** sorry *********
 
C

C Hayes

I'll get this right one of these days...

Private Sub cmbSearchAccounts_AfterUpdate()

On Error GoTo Err_cmbSearchAccounts_AfterUpdate

Me.FilterOn = False

Dim rst As Recordset
Dim strSearchName As String

Set rst = Me.RecordsetClone
strSearchName = str(Me!cmbSearchAccounts)

'****in the following line: make sure the word
''key' is the name of your primary key****

rst.FindFirst "key = " & strSearchName

If rst.NoMatch Then
MsgBox "Record not Found"
Else
Me.Bookmark = rst.Bookmark
End If

rst.Close
Err_cmbSearchAccounts_AfterUpdate:
Resume Exit_cmbSearchAccounts_AfterUpdate

Exit_cmbSearchAccounts_AfterUpdate:
Exit Sub

End Sub
 
I

iLevy

Sorry I'm still a little confused, it's not you i'm just very new to this...

I did what you said except one diff. the account name is the primary key so
i left that part out. I made the combo box on the form and wrote the code
behind it. Then when i went into form veiw it didn't even let me select
anything. Also i want the rest of the record to show up on the form and
nothing is happening!
 
C

C Hayes

ok, try it this way:

1) create a form with all the fields you want to show up

2) create a query that only has the account name in it, name it
"qrySearchAccounts"

3) add a combo box and open the properties of that control

4) go to the "all" tab and at the very top change the name to
"cmbSearchAccounts"

5) go to the "data" tab and change the row source to "qrySearchAccounts"

6) go to the "event" tab and add the following code to the "after update"
event

Private Sub cmbSearchAccounts_AfterUpdate()

On Error GoTo Err_cmbSearchAccounts_AfterUpdate

Me.FilterOn = False

Dim rst As Recordset
Dim strSearchName As String

Set rst = Me.RecordsetClone
strSearchName = str(Me!cmbSearchAccounts)

'****in the following line: make sure the word
''AccountName' is the name of your primary key - Account Name****

rst.FindFirst "AccountName = " & strSearchName

If rst.NoMatch Then
MsgBox "Record not Found"
Else
Me.Bookmark = rst.Bookmark
End If

rst.Close
Err_cmbSearchAccounts_AfterUpdate:
Resume Exit_cmbSearchAccounts_AfterUpdate

Exit_cmbSearchAccounts_AfterUpdate:
Exit Sub

End Sub
 
Top