Re: Lookup record in subform based on combo im main form

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

SAC wrote:

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."

--
Marsh
MVP [MS Access]
.

Relevant Pages

  • Re: Lookup record in subform based on combo im main form
    ... EventID AutoNumber Primary Key ... ID Long foreign key to facilities table ... Controls on main form: ... to change on the facility subform. ...
    (microsoft.public.access.formscoding)
  • Re: delete unneccesary tabs
    ... AllInfo record source table in some way, then a subform is ... subform to be on a tab page, ... The unbound controls are probably being used to filter the ... connect the event to the event procedure, ...
    (microsoft.public.access.formscoding)
  • RE: Y/N fields/ Option groups. How to design a table?
    ... Now that you have the form as a subform, there's no need to execute the ... about 30 different controls. ... By real estate, I meant room on your form that you could place the invisible ... Do not use quotes when entering a value into the Tag property, ...
    (microsoft.public.access.tablesdbdesign)
  • RE: Y/N fields/ Option groups. How to design a table?
    ... Also when in the subform when I use the scroll down wheel on the mouse the ... what "relevant information" do you want populated? ... about 30 different controls. ... By real estate, I meant room on your form that you could place the invisible ...
    (microsoft.public.access.tablesdbdesign)
  • RE: Y/N fields/ Option groups. How to design a table?
    ... that the ProductID does not show in the subform. ... "WoodyAccess" wrote: ... about 30 different controls. ... Do not use quotes when entering a value into the Tag property, ...
    (microsoft.public.access.tablesdbdesign)