Combo Box Help

[Boz]

Table Fields:

Stager
Zone

Linked as a subform.

Using combo box for Stager with the following code:

Private Sub Stager_AfterUpdate()

Me.Zone = Me.Stager.Column(1)

End Sub

This works for the first record on the subform. The problem arises when I
go to the next record on the subform (continuous form), It automatically
changes the information in the first record to whatever I put in the second
record.

Layout of form:

LogID (Auto Number)
Auditor
Date
Shift
Entered by

Layout of subform:
LogId (Number format)
Round (First or Second)
Stager (combo box listed above)
Zone (combo box from above)
System Count
Actual Count

As each auditor may audit up to 100 lanes, I obviously do no want to
constantly enter their name, date and shift, thus the subform linked through
the ID number.

How do I resolve the issue of the records changing?

THanks,

 

[Beetle]

Based on the fact that you are using the column method, I'm assuming that the
Zone control on your form is unbound. Unfortunately, that's how unbound
controls behave on continuous forms.

However, I would ask why you are doing it this way. If Zone is a field in
the forms recordsource, then why not just bind the control to the zone field
in the table instead of using the column method?

Perhaps if you give us a better idea of what you are trying to accomplish,
someone can offer some more advice.

 

[Boz]

Ok here goes....

Out auditors audit product in staging lanes. I want to be able to track
their productivity (i.e., how many lanes have they audited, how much product
was in each lane and how many times did they audit the lane during their
shift). I also want to be able to track how many times each stager was
audited. The stagers are assigned to zones (stager S001-S036 in Zone 1,
etc.) As part of the reporting I also need to be able to determine if each
zone was audited and how many stagers in the zone were audited.

My tables are set up:

Main Table:
Log ID (Autonumber)
Auditor (Lookup to Names table)
Shift (Lookup to Shift table)
Date Audited

Item Entry Table:
Log ID (Number)
Round (Lookup to Round table --First, Second, Third)
Stager (Lookup to Stager/Zone table)
System Count
Actual Count

Stager/Zone Table
Stager
Zone

The form is set up with the Main Table as the parent and the Item Entry
table as the subform (continuous).

I have queries set up that will determine the number of stagers audited by
each auditor and how many rounds have been completed. Now I need to set up
the query to determine which stagers in which zones were completed. I
thought that by using combo boxes for the stager and zone (with zone
autofilling based on stager entered) that I would be able to run these
queries, but as stated above, it won't work with continuous forms. Any help
you can provide would be greatly appreciated.

 

[Beetle]

If I understand your post correctly, you have multiple Stagers in each Zone,
if that's the case, then they should really be in separate tables. Maybe
something like;

Main Table:
Log ID (Autonumber - Primary Key)
Auditor (Lookup to Names table)
Shift (Lookup to Shift table)
Date Audited

Item Entry Table:
EntryID (AutoNumber - PK)
LogID (Number - Foreign Key to Main Table)
RoundID (Lookup to Round table --First, Second, Third)
ZoneID (Lookup to Zone table)
System Count
Actual Count

Zone Table
ZoneID (AutoNumber - PK)
ZoneName

Stager Table
StagerID (AutoNumber - PK)
ZoneID (Number - FK to Zone table)
StagerName


Then I would also suggest having a form with two subforms. Subform1 would be
based on the Item Entry table and Subform2 would be based on the Stager
table, linked by ZoneID.

Post back if you have other questions, or if Iv'e misunderstood your
structure.

BTW - some of your table fields you describe as *lookups* to other tables.
Hopefully you are not using actual Lookup/combo boxes in the tables as this
can cause problems in your db. Lookups/combo boxes in forms are good,
lookups/combo boxes in tables are bad.