One form insert multiple records

[Jrzy3]

I am a newbie at this!



My question is how can I use a form to insert multiple records (up to 20) from one form? There are six (which includes the AutoID) columns within the table, but I want to enter data in five columns...of these five columns I want to only enter four of the five once and the remaining column will be different values. For example:

Table: Hours
Columns:
AutoID (Self Explanatory)
Item Number
Vehicle ID
Date
Hours
Shift (Morning = 1 / Afternoon = 2)

I have the AutoID because there is an instance where the other info could be the same, so that is what ensures there is a unique record for each.

If possible the form will look like the example below...

Vehicle ID: Date: Hours: Shift:

Item Number:
Item Number:
Item Number:
Item Number:
Item Number:
Item Number:
Item Number:
Item Number:
Item Number:
Item Number:
Item Number:
Item Number:
Item Number:
Item Number:
Item Number:
Item Number:
Item Number:
Item Number:
Item Number:
Item Number:

Or do I have to list Vehicle ID, Date, Hours and Shift each time as well?

Any help will be greatly appreciated!

[June7]

Use VBA code to set the DefaultValue property of each of the 4 data controls (textboxes or comboboxes) with the values entered for the first record, then those values will carry forward to each new record until different values are entered. Put code in the BeforeUpdate event of each data controls, like:

Me.controlname.DefaultValue = Me.controlname

[Jrzy3]

This is where I am going to need additional help...with the code and form.

I currently have a bound form...the form was created from the table. Do I need to start over with an unbound form and set it up as the example in the OP? Is there more to the code than what is below?

My appologies...very limited resources at my current location...as well as trying to teach myself!

Use VBA code to set the DefaultValue property of each of the 4 data controls (textboxes or comboboxes) with the values entered for the first record, then those values will carry forward to each new record until different values are entered. Put code in the BeforeUpdate event of each data controls, like:

Me.controlname.DefaultValue = Me.controlname

[Jrzy3]

This is where I am going to need additional help...with the code and form.

I currently have a bound form...the form was created from the table. Do I need to start over with an unbound form and set it up as the example in the OP? Is there more to the code than what is below?

My appologies...very limited resources at my current location...as well as trying to teach myself!

I was able to set the form up to meet the example above; however, when I enter a value in one of the Item Number fields and move to the second field...all fields populate with the same value as the first entry. I also think I am missing code with the onclick event because only one record populated in the Hours table.

[June7]

Form should be bound and controls should be bound. I suggested code in each control BeforeUpdate event. I did not suggested any code that will create 20 records. You must still do data entry into the 5th field and that will create records.

[Jrzy3]

Form should be bound and controls should be bound. I suggested code in each control BeforeUpdate event. I did not suggested any code that will create 20 records. You must still do data entry into the 5th field and that will create records.

Thank you! Hopefully this will help clear any confusion I may have caused with my explanation. I want to be able to insert up to 20 records with only having to enter the Vehicle ID, Date, Hours and Shift once. Each Item Number will be different and up to 20 can be added at the same time on a given day upon the submit button. Each Item Number is bound to the table/Item Number column and the BeforeUpdate event code was added as suggested.

My issue is that when I populate the first Item Number text box and click the second box to enter data...all Item Number boxes populate with the data populated in the first box.

[June7]

I expect that is because you have 20 textboxes bound to the same field. That won't accomplish what you want. What you want will require code to save 20 records.

How is that Item Number generated? Is that an autonumber type field?

Code could be:

For i = 1 to 20
CurrentDb.Execute "INSERT INTO tablename(VehicleID, DateWork, Hours, Shift) VALUES(" & Me.VehicleID & ", #" & Me.DateWork & "#, " & Me.Hours & ", " & Me.Shift
Next


Date is a reserved word. Should not use reserved words as names. Also suggest no spaces in names.

[Jrzy3]

I expect that is because you have 20 textboxes bound to the same field. That won't accomplish what you want. What you want will require code to save 20 records.

How is that Item Number generated? Is that an autonumber type field?

Code could be:

For i = 1 to 20
CurrentDb.Execute "INSERT INTO tablename(VehicleID, DateWork, Hours, Shift) VALUES(" & Me.VehicleID & ", #" & Me.DateWork & "#, " & Me.Hours & ", " & Me.Shift
Next


Date is a reserved word. Should not use reserved words as names. Also suggest no spaces in names.

The Item Number is a manual entry by the user...set up as Data Type "Text" because it is an alphanumeric value.

Item Number 1 = A1
Item Number 2 = Z3
and so on...currently there are over 200 Item Numbers in the Item Number's table. I do not have spaces in any of the names...just changed the names to post pic on forum.

Do I need to unbound those fields from the table and have the code entered after the "Next"?

I am sorry if I am asking off the wall questions...there is a lot I do not understand and trying to figure out. My resources are very limited at the moment.

[June7]

If user must enter the Item Number (by typing or selecting from combobox list) the last code I described will not work. Set up form with 5 controls, not 24 controls. User enters values into VehicleID, DateWork, Hours, Shift and code sets their DefaultValue. User enters the Item Number and this completes record 1. User moves to New Record row, user selects another Item Number and 4 fields are automatically populated by DefaultValue and this creates second record. User repeats until 20 records are created. User enters new values in all fields which sets new DefaultValue and continues data entry, and the cycle continues.

[Jrzy3]

Thank you for the reply!

Is there a way to enter the data as I described above? It would cut down data entry time.

[June7]

Not much quicker and would require lot more VBA code. User has to make 20 entries to select Item Number regardless. Have the form set to Continuous or Datasheet view. Just advance down the form for each new record.

[Jrzy3]

I will try that suggestion tomorrow when I get in to work and let you know! Thank you for patience and time!

I was able to get the continous form to work with one slight issue...

Me.controlname.DefaultValue = Me.controlname

I receive a #Name? for the VehicleID (it is an alphanumeric value i.e. D510); and #Error for DateWork. The Hours work great as it populates. Now if I enter only numeric values in the other fields they will autopopulate.

Thanks!

[Jrzy3]

I found the answer on another post:

Me.DateControlName.DefaultValue = "'" & Me.DateControlName.Value & "'"

Thank you!