Subform displaying incorrect record

[SAC]

Main form - Events
Combo Box on Main Form - Facility Lookup

Subform - Facility

This is a little odd becasue several events (Main Form) could use the same
facility (Subform)

So there a many (Events using the same facility) to one relationship (the
facility)

The idea is that the facility subform gives details about the facility in
which the event is taking place.

So, when I open the form I have it goto a new record. At this point the
subform is display a record but there's nothing yet selected for it on the
main form.

How can I fix this?

Also, when I click on the * to make a new record, then the subform is
displaying the previous record's data.

I tried clearing the controls, but that just erases the fields in the
current record.

The subform is on a tab control so I tried using the on change event for the
tabcontrol and then setting the tab to 0 so the user doesn't see the tab
with the wrong record in the fsacility for, but that makes the change event
fire again so I end up in a loop

Hope I've made this clear enough.

Any help would be appreciated.

Thanks.

 

[Steve McLeod]

Usually a subform is the many table related to the one table in a one to many
relationship. The one table is bound to the main form and the many table to
the subform and everything gets done on automatic.

I suspect that you do not have this ordinary kind of relationship. It is
not necessary that a subform recordset be related in the way just described.
You can use a parameterized query in your main form combo box click event to
select just the records you want and then set the subform recordset to that.

In main form combobox click event:
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset
Set db = CurrentDb
Set qdf = db.QueryDefs("qryVar")
qdf.Parameters("ItemID") = lngRecordID
Set rst = qdf.OpenRecordset(dbOpenDynaset)
set Me.subfrmname.Form.RecordSet = rst

This means that the subform will NOT automatically change when focus moves
from one record to the next in the main form. You may have to put something
similar in the main form onCurrent event to do that.

 

[SAC]

Thanks. I'll tryworking with that!

Appreciate the suggestion.