Combo Box subform 'Reverse lookup?'

[russ25rsa]

I have two tables A and B. There is a 1 to many relationship between A and B. Table A's data is controlled by FormA, Table B's data is controlled by FormB which is a subform on FormA. I already have a combo box for navigation on FormA, however I would also like to be able to have a combo box on FormB (FormA's subform) for navigation, so that when I choose an record from the subforms (FormB) combo box, FormA then jumps to the 1 record from FormA that contains the related record choosen in the SubFormB's combo box (along with the subforms choosen record on the subform) .

Kindalike the combo box on FormA, except it's a 'reverse lookup' if that makes sense. Is this at all possible, does this even make sense?

_______________
HOLY CRAP BATMAN!!!

 

[pono1]

If you're not uncomfortable sharing a little more info, I would guess someone here could help move you forward if you provided more details on your database -- data tables and their relationships and what you hope to accomplish and why with your forms. It's not completely clear, as is.

Regards,
Tim

 

[russ25rsa]

Thanks antway

Thanks anyway, "I" have managed to sort it. But as a matter of interest this what I was try to accomplish...

The database is used to track a series of insurance claims... More specifically workers compensation claims... btw I am not an insurance salesman . There are employers (1) and claimants (m). Each Employer can have multiple claimants (employees who claim compensation). The employers form constitutes the one side of the relationship and the claimaints subform constitutes the many side.

Browsing by employers on the employers form with a combo box was easy, however placing a combo box on the claimants subform and then trying to browse by claimants had no effect. The combobox in the Claimants subform when running as a form on it's own worked, however when trying to use the combo box with the employers form and the Claimants form open as a subform, the subform combo were visible, but clicking on the options had no effect.

I try to choose a name from the claimaints subform combo and browse to that record (not only on the claimants subform but also have the employers form jump to the associated 1 employer record for the claimant chosen.

Through some assistance, i was able to sort the problem...

the properties for the subform combo...

RowSource = "SELECT ClaimantID, Claiment, EmployerID FROM tblClaimants INNER JOIN tblEmployers ON tblClaimant.EmployerID = tblEmployers.EmployerID"

the air code for the AfterUpdate event of the subform combo...

Private Sub SubFormCombo_AfterUpdate()
Dim rstEmployers As DAO.Recordset
Dim rstClaimants As DAO.Recordset

'TRY Navigate to the employer IF a claimant has been selected
If Me.SubFormCombo.Column(2) & "" <> "" Then 'Note the column property is ZERO based

Set rstEmployers = Forms("FrmEmployersReview").RecordsetClone
rstEmployers.FindFirst "[Client ID] = " & CLng(Me.SubFormCombo.Column(2))

If Not rstEmployers.NoMatch Then

'Navigate to the employer
Forms("FrmEmployersReview").Bookmark = rstEmployers.Bookmark

'Now that the employer is found, navigate to the appropriate claimant
Set rstClaimants = Me.RecordsetClone
rstClaimants.FindFirst "ID = " & Me.SubFormCombo


End If

End If

Set rstEmployers = Nothing
Set rstClaimants = Nothing

End Sub