Generate custom form based on query results

[queebs]

I posted this in the VBA forum but maybe someone here could help:


I've read quite a few posts about similar topics and the general consensus seems to be to just "give up." I'm trying to track daily production at a manufacturing company running many different processes at different locations each running multiple parallel "lines." The tricky part is that the number of lines running and the shift schedules e.g. 2shifts 10hrs/day 4days/week or 3shifts 8hrs/day 5days/week change frequently for each process. Right now I have a form for process data that specifies the schedule and number of lines running each day. Then I'm running an append query to a "production" table that generates blank production records associated with each item made in each process for the correct shift/line combinations. Up to now I've been manually changing the date on the append query each time I run it. Then I have a seperate query for each process that pulls out the production records for each day. My problem is that the preferred interface for production data entry is a spreadsheet with the following layout:

--------DAYS LINE 1 DAYS LINE 2 SWING LINE 1 SWING LINE 2
ITEM 1 100
ITEM 2 2250
ITEM 3

which changes each time the shift/line schedule changes for each process. The only way to achieve this layout I know of is a crosstab query which isn't updateable. Ideally, each day the manager will specify the shift schedules and forms will be automatically generated with the correct structure and sent to the process supervisors. If anybody could give me advice on how to make this work I'd greatly appreciate it. I'm open to different form layouts and even redesigning the database completely.

 

[CJ_London]

if you fix the column headingss in a crosstab you can use it in a form - or run a make table query on the crosstab and base your form on the resultant table.

You may need to put some vba code in the form load or current event to change control sources and label captions. But better if you can do this within the query

 

[johnbirt]

if you fix the column headingss in a crosstab you can use it in a form - or run a make table query on the crosstab and base your form on the resultant table.

You may need to put some vba code in the form load or current event to change control sources and label captions. But better if you can do this within the query

I have done something similar. If the crosstab is going to have varying number of columns then display in a datasheet of a dynamic table becomes problematic. I got round this by setting the RecordSource of the form to "" in design. Set it to the make table as you suggest in the form load where the table will be created anyway. I needed an update routine for the form to provide different crosstable views of another table but seems to work OK by re-using the form load code.

John B