Save data in a subform as new records in a table

[SeanA]

Hello all!



Back with more questions. I have a form where a user selects a certain variant code of a vehicle. Each variant (7 variants) has a couple hundred vehicles assocaited with it. These vehicles each have a unique ID number. So, in my form i have a combo box that the user will select a certaiin variant. After they do this, I have a subform that populates with the records for that specific variant (using a query of the the vehicles table) and it list them by the unique ID number. However, I have two blank fields in my subform, Work Order and Description. Currently I have two text boxes in the original form with the same names. So, the user will pick a variant, and then type in a work order and description and the fields in the subform are tied to these text boxes. Those fields then update and the data entry is complete.

This is the part I am stuck on. I would like to now save the data in the subform (which has a record source as the vehicles table) as a new record into a seperate table called Work orders. Basically, we are trying to build a mass update feature. So, if 200 vehicles of a certain variant are getting a certain work order, we can quickly update them.

Any help or suggestions are appreciated!

[June7]

Subform is bound to Vehicles table? Why would you save WorkOrder and Description into Vehicles table? Will each vehicle have only one work order?

If you want to provide db for analysis, follow instructions at bottom of my post.

[SeanA]

I have had to change the design of the form some. I have several seperate tables for the categories now. What I would like to do is in "AddServiceBulletins" select an EquipmentCode/MAS. After this is down I ahve a subform that filters the results below. I would then like to select a "Service Bulletin" and have it add all of the "Vehicle IDs" listed in the subform and the selected" Service Bulletin" to the the table "tblWO". Thus, having a mass update of the service bulletins in the database.

I've attached a copy of my database. Let me know if you have any questions about where I am going/what I was thinking.

Thanks!

[June7]

I wouldn't use autonumber as primary key in tblVehicles, tblLocation, tblType, tblStatus, tblServiceBulletins, tblLocationType, tblECMAS. I avoid lookup aliases whenever possible.

Field name in tblWO is misspelled - EqupimentCodeMASID. It is also the wrong datatype. PK/FK fields must be same datatype. This means the Reg, EquipmentCodeMASID, LocationID, LocationTypeID, StatusID fields in tblVehicles are also wrong datatype.

Expression in txtDescription needs to be: =[cmbServiceBulletinID].[column](1)

The main form AddServiceBulletins should be unbound and cmbServiceBulletinID should also be unbound. The subform isn't even needed for this. In fact, if you want it to show the new records added be the sql action, will need to requery the form.

Can do a mass append of records to tblWO with an INSERT sql action:
CurrentDb.Execute "INSERT INTO tblWO(VehicleID, ServiceBulletinID, EquipmentCodeMASID) SELECT VehicleID, " & Me.txtDescription & " As BullID, " & Me.txtEquipmentCodeMASID & " As MASID FROM tblVehicles WHERE EquipmentCodeMASID = " & Me.txtEquipmentCodeMASID

Why open tblWO when AddServiceBulletins form is opened?

[SeanA]

I wouldn't use autonumber as primary key in tblVehicles, tblLocation, tblType, tblStatus, tblServiceBulletins, tblLocationType, tblECMAS. I avoid lookup aliases whenever possible.
Ok, I can set the tables up differently. I just assumed that may be the easiest way.

Field name in tblWO is misspelled - EqupimentCodeMASID. It is also the wrong datatype. PK/FK fields must be same datatype. This means the Reg, EquipmentCodeMASID, LocationID, LocationTypeID, StatusID fields in tblVehicles are also wrong datatype.
So, if I keep my database as is, would I need to change the datatype to numbers?

The main form AddServiceBulletins should be unbound and cmbServiceBulletinID should also be unbound. The subform isn't even needed for this. In fact, if you want it to show the new records added be the sql action, will need to requery the form.
Ok, part of the reason I was doing the subform was so I could visually see if the changes were working.

Can do a mass append of records to tblWO with an INSERT sql action:
CurrentDb.Execute "INSERT INTO tblWO(VehicleID, ServiceBulletinID, EquipmentCodeMASID) SELECT VehicleID, " & Me.txtDescription & " As BullID, " & Me.txtEquipmentCodeMASID & " As MASID FROM tblVehicles WHERE EquipmentCodeMASID = " & Me.txtEquipmentCodeMASID
I'm not sure I follow this. What are As BullID and As MASID? Also, should ME.txtEquipmentCodeMASID be Me.cmbEquipmentCodeMASID?

Why open tblWO when AddServiceBulletins form is opened?
Not sure why I did this.

I do have a table already set up without lookup aliases. I originally set it up this way, but could not figure out how to do the mass update. So, I went a different direction. I've attached that file as well.

[June7]

Yes, if you retain the autonumber fields as primary key then the related foreign key fields must be number type.

If you keep the subform then will need to requery it after the new records are committed to table in order to show those records.

BullID and MASID are alias field names for the SELECT part of the sql statement. The data comes from the form controls but the query must have fields.

Yes, can pull the MASID code from the combobox. Referencing the textbox that references the combobox also works.

[SeanA]

Ok, I'll play with this some and see where I can get. Thanks!

[SeanA]

Hokay, I've got it where I can add two of the fields (EquipmentCodeMASID and VehicleID) because i am pulling them from one table. The part I am still have trouble with is pulling ServiceBulletinID from the table "tblServiceBulletins". This is my code currently:

CurrentDb.Execute "INSERT INTO tblWO(EquipmentCodeMASID,VehicleID) SELECT EquipmentCodeMASID, VehicleID FROM tblVehicles WHERE EquipmentCodeMASID = " & Me.cmbEquipmentCodeMAS

I'm not sure how to pull that ServiceBulletinID as along with all of this. I have tried just pulling the ServiceBulletinID from the actualt form ( "txtServiceBulletinID"), but that has not worked for me.

[June7]

As shown in earlier example, concatenate the variable, just like done with cmbEquipmentCodeMAS in the WHERE clause:

CurrentDb.Execute "INSERT INTO tblWO(EquipmentCodeMASID, VehicleID, ServiceBulletinID) SELECT EquipmentCodeMASID, VehicleID, " & Me.txtServiceBulletinID & " As BullID FROM tblVehicles WHERE EquipmentCodeMASID = " & Me.cmbEquipmentCodeMAS

[SeanA]

Works like a charm. I must of done something wrong earlier... sorry about that. Thanks for the help, June7!!!