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 02:15:57 GMT

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));