I'm struggling with my limited knowledge of code

  • Thread starter Thread starter Kate
  • Start date Start date
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));
 
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.
 
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...
 
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...
 
Back
Top