Update Query Asking For Value

[Anthony Lewis via AccessMonster.com]

I have been fighting with this update for too long now. Here's the code I'm
trying to use:

Dim rs As DAO.Recordset

Dim strSql As String

strSql = "Select [Recommendations].Vehicle_ID, IIF
(category=121,RecommendText,'none') AS Box1, IIF
(category=146,RecommendText,'') AS 146, IIF(category=109,RecommendText,'')
AS 109, IIF(category = 148,RecommendText,'') AS 148, IIF
(category=149,RecommendText,'') AS 149, IIF(category=130,RecommendText,'')
AS 130, IIF(category=129,RecommendText,'') AS 129, IIF
(category=115,RecommendText,'') AS 115, IIF(category=120,RecommendText,'')
AS Box9, IIF(category=124,RecommendText,'') AS 124 FROM [Recommendations]
INNER JOIN [Category] ON [Recommendations].category=[Category].Code;"
' strSql = "Select Vehicle_ID from [Recommendations];"

Set rs = CurrentDb.OpenRecordset(strSql)

' process untill end of file
Do While rs.EOF = False

' Value Set for Testing Purposes here
If rs!Vehicle_ID = 1874 Then
' Showing Values Only For Testing
MsgBox _
rs!Vehicle_ID
Dim Veh_ID As String
Dim Maint As Variant

Maint = rs!Box1

Veh_ID = rs!Vehicle_ID

MsgBox _
[Veh_ID]
MsgBox _
[Maint]

Dim Sql As String
Sql = "Update [Recommendations_Master] set Recommendations_Vehicle_ID = "
& Veh_ID & _
", 120 = " & Veh_ID & _
", 121 = " & 121 & ";"
DoCmd.RunSQL (Sql)
rs.MoveNext

Else
rs.MoveNext
End If
Loop

rs.Close

The problem is in the update Sql. Two problems. The code is correct in
putting in the value for Veh_ID, but not for Maint or 121. 121 is a direct
field from the recordset. Maint as you can see is a value set to a field in
the recordset, as is Veh_ID. I do not understand why Veh_ID works but Maint
does not. When I run the code, a popup prompts me for a value for 'none'
(which is the actual value of the Maint variable). Please help! This code
is going to have more to it, but I need this part to work first. Thanks

Anthony

 

[Anthony Lewis via AccessMonster.com]

Oops, one typo there
the Line that reads:

", 120 = " & Veh_ID & _
should be:
", 120 = " & Maint & _

I was putting Veh_ID in there again just to show that it wasn't a syntax
mistake, since Veh_ID works properly and Maint does not.

 

[Per Larsen]

Oops, one typo there
the Line that reads:

", 120 = " & Veh_ID & _
should be:
", 120 = " & Maint & _

I was putting Veh_ID in there again just to show that it wasn't a syntax
mistake, since Veh_ID works properly and Maint does not.

And what about giving us the SQL string (instead of VB code), since it's your update query that seems to have problems.
Maybe someone would find out a bit more then.

I, for instance, really hesitate to 'step' through the VB code just to find out how the SQL (update) will be when I know you could just cut and paste it from VBA.

Regards
PerL

 

[Anthony Lewis via AccessMonster.com]

I actually found my own solution. First of all, VB doesn't seem to like
dealing with numeric column headings, so I renamed them in my initial
select for the recordset. Then I made a variable for each, (had to append
multiple fields to create one for the update). As for the update query, I
needed to also put in single quotes since the value passed in through the
variables were text strings. I'm sure there's likely a better/more
effiecent solution, but it works well for me. Thanks.

Anthony