Form With Multiple Enteries

[Scott12345]

I have created a database that logs machine downtime. Basically everyday the user will enter downtime for every machine. I would like to create a form that has all the machines listed along with the fields for the user to enter. I have a form set up for one entry at a time, but i would much rather to be able to enter the information for every machine on a specified date.

I have a table that contains all the data for the database (Data), with fields
-Date (date)
-Machine ID (text)
-Machine Inuse? (yes/no)
-Downtime Code 1 (number)
-Downtime Code 2 (number)
-Downtime Code 3 (number)
-Downtime Code 4 (number)

I also have a table that contains all the machine information (Machine/shifts operated), with fields
- Machine (text)

I would like the form to ask the user to enter a date and then list all the machines from (machine/shifts operated), and then have dummy values for fields Machine Inuse?, Downtime Code 1, Downtime Code 2, Downtime Code 3, Downtime Code 4 for which the user can change. After all the values have been changed i would like it to be saved into the Data table.

Is this possible?
Thanks for your help.

[Stewart Ross]

I have created a database that logs machine downtime. Basically everyday the user will enter downtime for every machine. I would like to create a form that has all the machines listed along with the fields for the user to enter. I have a form set up for one entry at a time, but i would much rather to be able to enter the information for every machine on a specified date.

Hi. Try the following SQL append statement:
[code=sql]INSERT INTO Data ( [Machine ID], [Date], [Machine In Use?], [Downtime Code 1], [Downtime Code 2], [Downtime Code 3], [Downtime Code 4] )
SELECT Machine.Machine , [What Date?] AS TheDate, False AS InUse, 0 AS DT1, 0 AS DT2, 0 AS DT3, 0 AS DT4
FROM Machine
ORDER BY Machine.Machine ;[/code]
and see how you get on.

Sample data for dummy machines one, two, three and four:

Machine ID Date Machine In Use? Downtime Code 1 Downtime Code 2 Downtime Code 3 Downtime Code 4
Four  12/03/2008 No 0 0 0 0
One   12/03/2008 No 0 0 0 0
Three 12/03/2008 No 0 0 0 0
Two   12/03/2008 No 0 0 0 0

-Stewart

[Scott12345]

Thanks, that works great! But now what do i use to allow users to enter the information? Forms will not let me access an append query. Also, if different people will be entering information at different times during the day, every time the append query is run will it delete all the previous information?

[Stewart Ross]

Thanks, that works great! But now what do i use to allow users to enter the information? Forms will not let me access an append query. Also, if different people will be entering information at different times during the day, every time the append query is run will it delete all the previous information?

Hi Scott. For data entry you define a form based upon your Data table (or on a query which is based on that table) and your users will be able to modify/add details accordingly.

The append query does not delete any existing data; it simply appends values into the existing table, as long as to do so would not cause key violations.

-Stewart

[Scott12345]

Okay awesome!

If the append query is executed twice, it will create duplicate enteries for the same date. There will be approximately 4 users updating this DB at different times, so i would get 4 times the amont of enteries for a single day. Is there a way around this? Also, is there anyway to remove that warning message that pops up when the append query is executed?

Thanks so much for your help!

[Stewart Ross]

Okay awesome!

If the append query is executed twice, it will create duplicate enteries for the same date. There will be approximately 4 users updating this DB at different times, so i would get 4 times the amont of enteries for a single day. Is there a way around this? Also, is there anyway to remove that warning message that pops up when the append query is executed?

Thanks so much for your help!

Hi Scott. It is possible to remove the warning message, depending on how you are running the append, but given you are running it infrequently you might be better to keep it on as a reminder. If you were running the update as a macro you can precede the query with the macro action SetWarnings with its Warnings On value as No, and add the same action in the line after the update query except with Warnings On as Yes.

If you are running the append from a command button you can do something similar in the command button code - precede it with DoCmd.SetWarnin gs False and follow it with DoCmd.SetWarnin gs True.

If you are just running the append query from the Queries window you'll just have to accept that the warnings will occur.

If your table design was fully appropriate in the relational sense Access itself would prevent you from storing duplicate row values as these would violate key value constraints. Since it is unlikely that the table key relations are set to involve the machine ID and the date you are just going to have to make sure the query is not run more than once for each date.

-Stewart

[Scott12345]

Okay thanks. I used a delete query to remove the dummy rows that haven't been updated, this way the append query can be executed several times followed by the delete query after to remove the blank rows.