Add records using recordsets

[mrfixit1170]

Hello all,
Hope someone can point me in the right direction, I am using access 2010 as my front end and SQL server 2008r2 for the back end. I am trying to give my analysts the ability on a button click to add a series of records based on the title of the button. My example for the button is to remove a tire.
In a separate table (tbl_List_MME_Subtasks) I have the tasks involved in removing the tires (MME_ID 1), the idea is the fields will get populate into the table that records all the events (tbl_AVUM_Scored_Data).


Here is my code:

Private Sub btnRIXMSN_Click()
Dim db As DAO.Database
Dim rs1 As DAO.Recordset


Set db = CurrentDb()
Set rs1 = db.OpenRecordset("dbo_tbl_AVUM_Scored_Data", dbOpenDynaset, dbSeeChanges)
        Set ctl1 = "SELECT IETM_ID FROM tbl_List_MME_SubTasks WHERE [MME_ID] = '1'"
            With rs1
                .AddNew
                !EI_ID = Forms!frm_Acft_Score!EI_ID
                !Event_Date = Forms!frm_Acft_Score!Event_Date
                !Event_No = Forms!frm_Acft_Score!Event_No
                !Sys_Code = Forms!frm_Acft_Score!Sys_Code
                !IETM_ID = ctl1
                !PHASE_ID = Forms!frm_Acft_Score!txboPhase_ID
                !BOX_ID = Forms!frm_Acft_Score!txboBox_ID
                .Update
            End With


Set ctl1 = Nothing
Set rs1 = Nothing
End Sub

I am not sure if I am using the recordset code correctly. The other issue is there are 72 IETM_IDs that have MME_ID "1" so how does the record set moves thru the list? is there a .Move til EOF or something?

[RuralGuy]

Have you used Help to review the Methods and Properties of a DAO Recordset?

[mrfixit1170]

Thank you RuralGuy, I will chk that out. I should have chkd first before posting.........

[June7]

You probably need to declare the ctl1 as a recordset to retrieve IETM_ID field and loop through its records. Yes, there is an EOF property and MoveNext method. Then !IETM_ID = ctl1 would be: !IETM_ID = ctl1!IETM_ID

[mrfixit1170]

Thanks June7 good to hear from you again....I am in the middle of rewriting the snippet of code with the help of the example at the msdn site.

[mrfixit1170]

Here is the newer code:

Option Compare Database
Private Sub btnRIXMSN_Click()
Dim db As DAO.Database
Dim rs1 As DAO.Recordset
Dim strSQL As String
Dim intl As Integer


On Error GoTo ErrorHandler


Set db = CurrentDb


strSQL = "SELECT IETM_ID FROM tbl_List_MME_SubTasks WHERE [MME_ID] = '1'"
Set rs1 = db.OpenRecordset(strSQL, dbOpenDynaset)


If rs1.EOF Then Exit Sub


intl = 1


With rs1
    Do Until .EOF
        
        .AddNew
                !EI_ID = Forms!frm_Acft_Score!EI_ID
                !Event_Date = Forms!frm_Acft_Score!Event_Date
                !Event_No = Forms!frm_Acft_Score!Event_No
                !Sys_Code = Forms!frm_Acft_Score!Sys_Code
                !PHASE_ID = Forms!frm_Acft_Score!txboPhase_ID
                !BOX_ID = Forms!frm_Acft_Score!txboBox_ID
                !IETM_ID = strSQL
        .Update
        .MoveNext
        intl = intl + 1
    Loop
End With
    rs1.Close
    db.Close
    
    Set rs1 = Nothing
    Set db = Nothing




ErrorHandler:
        MsgBox "Error #: " & Err.Number & vbCrLf & Err.Description
End Sub

How do I reference the table that I want the records to go into? The records need to be entered into the tbl_AVUM_Scored_Data table?

[June7]

Code is wrong. You cannot set a field equal to a recordset, certainly not one that returns multiple records. You need to open two recordsets. One that will be the source of values for IETM_ID (should that be ITEM_ID?) and this is the recordset that you need to use EOF and MoveNext on. The other recordset is the one that will append records to table or instead of this recordset do an SQL INSERT action statement.

strSQL = "SELECT IETM_ID FROM tbl_List_MME_SubTasks WHERE [MME_ID] = '1'"
Set rs1 = db.OpenRecordset(strSQL, dbOpenDynaset)
If rs1.EOF Then Exit Sub
With rs1
    Do Until .EOF
         CurrentDb.Execute "INSERT INTO dbo_tbl_AVUM_Scored_Data(EI_ID, Event_Date, Event_No, Sys_Code, PHASE_ID, BOX_ID, IETM_ID) " & _
               "VALUES(" & Me!EI_ID & ", #" & Me!Event_Date & "#, " & Me!Event_No & _
                ", " & Me!Sys_Code & ", " & Me!txboPhase_ID & ", " & Me!txboBox_ID & _
                ", " & rs1!IETM_ID & ")"
        If Not .EOF Then .MoveNext
    Loop
End With

Is MME_ID a number datatype? If so, remove the apostrophes. If any of the fields in the SQL statement are text datatype, use apostrophe delimiters around the variables, and dates use # as I show in the SQL.

Check out this two recordset example https://www.accessforums.net/showthr...for-X-quantity

[mrfixit1170]

Thanks June7, I will check the link out and see If I can get this working...I will post back my results.....

[mrfixit1170]

June7 thank you for your help.....I tried your suggestion but could not get it working,

strSQL = "SELECT IETM_ID FROM tbl_List_MME_SubTasks WHERE [MME_ID] = '1'"
Set rs1 = db.OpenRecordset(strSQL, dbOpenDynaset)
If rs1.EOF Then Exit Sub
With rs1
    Do Until .EOF
         CurrentDb.Execute "INSERT INTO dbo_tbl_AVUM_Scored_Data(EI_ID, Event_Date, Event_No, Sys_Code, PHASE_ID, BOX_ID, IETM_ID) " & _
               "VALUES(" & Me!EI_ID & ", #" & Me!Event_Date & "#, " & Me!Event_No & _
                ", " & Me!Sys_Code & ", " & Me!txboPhase_ID & ", " & Me!txboBox_ID & _
                ", " & rs1!IETM_ID & ")"
        If Not .EOF Then .MoveNext
    Loop
End With

I like your suggestion better but I think I will have to play around with it to get it working consistently......I can see the performance benefit to it. This is what I ended up with...I am kind of slow so it took me alittle bit to figure out what I was doing wrong......

Private Sub btnMMEXSMN_Click()
Dim db As DAO.Database
Dim rs1 As DAO.Recordset
Dim rsMME As DAO.Recordset
Dim strSQL As String




On Error GoTo ErrorHandler
     
    strSQL = "SELECT IETM_ID FROM dbo_tbl_List_MME_SubTasks WHERE MME_ID = 1 "
    
    Set db = CurrentDb()
    Set rs1 = db.OpenRecordset("dbo_tbl_AVUM_Scored_Data", dbOpenDynaset, dbSeeChanges)
    Set rsMME = db.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)
                    
        Do While Not rsMME.EOF
                                       
            With rs1
             .AddNew
             For i = 0 To rsMME.Fields.Count - 1
                !EI_ID = Forms!frm_Acft_Score!EI_ID
                !Event_Date = Forms!frm_Acft_Score!Event_Date
                !Event_No = Forms!frm_Acft_Score!Event_No
                !Sys_Code = Forms!frm_Acft_Score!Sys_Code
                !PHASE_ID = Forms!frm_Acft_Score!txboPhase_ID
                !BOX_ID = Forms!frm_Acft_Score!txboBox_ID
                !IETM_ID = rsMME(i).Value
            Next i
             .Update
            End With
            rsMME.MoveNext
        Loop
        
ExitHandler:
    
    rs1.Close
    db.Close
    
    Set rs1 = Nothing
    Set db = Nothing


Exit Sub


ErrorHandler:
   MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description
Exit Sub


End Sub

How would I go about using this code for several different buttons on my page, instead of copying this code for every button and changing this Where clause....

strSQL = "SELECT IETM_ID FROM dbo_tbl_List_MME_SubTasks WHERE MME_ID = 1 "

I understand that I would have to make this a function and then make the WHERE MME_ID = & '', & "a variable" ....but any suggestions what to use.... maybe a button name or something like a IF..Then statement with the different choices...IE:

If a = btnName1 Then MME_ID = 1
If a = btnName2 Then MME_ID = 2

Am I headed in a logical direction?

Thanks again for all the help......

[June7]

What is MME_ID? Why is it changing? How many are there? You want to add new records for every MME_ID?

[mrfixit1170]

June7,
"What is MME_ID?"
The MME_ID is what I am using to group commonly selected task (tbl_MME)

MME_ID	Number	PK
MME_Name	Text

Then in the (tbl_List_MME_SubTask) where the tasks numbers for each MME is stored

Rec_ID_MME	AutoNum
MME_ID	Number
IETM_ID	Number

"Why is it changing?"
The MME_ID would only change to accommodate whichever MME_ID the analyst needs to use.

"How many are there?"
So far I have a total of 6 MME_ID's, The Form right now has 6 buttons with the same exact string of code except for a different MME_ID in the WHERE statement. .....

"You want to add new records for every MME_ID?"
Yes I would need to add records for each MME, MME_ID 1 has 75 individual tasks, MME_ID 2 has 92, So for the analyst it would be easier to have a button selection to add items for each MME_ID at once.

Hope this info helps....Like I said the code works as is, but I was looking for an easier way to cut down on all that code...Thanks for all the assistance.....

[June7]

Think I would have a combobox with the MME_ID values. Then code to concatenate the variable:

strSQL = "SELECT IETM_ID FROM tbl_List_MME_SubTasks WHERE [MME_ID] = '" & Me.cbxMME_ID & "'"

If MME_ID is a number field, then eliminate the apostrophe delimiters.

Code could be in the AfterUpdate event of combobox or button Click.

[mrfixit1170]

Thanks June7,
I'll try the combobox method you suggested....but its just alittle icing on the cake....I have the page working the way I want it now with 4 buttons...I'll comeback with an example when I get it working but other than that this topic is fixed for now....thanks again for all the help and suggestions.