Form/Query/Module Problem

**glamster7** · Oct 1 '07, 09:06 AM

Originally posted by glamster7

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

I have a form Salonmanagerdet ail wich allows the user to enter the following details Stylist_Id,Styl ist_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 Salonmanagerdet ail. 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

Code:

Function ShowDetails(BookingNumber)
On Error GoTo err_tag
Dim cSQL As String, DEBUGTrueorFalse As Boolean


'Change value to switch debug mode on or off
DEBUGTrueorFalse = True '\\ Change value
'

If IsNull(BookingNumber) Then
    Exit Function
End If
    

   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 " & _
        "FROM [Booking]" & _
        "WHERE ((Booking.Booking_Number)=" & BookingNumber & ")"
If DEBUGTrueorFalse = True Then Debug.Print cSQL

    Dim con As Object
    Dim rst As Object
    Dim stsql As String, rv As String, cQuals
    
    Set con = Application.CurrentProject.Connection
If DEBUGTrueorFalse = True Then Debug.Print con
    Set rst = CreateObject("ADODB.Recordset")
    rst.Open cSQL, con, 1 ' 1 = adOpenKeyset
    

If rst.EOF Then Exit Function

    rst.MoveFirst
    ShowDetails = rst!apptdetails
'If DEBUGTrueorFalse = True Then Debug.Print rst!apptDetails

exit_tag:
    rst.Close
    Exit Function
err_tag:
    Select Case Err.Number
    Case 2427
        ' No value, carry on
        ShowDetails = ""
        Exit Function
    Case Else
        MsgBox Err.Number & " " & Err.Description
        'Resume Next
    End Select
End Function

Was thinking that maybe another If statement at IF ISNULL(BookingN umber) 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(BookingN umber) 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.

**nico5038** · Oct 6 '07, 01:25 PM

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 Salonmanagerdet ail 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)

**glamster7** · Oct 8 '07, 12:28 PM

Cheers for the reply Nic,

I'll see if I can explain this better. The form stylistmanagerd etail allows a member of staff to enter the dates a stylist is assigned as Salon Manager. This data is then stored in the table Salonmanagerdet ail. 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

Code:

 Sub popbooking()
DoCmd.SetWarnings False
    DoCmd.OpenQuery "1/1 - Delete BookingGrid" {Delete Query - deletes any    existing BookingGrid}
    DoCmd.OpenQuery "1/2 - Create BookingGrid" {Creates a new BoookingGrid}
    Populate_Stylists "" {Runs populate stylist module}
    DoCmd.OpenQuery "1/3 Delete ClientGrid" {Deletes any existing ClientGrid}
    DoCmd.OpenQuery "1/3a - Pull in data" {Make table query -Pulls In data}
    DoCmd.OpenQuery "Append Booking Details" {Append Query}
    PopulateControlHours TheBookingDate() '"05/09/07" {Runs the popluate contol hours module} 
    DoCmd.SetWarnings True
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)

**nico5038** · Oct 8 '07, 07:29 PM

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)

**glamster7** · Oct 9 '07, 08:29 AM

Originally posted by nico5038

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

**glamster7** · Oct 9 '07, 08:34 AM

Originally posted by glamster7

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

**nico5038** · Oct 10 '07, 09:36 AM

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 "overlappin g" 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)

**glamster7** · Oct 10 '07, 10:40 AM

Originally posted by nico5038

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 "overlappin g" 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