clipster
asked on
currentdb.execute won't run query
I have an update query that computes fields from unbound controls on an open form:
UPDATE NoticeTemp SET NoticeTemp.Appt = ([Forms]![frmLtrAppt]![grp DateByOrOn ]=1), NoticeTemp.TickleType = [Forms]![frmLtrAppt]![grpI nterviewTy pe], NoticeTemp.EncloseForms = ([Forms]![frmLtrAppt]![chk EncloseFor ms]=-1)
WHERE (((NoticeTemp.PinTag)=getu sername()) )
The update works fine using docmd.openquery (so long as I setwarnings off), but when I use currentdb.execute I get error #3061 "Too few parameters, expected 3.
Why? Would it work if I referred to the controls using different syntax? Does it matter? Would it be more efficient to update the records in code, by stepping through the recordset? I usually figure Access's native implementation of a query is more efficient than anything I do, but maybe this is an exception.
Thanks.
-Bob
UPDATE NoticeTemp SET NoticeTemp.Appt = ([Forms]![frmLtrAppt]![grp
WHERE (((NoticeTemp.PinTag)=getu
The update works fine using docmd.openquery (so long as I setwarnings off), but when I use currentdb.execute I get error #3061 "Too few parameters, expected 3.
Why? Would it work if I referred to the controls using different syntax? Does it matter? Would it be more efficient to update the records in code, by stepping through the recordset? I usually figure Access's native implementation of a query is more efficient than anything I do, but maybe this is an exception.
Thanks.
-Bob
Hi clipster,
Have you tried it without the...
=getusername()
criteria? I think you will find that .execute won't allow this becuase it uses the dbenjine direct as it would in something like VB.
Perhaps if you physically create the query and run it by name using .execute it may work.
Good Luck
Simon
Have you tried it without the...
=getusername()
criteria? I think you will find that .execute won't allow this becuase it uses the dbenjine direct as it would in something like VB.
Perhaps if you physically create the query and run it by name using .execute it may work.
Good Luck
Simon
Try enclosing your parms in quotes and single quotes like this:
UPDATE NoticeTemp SET NoticeTemp.Appt = & "'"([Forms]![frmLtrAppt]![ grpDateByO rOn]=1)& "'", NoticeTemp.TickleType = & "'" [Forms]![frmLtrAppt]![grpI nterviewTy pe]& "'", NoticeTemp.EncloseForms = & "'"([Forms]![frmLtrAppt]![ chkEnclose Forms]=-1& "'")
WHERE (((NoticeTemp.PinTag)= & "'" getusername()& "'"))
This should pass the parms to SQL. They need to be strings if you are passing text.
It looks as though you could just remove you control references where you are explicitly defining the value of the update. i.e. NoticeTemp.Appt = ([Forms]![frmLtrAppt]![grp DateByOrOn ]=1)
change it to NoticeTemp.Appt = 1
Good luck!
jshaw
UPDATE NoticeTemp SET NoticeTemp.Appt = & "'"([Forms]![frmLtrAppt]![
WHERE (((NoticeTemp.PinTag)= & "'" getusername()& "'"))
This should pass the parms to SQL. They need to be strings if you are passing text.
It looks as though you could just remove you control references where you are explicitly defining the value of the update. i.e. NoticeTemp.Appt = ([Forms]![frmLtrAppt]![grp
change it to NoticeTemp.Appt = 1
Good luck!
jshaw
ASKER
Simon, I .execute other queries that include my getusername() function as a criterion, and this query is saved. I can docmd.openquery "qupdnotctmpafter", but I cannot currentdb.execute "qupdnotctmpafter".
Mike, your syntax (running the SQL instead of the saved query) will undoubtedly work, and might even be the better way to do this, but I'm still curious why I cannot .execute the saved query.
-Bob
Mike, your syntax (running the SQL instead of the saved query) will undoubtedly work, and might even be the better way to do this, but I'm still curious why I cannot .execute the saved query.
-Bob
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
jshaw, it's not as clear as it could be without seeing the datatypes, but APPT is a logical field, TRUE if (grpByDateOrOn = 1).
Mike, I guess "It's the way Access is designed" is as good an answer as any. But note that I CAN use getusername(), it's ONLY the Forms!... references that cause .execute to fail.
-Bob
Mike, I guess "It's the way Access is designed" is as good an answer as any. But note that I CAN use getusername(), it's ONLY the Forms!... references that cause .execute to fail.
-Bob
you could:
dim qdf as querydef
Set qdf = CurrentDb.QueryDefs("qupdn otctmpafte r")
qdf.Parameters![PinTag]="' " & getusername() & "'"
qdf.execute
dim qdf as querydef
Set qdf = CurrentDb.QueryDefs("qupdn
qdf.Parameters![PinTag]="'
qdf.execute
ASKER
Mike, I don't understand (maybe because I've never used a parameter query).
[PinTag] is the name of a text field in the table. getusername() returns a string. The criterion for the query is [PinTag] = getusername()
Was that clear to you when you suggested the above qdf.Parameters code?
-Bob
[PinTag] is the name of a text field in the table. getusername() returns a string. The criterion for the query is [PinTag] = getusername()
Was that clear to you when you suggested the above qdf.Parameters code?
-Bob
currentdb.execute "UPDATE NoticeTemp SET NoticeTemp.Appt = (" & [Forms]![frmLtrAppt]![grpD