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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|