Simple Subform Question

[Emohawk]

This is probably an easy one but I can't seem to work it out.

I have a combo box linked to a table containing [Customer Name] and a [Serial Number] an autonumber key.

On the same form are two subforms, GBS Clients and ACBS Clients, linked to the main form via the [Serial Number].

My problem is this, when I open the GBS Clients and ACBS Clients subforms they jump straight to the first [Serial Number] in their respective tables while the main form opens up the first [Customer Name] record.

I need to syncronise this so that the subforms show the corresponding records for the opening record on the Form.

FYI - A record does not have to exist in either GBS Clients or ACBS Clients for there to be a Customer Name

Cheers

 

[DBL]

I think you'll need to change the parent and child links from the main form and the subform. The subforms should show you the data relevant to that client so the client id field should be the link between the three tables and forms.

Do you have an impressive collection of telegraph pole photographs?

 

[Emohawk]

Thanks DBL,
Currently the Parent and Child Links are set to [Serial Number] so I dont think that is the problem.

I've even tried code in the Load event in both the main form and the subform that refreshes and sets the record to the first one in the Customer Name table...

Argh!!! I'm tearing my hair out...

Luckily it's only a holographic representation of hair so I can just get Holly to refresh my data from the disk...

 

[DBL]

It really shouldn't be an issue if the relationships are right. If it's not too large and you can zip it, I'll take a look if you like. Email it to me at dawn.lawson@1stclass.uk.com.

And if I get it working, you can smoke me a kipper

 

[Emohawk]

Thanks for your help dawn but I've worked it out for myself...
I just needed this bit of code in the On_Load property

Private Sub Form_Load()
    CustomerName = CustomerName.ItemData(0)
    Me.RecordsetClone.FindFirst "[Customer Serial Number] = " & Me![CustomerName]
    Me.Bookmark = Me.RecordsetClone.Bookmark
End Sub

Any comments or suggestions on my DB would still be appriciated.

Thanks again, I'll still smoke you a kipper if you want...

 

[DBL]

Glad you got it fixed. I've had a quick look but not sussed why it's happening yet. I want to solve this so I'll have a fiddle later!

 

[RichMorrison]

Emo,

If you have it fixed then never mind. But you might consider unlinking the subforms and requerying them from the main form. This requires a query for each subform that will contain a criteria expression.

For example, when you select something from the combo box on the main form;
store the selected value in a global variable or property,
requery each subform control (the form's query will use the global variable or property).

HTH,
RichM