Search for Record using combo box

[magmike]

I have a contact form that when opened, shows all the records (one at
a time of course). However, I also have a combo box on the form, that
uses the company name field, and allows the user to start typing a
company name and when the desired company name is present, the form
will show that company's record.

When it does that, however, the form is filtered and there is now only
one record displayed in the form. If you go back to the combo box and
select another company, it wont find it of course, because there is
only the one company in the set now. Is there a way I can code the
combo box, to where it simply goes to that record in the set leaving
all the other records in the form, or at least, a way to reset the
form so that when a new company name is typed it seaches all those
records again and find the new company?

is this making any sense?

thanks in advance,

magmike

 

[Jeff Boyce]

Rather than using the combobox selection as a filter, another approach is to
feed the form from a query, and make that query look to the combobox for the
selection criterion (i.e., the company). This way, when the form is first
opened the combobox is empty, so the form has NO records connected. When
the user selects a company from the combobox, you use the AfterUpdate event
of the combobox to requery the form (e.g., Me.Requery), causing the
underlying query to run again, finding the selected combobox company, and
returning ONLY that record.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Dale Fye]

Mike,

What is the code that is currently in the afterUpdate event of your company
search combo box?

If you reset the forms filter property, it should work properly.

me.Filter = "[CompanyName] = '" & me.cbo_CompanyFind & "'"
me.filteron = true

Another way to address this is rather than filter the records, just find the
appropriate record and move to it, no filter at all. The code for that might
look like:

Private Sub cbo_CompanyFind_AfterUpdate

Dim strCriteria as string
Dim rs as DAO.Recordset

strCriteria = "[CompanyName] = '" & me.cbo_CompanyFind & "'"
Set rs = me.recordsetclone

rs.findfirst strCriteria
if rs.Nomatch then
msgbox "No match found"
else
me.bookmark = rs.bookmark
endif

rs.close
set rs = nothing

End sub

HTH
Dale

 

[magmike]

Mike,

What is the code that is currently in the afterUpdate event of your company
search combo box?

If you reset the forms filter property, it should work properly.

me.Filter = "[CompanyName] = '" & me.cbo_CompanyFind & "'"
me.filteron = true

Another way to address this is rather than filter the records, just find the
appropriate record and move to it, no filter at all. The code for that might
look like:

Private Sub cbo_CompanyFind_AfterUpdate

Dim strCriteria as string
Dim rs as DAO.Recordset

strCriteria = "[CompanyName] = '" & me.cbo_CompanyFind & "'"
Set rs = me.recordsetclone

rs.findfirst strCriteria
if rs.Nomatch then
msgbox "No match found"
else
me.bookmark = rs.bookmark
endif

rs.close
set rs = nothing

End sub

HTH
Dale
--
Don''t forget to rate the post if it was helpful!

Email address is not valid.
Please reply to newsgroup only.

I have a contact form that when opened, shows all the records (one at
a time of course). However, I also have a combo box on the form, that
uses the company name field, and allows the user to start typing a
company name and when the desired company name is present, the form
will show that company's record.

When it does that, however, the form is filtered and there is now only
one record displayed in the form. If you go back to the combo box and
select another company, it wont find it of course, because there is
only the one company in the set now. Is there a way I can code the
combo box, to where it simply goes to that record in the set leaving
all the other records in the form, or at least, a way to reset the
form so that when a new company name is typed it seaches all those
records again and find the new company?

is this making any sense?

thanks in advance,

magmike- Hide quoted text -

- Show quoted text -

Private Sub Combo137_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ID] = " & Str(Nz(Me![Combo137], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

However, I realize my problem. It is in another form entirely. I have
a table for setting call backs or action items on a particular
contact. There is also a button on the form to view all call backs in
order according to date, time and priority. When viewing this and a
call back on a contact not currently showing in the other form, a
button is provided that switches to the contact form and populates it
with the current call backs related contact. Here is the code for that
button:

Private Sub GetCompany_Click()
On Error GoTo Err_GetRecord_Click

Dim stDocName As String
Dim stCloseDocName As String
Dim stLinkCriteria As String

stDocName = "ProspectForm"
stCloseDocName = "CallsForm"

stLinkCriteria = "[ID]=" & Me![COMPANY]
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.Close acForm, stCloseDocName, acSaveYes

Exit_GetRecord_Click:
Exit Sub

Err_GetRecord_Click:
MsgBox Err.Description
Resume Exit_GetRecord_Click

End Sub

How could I modify this to simply find within results?