finding record in subform based on a combi in main form

[Derek Brown]

Hi all
I have a combobox on a main form that selects the correct customer record on
the main form. The combo also lists all appointments for each customer that
are individualy shown in a subform. Is it possible to have the combobox
first select the main form record and then use the second column of the
combo to automatically navigate the subform to the correct subform record. I
have tried the following

Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Account Code] = " & Str(Nz(Me![Combo101], 0))
rs.FindFirst "[AppointmentsForm].Form![AppointmentNumber] = " &
Str(Nz(Me![Combo101], 1))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

You can see I am punching in the dark here. when I run this I get message
"access does not recognise [AppointmentsForm].Form![AppointmentNumber]"
although Im sure if it did there would be further problems. Any help
available?

 

[Allen Browne]

On the right track, Derek.

Suggestions:
1. Be specific about the recordset type. The example below assumes a
reference to the DAO library.

2. Test NoMatch (not EOF) after a Findxxxx

3. Move the main form to the found bookmark before you start searching the
subform.

4. Make sure the combo is not null before you start.

5. I didn't understand how the combo happened to contain the right number
for both [Account Code] and AppointmentNumber, and why you were changing to
1 when null. Did you mean Combo101.Column(1)?

Something like this:

Dim rs As DAO.Recordset

If Not IsNull(Me.Combo101) Then
Set rs = Me.RecordsetClone
rs.FindFirst "[Account Code] = " & Str(Nz(Me![Combo101], 0))
If rs.NoMatch Then
MsgBox "Not found."
Else
Me.Bookmark = rs.Bookmark
Set rs = Nothing
Set rs = Me.[AppointmentsForm].Form.RecordsetClone
rs.FindFirst "[AppointmentNumber] = " & Str(Nz(Me![Combo101], 1))
If rs.NoMatch Then
MsgBox "Not found in subform"
Else
Me.[AppointmentsForm].Form.Bookmark = rs.Bookmark
End If
End If
End If

 

[Derek Brown]

Thanks Allen

The combobox's record source is a query using the the "customer table" and
the "apointments Table" so it shows all appointments (usually only 1-3 lines
per customer). The first hidden column is customer account code and the 8th
column hols the hidden appointment number so that with one click you can
select both the customer and the appointment you need to veiw.

I pasted the code that you kindly posted. I get message "Not in form" as per
your code. I was, sorry, I am, confused about the referance to Else after
checking for no value in the combo and selecting the customer record. I can
see how I could have confused the issue with my question. Below is exactly
what I have in the code behind the after update of the combo box.

Dim rs As DAO.Recordset

If Not IsNull(Me.Combo101) Then
Set rs = Me.RecordsetClone
rs.FindFirst "[Account Code] = " & Str(Nz(Me![Combo101], 0))
If rs.NoMatch Then
MsgBox "Not found."
Else
Me.Bookmark = rs.Bookmark
Set rs = Nothing
Set rs = Me.[AppointmentsForm].Form.RecordsetClone
rs.FindFirst "[AppointmentNumber] = " &
Str(Nz(Me![Combo101].Column(7), 0))

If rs.NoMatch Then
MsgBox "Not found in subform"
Else
Me.[AppointmentsForm].Form.Bookmark = rs.Bookmark
End If
End If
End If

Thanks again Allen