UPDATE query asking for parameter

[Lisa]

I am using TransferSpreadsheet to import data from Excel
back into Access 2000. I currently have the code setting
up a temptable and then using an UPDATE query to make
changes in the necessary table(s). The data is importing
into the tempTable, but the query always asks for a
parameter for the tempTable fields (Phone,
AdminOfficer, ...). I have already checked spelling and
tried using field numbers like [F19] instead of the field
name to no avail.

Can anyone help me figure out the problem with my update
query to get it working properly?

Dim query As String
query = "UPDATE IDRequest INNER JOIN BpnForm " _
& "ON IDRequest.DBRequestID =
BpnForm.DBRequestID " _
& "SET IDRequest.Phone = [" & tempTable
& "].Phone, " _
& "IDRequest.AdminOfficer = [" & tempTable
& "].AdminOfficer, " _
& "IDRequest.SubmitterName = [" & tempTable
& "].SubmitterName, " _
& "IDRequest.SubmitterEmail = [" & tempTable
& "].SubmitterEmail, " _
& "IDRequest.SubmitterPhone = [" & tempTable
& "].SubmitterPhone " _
& "WHERE (((IDRequest.DBRequestID) = [" &
tempTable & "].DBRequestID));"
DoCmd.RunSQL (query)

I am attempting to update records in the IDRequest table
where the DBRequestID field in the tempTable = the
DBRequestID in the IDRequest table.

thanks in advance for your help!!

 

[John Viescas]

What's in the variable "tempTable" and why aren't you including it in the
JOIN clause? "tempTable" isn't defined as part of the row source for the
query, so Access treats all those references as parameters and prompts you
for the values.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You have to have the name of the tempTable in the UPDATE clause.
E.g.:

strSQL = "UPDATE IDRequest INNER JOIN [" & tempTable & "] As T " & _
"ON IDRequest.DBRequestID = T.DBRequestID " & _
"SET IDRequest.Phone = T.Phone, " & _
.... etc. .

You, also, have to have an ampersand (&) after the double-quote and
before the underline character to continue the variable assignment on
the next line.

MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQDOtiIechKqOuFEgEQI6XQCg8TdZrbWiaNJDWV/yklowr3tDjLkAn3GZ
x6D9FC9pS9FFHLI5Ue+/GxRL
=W8Fl
-----END PGP SIGNATURE-----

 

[Lisa]

Thanks guys. It works.

-----Original Message-----
What's in the variable "tempTable" and why aren't you including it in the
JOIN clause? "tempTable" isn't defined as part of the row source for the
query, so Access treats all those references as parameters and prompts you
for the values.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)

I am using TransferSpreadsheet to import data from Excel
back into Access 2000. I currently have the code setting
up a temptable and then using an UPDATE query to make
changes in the necessary table(s). The data is importing
into the tempTable, but the query always asks for a
parameter for the tempTable fields (Phone,
AdminOfficer, ...). I have already checked spelling and
tried using field numbers like [F19] instead of the field
name to no avail.

Can anyone help me figure out the problem with my update
query to get it working properly?

Dim query As String
query = "UPDATE IDRequest INNER JOIN BpnForm " _
& "ON IDRequest.DBRequestID =
BpnForm.DBRequestID " _
& "SET IDRequest.Phone = [" & tempTable
& "].Phone, " _
& "IDRequest.AdminOfficer = [" & tempTable
& "].AdminOfficer, " _
& "IDRequest.SubmitterName = [" & tempTable
& "].SubmitterName, " _
& "IDRequest.SubmitterEmail = [" & tempTable
& "].SubmitterEmail, " _
& "IDRequest.SubmitterPhone = [" & tempTable
& "].SubmitterPhone " _
& "WHERE (((IDRequest.DBRequestID) = [" &
tempTable & "].DBRequestID));"
DoCmd.RunSQL (query)

I am attempting to update records in the IDRequest table
where the DBRequestID field in the tempTable = the
DBRequestID in the IDRequest table.

thanks in advance for your help!!

.