Array in a Docmd.Runsql query

[Guest]

I am reading values from a series of combo boxes into an array
(ValueToWrite(1 to 4)). On clicking a "Confirm All" button, I want an
update query to run using the values from the array as per below:

Dim strQuery As String
strQuery = "UPDATE [Loan Laptops] "
strQuery = strQuery + "SET Equipment_id = ValueToWrite(1), User_id =
ValueToWrite(2), "
strQuery = strQuery + "Production_id = ValueToWrite(3) "
strQuery = strQuery + "WHERE Loan_id = 87;"

DoCmd.RunSQL (strQuery)

When the code gets to the DoCmd line, it returns an error telling me that
ValueToWrite() is an undefined function.

What a m I doing wrong?

 

[Brian]

I am reading values from a series of combo boxes into an array
(ValueToWrite(1 to 4)). On clicking a "Confirm All" button, I want an
update query to run using the values from the array as per below:

Dim strQuery As String
strQuery = "UPDATE [Loan Laptops] "
strQuery = strQuery + "SET Equipment_id = ValueToWrite(1), User_id =
ValueToWrite(2), "
strQuery = strQuery + "Production_id = ValueToWrite(3) "
strQuery = strQuery + "WHERE Loan_id = 87;"

DoCmd.RunSQL (strQuery)

When the code gets to the DoCmd line, it returns an error telling me that
ValueToWrite() is an undefined function.

What a m I doing wrong?

Assuming ValueToWrite is an array of strings:

Dim strQuery As String
strQuery = "UPDATE [Loan Laptops] "
strQuery = strQuery + "SET Equipment_id = """ & ValueToWrite(1) & """,
User_id = """ & ValueToWrite(2) & """, "
strQuery = strQuery + "Production_id = """ & ValueToWrite(3) & """"
strQuery = strQuery + " WHERE Loan_id = 87;"

If it's an array of a numeric type:

Dim strQuery As String
strQuery = "UPDATE [Loan Laptops] "
strQuery = strQuery + "SET Equipment_id = " & ValueToWrite(1) & ", User_id =
" & ValueToWrite(2) & ", "
strQuery = strQuery + "Production_id = " & ValueToWrite(3)
strQuery = strQuery + " WHERE Loan_id = 87;"

 

[Nick Coe \(UK\)]

I think you need to concatenate ValueToWrite() outside the
SQL string you're making:-

strQuery = strQuery + "SET Equipment_id = ValueToWrite(1),
User_id =
ValueToWrite(2), "

strQuery = strQuery + "SET Equipment_id = " &
ValueToWrite(1) & ", User_id =

and so on...

Good luck

--
Nick Coe (UK)
AccHelp v1.01 Access Application Help File Builder
http://www.alphacos.co.uk/
Download Free Copy
----

I am reading values from a series of combo boxes into an
array
(ValueToWrite(1 to 4)). On clicking a "Confirm All"
button, I want
an update query to run using the values from the array as
per below:

Dim strQuery As String
strQuery = "UPDATE [Loan Laptops] "
strQuery = strQuery + "SET Equipment_id = ValueToWrite(1),
User_id =
ValueToWrite(2), "
strQuery = strQuery + "Production_id = ValueToWrite(3) "
strQuery = strQuery + "WHERE Loan_id = 87;"

DoCmd.RunSQL (strQuery)

When the code gets to the DoCmd line, it returns an error
telling me
that ValueToWrite() is an undefined function.

What a m I doing wrong?

 

[Guest]

Thanks very much. Just the ticket!

I am reading values from a series of combo boxes into an array
(ValueToWrite(1 to 4)). On clicking a "Confirm All" button, I want an
update query to run using the values from the array as per below:

Dim strQuery As String
strQuery = "UPDATE [Loan Laptops] "
strQuery = strQuery + "SET Equipment_id = ValueToWrite(1), User_id =
ValueToWrite(2), "
strQuery = strQuery + "Production_id = ValueToWrite(3) "
strQuery = strQuery + "WHERE Loan_id = 87;"

DoCmd.RunSQL (strQuery)

When the code gets to the DoCmd line, it returns an error telling me that
ValueToWrite() is an undefined function.

What a m I doing wrong?

Assuming ValueToWrite is an array of strings:

Dim strQuery As String
strQuery = "UPDATE [Loan Laptops] "
strQuery = strQuery + "SET Equipment_id = """ & ValueToWrite(1) & """,
User_id = """ & ValueToWrite(2) & """, "
strQuery = strQuery + "Production_id = """ & ValueToWrite(3) & """"
strQuery = strQuery + " WHERE Loan_id = 87;"

If it's an array of a numeric type:

Dim strQuery As String
strQuery = "UPDATE [Loan Laptops] "
strQuery = strQuery + "SET Equipment_id = " & ValueToWrite(1) & ", User_id =
" & ValueToWrite(2) & ", "
strQuery = strQuery + "Production_id = " & ValueToWrite(3)
strQuery = strQuery + " WHERE Loan_id = 87;"