Help Very frustrated with RunSQL

  • Thread starter Thread starter Kate
  • Start date Start date
K

Kate

I want to set the follwoing SQL up as a string so that I
can do a DoCmd.RunSQL strSQL but I keep getting syntax
and other errors. What should this look like:

Update AVAILABILITY
Set AVAILABILITY.[Bokking ID] = BOOKING.[Booking ID]
WHERE (((AVAILABILITY.BookingDate)=Forms![SINGLE BOOKING
AVAILABILITY]!BookingDate) And ((AVAILABILITY.Period)
=Forms![SINGLE BOOKING AVAILABILITY]!Combo8) And
((AVAILABILITY.Room)=Forms![SINGLE BOOKING AVAILABILITY]!
Combo10));

Thanks for your help
 
This will depend on the data types of your fields but the following might
work if Period is numeric and Room is text.
Dim strSQL as String
strSQL = "Update AVAILABILITY " & _
"Set AVAILABILITY.[Bokking ID] = BOOKING.[Booking ID] " & _
"WHERE AVAILABILITY.BookingDate=#" & _
Forms![SINGLE BOOKING AVAILABILITY]!BookingDate & _
"# And AVAILABILITY.Period=" & Forms![SINGLE BOOKING
AVAILABILITY]!Combo8 & _
" And AVAILABILITY.Room=""" & Forms![SINGLE BOOKING
AVAILABILITY]!Combo10 & """"
DoCmd.RunSQL strSQL

Also, do yourself a big favor and rename your combo boxes to something like
cboPeriod and cboRoom. I don't see the Booking table in your SQL anywhere.
This would be required.
 
Kate said:
I want to set the follwoing SQL up as a string so that I
can do a DoCmd.RunSQL strSQL but I keep getting syntax
and other errors. What should this look like:

Update AVAILABILITY
Set AVAILABILITY.[Bokking ID] = BOOKING.[Booking ID]
WHERE (((AVAILABILITY.BookingDate)=Forms![SINGLE BOOKING
AVAILABILITY]!BookingDate) And ((AVAILABILITY.Period)
=Forms![SINGLE BOOKING AVAILABILITY]!Combo8) And
((AVAILABILITY.Room)=Forms![SINGLE BOOKING AVAILABILITY]!
Combo10));

Thanks for your help
Click to expand...

Kate,

UPDATE AVAILABILITY
SET AVAILABILITY.[Bokking ID] = BOOKING.[Booking ID]
WHERE (((AVAILABILITY.BookingDate) = Forms![SINGLE BOOKING
AVAILABILITY]!BookingDate)
AND ((AVAILABILITY.Period) = Forms![SINGLE BOOKING
AVAILABILITY]!Combo8)
AND ((AVAILABILITY.Room) = Forms![SINGLE BOOKING
AVAILABILITY]!Combo10)
);

Try changing:

"AVAILABILITY.[Bokking ID]", to:
"AVAILABILITY.[Booking ID]"

There appears to be a one-letter error.

Also, as has already been noted, the table BOOKING is not referenced.


Sincerely,

Chris O.
 
Back
Top