search with combo box on subform

[brownti via AccessMonster.com]

I have a main form with addresses on it and I am using a combo box to search
through the addresses, this works fine it is set up as follows.

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

Set rs = Me.Recordset.Clone
rs.FindFirst "[Address] = '" & Me![Combo75] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
Me.Combo75 = ""
Combo75.Requery
End Subworks great, I have it set up as follows

Next I have a subform with RoomNames on it for each of the addresses, I want
to be able to search the RoomNames with a combo box also. I have triied to
set it up in the same manor, but I can't seem to get it to work. When I have
it set up as follows the combo box lets me pick a record but nothing happens.

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

Set rs = Me.Recordset.Clone
rs.FindFirst "[RoomName] = '" & Me![Combo34] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
Me.Combo34 = ""
Combo34.Requery
End Sub

Any suggestions would be great.

 

[Marshall Barton]

I have a main form with addresses on it and I am using a combo box to search
through the addresses, this works fine it is set up as follows.

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

Set rs = Me.Recordset.Clone
rs.FindFirst "[Address] = '" & Me![Combo75] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
Me.Combo75 = ""
Combo75.Requery
End Subworks great, I have it set up as follows

Next I have a subform with RoomNames on it for each of the addresses, I want
to be able to search the RoomNames with a combo box also. I have triied to
set it up in the same manor, but I can't seem to get it to work. When I have
it set up as follows the combo box lets me pick a record but nothing happens.

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

Set rs = Me.Recordset.Clone
rs.FindFirst "[RoomName] = '" & Me![Combo34] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
Me.Combo34 = ""
Combo34.Requery
End Sub

That looks like wizard generated code, which uses an
incorrect check for not finding a matching record. Change
the rs.EOF to rs.NoMatch.

While it probably won't cause a problem, you should use
RecordsetClone instead of Recordset.Clone.

If there is any chance that an address or room name can
contain an apostrophe, you should use:
"[RoomName] = """ & Me![Combo34] & """ "

I suspect that your specific problem might be because a
combo box is not set appropriately, probably the BoundColumn
property does not corresponf to the room name field.

 

[John Spencer]

Do you mean you want to search all records in the main form's recordset that
has a RoomName that is in the subform's recordset?

If so, I don't know of a way to do it directly.

Move the combo for RoomNames to the main form and then use dLookup to get an
address record with the roomname.

UNTESTED AIR CODE follows for the after update event of the combobox. You
will need to modify it for the proper field and table names.

Private sub ComboSelectRoomNames_AfterUpdate()
Dim StrFind as String
Dim rs as DAO.Recordset
Dim dbAny as DAO.Database

set dbAny = CurrentDB()

strFind = "SELECT Address " & _
" FROM AddressTable INNER JOIN RoomsTable " & _
" On AddressTable.AddressID = RoomsTable.AddressID" & _
" WHERE RoomsTable.RoomName = """ &
Me.ComboSelectRoomNames & """"
Set Rs = dbany.openRecordset (strFind)

IF Rs.RecordCount > 0 Then strFind = Rs.Fields(0)

rs = Me.RecordsetClone
rs.FindFirst "Address = '" & strFind & "'"
If rs.NoMatch = False Then
me.BookMark = rs.Bookmark
Me.ComboSelectRoomNames= Null
End IF

END IF
End Sub


By the way, name your objects with more descriptive names.

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[John W. Vinson]

Next I have a subform with RoomNames on it for each of the addresses, I want
to be able to search the RoomNames with a combo box also. I have triied to
set it up in the same manor, but I can't seem to get it to work. When I have
it set up as follows the combo box lets me pick a record but nothing happens.

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

Set rs = Me.Recordset.Clone
rs.FindFirst "[RoomName] = '" & Me![Combo34] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
Me.Combo34 = ""
Combo34.Requery
End Sub

The problem is probably that the Subform's Recordsource is just the few
records related to the mainform's current record - not the entire table.

You'll need to create a new query joining the master table with the rooms
table, and synchronize both the main form and the subform.

John W. Vinson [MVP]