Lookup record in subform based on combo im main form

[SAC]

I''d like to lookup a record in a subform based on a combo box in the main
form.

In the afterupdate event of the combo box I like to lookup and display the
record in the subform.

Not sure how to do this.

Thanks for your help.

 

[SAC]

Here's what I have now:

Private Sub Combo116_AfterUpdate()
Dim lngID As Long
lngID = Combo116
Form![frmBookingShowsAssociations].SetFocus
DoCmd.GoToControl "txtID"
Form![txtID].SetFocus

'*************
'At this point it says "Can't find the field 'txtID' referred to in your
expression.
'*************
DoCmd.FindRecord lngID
End Sub

Also will it change if I place the subform on a tabbed control?

Thanks.

 

[Marshall Barton]

In the afterupdate event of the combo box I like to lookup and display the
record in the subform.

If the combo box's bound column is a numeric type field:
Me.subformcontrol.Form.Recordset.FindFirst _
"somefield = " & Me.combobox

If it's a Text field, use:
Me.subformcontrol.Form.Recordset.FindFirst _
"somefield = """ & Me.combobox & """ "

 

[SAC]

WOW!!! Thanks so much!!

If the combo box's bound column is a numeric type field:
Me.subformcontrol.Form.Recordset.FindFirst _
"somefield = " & Me.combobox

If it's a Text field, use:
Me.subformcontrol.Form.Recordset.FindFirst _
"somefield = """ & Me.combobox & """ "

 

[SAC]

I got it working on a form/subform, but now I want to place the subform on a
tabbed control and it's not working....what do I need to change?

Thanks so much for your help!

 

[Marshall Barton]

Since a tab control has nothing to with this kind of thing,
you must have changed more than just moving the subform
control. The first thing to check is the Name of the
subform ***control***

If this tab control is inside another subform, then maybe
you added a level of subform nesting??

 

[SAC]

Thanks, Marsh. I got one of them working...trying to duplicate it for
another one.

I really appreciate your help!

Since a tab control has nothing to with this kind of thing,
you must have changed more than just moving the subform
control. The first thing to check is the Name of the
subform ***control***

If this tab control is inside another subform, then maybe
you added a level of subform nesting??
--
Marsh
MVP [MS Access]

I got it working on a form/subform, but now I want to place the subform on
a
tabbed control and it's not working....what do I need to change?


"Marshall Barton" wrote

 

[SAC]

OK, I have this working if I make a new form. I place the combo box on it
and then a subform. This works fine.

The Main Form's data source is Events with EventID being the primary key.

The Combo Box is called cbo FacilityLookup and is bound to a field in the
Events Table and Has an ID field for it's data source which is the
Facilities Table.

The Subform's Datasource if the Facilities Table.

Everything works fine until I set up the Link Child/Master Fields in the
properties of the subform. The Link is the record source for the combo box
in the main form (FacilityLookup) and the child is ID.

It stops working when I set this up. Doesn't work when I add a new record
or attempt to change an existing record.

Any ideas on how to correct this?

Thanks for your help.

Since a tab control has nothing to with this kind of thing,
you must have changed more than just moving the subform
control. The first thing to check is the Name of the
subform ***control***

If this tab control is inside another subform, then maybe
you added a level of subform nesting??
--
Marsh
MVP [MS Access]

I got it working on a form/subform, but now I want to place the subform on
a
tabbed control and it's not working....what do I need to change?


"Marshall Barton" wrote

 

[Marshall Barton]

OK, I have this working if I make a new form. I place the combo box on it
and then a subform. This works fine.

The Main Form's data source is Events with EventID being the primary key.

The Combo Box is called cbo FacilityLookup and is bound to a field in the
Events Table and Has an ID field for it's data source which is the
Facilities Table.

The Subform's Datasource if the Facilities Table.

Everything works fine until I set up the Link Child/Master Fields in the
properties of the subform. The Link is the record source for the combo box
in the main form (FacilityLookup) and the child is ID.

It stops working when I set this up. Doesn't work when I add a new record
or attempt to change an existing record.

I am having trouble understanding that description of your
forms and the problem.

First, I need to know how your tables are related. I think
you are saying that your tables look like:

Events table:
EventID AutoNumber Primary Key
ID Long foreign key to facilities table
. . .

Facilities table
ID AutoNumber Primary Key
. . .

This means that a single facility can be related to more
than one event. Do you have Referential Integrity enforced
between these two tables?

Note that the words "data source" is a generic description
that is too imprecise to have much meaning in this
situation. Forms/reports have a RecordSource, combo/list
boxes have a RowSource and most controls have a
ControlSource.

Also note that forms/reports contain Controls while
tables/queries have Fields. Controls can be bound to a
field via the ControlSource property.

Now, we can try to describe your forms and the combo box.

Main form:
Name: ? ? ?
RecordSource: Events (table)
Controls on main form:
combo box:
Name: cboFacilityLookup
ControlSource: ID
RowSource: Facilities (table)
ColumnCount: 1
BoundColumn: 1
ColumnWidths: <blank>
subformcontrol:
Name: ? ? ?
SourceObject: ? ? ?
LinkMaster cboFacilityLookup
LinkChild ID
. . .

Subform:
Name: ? ? ?
RecordSource: Facilities (table)
controls on subform:
text box:
Name: ? ? ?
ControlSource ID
. . .

Please correct any of the above that I have misinterpreted.

Then, the problem is that something doesn't work when you
try to add a new record or edit an existing record. As far
as I can see, you never said which form contains the record
you are trying to add/edit nor at what point the problem
occurs. I also need to know exactly what happens when "it
doesn't work."

 

[SAC]

Thank you very nuch for your help!

The tables are related as you described them:

Events table:
EventID AutoNumber Primary Key
ID Long foreign key to facilities table
. . .
Facilities table
ID AutoNumber Primary Key

There is no referential integrity.

Main form:
Name: frmEvents
RecordSource: tblEvents (table)
Controls on main form:
Combo box:
Name: cboFacilityLookup
ControlSource: ID
RowSource: Facilities (table)
ColumnCount: 2
BoundColumn: 1
ColumnWidths: 0";2"

subformcontrol:
Name: txtEventID
SourceObject: Control Source = ID
LinkMaster FacilityLookup - this is the name of the control source for the
cboFacilityLookup control on the main form.
LinkChild ID
. . .

Subform:
Name: frmFacilities
RecordSource: Facilities (table)
controls on subform:
text box:
Name: txtID
ControlSource ID
. . .


The problem occurs when I attempt to add/edit a record in the Main Form.
When I select a facility in the combo box on the main form I want the data
to change on the facility subform. If I change the information on the
conbobox on the main form I want the data to change on the subform.

Please let me know if that is not clear enough. Sorry for the previous
confusion.

Thanks for your help.






Events Table:

 

[Marshall Barton]

The tables are related as you described them:

Events table:
EventID AutoNumber Primary Key
ID Long foreign key to facilities table
. . .
Facilities table
ID AutoNumber Primary Key

There is no referential integrity.

Main form:
Name: frmEvents
RecordSource: tblEvents (table)
Controls on main form:
Combo box:
Name: cboFacilityLookup
ControlSource: ID
RowSource: Facilities (table)
ColumnCount: 2
BoundColumn: 1
ColumnWidths: 0";2"

subformcontrol:
Name: txtEventID
SourceObject: Control Source = ID
LinkMaster FacilityLookup - this is the name of the control source for the
cboFacilityLookup control on the main form.
LinkChild ID
. . .

Subform:
Name: frmFacilities
RecordSource: Facilities (table)
controls on subform:
text box:
Name: txtID
ControlSource ID
. . .

The problem occurs when I attempt to add/edit a record in the Main Form.
When I select a facility in the combo box on the main form I want the data
to change on the facility subform. If I change the information on the
conbobox on the main form I want the data to change on the subform.

I think you have something mixed up. Is it really this on
the main form?

Controls on main form:
Combo box:
Name: cboFacilityLookup
ControlSource: ID
RowSource: Facilities (table)
ColumnCount: 2
BoundColumn: 1
ColumnWidths: 0";2"

Text box:
Name: txtEventID
Control Source = EventID <--------------------???

subformcontrol:
Name: ??? <--------------------???
SourceObject: frmFacilities <--------------------???
LinkMaster FacilityLookup - this is the name of the control
source for the cboFacilityLookup
control on the main form.
LinkChild ID
. . .

If this interpretation is correct, then there is an
inconsistency. It says the combo box's ControlSource is ID,
but your comment about the subform control's LinkMaster
property says the combo box's control source is
FacilityLookup. Your list of fields in Events table says:
ID Long foreign key to facilities table
so I am not in agreement with the LinkMaster comment. I
think the LinkMaster property needs to be:
cboFacilityLookup
or just
ID

Also, the problem could be because the txtEventID text box
on the main form has the wrong control source so double
check all these things very carefully.

I still need to know exactly what happens when "it doesn't
work." If you get an error message, what is the message.

When we're engaged in this kind of remote debugging,
precision in communications is critical or both of us will
be running around in circles.

 

[SAC]

Marshall,

Thanks so much for your help. Just thought of something that may allow us
not to head in this direction....

When I move to the previous record on the main form, it does update the info
on the subform. Can't I put some code in the after update of the combo box
on the main form that would refresh or requery the subform?

I've tried this and it doesn't work.

So...what do you think of that?

Thanks.

 

[Marshall Barton]

That idea might just be piling an excess mechanism on top of
the problem. If the combo box is the Link Master, then the
subform will be resynched automatically when the combo box
is updated.

Did you check all the things I said to double check? And
what about a precise explanation of what does happen when it
doesn't work?
--
Marsh
MVP [MS Access]

Thanks so much for your help. Just thought of something that may allow us
not to head in this direction....

When I move to the previous record on the main form, it does update the info
on the subform. Can't I put some code in the after update of the combo box
on the main form that would refresh or requery the subform?

I've tried this and it doesn't work.

So...what do you think of that?

Thanks.

 

[SAC]

Thanks Marshall!

I double-checked the info.

Thanks for your insight. I did have the cboFacilityLookup control source
incorrect. It's exactly as you thought it should be.

Controls on main form:

Combo box:
Name: cboFacilityLookup
ControlSource: FacilitiesLookup
RowSource: Facilities (table)
ColumnCount: 2
BoundColumn: 1 - This is the ID field in the Facilities Table
ColumnWidths: 0";2"

Text box:
Name: txtEventID
Control Source = EventID -- This is the primary field in the Events Table

subformcontrol:
Name: frmFacilities
SourceObject: frmFacilities - This is what it shows in the properties sheet
for this form.
LinkMaster FacilityLookup
--source for the cboFacilityLookup <------Yes it is.
--control on the main form.
LinkChild ID


When "it doesn't work" the subform is not populated when adding a new event
record on the main form and selecting a facility in the combo box on the
mainform. If I move to the previous record and then back the subform is
populated correctly. Also, if I change the selection in the combobox on the
main form the subform does not change until I move to previous record and
then back.

Thanks again!!

That idea might just be piling an excess mechanism on top of
the problem. If the combo box is the Link Master, then the
subform will be resynched automatically when the combo box
is updated.

Did you check all the things I said to double check? And
what about a precise explanation of what does happen when it
doesn't work?

 

[Marshall Barton]

I double-checked the info.

Thanks for your insight. I did have the cboFacilityLookup control source
incorrect. It's exactly as you thought it should be.

Controls on main form:

Combo box:
Name: cboFacilityLookup
ControlSource: FacilitiesLookup
RowSource: Facilities (table)
ColumnCount: 2
BoundColumn: 1 - This is the ID field in the Facilities Table
ColumnWidths: 0";2"

Text box:
Name: txtEventID
Control Source = EventID -- This is the primary field in the Events Table

subformcontrol:
Name: frmFacilities
SourceObject: frmFacilities - This is what it shows in the properties sheet
for this form.
LinkMaster FacilityLookup
--source for the cboFacilityLookup <------Yes it is.
--control on the main form.
LinkChild ID


When "it doesn't work" the subform is not populated when adding a new event
record on the main form and selecting a facility in the combo box on the
mainform. If I move to the previous record and then back the subform is
populated correctly. Also, if I change the selection in the combobox on the
main form the subform does not change until I move to previous record and
then back.

Thanks again!!

"Marshall Barton" wrote

This is starting to sound like you have another mechanism
stomping on what we're trying to do here. Check the sub
form's record source query to see if it has a criteria that
filters the subform data by the main form's combobox. If
that's what happening. Try removing that criteria and
removing the subform,Form.Requery in the main form's Current
event.

Using the criteria in the query is a valid approach and it
might even be necessary in some situations. But, it takes
more work and code than using the link master/child
properties in what I understand about your situation.

 

[SAC]

I checked that and there's no criteria in the subform's recordsource query.

I did check and this line is in the cboFacilitiesLookup control's (on the
main form) after update event and it does not find a record:

Me.frmBookingShowsFacilities.Form.Recordset.FindFirst "ID = " &
Me.cboFacilityLookup

Is this the correct syntax or os there something else to check?

Thanks.

 

[Marshall Barton]

That's another thing that conflicts with the Link
Master/Child approach. I believe that line is ineffective,
but you should remove it (or comment it out) just in case it
is getting in the way.

If you drop the link master/child, then that find first
approach just finds a record in the subform. Records with
other IDs would still be there and users could navigate to
them and mess over records that have nothing to do with the
ID selected on the main form. OTOH, the link master/child
approach temporarily filters the subform to only the
matching records so the other records are safe.

If you have no criteria in the subform's record source, then
the only explanation I can think of is that the link master
property is still not set to cboFacilityLookup.

 

[SAC]

Thanks!!

OK, I'm making progress. Just to test, I removed the Link Master/Child and
the lookup in the after update event in the combo box does work!

Now when I remove the event in the combo box and reset the Link
Master/Child, it does not work....meaning...when I edit a record on the main
form by changing the combo box, it does not change the record in the
subform.

The Link Master/Child is set to:

Child - ID
Master - FacilitiesLookup --- ***** NOT cboFacilitiesLookup

It doesn't give me the option of setting it to the name of the control, but
rather to it's control source.

I'd like to make it work with the Link Master/Child but I can settle for the
lookup code in the after update event. I can remove the record navigation
buttons on the subform.

What do you thnk?

Again, I really appreciate your great help in troubleshooting the problem.

That's another thing that conflicts with the Link
Master/Child approach. I believe that line is ineffective,
but you should remove it (or comment it out) just in case it
is getting in the way.

If you drop the link master/child, then that find first
approach just finds a record in the subform. Records with
other IDs would still be there and users could navigate to
them and mess over records that have nothing to do with the
ID selected on the main form. OTOH, the link master/child
approach temporarily filters the subform to only the
matching records so the other records are safe.

If you have no criteria in the subform's record source, then
the only explanation I can think of is that the link master
property is still not set to cboFacilityLookup.
--
Marsh
MVP [MS Access]

I checked that and there's no criteria in the subform's recordsource
query.

I did check and this line is in the cboFacilitiesLookup control's (on the
main form) after update event and it does not find a record:

Me.frmBookingShowsFacilities.Form.Recordset.FindFirst "ID = " &
Me.cboFacilityLookup

Is this the correct syntax or os there something else to check?


"Marshall Barton" wrote

 

[Marshall Barton]

Removinfg the nav buttons will not prevent uses from
changing records. Scroll bars and keyboard actions are all
it takes. However, since it works, you can use the
FindFirst approach if you want.

As for the Link Master/Child approach, where is this
FacilitiesLookup field in the main form's table??? I
thought you said that the main table has an ID field to link
to the Facilities table.

 

[SAC]

Yes. The FacilitiesLookup field is in the main form's table.

Sorry for the confusion.

So I would like a Link Master/Child

Master - FacilitiesLookup
Child - ID

Is this clearer?

I know it's an odd structure. Should I not do this?

Thanks.

Removinfg the nav buttons will not prevent uses from
changing records. Scroll bars and keyboard actions are all
it takes. However, since it works, you can use the
FindFirst approach if you want.

As for the Link Master/Child approach, where is this
FacilitiesLookup field in the main form's table??? I
thought you said that the main table has an ID field to link
to the Facilities table.
--
Marsh
MVP [MS Access]

OK, I'm making progress. Just to test, I removed the Link Master/Child
and
the lookup in the after update event in the combo box does work!

Now when I remove the event in the combo box and reset the Link
Master/Child, it does not work....meaning...when I edit a record on the
main
form by changing the combo box, it does not change the record in the
subform.

The Link Master/Child is set to:

Child - ID
Master - FacilitiesLookup --- ***** NOT cboFacilitiesLookup

It doesn't give me the option of setting it to the name of the control,
but
rather to it's control source.

I'd like to make it work with the Link Master/Child but I can settle for
the
lookup code in the after update event. I can remove the record navigation
buttons on the subform.

.