Solved record loop: need to append multiple records to table when forms field check box is selected. (1 Viewer)

[narrellj]

hello all! i am currently trying to create a code to look at my asset list form and upload selected records based on criteria. the issue is my loop continues to upload the same record over and over rather than moving though the records in the form. can you please help me? thank you so much.

as you can see each asset record has a rental number to check assets into. the first step is to ensure the IRQNumber is filled in prior to clicking the rented check box. this works great. the next step is to hit a form button to append each of the selected records. unfortunately my code as is only loops the first record rather than moving to the next checked record.

Private Sub Rented_Click()
Dim IDvar As Integer
IDvar = Me.ID.Value
If IsNull(Me.IRQNumber) Then
Me.Rented.Value = 0
MsgBox " These records cannot be archived until IRQ number is entered"
Else
DoCmd.RefreshRecord
End If
End Sub

Private Sub Command271_Click()
Dim rst As Recordset, IDvar As Integer
Set rst = Me.RecordsetClone
IDvar = Me.ID.Value
rst.MoveFirst
Do While Not rst.EOF
If Me.Rented = True Then

DoCmd.RunSQL "Insert into TblAssetsOut (IRQNumber, ID, Rented)" & _
"Select AssetsExtended.IRQNumber, AssetsExtended.ID, AssetsExtended.Rented " & _
"from AssetsExtended where AssetsExtended.ID=" & IDvar
End If
Loop
rst.Close
MsgBox IDvar & " Transfer Complete"
Set rst = Nothing

End Sub

 

[Uncle Gizmo]

Code in your Rented click:-

Dim IDvar As Integer
IDvar = Me.ID.Value

This does nothing?

Please explain what you are trying to achieve with it.

 

[Uncle Gizmo]

In your records set loop you will insert the same record every time because the record is dictated by the same number:-

IDvar

I suspect you need two loops one to do what you are doing, and the other one to loop through, and get all of these numbers in turn:- IDvar.

I suggest you posted a cut down version of your database with just the necessary table and form. Remove most of the records from the table and adjust them so that they do not reveal any confidential information.

Make sure you make backups before making any major changes to your database.

 

[Rene vK]

Stupid maybe but doesn't a loop need a movenext?

do while not rs.EOF
  debug.print "Whatever"
  rs.movenext
loop

 

[Uncle Gizmo]

On second thoughts, I think this might be the issue:-

If Me.Rented = True Then

You need to get "Rented" from the record set not from the form.

 

[Rene vK]

the first step is to ensure the IRQNumber is filled in prior to clicking the rented check box. this works great. (= Rented_Click())
In the button, the code needs a movenext!

Private Sub Command271_Click()
Dim rst As Recordset, IDvar As Integer
Set rst = Me.RecordsetClone
IDvar = Me.ID.Value
rst.MoveFirst
Do While Not rst.EOF
If Me.Rented = True Then

DoCmd.RunSQL "Insert into TblAssetsOut (IRQNumber, ID, Rented)" & _
"Select AssetsExtended.IRQNumber, AssetsExtended.ID, AssetsExtended.Rented " & _
"from AssetsExtended where AssetsExtended.ID=" & IDvar
End If
MoveNext        ' to jump to the next record in the set!
Loop
rst.Close
MsgBox IDvar & " Transfer Complete"
Set rst = Nothing

End Sub

 

[narrellj]

the first step is to ensure the IRQNumber is filled in prior to clicking the rented check box. this works great. (= Rented_Click())
In the button, the code needs a movenext!

Private Sub Command271_Click()
Dim rst As Recordset, IDvar As Integer
Set rst = Me.RecordsetClone
IDvar = Me.ID.Value
rst.MoveFirst
Do While Not rst.EOF
If Me.Rented = True Then

DoCmd.RunSQL "Insert into TblAssetsOut (IRQNumber, ID, Rented)" & _
"Select AssetsExtended.IRQNumber, AssetsExtended.ID, AssetsExtended.Rented " & _
"from AssetsExtended where AssetsExtended.ID=" & IDvar
End If
MoveNext        ' to jump to the next record in the set!
Loop
rst.Close
MsgBox IDvar & " Transfer Complete"
Set rst = Nothing

End Sub

thank you yes i caught that but i think uncle Gizmo is onto something because even with that it continues to append the same record.

 

[Rene vK]

thank you yes i caught that but i think uncle Gizmo is onto something because even with that it continues to append the same record.

Ah ok, I just overlooked your code and you mentioned that the first part worked.
Hope @Uncle Gizmo 's help will create a solution for that part.

 

[narrellj]

 

[Uncle Gizmo]

uncle Gizmo is onto something because even with that it continues to append the same record.

I did a Blog on something very similar a few years back. It might point you in the right direction, it might help you develop some more questions!

Move Info from One Table to Another - Nifty Access

Move Info from One Table to Another A brief summary summary of the "OP's" (Original Posters) question HERE. I've done a set of YouTube videos to answer the question. The first answer demonstrates the INSERT INTO method of adding records to another table. This method is where you update the...

www.niftyaccess.com

 

[narrellj]

so ive removed the rented field from the record set as suggested. the code runs, but appends the first records data with rented checked for every record in the form. im lost and feel my brain is mush even more so than when i started. thank you to all involved and also to Apr Pillai.

Private Sub Command271_Click()
Dim rst As Recordset, IDvar As Integer
Set rst = Me.RecordsetClone
IDvar = Me.ID.Value
rst.MoveFirst
Do While Not rst.EOF
If Me.Rented = -1 Then
DoCmd.SetWarnings False
DoCmd.RunSQL "Insert into TblAssetsOut (IRQNumber, ID)" & _
"Select AssetsExtended.IRQNumber, AssetsExtended.ID  " & _
"from AssetsExtended where AssetsExtended.ID=" & IDvar
DoCmd.SetWarnings True
End If
rst.MoveNext
Loop
rst.Close
MsgBox IDvar & " Transfer Complete"
Set rst = Nothing

End Sub

 

[Rene vK]

I think it would be a good move to post you application here (or part) There are people here that are really quick and smart to rework your code and come up with a solution.

 

[arnelgp]

Private Sub Command271_Click()
    Dim rst As dao.Recordset, IDs As String
    Set rst = Me.RecordsetClone
    With rst
        If Not (.BOF And .EOF) Then
            .MoveFirst
        End If
        Do Until .EOF
            If !rented Then
                IDs = IDs & !ID & ","
            End If
            .MoveNext
        Loop
    End With
    Set rst = Nothing
    
    If Len(IDs) > 0 Then
        IDs = Left$(IDs, Len(IDs) - 1)
    
        'in case the id's already exists, delete them first from the
        'table and add them again
        
        CurrentDb.Execute "delete * from TblAssetsOut where ID IN (" & IDs & ");"
        
        DoCmd.RunSQL "Insert into TblAssetsOut (IRQNumber, ID, Rented)" & _
        "Select AssetsExtended.IRQNumber, AssetsExtended.ID, AssetsExtended.Rented " & _
        "from AssetsExtended where AssetsExtended.ID IN (" & IDs & ");"
    End If
    
    MsgBox IDvar & " Transfer Complete"

End Sub

 

[Gasman]

so ive removed the rented field from the record set as suggested. the code runs, but appends the first records data with rented checked for every record in the form. im lost and feel my brain is mush even more so than when i started. thank you to all involved and also to Apr Pillai.


Private Sub Command271_Click()
Dim rst As Recordset, IDvar As Integer
Set rst = Me.RecordsetClone
IDvar = Me.ID.Value
rst.MoveFirst
Do While Not rst.EOF
If Me.Rented = -1 Then
DoCmd.SetWarnings False
DoCmd.RunSQL "Insert into TblAssetsOut (IRQNumber, ID)" & _
"Select AssetsExtended.IRQNumber, AssetsExtended.ID " & _
"from AssetsExtended where AssetsExtended.ID=" & IDvar
DoCmd.SetWarnings True
End If
rst.MoveNext
Loop
rst.Close
MsgBox IDvar & " Transfer Complete"
Set rst = Nothing

End Sub

Why don't people use code tags and indent their code.? It would make it easier for them and others to spot the mistakes.

Private Sub Command271_Click()
    Dim rst As Recordset, IDvar As Integer
    Set rst = Me.RecordsetClone
    IDvar = Me.ID.Value
    rst.MoveFirst
    Do While Not rst.EOF
        If Me.Rented = -1 Then
            DoCmd.SetWarnings False
            DoCmd.RunSQL "Insert into TblAssetsOut (IRQNumber, ID)" & _
                         "Select AssetsExtended.IRQNumber, AssetsExtended.ID " & _
                         "from AssetsExtended where AssetsExtended.ID=" & IDvar
            DoCmd.SetWarnings True
        End If
        rst.MoveNext
    Loop
    rst.Close
    MsgBox IDvar & " Transfer Complete"
    Set rst = Nothing

End Sub

You are using a recordsetclone, so shouldn't you be referring to that data and not the form data?

 

[arnelgp]

Private Sub Command271_Click()
       Me.Dirty = False
        CurrentDb.Execute "delete * from TblAssetsOut where ID IN (Select ID From AssetsExtend Where Rented=-1);"
       
        DoCmd.RunSQL "Insert into TblAssetsOut (IRQNumber, ID, Rented)" & _
        "Select AssetsExtended.IRQNumber, AssetsExtended.ID, AssetsExtended.Rented " & _
        "from AssetsExtended where AssetsExtended.Rented = -1;"
   
    MsgBox IDvar & " Transfer Complete"

End Sub

 

[narrellj]

arnelgp,

thank you for the me.dirty code. although the code correctly lists the info for all selected assets it overlooks the applied filter for only available assets. when a new rental package is created i want to only look at the available product and when selected append the checked assets into the tblAssetsOut archive. i really do appreciate the help because i honestly had no idea what access was until about 2 weeks ago. i feel like ive been living under a rock.


i would like the code to only select the newly selected records. so selecting the field below i would only append the highlighted asset rather than reloading all records filtered prior.


thank you again. the code works and i could add another field to identify new entries but over time i will be reviewing thousands of records to compare and delete rather than just appending the few selected in the existing form with only 2k assets... if that makes sense.

 

[narrellj]

Private Sub Command271_Click()
    Dim rst As dao.Recordset, IDs As String
    Set rst = Me.RecordsetClone
    With rst
        If Not (.BOF And .EOF) Then
            .MoveFirst
        End If
        Do Until .EOF
            If !rented Then
                IDs = IDs & !ID & ","
            End If
            .MoveNext
        Loop
    End With
    Set rst = Nothing
   
    If Len(IDs) > 0 Then
        IDs = Left$(IDs, Len(IDs) - 1)
   
        'in case the id's already exists, delete them first from the
        'table and add them again
       
        CurrentDb.Execute "delete * from TblAssetsOut where ID IN (" & IDs & ");"
       
        DoCmd.RunSQL "Insert into TblAssetsOut (IRQNumber, ID, Rented)" & _
        "Select AssetsExtended.IRQNumber, AssetsExtended.ID, AssetsExtended.Rented " & _
        "from AssetsExtended where AssetsExtended.ID IN (" & IDs & ");"
    End If
   
    MsgBox IDvar & " Transfer Complete"

End Sub

thankfully i had backups.... this code is really something i had to close out of the db after trying to delete over a million looped records. lol.... thank you for the help. very useful idea generating commands.... sadly i still cant get this thing figured out. as of now i can use only the checkbox to upload correctly but i would rather be able to select assets for a package, review accuracy then archive together.

 

[Uncle Gizmo]

As I said earlier, and others have said, if you post a sample of your database, just the relevant table and form, remove most of the records, modify the few so that they don't reveal any confidential information.

Then we have something to work on...

Don't forget to make backups!

 

[narrellj]

Thank you, will do first thing in the morning. I really am appreciative and amazed at how courteous and responsive this group is. I have read so many of the posts, many from years ago, and to see you all still being so professional and eager to help is really something. Have a great rest of your weekend guys, talk soon!

 

[arnelgp]

another possible solution to saved all Checked ID to a temporary table.
the temp table (tblIDs) will hold two fields (ID, long and IRQNumber, short text).

on opening of your form add code to the Form's Open event
add code to initialize (zero out) the temp table:

private sub form_load()
Currentdb.execute "delete * from tblIDs;"
end sub

add code to the AfterUpdate event of the Rented field:

private sub Rented_AfterUpdate()
If Me.Rented Then
'add ID to tblIDs
currentdb.execute "insert into tblIDS(id, irqNumber) SELECT " & Me!ID & ", '" & Me!IRQNumber & "';"
Else
'remove from tblIDs
currentdb.execute "delete * from tblIDs WHERE ID = " & Me!ID & " And IRQNumber = '" & Me!IRQNumber & "'"
End If
end sub

pressing Command271 command button will insert tmp table records to table TblAssetsOut.
and at the same time initialize again the temp table:

Private Sub Command271_Click()
'Check if there is something to insert
If DCount("1", "tblIDs") > 0 Then
'there is atleast a record, add it
With Currentdb
.execute "Insert into TblAssetsOut (IRQNumber, ID, Rented) SELECT IRQNumber, ID, -1 From tblIDs;"

'initialized tblIDs
.execute "Delete * from tblIDs;"
End With
End If
Msgbox "Transfer completed."
End Sub