Make a form show a record

[SAC]

I have a main form with a tabbed form on it. The tabbed form (not a
datasheet form) has a single form on one of the tabs.

The main form has a drop down box which is bound to the primark key of the
record that I want to display on the single form.

The Single form has child link and master link set up correctly.

I want to lookup a record in the drop down bax and have the single form find
that record and display it using the after update event of the drop down
box.

I haven't had any success with this.

Any ideas?

Thanks.

 

[JK]

Sac,

Private Sub YrCombo_AfterUpdate()

DoCmd.GotoControl "YrPrimaryKey"
DoCmd.FindRecord Me.YrCombo
'(Note *no* equal sign)
DoCmd.GotoControl "Whatever"

End Sub

Regards/JK

 

[SAC]

Excellent! Thanks!

Sac,

Private Sub YrCombo_AfterUpdate()

DoCmd.GotoControl "YrPrimaryKey"
DoCmd.FindRecord Me.YrCombo
'(Note *no* equal sign)
DoCmd.GotoControl "Whatever"

End Sub

Regards/JK

 

[JK]

My pleasure Sac

Excellent! Thanks!

 

[SAC]

It didn't work. The FindRecord is only for the active form. The control I
want to use is on a single form which is on a tabbed form.

Any other ideas?

 

[JK]

Sac,

True, my code meant to go to a record on your main form.

I'm not sure what do you mean by "tabbed form". Is it a subform in a tab
control or just a standard tab with fields in it? In any event your combo
must have a column, preferably bound but not mandatory, containing the data
you want to look for.

Please elaborate

Regards
Jacob

 

[SAC]

Yes, it is a subform on a tab control.

What would I do to find the record on the subform after I update the combo
box on the main form?

Thanks.

 

[SAC]

Also, the Master/Child link fields are set properly on the subform.

 

[JK]

Sac,

I assume a "normal", if there is such thing, whereby your subform is linked
to the main by say, "MainForm_ID" and the subForm has it's Own SubForm_ID.
I also assume that the bound column on combo is the "SubForm_ID". If this is
the case you need to do the following

1. Find the record in the subform by its "SubForm_ID"
2. Find which record on your main form will display the subform with the
relevant "SubForm_ID, Go to that record
3. Go to the subForm and find the SubForm_ID

Private Sub YrCombo_AfterUpdate()

'Define variables
Dim IdMain as Long, idSub as long

idSub=Me.Combo
'Find The Record in the main Form
idMain=Nz(DLookup("[MainForm_ID]","tblBaseForSubForm",[SubForm_ID]=" _
& Me.Combo),0)
'(Not: Your can replace a table with a query, if your sub form is based
on a query)

'Cannot Locate MainForm_ID in tblBaseForSubForm
If idMain=0 then
MsgBox "Cannot find MainForm_ID"
Exit Sub
End IF

'Move to the relevant records in the *Main Form*
DoCmd.GoToControl "MainForm_ID"
DoCmd.FindRecord idMain

'Open The Tab
Me.TabControlName.SetFocus

'Go to the subFrom
DoCmd.GoToControl "SubFormControlName"

'Move The focus to the SubForm
Form![SubFormControlName].SetFocus
'(Note: Form is in singular)

'Find The Record on the subForm
DoCmd.FindRecord idSub
DoCmd.GoToControl "AnotherFiled On The subForm"

End Sub
Note: All ID's must be *visible* on both the subform and the main form.

If any of my assumption are incorrect, please advise the correct situation

Regards
Jacob