A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » New Users
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Passing parms to a query?



 
 
Thread Tools Display Modes
  #1  
Old May 7th, 2004, 04:06 PM
Brian
external usenet poster
 
Posts: n/a
Default Passing parms to a query?

If I have a query such as :
PARAMETERS NEWID IEEEDouble;
UPDATE AVAILABILITY SET AVAILABILITY.[Booking ID] = NewID
WHERE (((AVAILABILITY.BookingDate)=Forms![SINGLE BOOKING
AVAILABILITY]!BookingDate) And ((AVAILABILITY.Period)
=Forms![SINGLE BOOKING AVAILABILITY]!Combo8) And
((AVAILABILITY.Room)=Forms![SINGLE BOOKING AVAILABILITY]!
Combo10));

and in my macro I want to pass a parameter then what
would be the format?

i.e. where would it go in the following code:
stDocName = "UpdateAv"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Thanks in advance
  #2  
Old May 10th, 2004, 01:00 PM
Nikos Yannacopoulos
external usenet poster
 
Posts: n/a
Default Passing parms to a query?

Brian,

It depends where the parameter values come from.

If, for example, they are read from controls on an open form, then the
easiest way is to add references to them in the Update To expressions in
your saved query, like
Forms!FormName!ControlName

If, on the other hand, the parameter values are only available in code, I
would build the SQL expression for the update query in the code, where it
can read the parameters value as well as read values for the Where clause,
and execute it from within the code, without using a saved query, something
like:

strSQL = "UPDATE AVAILABILITY SET AVAILABILITY.[Booking ID] = '" & NewID &
"'"
strSQL = strSQL & " WHERE (((AVAILABILITY.BookingDate)="
strSQL = strSQL & Forms![SINGLE BOOKING AVAILABILITY]!BookingDate)
strSQL = strSQL & " And ((AVAILABILITY.Period) ="
strSQL = strSQL & " Forms![SINGLE BOOKING AVAILABILITY]!Combo8 & ")"
strSQL = strSQL & " And ((AVAILABILITY.Room)="
strSQL = strSQL & Forms![SINGLE BOOKING AVAILABILITY]! Combo10 & "));"
DoCmd.RunSQL (strSQL)

Just mind the use of quotes around form control references, depending on
whether they are text or not. Above I have assumed that only the NewID field
is text.

HTH,
Nikos

"Brian" wrote in message
...
If I have a query such as :
PARAMETERS NEWID IEEEDouble;
UPDATE AVAILABILITY SET AVAILABILITY.[Booking ID] = NewID
WHERE (((AVAILABILITY.BookingDate)=Forms![SINGLE BOOKING
AVAILABILITY]!BookingDate) And ((AVAILABILITY.Period)
=Forms![SINGLE BOOKING AVAILABILITY]!Combo8) And
((AVAILABILITY.Room)=Forms![SINGLE BOOKING AVAILABILITY]!
Combo10));

and in my macro I want to pass a parameter then what
would be the format?

i.e. where would it go in the following code:
stDocName = "UpdateAv"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Thanks in advance



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 08:35 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.