One combo updates another comob, but need all records listed.

[Guest]

I have two combo boxes. Search by & Search For. Select something in Search
by like "company name", it then puts all the records in Search by so you can
select one (ie ABC Flowers). But if I have two records, different company
names, but same merchant's names, they own two stores, it wont show both
records when I select merchants name - since that will be the same. What can
I do to fix this?

CODE:

Search by:

Private Sub SearchBy_AfterUpdate()
Me.SearchFor.RowSourceType = "Table/Query"
Me.SearchFor.RowSource = "SELECT DISTINCT [" & _
Me.SearchBy & "] FROM [" & _
Me.SearchBy.RowSource & _
"] ORDER BY [" & Me.SearchBy & "]"
End Sub


Search for:

Private Sub SearchFor_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[" & Me.[SearchBy] & "] = " & Chr(34) & Me![SearchFor] & Chr(34)
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
Me.SearchFor = Null
End Sub


Thanks
Curtis

 

[Douglas J. Steele]

As the name implies, FindFirst is only going to find the first record that
matches the key.

You can use use FindNext until no more records are found.

 

[Guest]

Do you mean replace FindFirst above with FindNext? I tried that and didn't
work. Are you talking about using the Find tool in Access instead, that's
the only way to do it?

Thanks!

Curtis

 

[Douglas J Steele]

"Didn't work" doesn't say much...

No, I didn't mean to replace FindFirst with FindNext. I meant that after you
did the FIndFirst, you use FindNext to determine whether any other records
match the criteria given.

If more than one record is going to be returned by the Find, you need to
code in some way to deal with it. You could put in logic that if the user
selects a merchant name that's going to return more than one record, you
prompt the user to provide more details, or you could add a Next button to
use FindNext to move from record to record.

 

[Guest]

Sorry, when it comes to coding, I don't know it well enough to simply start
writing it on my own......

 

[Douglas J Steele]

Sorry, but the point of a newsgroup isn't to write code for you: it's to
help you when you have problems.

See what you can accomplish, and post back with specific problems. Start a
new thread when you post back, btw: people often don't respond to threads
that have already had a lot of activity.