Subform Issue - retrieving wrong record

[buscher75]

I added a subform (schedule_subform) to my main form (formRecords). Each
form is connected to their matching tables – “Records†and “Scheduleâ€. I set
parent/child fields to match the Month field in each form and also the
AuditorNumber field in each form. I’m using the subform as a reference only
to show the auditor’s assigned departments for that month.

My issue is the subform does not pull the record for the month and auditor
number listed in the main field. It pulls a random month and the correct
auditor’s number. It does however try to start a new record with the correct
Month and auditor#. What am I missing here? I do not want the person
entering the information into the Records form to be able to edit or add
records to the Schedule table. Thanks for your help in advance.

 

[Ron2006]

The second part of your question is easy to answer.

On the Data tab of the properties for the subform, set the edit, add,
delete properties to NO.

It sounds like it is confused as to what is really the second matching
field.

Before you take off the add ability, have you tried this:
If you add a record and then get off of the main record and come back,
does it find the newly added record?

Ron

 

[Ray C]

You must set the parent/child fields to the primary key/foreign key of the
two matching tables (not the month).

Ray

 

[John W. Vinson]

I added a subform (schedule_subform) to my main form (formRecords). Each
form is connected to their matching tables – “Records” and “Schedule”. I set
parent/child fields to match the Month field in each form and also the
AuditorNumber field in each form. I’m using the subform as a reference only
to show the auditor’s assigned departments for that month.

My issue is the subform does not pull the record for the month and auditor
number listed in the main field. It pulls a random month and the correct
auditor’s number. It does however try to start a new record with the correct
Month and auditor#. What am I missing here? I do not want the person
entering the information into the Records form to be able to edit or add
records to the Schedule table. Thanks for your help in advance.

Please post the actual contents of the Master Link Field and Child Link Field
properties of the subform, and the SQL view of the subform's Recordsource. It
would also help to show some sample data.

Note that Month is a reserved word and Access may be getting it confused with
the builtin Month() function.

 

[buscher75]

Link Child Fields; MonthSchd;AuditorID
Link Master Fields: AuditMonth;AuditorNumb
Record Source: SELECT [schedule2].[AuditorID], [schedule2].[MonthSchd],
[schedule2].[first], [schedule2].[second], [schedule2].[third] FROM schedule2;

The "schedule2" table is set up with: Month field, an AuditorNumber field
and three other fields, First, Second, Third, that identifies what
departments that auditor is responsible for. Each department is assigned a
letter and from month to month, the auditors rotate to different departments.
The month fields and the auditor number fields are tied together by a single
relationship between the Records table and the schedule2 table.

My goal is to have the auditor select the month and their auditor number and
the three departments they are going to audit would show in the
schedule2_subform. Example: in January, Auditor 1 is to audit departments:
A, I, V

I also have other subforms that put the actual department name, supervisor
and area representative on the form for reference. These all work.

As for putting the primary key in the parent / child fields, each new record
in the Records table is numbered automatically and is the primary key. I set
up a table index so no two record can have the same month, auditor number and
department. I hope this helps. Thank You!

 

[John W. Vinson]

Link Child Fields; MonthSchd;AuditorID
Link Master Fields: AuditMonth;AuditorNumb
Record Source: SELECT [schedule2].[AuditorID], [schedule2].[MonthSchd],
[schedule2].[first], [schedule2].[second], [schedule2].[third] FROM schedule2;

The "schedule2" table is set up with: Month field, an AuditorNumber field
and three other fields, First, Second, Third, that identifies what
departments that auditor is responsible for. Each department is assigned a
letter and from month to month, the auditors rotate to different departments.
The month fields and the auditor number fields are tied together by a single
relationship between the Records table and the schedule2 table.

My goal is to have the auditor select the month and their auditor number and
the three departments they are going to audit would show in the
schedule2_subform. Example: in January, Auditor 1 is to audit departments:
A, I, V

I also have other subforms that put the actual department name, supervisor
and area representative on the form for reference. These all work.

As for putting the primary key in the parent / child fields, each new record
in the Records table is numbered automatically and is the primary key. I set
up a table index so no two record can have the same month, auditor number and
department. I hope this helps. Thank You!

What's the datatype of MonthSchd/AuditMonth? If it's an integer month number,
does 11 mean November 2008, or November 1997, or November 2011 - or all of the
above? What do you mean by "a single relationship"? If you have an autonumber
primary key in the Records (parent) table, why do you NOT want to do the
standard thing and have a Long Integer foreign key in the schedule2 table?