I'm in need of some assistance. I have a form displaying information on a particular record. On that form I have a combo box with details of members of staff this particular record refers to, the combo box is just selecting the name of the member of staff, this data is stored in an other table. I'm looking to display information on these members of staff on the same form, for instance job title ect... I attempted adding a sub form and trying to get it to open to the correct record, this failed the code I was using was a variation on some code I have used before.

DoCmd.OpenForm "formName", , , "NAME = " & Me.NAME

I couldn't convince it to accept

SubForm.SourceObject "formName", , , "NAME = " & Me.NAME

Any help would be greatly appreciated.
I don't need the data to be displayed in a sub form necessarily.

Simon:)

:)

If you want to browse to a record based on a selection from a combo box, then you just need to use the combo box wizard, and tell it to find a record on your form.

Tell it which field to use as the look up, in this case staff member names. Then when you use the combo box to enter a staff member's name, the form will move to that staff member's record.

Be sure to go to the query behind the combo box and sort alphabetically.

Also, on the form's On Current event, synchronize the combo box with the table's primary key, like this:

Sub On Current
   MyComboBox = tblStaffPK
End Sub

Is this what you are trying to do?

mmcdonal

I have been trying to get this right for a few days now and I have to say (unhappily) without success.

I have two tables, linked by a field (fldA). The subform has multiple instances or records which correspond to fldA while the mainform only has 1 record related to fldA.

When I do as you suggest, the subform reacts as you have indicated... Great. But,
the mainform data stays at the current record and even the navigation button remains pointing to the origional record.

How do I get the mainform data to cycle to the record that I have chosen in the combobox.

I'm sorry if I appear dense, I assure you i'm not, but I have been struggling with ths for a while.

Paul

Paul

Thanks for your help. Problem solved...

My combobox was linking to the subform because for some reason when I placed the combobox on the form, my subform was already present. Once i removed the subform and rebuilt the mainform placing the combobox on this form first and then later building the subform, the problem sorted itsef.

Great forum. Keep it up.

Paul

Paul