By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,543 Members | 2,246 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,543 IT Pros & Developers. It's quick & easy.

Form/Query/Module Problem

P: 33
Ok folks its Friday & I'm feeling a bit thick (also not very well).

I have a form Salonmanagerdetail wich allows the user to enter the following details Stylist_Id,Stylist_Name,Group_Name & SM_Dates. SM_Dates are the dates a stylist is designated Salon Manager for (This happens every x weeks).

This data is stored in the table Salonmanagerdetail. Users can view this data via the View Stylist form which is a tabbed form with 3 tabs - Stylist Details, Comments & History & Salon Manager( This uses the select query Salonman).

All this works ok.

I have a module show details which populates the sub form viewbookings-subform which is on the form viewbookings. This pulls in data about a booking. What I would now like to do is add to this module so that if SM_Dates is equal to todays date then it populates the grid with "SALON MANAGER" for any SM_Dates that match the current date.

Below is the code of my module ShowDetails

Expand|Select|Wrap|Line Numbers
  1. Function ShowDetails(BookingNumber)
  2. On Error GoTo err_tag
  3. Dim cSQL As String, DEBUGTrueorFalse As Boolean
  4.  
  5.  
  6. 'Change value to switch debug mode on or off
  7. DEBUGTrueorFalse = True '\\ Change value
  8. '
  9.  
  10. If IsNull(BookingNumber) Then
  11.     Exit Function
  12. End If
  13.  
  14.  
  15.    cSQL = "SELECT [Booking].[Client_Id_No] & ' ' & [Booking_Client_Name] & Chr(13) & Chr(10) & [Booking_Contact_Number] & Chr(13) & Chr(10) & [Booking_Number] & ' ' & [Booking_Appoint_Type] AS ApptDetails, Booking.Booking_Number " & _
  16.         "FROM [Booking]" & _
  17.         "WHERE ((Booking.Booking_Number)=" & BookingNumber & ")"
  18. If DEBUGTrueorFalse = True Then Debug.Print cSQL
  19.  
  20.     Dim con As Object
  21.     Dim rst As Object
  22.     Dim stsql As String, rv As String, cQuals
  23.  
  24.     Set con = Application.CurrentProject.Connection
  25. If DEBUGTrueorFalse = True Then Debug.Print con
  26.     Set rst = CreateObject("ADODB.Recordset")
  27.     rst.Open cSQL, con, 1 ' 1 = adOpenKeyset
  28.  
  29.  
  30. If rst.EOF Then Exit Function
  31.  
  32.     rst.MoveFirst
  33.     ShowDetails = rst!apptdetails
  34. 'If DEBUGTrueorFalse = True Then Debug.Print rst!apptDetails
  35.  
  36. exit_tag:
  37.     rst.Close
  38.     Exit Function
  39. err_tag:
  40.     Select Case Err.Number
  41.     Case 2427
  42.         ' No value, carry on
  43.         ShowDetails = ""
  44.         Exit Function
  45.     Case Else
  46.         MsgBox Err.Number & " " & Err.Description
  47.         'Resume Next
  48.     End Select
  49. End Function 
Was thinking that maybe another If statement at IF ISNULL(BookingNumber) line of code. My coding skills aren't that great so I'm not sure what would work to accomplish this. I'm hoping this is do able as I would then be looking to expand this to include Holiday dates as well (possibly). Also would like to be able to show a message box if the stylist is designated as Salon Manager & also has a booking (isn't supposed to occur)
Sep 28 '07 #1
Share this Question
Share on Google+
8 Replies


P: 33
Ok folks its Friday & I'm feeling a bit thick (also not very well).

I have a form Salonmanagerdetail wich allows the user to enter the following details Stylist_Id,Stylist_Name,Group_Name & SM_Dates. SM_Dates are the dates a stylist is designated Salon Manager for (This happens every x weeks).

This data is stored in the table Salonmanagerdetail. Users can view this data via the View Stylist form which is a tabbed form with 3 tabs - Stylist Details, Comments & History & Salon Manager( This uses the select query Salonman).

All this works ok.

I have a module show details which populates the sub form viewbookings-subform which is on the form viewbookings. This pulls in data about a booking. What I would now like to do is add to this module so that if SM_Dates is equal to todays date then it populates the grid with "SALON MANAGER" for any SM_Dates that match the current date.

Below is the code of my module ShowDetails

Expand|Select|Wrap|Line Numbers
  1. Function ShowDetails(BookingNumber)
  2. On Error GoTo err_tag
  3. Dim cSQL As String, DEBUGTrueorFalse As Boolean
  4.  
  5.  
  6. 'Change value to switch debug mode on or off
  7. DEBUGTrueorFalse = True '\\ Change value
  8. '
  9.  
  10. If IsNull(BookingNumber) Then
  11.     Exit Function
  12. End If
  13.  
  14.  
  15.    cSQL = "SELECT [Booking].[Client_Id_No] & ' ' & [Booking_Client_Name] & Chr(13) & Chr(10) & [Booking_Contact_Number] & Chr(13) & Chr(10) & [Booking_Number] & ' ' & [Booking_Appoint_Type] AS ApptDetails, Booking.Booking_Number " & _
  16.         "FROM [Booking]" & _
  17.         "WHERE ((Booking.Booking_Number)=" & BookingNumber & ")"
  18. If DEBUGTrueorFalse = True Then Debug.Print cSQL
  19.  
  20.     Dim con As Object
  21.     Dim rst As Object
  22.     Dim stsql As String, rv As String, cQuals
  23.  
  24.     Set con = Application.CurrentProject.Connection
  25. If DEBUGTrueorFalse = True Then Debug.Print con
  26.     Set rst = CreateObject("ADODB.Recordset")
  27.     rst.Open cSQL, con, 1 ' 1 = adOpenKeyset
  28.  
  29.  
  30. If rst.EOF Then Exit Function
  31.  
  32.     rst.MoveFirst
  33.     ShowDetails = rst!apptdetails
  34. 'If DEBUGTrueorFalse = True Then Debug.Print rst!apptDetails
  35.  
  36. exit_tag:
  37.     rst.Close
  38.     Exit Function
  39. err_tag:
  40.     Select Case Err.Number
  41.     Case 2427
  42.         ' No value, carry on
  43.         ShowDetails = ""
  44.         Exit Function
  45.     Case Else
  46.         MsgBox Err.Number & " " & Err.Description
  47.         'Resume Next
  48.     End Select
  49. End Function 
Was thinking that maybe another If statement at IF ISNULL(BookingNumber) line of code. My coding skills aren't that great so I'm not sure what would work to accomplish this. I'm hoping this is do able as I would then be looking to expand this to include Holiday dates as well (possibly). Also would like to be able to show a message box if the stylist is designated as Salon Manager & also has a booking (isn't supposed to occur)
Was looking at expanding the statement at line 10 of the code & was looking at trying something like below. I am not sure if this would work -- I think I also need to declare SM_Dates etc.

If IsNull(BookingNumber) Then
If SM_Dates = date Then
ShowDetails = "SALON MANAGER"
End If
Exit Function
End If

Any help would be appreciated as this is one of the few things I have left to do with the project.
Oct 1 '07 #2

nico5038
Expert 2.5K+
P: 3,072
Hmm, your design is a bit confusing.
I would have expected to see a table tblStylist that's connected to a table holding the date (or date-range) the Stylist is acting as a SalonManager.
The objective of the Salonmanagerdetail table isn't clear to me either.
It would help when you describe the needed functionality just in layman's terms.
A description like "We need to record the customer visits and the stylist that performed the treatment, to get a view about employee productivity" would make clear what to record.

Nic;o)
Oct 6 '07 #3

P: 33
Cheers for the reply Nic,

I'll see if I can explain this better. The form stylistmanagerdetail allows a member of staff to enter the dates a stylist is assigned as Salon Manager. This data is then stored in the table Salonmanagerdetail. A stylist may be Salon Mamager more than once in hte acemic year. I have a query SalonMan which pulls out all the Stylists with SM_Date = todays date. for these vaules I wish to populate my view bookings subform with "Salon Manager"

What I want from there is to then get the data showing on my view bookings subform on my view bookings form. The view bookings subform looks like a grid. something like

Stylist1 Stylist2 Stylist3 Stylist4
Ann Jane Sally Sarah

9.00am a b c d

9.30am e

10.00am

10.30am



each part of the subform (a,b,c,d Etc) is populated in the showdetails module & populated with the data Client_Id_No, Booking_Client_Name, Booking_Contact_Number, Booking_Number, Booking_Appoint_Type. So if there was no bookings for Stylist1 & that Stylist was Salon Manager I want to see Salon Manager in each slot for that stylist from 9.30 to 10.30


The process happens when a user clicks on the view bookings button. On click this runs the module popbooking which does the follwing

Expand|Select|Wrap|Line Numbers
  1.  Sub popbooking()
  2. DoCmd.SetWarnings False
  3.     DoCmd.OpenQuery "1/1 - Delete BookingGrid" {Delete Query - deletes any    existing BookingGrid}
  4.     DoCmd.OpenQuery "1/2 - Create BookingGrid" {Creates a new BoookingGrid}
  5.     Populate_Stylists "" {Runs populate stylist module}
  6.     DoCmd.OpenQuery "1/3 Delete ClientGrid" {Deletes any existing ClientGrid}
  7.     DoCmd.OpenQuery "1/3a - Pull in data" {Make table query -Pulls In data}
  8.     DoCmd.OpenQuery "Append Booking Details" {Append Query}
  9.     PopulateControlHours TheBookingDate() '"05/09/07" {Runs the popluate contol hours module} 
  10.     DoCmd.SetWarnings True
  11. End Sub 
I hope I've been able to make this easier to understand.


What I'd also like to do is

if the booking is for 30 mins it populates the view bookings sub form for 1 booking slot (ie a on example grid)

if the booking is for 45mins - 1hr it populates the view bookings sub form for 2 booking slots (i.e a & e on eample grid above)
Oct 8 '07 #4

nico5038
Expert 2.5K+
P: 3,072
It's getting clearer, but I'm puzzled why you want to store "Salon Manager" in the "view bookings subform" empty slots.
When you record a SalonManager by the day, I would e.g. give the heading and/or the column a different background.

I do find it hard to see how you're going to realize this multi grid subform for a variable number of employees and how to enter the data.

Personally I created once a reservation system and used a modified calendar to show in different backcolor the availability of the people. Clicking on such a date (or date/time) can be used to show just one subform for entering the details.

Getting the idea ?

Nic;o)
Oct 8 '07 #5

P: 33
It's getting clearer, but I'm puzzled why you want to store "Salon Manager" in the "view bookings subform" empty slots.
When you record a SalonManager by the day, I would e.g. give the heading and/or the column a different background.

I do find it hard to see how you're going to realize this multi grid subform for a variable number of employees and how to enter the data.

Personally I created once a reservation system and used a modified calendar to show in different backcolor the availability of the people. Clicking on such a date (or date/time) can be used to show just one subform for entering the details.

Getting the idea ?

Nic;o)
Hi Nic --- storing "Salon Manager in the view bookings subform was just the way I was thinking of doing it. Am open to any way of showing a stylist as "Salon Manager" - giving the column a different background would work (I could also later adapt/use that to include stylists "On holiday"

As for the grid for a variable number of employees. some additional information --- we know the maximum size of a group is 29 stylists/students this year (biggest group -liable to be split into 2 sessions in the Salon -as the salon only has 14 chairs) The subform is made up of (in laymans terms) 7 stylists across the grid & the appointment times going down. For example in the sample data I have been using we have group level 3 red with 14 stylists. The first 7 are displayed in the view bookings subform - to view the next 7 we choose page 2 in a combobox on the main viewbookings form. We can also change the group via another combobox & show bookings for that group. We get a certain amount of stylists/students dropping out each year so the number in a group will usually drop rather than increase. Each group of stylists is only in for 1 day in the salon (same day each week)

We are using the viewbookings form & subform more or less as a visual grid so the receptionist can see what bookings we have for a group on the day they are in & also what appointment slots are free. At the bottom of the viewbookings form we have a set of command buttons which allow the user to add/amend/delete bookings & clients
Oct 9 '07 #6

P: 33
Hi Nic --- storing "Salon Manager in the view bookings subform was just the way I was thinking of doing it. Am open to any way of showing a stylist as "Salon Manager" - giving the column a different background would work (I could also later adapt/use that to include stylists "On holiday"

As for the grid for a variable number of employees. some additional information --- we know the maximum size of a group is 29 stylists/students this year (biggest group -liable to be split into 2 sessions in the Salon -as the salon only has 14 chairs) The subform is made up of (in laymans terms) 7 stylists across the grid & the appointment times going down. For example in the sample data I have been using we have group level 3 red with 14 stylists. The first 7 are displayed in the view bookings subform - to view the next 7 we choose page 2 in a combobox on the main viewbookings form. We can also change the group via another combobox & show bookings for that group. We get a certain amount of stylists/students dropping out each year so the number in a group will usually drop rather than increase. Each group of stylists is only in for 1 day in the salon (same day each week)

We are using the viewbookings form & subform more or less as a visual grid so the receptionist can see what bookings we have for a group on the day they are in & also what appointment slots are free. At the bottom of the viewbookings form we have a set of command buttons which allow the user to add/amend/delete bookings & clients
Just recalled why we were thinking of going down the "Salon Manager " in each slot route. This was because at a later date we were thinking of using background colours to indicate progress of an appointment i.e green-arrived, amber - in Salon & red - completed
Oct 9 '07 #7

nico5038
Expert 2.5K+
P: 3,072
Good thought (about the coloring of the appointments), but using "Bold" or "Italics" for the Salonmanager column would be another option. It's the marking that's important, but only when it tells the user usefull information.

Still I think you need to start with designing the basic dataentry forms and envision the workprocess into detail. Keep in mind that a subform with multiple (variable) columns and dataentry in there won't be easy to handle without using some "heavy" coding and "tricked" unbound subforms.
Personally I prefer to have a graphical overview and when clicking e.g. a date/time section a filling of the subform to enter the details for the clicked section, e.g. for an appointment.

When appointments have a minimum time granularity of 5 minutes, then having this as separate "boxes" will allow the user to click on a starttime and an endtime and thus visually set the appointment time and prevent "overlapping" appointments by refusing to set a start (or end) marker on already colored "boxes".

Just some thoughts on to make the realisation easier...

Nic;o)
Oct 10 '07 #8

P: 33
Good thought (about the coloring of the appointments), but using "Bold" or "Italics" for the Salonmanager column would be another option. It's the marking that's important, but only when it tells the user usefull information.

Still I think you need to start with designing the basic dataentry forms and envision the workprocess into detail. Keep in mind that a subform with multiple (variable) columns and dataentry in there won't be easy to handle without using some "heavy" coding and "tricked" unbound subforms.
Personally I prefer to have a graphical overview and when clicking e.g. a date/time section a filling of the subform to enter the details for the clicked section, e.g. for an appointment.

When appointments have a minimum time granularity of 5 minutes, then having this as separate "boxes" will allow the user to click on a starttime and an endtime and thus visually set the appointment time and prevent "overlapping" appointments by refusing to set a start (or end) marker on already colored "boxes".

Just some thoughts on to make the realisation easier...

Nic;o)

Thanks for your input Nic --- thing is we have all the forms done & everything is working well (ok not all of it but most). The Salon Manager & filling in the correct amount of boxes based on the appointment duration are the last two real functional things we want to include before we let this db "go live"

I have found it hard to explain how it all works (my supervisor has done some of this work) . Think I'll have to pin him down & see what his ideas on these 2 remaining parts is. Without seeing this its quite hard to explain how it works
The whole Modpopbooking module runs when you click on a cmd button to view bookings
Oct 10 '07 #9

Post your reply

Sign in to post your reply or Sign up for a free account.