By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,925 Members | 732 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,925 IT Pros & Developers. It's quick & easy.

"Enter parameter value" input box when running SQL statement

P: n/a
lgo
I have read several variations of this topic posted on this news group.
However I was not able to find the answer for my problem.
I am trying to build code (see below) to update a large single record
using data from a temporary (editing) table with identical field
structure. I iterate through the fields (some 50 of them) with a For
Each - Next loop to update each field with the SQL statement. The
updating works fine with numeric data type but not with text type data.
Each time it tries to update a text data field, I get the following
input box appearing, "Enter parameter value". If I go to the query
design window I can build and run a query based on the same parameters
and update all the fields without any problems (no input box popping
up) regardless of data type. I am wondering why my SQL statement gives
me the pop up and how I can get around it. Is this the best way to
update a record in a table or is there a simpler record global
overwright method that could be used.

Sub subUpDateRec()

Dim db As Database, tbl As Recordset, tbl2 As Recordset, fld As
Field, fld2 As Field,
strFld As String, strValue As String, i As Integer, strCriteria As
String
Set db = CurrentDb
Set tbl = db.OpenRecordset("MyTable")
Set tbl2 = db.OpenRecordset("MyTempTable")
strCriteria = [Forms].[MyForm].[MyControl]
For Each fld In tbl.Fields
strFld = fld.Name
Set fld2 = tbl2.Fields(i)
DoCmd.RunSQL "UPDATE MyTable SET MyTable." & [strFld] & " = " & fld2
& " WHERE (((MyTable.Field)=" & strCriteria & "));"
i = i + 1
Next fld
Set db = Nothing
Set tbl = Nothing

End Sub

(Note: My input form is not bound to the table to be updated.)
I also considered using delete and append methods but the records will
be edited
regularly and I'm afraid the table will become too large and need to be
compacted too often.
(can one never compact often enough!?)

Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
lg*@golden.net wrote:
I have read several variations of this topic posted on this news group.
However I was not able to find the answer for my problem.
I am trying to build code (see below) to update a large single record
using data from a temporary (editing) table with identical field
structure. I iterate through the fields (some 50 of them) with a For
Each - Next loop to update each field with the SQL statement. The
updating works fine with numeric data type but not with text type data.
Each time it tries to update a text data field, I get the following
input box appearing, "Enter parameter value". If I go to the query
design window I can build and run a query based on the same parameters
and update all the fields without any problems (no input box popping
up) regardless of data type. I am wondering why my SQL statement gives
me the pop up and how I can get around it. Is this the best way to
update a record in a table or is there a simpler record global
overwright method that could be used.

Sub subUpDateRec()

Dim db As Database, tbl As Recordset, tbl2 As Recordset, fld As
Field, fld2 As Field,
strFld As String, strValue As String, i As Integer, strCriteria As
String
Set db = CurrentDb
Set tbl = db.OpenRecordset("MyTable")
Set tbl2 = db.OpenRecordset("MyTempTable")
strCriteria = [Forms].[MyForm].[MyControl]
For Each fld In tbl.Fields
strFld = fld.Name
Set fld2 = tbl2.Fields(i)
DoCmd.RunSQL "UPDATE MyTable SET MyTable." & [strFld] & " = " & fld2
& " WHERE (((MyTable.Field)=" & strCriteria & "));"
i = i + 1
Next fld
Set db = Nothing
Set tbl = Nothing

End Sub

(Note: My input form is not bound to the table to be updated.)
I also considered using delete and append methods but the records will
be edited
regularly and I'm afraid the table will become too large and need to be
compacted too often.
(can one never compact often enough!?)


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

If the data type of fld2 is Text you need to enclose the value w/ single
quotes:

DoCmd.RunSQL "UPDATE MyTable SET MyTable." & [strFld] & " = " & _
IIf(fld2.Type = dbText, "'" & fld2 & "'", fld2) & _
" WHERE ... etc. ...

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

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

iQA/AwUBQbtG4IechKqOuFEgEQLLZgCdFLFLOsSy4NWx0BNTGpO6Ta VJueMAn1gQ
1OI6pFGkKpONOTtphDtKW/cx
=L+lR
-----END PGP SIGNATURE-----
Nov 13 '05 #2

P: n/a
lgo
Thank you so much! That did it.
I continue to be amazed at all the knowledge out there of all the
nuances of programing in Access and with the people willing to share
their expertise. Without this news group I would have given up long ago
but as it is I am (forever) on the cusp of becoming a POWER
programer.UGH!
Thanks again
Lou

Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.