Multiple Records from a Form/Subform

[June7]

This is weird. I get the same error with your project. But when I paste your code into my existing project it does not error. I pasted the code into brand new file and that error does not occur. And I am opening it with 2007. Will have to try with 2010 later.

[Schowti]

This is weird. I get the same error with your project. But when I paste your code into my existing project it does not error. I pasted the code into brand new file and that error does not occur. And I am opening it with 2007. Will have to try with 2010 later.

That is weird. I didn't think there was that much of a difference between 2007 and 2010. Thanks for checking it out for me, though. So once I get that code to run, I will apply the same syntax to the other controls in the row. After that, how will I loop the code to cover all the rows in the form?

[June7]

The syntax is not different but I can't run Compact and Repair on the 2010 file with 2007 app. I don't know if that will fix whatever is happening but shouldn't hurt.

I showed looping structure in earlier post. Do you mean 'all the rows in the recordset'?

[June7]

Well, C&R didn't help any. I imported everything to a new file and no longer get error on CurrentDb. Your original db must be corrupted beyond repair. The new file does error on Me.InventorySkillGroup1_txt - 'not found'. I can't find any control by that name.

The 'Add New Skills' button is on main form but textboxes are on subform. Syntax for main form code to refer to subform control is:
Me.containername.Form.controlname

I always give subform/subreport container control a name different from the object it holds, like ctrSkills. Then your code will be:
!InventorySkillGroup = Me.ctrSkills.Form.InventorySkillGroup1_txt

The looping code would be like:

For i = 1 to 20
    With rst
        .AddNew
        !InventorySkillGroup = Me.ctrSkills.Form.Controls("InventorySkillGroup" & i & "_txt")
        '... same structure for other fields - get the names right - including the PayrollInventoryID and PayrollSkillID
        .Update 'I am not sure but this might should be outside the loop just before the rst.Close
    End With
Next
rst.Close

[Schowti]

When we say recordset what are we referring to? I was under the impression that it was the table in which we were populating data... I am also wondering what you mean by the subform container control. Is that referring to the subform itself?

[June7]

A subform/subreport is created by placing a subform/subreport container control on a form/report. This control has a SourceObject property. The source object can be a table, query, form, report. Click on the 'subform', this should select the container (see the edge has orange highlight) and can view its properties, click again and now the object should be selected and can view its properties. Master/Child links properties of the container will synchronize related records of main and sub forms (when both are bound).

By recordset I mean a set of records. The code opens a recordset that has a connection to the source table. Populate fields of the recordset and the .Update method will commit those edits to the source table. Alternative to coded recordset is shown in post 4. It demonstrates an sql UPDATE action that sends data directly to the table.

[Schowti]

Alright, I reverted back to the original database and ran the code. I removed the subform, and placed all the controls onto single main form. I am not getting the same errors as before, but it will not run the code completely as it gives me the error message that the Index or Primary key cannot be Null. I have a concatenated primary key for my PayrollInventoryDetails_tbl: PayrollInventoryID and PayrollSkillID, and I do not have the PayrollSkillID visible or even being filled out in the form. I am guessing that this could be a problem. I have the PayrolSkillID's set to autonumber in a master table. I just don't want it showing on the form. I am also wondering how I might code the addition of the top 4 rows that are bound to the PayrollInventory_tbl? How might I add this to the code that will add all the questionairre rows to the PayrollInventoryDetails_tbl?

[June7]

The 'original' database had multiple controls bound to the same field. Is that what you went back to? We already know that won't work.

[Schowti]

No, I took the same setup that I had with the most recent subform format--meaning 20 rows of unbound controls--and attached that to a single form. So now, I have the original top 4 rows that are bound to the PayrollInventory_tbl, and the remaining 20 rows of unbound controls all on the same, main form. I converted the macro that is embedded into the Add Record button on my other forms to code, and then altered it to match this PayrollInventoryDetail_frm so it would run with the code we have been working on. I have gotten that to run fine, but only up until the new code we have written is to be executed. Here is what I have, but I know there needs to be some things added to clean it up and have it run all the way through.

Private Sub AddRecord_btn_Click()
On Error GoTo AddRecord_btn_Click_Err
On Error Resume Next
DoCmd.GoToRecord , "", acNewRec
If (MacroError <> 0) Then
Beep
MsgBox MacroError.Description, vbOKOnly, ""
End If
AddRecord_btn_Click_Err:
MsgBox Error$

Dim db As DAO.Database
Dim rst As DAO.Recordset
Set db = CurrentDb
Set rst = db.OpenRecordset("PayrollInventoryDetail_tbl")
With rst
.AddNew
!InventorySkillGroup = Me.PayrollSkillGroup1_txt
.Update
End With
rst.Close
End Sub

Because PayrollSkillId is part of the concatenated Primary key on the PayrollInventoryDetails_tbl, do I need to add it to my code or form to ensure that the Primary Value isn't Null?

[June7]

I believe so. I remarked on that in the sample code I provided in post 19.

Don't have your project right now and can't remember the table structure. Aren't two tables involved - parent and child? In which case this code will not work.

I don't think your error handler code is correct. Review: http://allenbrowne.com/ser-23a.html

[Schowti]

That helps a lot, thanks. Now, I am placed in the predicament as to how I go about getting my PayrollInventoryID to record on both the PayrollInventory_tbl and PayrollInventoryDetails_tbl?

[June7]

The form is bound to PayrollInventory_tbl? Entry into the bound controls will establish that record. The record will have to be saved (DoCmd.RunCommand acCmdSaveRecord) then the ID will be available for code to save with each detail record in the same manner as the other detail fields.

[Schowti]

The form is bound to PayrollInventory_tbl? Entry into the bound controls will establish that record. The record will have to be saved (DoCmd.RunCommand acCmdSaveRecord) then the ID will be available for code to save with each detail record in the same manner as the other detail fields.

Alright, so I got the error handling taken care of by making sure that the code that I copied over and tweaked, matched what was explained on the site you sent me to. I try running it with the code we have been working on but I don't think it is continuing on. I have no idea why, here is the code that I have so far. The first 4 rows are populating the PayrollInventory_tbl, but nothing is populating the PayrollInventoryDetails_tbl. I am not getting any errors, so I am wondering if the code just stops when the error handling code is complete. I also have no idea where and how to implement the (DoCmd.RunCommand acCmdSaveRecord) line, so if you could help me out with that, that would be great. Here is the code I have so far:


Private Sub AddRecord_btn_Click()
On Error GoTo AddRecord_btn_Click_Err
On Error Resume Next
DoCmd.GoToRecord , "", acNewRec
If (MacroError <> 0) Then
Beep
MsgBox MacroError.Description, vbOKOnly, ""
End If

AddRecord_btn_Click_Exit:
Exit Sub
AddRecord_btn_Click_Err:
MsgBox Error$
Resume AddRecord_btn_Click_Exit

Dim db As DAO.Database
Dim rst As DAO.Recordset
Set db = CurrentDb
Set rst = db.OpenRecordset("PayrollInventoryDetail_tbl")
With rst
.AddNew
!PayrollInventoryID = "1"
!InventorySkillGroup = Me.PayrollSkillGroup1_txt
.Update
End With
rst.Close
End Sub

[June7]

In all error handler examples I have seen, the error handling code is at the end of the procedure. I suggest you get the other code working first then put in error handling. Error handler gets in the way for debugging.

The save would be at beginning of procedure just after the On Error lines.

[Schowti]

In all error handler examples I have seen, the error handling code is at the end of the procedure. I suggest you get the other code working first then put in error handling. Error handler gets in the way for debugging.

The save would be at beginning of procedure just after the On Error lines.

Here is the code I have so far.

Private Sub AddRecord_btn_Click()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Set db = CurrentDb
Set rst = db.OpenRecordset("PayrollInventoryDetail_tbl", dbOpenDynaset)
With rst
.AddNew

!PayrollInventoryID = Me.PayrollInventoryID_txt
!PayrollSkillID = "1"
!InventorySkillGroup = Me.PayrollSkillGroup1_txt
!InventoryActualCompetency = Me.ActualCompetency1_txt
!InventoryExpectedCompetency = Me.ExpectedCompetency1_txt
!InventoryRelevance = Me.Relevance1_txt

.Update
End With
rst.Close
End Sub

I try running this by itself, and the ".update" is highlighted after an error saying that I cannot change or add a record because a related record is required in another table "PayrollInventory_tbl". Now, I know this is because PayrollInventoryId is a primary key in the PayrollInventory_tbl, as well as a part of a concatenated primary key in the PayrollInventoryDetails_tbl. So, if I understand it right, this code is working but cannot be fully executed until I add the record to the PayrollInventory_tbl and save that record before the execution of the code. Now, if this is correct, I have NO idea how to code that. Could you help me out there? If this isn't correct, now what? haha thanks again!