I'm struggling with my limited knowledge of code

K

Kate

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

PC Datasheet

Kate,

Just want you to know that I am available on a paid assistance basis if you want
to go that way. My fees are very reasonable.
 
T

tina

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 said:
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
Click to expand...


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 said:
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));
Click to expand...
 
T

tina

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 said:
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 said:
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
Click to expand...


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 said:
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));
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Using recordset I get errors 4
Update not working 1
Passing parms to a query? 1
RunSQL asking for a parameter 2
Why doesn't this update work? 2
Why no update? Help! 1
No rows returned. Help! 1
Too few parameters error. Help needed 1

Top