If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
I'm struggling with my limited knowledge of code
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)); |
#2
|
|||
|
|||
I'm struggling with my limited knowledge of code
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. -- PC Datasheet Your Resource For Help With Access, Excel And Word Applications www.pcdatasheet.com "Kate" wrote in message ... 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)); |
#3
|
|||
|
|||
I'm struggling with my limited knowledge of code
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" wrote in message ... 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)); |
#4
|
|||
|
|||
I'm struggling with my limited knowledge of code
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" wrote in message ... 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" wrote in message ... 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)); |
Thread Tools | |
Display Modes | |
|
|