I have a form where I want to track asset information for many assets (Assets are already written on the form that the employees fill out)

The fields are unbound and the field names are exactly the same but just numbered chronologically (asset1, hours1, dsl1...asset2, hours2, dsl2...etc). I want to know if I can create a loop to save all the rows at the same time by changing the field name.

I'm kind of new to loops, but I wanted to see if it was even possible to change the field name through a loop so I used:

For i = 1 To 3
   Me.varlooptest = Me("asset" & i).Value
   MsgBox "checkpoint"
 Next i

And what this did was put the asset number into my Me.varlooptest textbox then give me the message "checkpoint" when it worked for each row(and it did).

Knowing that the code to change the field name would work, I tried to save it and it would give me a "Query input must contain at least one table or query" error, and I based this query off my other ones so I feel like it should be right but I think I'm missing something

 For i = 1 To 3
  Set db = DBEngine.Workspaces(0).Databases(0)
  If Len(Me.Controls("asset" & i).Value) <> 0 Then
 insert_shop_qry = "INSERT INTO tbl_shop(tracking_shop_id,asset_id,hours,dsl,def,cool,W10,W30,W40,glass,fittings,airfilter,wheels,hose,mirrors,battery,lights,grease,leaks,blowout,comment) SELECT " & Me.shop_tracking_id & " AS tracking_shop_id, " & _
 Me("asset" & i).Value & " AS asset_id, " & _
 Me("hours" & i).Value & " AS hours, '" & _
 Me("dsl" & i).Value & "' AS dsl, '" & _
 Me("def" & i).Value & "' AS def, '" & _
 Me("cool" & i).Value & "' AS cool, '" & _
 Me("W10_" & i).Value & "' AS W10, '" & _
 Me("W30_" & i).Value & "' AS W30, '" & _
 Me("W40_" & i).Value & "' AS W40, '" & _
 Me("glass" & i).Value & "' AS glass, '" & _
 Me("fittings" & i).Value & "' AS fittings, '" & _
 Me("airfilter" & i).Value & "' AS airfilter, '" & _
 Me("wheels" & i).Value & "' AS wheels, '" & _
 Me("hose" & i).Value & "' AS hose, '" & _
 Me("mirrors" & i).Value & "' AS mirrors, '" & _
 Me("battery" & i).Value & "' AS battery, '" & _
 Me("lights" & i).Value & "' AS lights, '" & _
 Me("grease" & i).Value & "' AS hose, '" & _
 Me("leaks" & i).Value & "' AS mirrors, '" & _
 Me("blowout" & i).Value & "' AS blowout, '" & _
 Me("comment" & i).Value & "' AS comment );"
  CurrentDb.Execute insert_shop_qry
     End If
   Next i

  MsgBox "saved!"

CodePudding user response：

Look at comments Thank you @MichaelMurphy and @June7; it works PERFECTLY!!

CodePudding user response：

Consider separating SQL and VBA with use of QueryDef parameters and a saved Access query:

SQL (save as query object, no VBA variables, quotes, ampersands line breaks)

PARAMETERS [prm_shop_tracking_id] INTEGER, [prm_asset] INTEGER, [prm_hours] DOUBLE, 
           [prm_dsl] TEXT, [prm_def] TEXT, [prm_cool] TEXT, 
           [prm_W10] TEXT, [prm_W30] TEXT, [prm_W40] TEXT,
           [prm_glass] TEXT, [prm_fittings] TEXT, [prm_airfilter] TEXT, 
           [prm_wheels] TEXT, [prm_hose] TEXT, [prm_mirrors] TEXT, 
           [prm_battery] TEXT, [prm_lights] TEXT, [prm_grease] TEXT, 
           [prm_leaks] TEXT, [prm_blowout] TEXT, [prm_comment] TEXT;
INSERT INTO tbl_shop (tracking_shop_id, asset_id, hours, dsl, def, cool,
                      W10, W30, W40, glass, fittings, airfilter, wheels,
                      hose, mirrors, battery, lights, grease, leaks,
                      blowout, comment) 
VALUES ([prm_shop_tracking_id], [prm_asset], [prm_hours], [prm_dsl],
        [prm_def], [prm_cool], [prm_W10], [prm_W30], [prm_W40],
        [prm_glass], [prm_fittings], [prm_airfilter], [prm_wheels],
        [prm_hose], [prm_mirrors], [prm_battery], [prm_lights],
        [prm_grease], [prm_leaks], [prm_blowout], [prm_comment]);

VBA (no SQL commands, punctuation, or syntax)

' INITIALIZE DAO OBJECTS ONCE
Set db = CurrentDb
Set qdef = db.QueryDefs("mySavedInsertQuery")

For i = 1 To 3
    If Len(Me.Controls("asset" & i).Value) <> 0 Then
        ' BIND PARAMETERS
        qdef![prm_shop_tracking_id] = Me.shop_tracking_id
        qdef![prm_asset] = Me("asset" & i).Value
        qdef![prm_hours] = Me("hours" & i).Value
        qdef![prm_dsl] = Me("dsl" & i).Value
        qdef![prm_def] = Me("def" & i).Value
        qdef![prm_cool] = Me("cool" & i).Value
        qdef![prm_W10] = Me("W10_" & i).Value
        qdef![prm_W30] = Me("W30_" & i).Value
        qdef![prm_W40] = Me("W40_" & i).Value 
        qdef![prm_glass] = Me("glass" & i).Value
        qdef![prm_fittings] = Me("fittings" & i).Value
        qdef![prm_airfilter] = Me("airfilter" & i).Value
        qdef![prm_wheels] = Me("wheels" & i).Value
        qdef![prm_hose] = Me("hose" & i).Value
        qdef![prm_mirrors] = Me("mirrors" & i).Value
        qdef![prm_battery] = Me("battery" & i).Value
        qdef![prm_lights] = Me("lights" & i).Value 
        qdef![prm_grease] = Me("grease" & i).Value
        qdef![prm_leaks] = Me("leaks" & i).Value
        qdef![prm_blowout] = Me("blowout" & i).Value
        qdef![prm_comment] = Me("comment" & i).Value

        ' EXECUTE ACTION
        qdef.Execute
    End If
Next i

' RELEASE DAO OBJECTS
Set qdef = Nothing: Set db = Nothing