By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,148 Members | 748 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,148 IT Pros & Developers. It's quick & easy.

Form With Multiple Enteries

P: 9
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.
Mar 11 '08 #1
Share this Question
Share on Google+
6 Replies


Expert Mod 2.5K+
P: 2,545
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:
Expand|Select|Wrap|Line Numbers
  1. INSERT INTO Data ( [Machine ID], [Date], [Machine In Use?], [Downtime Code 1], [Downtime Code 2], [Downtime Code 3], [Downtime Code 4] )
  2. SELECT Machine.Machine, [What Date?] AS TheDate, False AS InUse, 0 AS DT1, 0 AS DT2, 0 AS DT3, 0 AS DT4
  3. FROM Machine
  4. ORDER BY Machine.Machine;
and see how you get on.

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

Expand|Select|Wrap|Line Numbers
  1. Machine ID Date Machine In Use? Downtime Code 1 Downtime Code 2 Downtime Code 3 Downtime Code 4
  2. Four  12/03/2008 No 0 0 0 0
  3. One   12/03/2008 No 0 0 0 0
  4. Three 12/03/2008 No 0 0 0 0
  5. Two   12/03/2008 No 0 0 0 0
-Stewart
Mar 11 '08 #2

P: 9
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?
Mar 12 '08 #3

Expert Mod 2.5K+
P: 2,545
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
Mar 12 '08 #4

P: 9
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!
Mar 13 '08 #5

Expert Mod 2.5K+
P: 2,545
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.SetWarnings False and follow it with DoCmd.SetWarnings 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
Mar 13 '08 #6

P: 9
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.
Mar 13 '08 #7

Post your reply

Sign in to post your reply or Sign up for a free account.