Re: I'm struggling with my limited knowledge of code

From: tina (nospam_at_address.com)
Date: 04/30/04 

Date: Fri, 30 Apr 2004 04:06:01 GMT

yes, i got the SQL syntax wrong - thanks Van for bringing that to my
attention. fixed syntax for Period and Room as number values is:

strSQL = "SELECT BookingDate, Period, Room, [Day Number], [Booking ID] " _
     & "FROM AVAILABILITY WHERE BookingDate = #" _
     & Forms![SINGLE BOOKING AVAILABILITY]!BookingDate _
     & "# And Period = " & Forms![SINGLE BOOKING AVAILABILITY]!Combo8 _
     & " And Room = " & Forms![SINGLE BOOKING AVAILABILITY]!Combo10

and for text values is:

strSQL = "SELECT BookingDate, Period, Room, [Day Number], [Booking ID] " _
     & "FROM AVAILABILITY WHERE BookingDate = #" _
     & Forms![SINGLE BOOKING AVAILABILITY]!BookingDate _
     & "# And Period = '" & Forms![SINGLE BOOKING AVAILABILITY]!Combo8 _
     & "' And Room = '" & Forms![SINGLE BOOKING AVAILABILITY]!Combo10 _
     & "'"

"tina" <nospam@address.com> wrote in message
news:xFikc.5314$Xj6.80806@bgtnsc04-news.ops.worldnet.att.net...
> you actually got the right answer from Marshall Barton in your posted
thread
> started at 12:45 pm this date. (fyi, usually best to stick to one thread
for
> the same problem, so others can see what's been said, done and tried
> already - 6 threads in 3 hours is way too many) Marshall's answer didn't
> work for you because, not seeing the query SQL statement you posted in a
> later thread, he couldn't advise how to handle a query with parameters.
> here's how:
>
> Dim db As DAO.Database
> Dim rs As DAO.Recordset, strSQL As String
>
> strSQL = "SELECT BookingDate, Period, Room, [Day Number], [Booking ID] " _
> & "FROM AVAILABILITY WHERE BookingDate = #" _
> & Forms![SINGLE BOOKING]!AVAILABILITY!BookingDate _
> & "# And Period = " & Forms![SINGLE BOOKING]!AVAILABILITY!Combo8 _
> & " And Room = " & Forms![SINGLE BOOKING]!AVAILABILITY!Combo10
>
> Set db = CurrentDb()
> Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
>
> If rs![Booking ID] = 1 Then
> 'put here the code you need to run
> End If
>
> rs.Close
> Set rs = Nothing
> Set db = Nothing
>
> the above SQL statement is written assuming that Period and Room are of
> number data type. if a value is text, single quotes must be included
inside
> the double quotes, as
>
> strSQL = "SELECT BookingDate, Period, Room, [Day Number], [Booking ID] " _
> & "FROM AVAILABILITY WHERE BookingDate = #" _
> & Forms![SINGLE BOOKING]!AVAILABILITY!BookingDate _
> & "# And Period = '" & Forms![SINGLE BOOKING]!AVAILABILITY!Combo8 _
> & "' And Room = '" & Forms![SINGLE BOOKING]!AVAILABILITY!Combo10 _
> & "'"
>
> btw, you'll find it much easier in future to refer to your tables,
queries,
> forms, controls, etc if you don't name them using spaces or special
> characters in the names. [Booking ID], for instance, is easier when named
> BookingID, [SINGLE BOOKING] as SingleBooking, etc.
>
>
> pasted from previous thread:
>
> ******
> Katie wrote:
>
> >I have the following code attached to a form button and
> >get run-time error Object Required. What is the problem?
> >Is the record I get in executing the query not available
> >in this macro? How do I get around this? Thanks.
> >
> >Private Sub Command14_Click()
> >
> >DoCmd.OpenQuery "QueryAv"
> >
> >If AVAILABILITY.[Booking ID] = 1 Then
> > 'Hide availability form
> > Forms![SINGLE BOOKING AVAILABILITY].Visible = False
> > 'Open the detail form
> > DoCmd.OpenForm "SINGLE BOOKING DETAIL"
> >End If
> >
> >Exit_Command14_Click:
> > Exit Sub
> >
> >End Sub
>
>
> I think you want to open a recordset on the query instead of
> displaying the query on the screen.
>
> Dim db As DAO.Database
> Dim rs As DAO.Recordset
>
> Set db = CurrentDb()
> Set rs = db.OpenRecordset("QueryAv")
>
> If rs![Booking ID] = 1 Then
> DoCmd.OpenForm "SINGLE BOOKING DETAIL"
> End If
>
> rs.Close : Set rs = Nothing
> Set db = Nothing
> --
> Marsh
> MVP [MS Access]
>
>
>
> Tried that and get the message too few parameters expected
> 3. The following statement gets highlighted:
>
> Set rs = db.OpenRecordset("QueryAv")
>
> Any idea? Thanks
>
>
>
> *******
>
>
>
>
>
> "Kate" <anonymous@discussions.microsoft.com> wrote in message
> news:63f701c42e3d$b10fec30$a301280a@phx.gbl...
> > I really need help here! I have a button on a form with
> > the code below. It doesn't work because AVAILABILITY.
> > [Booking ID] isn't accessible. How can I solve this
> > problem?
> >
> > Option Compare Database
> >
> > Private Sub Command14_Click()
> > 'On Error GoTo Err_CheckAvail_Click
> >
> > DoCmd.OpenQuery "QueryAv"
> >
> > DoCmd.Close acQuery, "QueryAv"
> >
> > If AVAILABILITY.[Booking ID] = 1 Then
> > ' 'Close query
> > DoCmd.Close acQuery, "QueryAv"
> > ' 'Hide availability form
> > Forms![SINGLE BOOKING AVAILABILITY].Visible = False
> > ' 'Open the detail form
> > DoCmd.OpenForm "SINGLE BOOKING DETAIL"
> > ' 'Show new form
> > Forms![SINGLE BOOKING DETAIL].Visible = True
> > ' 'DoCmd.Close acForm, "SINGLE BOOKING AVAILABILITY"
> > End If
> >
> > Exit_Command14_Click:
> > Exit Sub
> >
> > Err_CheckAvail_Click:
> > MsgBox Err.Description
> > Resume Exit_Command14_Click
> >
> > End Sub
> >
> > QueryAv is:
> > SELECT AVAILABILITY.BookingDate, AVAILABILITY.Period,
> > AVAILABILITY.Room, AVAILABILITY.[Day Number], AVAILABILITY.
> > [Booking ID]
> > FROM AVAILABILITY
> > 'WHERE (((AVAILABILITY.BookingDate)=Forms![SINGLE BOOKING
> > AVAILABILITY]!BookingDate) And ((AVAILABILITY.Period)
> > =Forms![SINGLE BOOKING AVAILABILITY]!Combo8) And
> > ((AVAILABILITY.Room)=Forms![SINGLE BOOKING AVAILABILITY]!
> > Combo10));
>
>