Multiple Records from a Form/Subform

[June7]

I tested your code. Just needs the DoCmd.RunCommand acCmdSaveRecord line. Put it just before the With rst line

[Schowti]

I tested your code. Just needs the DoCmd.RunCommand acCmdSaveRecord line. Put it just before the With rst line

So, I did just that. I added back the error handler and add record portion, as well as the Docmd.RunCommand acCmdSaveRecord line just before the With rst line. This is what it looks like. Now, for whatever reason, the PayrollInventory_tbl is being populated (bound controls), but the PayrollInventoryDetails_tbl (unbound controls) is not being touched. I feel like there is just one little thing missing and this would all come together. Here is the code that I have being testing. I have moved the SaveRecord line around, and moved the error handler portions of the code to the end of the code, but no difference. I don't know what is going on.

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

EmployeeAddRecord_btn_Click_Exit:
Exit Sub
EmployeeAddRecord_btn_Click_Err:
MsgBox Error$
Resume EmployeeAddRecord_btn_Click_Exit

Dim db As DAO.Database
Dim rst As DAO.Recordset
Set db = CurrentDb
Set rst = db.OpenRecordset("PayrollInventoryDetail_tbl", dbOpenDynaset)
DoCmd.RunCommand acCmdSaveRecord
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

The Me.PayrollInventoryID_txt is and unbound control, and the PayrollInventoryID is a bound control. Me.PayrollSkillGroup1_txt, Me.ActualCompetency1_txt, Me.ExpectedCompetency1_txt, and Me.Relevance1_txt are all unbound controls that I have filled with default values. If you look at the properties of those controls, the default value is determined through a DLookup of a master table. I'm just trying to give you as much info as possible to help you, help me.

[June7]

I used your project, revised the form as you described to eliminate the subform, pasted code into the button event, added the save command. Code works. Provide your revised db for anaysis.

[Schowti]

I used your project, revised the form as you described to eliminate the subform, pasted code into the button event, added the save command. Code works. Provide your revised db for anaysis.

Wow. It's good that yours is working. Maybe I messed around with something I shouldn't have. Here is what I have so far for my db. Now, as I described earlier, I moved everything to a single, main form. I no longer am using a Main Form/Subform format.

[June7]

I haven't looked at the new file yet but seeing the code gives me an idea of what is wrong. You have code to move to new record and the code continues to the SaveRecord command but there is nothing to save because no data has been entered into the new main record fields. In my edit and test of your earlier db, I used the navigation bar to move to new record, entered data, then clicked button to commit records to tables. Need to break this code into two buttons.

[Schowti]

I haven't looked at the new file yet but seeing the code gives me an idea of what is wrong. You have code to move to new record and the code continues to the SaveRecord command but there is nothing to save because no data has been entered into the new main record fields. In my edit and test of your earlier db, I used the navigation bar to move to new record, entered data, then clicked button to commit records to tables. Need to break this code into two buttons.

So, I don't know if I completely understand. I am fine with 2 different buttons in order to populate the tables, but I really don't have any idea how I would do this. Anyway you could set it up in file you have and send it to me. I am wanting to add the bound information to its respective table, but I don't want the controls to clear until the unbound controls have populated their respective table. Hopefully that makes sense. I tried splitting the code into two different buttons, but it would just keep adding the bound information to its table, and then the other button wouldn't allow me to add the unbound information to the respective table because the Primary key couldn't be null.

[June7]

Do not include PayrollInventoryDetail_tbl in the form's RecordSource.

Should not duplicate the skill description, group, relevance, expected competency in the details table.

Thought I had the code working then it wasn't. I finally checked the relationship setting between PayrollInventory and PayrollInventoryDetail in the Relationships window. The join is on the wrong field in the Detail table.

The ExpectedCompetency field is number type but InventoryActualCompetency is text type. Therefore, had to use apostrophe delimiters in the SQL statement.

This code works after fixing the relationship:

Private Sub btnNew_Click()
DoCmd.GoToRecord , "", acNewRec
End Sub

Private Sub btnSave_Click()
    Dim i As Integer
    DoCmd.RunCommand acCmdSaveRecord
    For i = 1 To 20
        CurrentDb.Execute "INSERT INTO PayrollInventoryDetail_tbl([PayrollInventoryID], [PayrollSkillID], [InventoryActualCompetency]) " & _
        "VALUES(" & Me.PayrollInventoryID_txt & ", " & i & ", '" & Me.Controls("ActualCompetency" & i & "_txt") & "')"
    Next
End Sub

If you prefer the DAO recordset method, it should also work after fixing the relationship. If you want error handler code, it goes at the end of procedure.

[Schowti]

So, when you say not to duplicate those fields in the details table, will that inhibit my ability to run reports about a specific employee later on? I am also wondering if there is a way to have the information related to the first button added to the table, but remain until the second button is clicked and the related code is run.

[June7]

If form is already on (New) record, don't click the first button. Enter all skills, save by second button and then click the 'New Record' button only if want to start a new PayrollInventory record.

Base report on a query that joins the related tables. That is the power of a relational database.

[Schowti]

If form is already on (New) record, don't click the first button. Enter all skills, save by second button and then click the 'New Record' button only if want to start a new PayrollInventory record.

Base report on a query that joins the related tables. That is the power of a relational database.

Alright,

I cannot get this code to work. I have no idea what I am doing any more. I don't want to have to, but I might have to just do a crappy excel spreadsheet. You have seen my database, you have worked with it a little bit. What can I do to get this thing working? Is there a different way to do what I want to do? Hopefully, you can see what I am trying to accomplish here. I cannot, for the life of me, get this code to work. Now, my boss is wanting me to get a radar chart together, so I need to link my access tables to powerpoint. If there is a better, more efficient way to do all of this, please, do tell. If not, I really need some heavy help on this whole code thing, even if it means you get the code working on the database I sent you, and you send it me or whatever. Please, I have reached a point of complete and total desperation!