我有一個表格，我想在其中跟蹤許多資產的資產資訊（資產已經寫在員工填寫的表格上）
| 資產 | 小時 | DSL | 國防軍 | 涼爽的 | 10W | 30W | 40W | *然后還有 12 個欄位 |
|---|---|---|---|---|---|---|---|---|
| 資產 1 | ||||||||
| 資產 2 | ||||||||
| *然后還有 55 種資產 |
欄位未系結，欄位名稱完全相同，只是按時間順序編號（asset1、hours1、dsl1...asset2、hours2、dsl2...等）。我想知道是否可以通過更改欄位名稱來創建一個回圈來同時保存所有行。
我對回圈有點陌生，但我想看看是否可以通過回圈更改欄位名稱，所以我使用了：
For i = 1 To 3
Me.varlooptest = Me("asset" & i).Value
MsgBox "checkpoint"
Next i
它所做的是將資產編號放入我的 Me.varlooptest 文本框中，然后在它適用于每一行時給我訊息“檢查點”（并且確實如此）。
知道更改欄位名稱的代碼可以作業，我嘗試保存它，它會給我一個“查詢輸入必須包含至少一個表或查詢”錯誤，我將此查詢基于我的其他查詢，所以我覺得應該是對的，但我想我錯過了一些東西
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!"
uj5u.com熱心網友回復：
考慮使用QueryDef 引數和保存的 Access 查詢來分離 SQL 和 VBA：
SQL （另存為查詢物件，無 VBA 變數、引號、& 換行符）
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 （無 SQL 命令、標點符號或語法）
' 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
uj5u.com熱心網友回復：
看看有問題的評論！ 謝謝@MichaelMurphy 和@June7；它完美無缺！
編輯：書面解釋
我很愚蠢，這些欄位不正確（謝謝@MichaelMurphy）：
Me("grease" & i).Value & "' AS hose, '" & _
Me("leaks" & i).Value & "' AS mirrors, '" & _
更正為：
Me("grease" & i).Value & "' AS grease, '" & _
Me("leaks" & i).Value & "' AS leaks, '" & _
而且我在評論末尾有一個不成對的括號，我猜 SQL 不使用分號（謝謝@June7）：
Me("comment" & i).Value & "' AS comment );"
更正為：
Me("comment" & i).Value & "' AS comment "
所以更正和作業的代碼是：
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 grease, '" & _
Me("leaks" & i).Value & "' AS leaks, '" & _
Me("blowout" & i).Value & "' AS blowout, '" & _
Me("comment" & i).Value & "' AS comment "
CurrentDb.Execute insert_shop_qry
Else
Cancel = True
End If
Next i
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/451419.html