Is it possible 1

[Assassin0UK]

I have a problem I'm looking to solve, and not sure about the best way to do it.

I have a booking system where the user will be booking in up to 10 lessons per customer each week. I'm trying to come up with a method of making this easy in one step. rather than making the user manually input 10 entries at a time.

I thought of having a form which the user can fill in which will allow for multiple entries. I had the idea of giving one date field for the week commencing.. (validate this by checking its a Monday) Then a series of 10 check boxes, 2 for each day (Morning and Afternoon).
I would also have a combo box giving a drop down list of customer where they can select the customer name.

the form would also have a "Book Lessons" button.


What I want to happen now is for 10 seperate entries to be made in the bookings tables.

The bookings table has fields for
BookingID (Autonumber) PK
CustomerID
LessonDate
SlotID (morning or afternoon)

I'm not sure how to achieve this with access. I was thinking along the lines of a for loop, if the corresponding value is checked save the entry, if not ignore. the date can be incremented via the loop. I've no idea if this can be done in Access or how to achieve it. An alternative would be to create a new table with one record containing all the data and then somehow split it, I was reading a post further down the forum which did something similar using a split function.

Any ideas on the best approach for this problem.

Thanks

 

[MajP]

I think if I was going to do it I would do something like you said. The main form would be bound to your persons table. On the main form I would put a calendar control, but basically no matter what day you click it will return the Monday of that week which is easy to do.
The subform needs to be unbounded, and like you said it has five days and 10 checkboxes. By clicking the calendar control on the main form, it will control which week is shown.
If you change the person or week in the mainform, your code will read the booking table and populate any existing checks for that date. If you click on an existing check it will delete that record from the table. After you are done all dates with checks will be written to the booking table.
If you are comfortable with DAO or ADO recordsets, this should be rather easy to code. If you go this approach I can help you with the code. I just helped someone with a similar booking system on this site, with a similar approach.

 

[Assassin0UK]

I'm not really happy with anything much in access sort of learning it as I'm doing it at the moment, but I'm definitely willing to try and learn anything.

I never thought about reading back in the weeks data, I like that idea.

I'll have a look for the other thread you mentioned.

but any other help you can give me will be great.. Thanks

 

[MajP]

If you are still interested, I did some work on this. Disregard what I said about using a subform.
I just built a form with a combo box, a calendar control, 5 text boxes, and 10 checkboxes. The combobox's rowsource is your People. I show the persons name, but bind the PersonID. The calendar control controls which week is shown. No matter what day you click it returns the Monday of that week. The text boxes are labled "Monday",..."Friday". The text box value is the days date: locked and disabled. Next to each text box is a Morning and Afternoon checkbox. I used this naming convention

txtBxMon, chkMonMorn, chkMonAft
...
txtBxFri, chkFriMorn, chkFriAft

Private Sub calWeek_AfterUpdate()
  Me.calWeek = Me.calWeek + 2 - Weekday(Me.calWeek)
  Call loadWeek(Me.calWeek.Value)
  Call loadChecks
End Sub

Public Sub loadWeek(dtmMonday As Date)
  Me.txtBxMon = dtmMonday
  Me.txtBxTues = dtmMonday + 1
  Me.txtBxWed = dtmMonday + 2
  Me.txtBxThurs = dtmMonday + 3
  Me.txtBxFri = dtmMonday + 4
End Sub

Public Sub loadChecks()
  Dim intPersonID As Integer
  Dim dtmMonday As Date
  Dim strSqlPerson As String
  Dim strSqlDay As String
  Dim strSqlMorning As String
  Dim strSqlAfternoon As String
  Dim strSql As String
  If Trim(Me.cmboPerson & " ") = "" Then
    MsgBox "Enter a Customer"
    Exit Sub
  End If
  intPersonID = Me.cmboPerson.Value
  dtmMonday = Me.calWeek.Value
  strSqlPerson = "CustomerID = " & intPersonID
  strSqlMorning = " AND SlotID = 'Morning'"
  strSqlAfternoon = " AND SlotID = 'Afternoon'"
  strSqlDay = " AND LessonDate = "
  'Mon Morn
  strSql = strSqlPerson & strSqlMorning & strSqlDay & SQLDate(Me.txtBxMon.Value)
  Me.chkMonMorn = checkValue(strSql)
  'Mon Aft
  strSql = strSqlPerson & strSqlAfternoon & strSqlDay & SQLDate(Me.txtBxMon.Value)
  Me.chkMonAft = checkValue(strSql)
  'Tues Morn
  strSql = strSqlPerson & strSqlMorning & strSqlDay & SQLDate(Me.txtBxTues.Value)
  Me.chkTuesMorn = checkValue(strSql)
  'Tues Aft
  strSql = strSqlPerson & strSqlAfternoon & strSqlDay & SQLDate(Me.txtBxTues.Value)
  Me.chkTuesAft = checkValue(strSql)
  'Wed Morn
  strSql = strSqlPerson & strSqlMorning & strSqlDay & SQLDate(Me.txtBxWed.Value)
  Me.chkWedMorn = checkValue(strSql)
  'Wed Aft
  strSql = strSqlPerson & strSqlAfternoon & strSqlDay & SQLDate(Me.txtBxWed.Value)
  Me.chkWedAft = checkValue(strSql)
   'Thurs Morn
  strSql = strSqlPerson & strSqlMorning & strSqlDay & SQLDate(Me.txtBxThurs.Value)
  Me.chkThursMorn = checkValue(strSql)
  'Tues Aft
  strSql = strSqlPerson & strSqlAfternoon & strSqlDay & SQLDate(Me.txtBxThurs.Value)
  Me.chkThursAft = checkValue(strSql)
  'Tues Morn
  strSql = strSqlPerson & strSqlMorning & strSqlDay & SQLDate(Me.txtBxFri.Value)
  Me.chkFriMorn = checkValue(strSql)
  'Tues Aft
  strSql = strSqlPerson & strSqlAfternoon & strSqlDay & SQLDate(Me.txtBxFri.Value)
  Me.chkFriAft = checkValue(strSql)
End Sub
 Function SQLDate(varDate As Variant) As String
   If IsDate(varDate) Then
      SQLDate = "#" & Format$(varDate, "yyyy-mm-dd") & "#"
   End If
 End Function

Public Function checkValue(strSql As String) As Boolean
  checkValue = Not (Nz(DLookup("BookingID", "tblBooking", strSql)) = 0)
End Function

Private Sub cmboPerson_AfterUpdate()
  Call loadChecks
End Sub

This is part one. It selects the weeks and customers and loads the checks for that customer and that date. Next step is to read the checks and update the table. Get this done first.
The final procedure is the update your table based on the checks selected. I did not get around to that yet.

 

[Assassin0UK]

Many thanks for that,

I'll get the page built and test out what you've provided so far, it make its so much easy to learn when you can see how things knit together.

 

[MajP]

Here is the rest

Private Sub chkMonAft_AfterUpdate()
   Call addDeleteCheck(Me.txtBxMon, "Afternoon", Me.chkMonAft.Value)
End Sub

Private Sub chkMonMorn_AfterUpdate()
  Call addDeleteCheck(Me.txtBxMon, "Morning", Me.chkMonMorn.Value)
End Sub

Private Sub chkTuesAft_AfterUpdate()
  Call addDeleteCheck(Me.txtBxTues, "Afternoon", Me.chkTuesAft.Value)
End Sub

Private Sub chkTuesMorn_AfterUpdate()
  Call addDeleteCheck(Me.txtBxTues, "Morning", Me.chkTuesMorn.Value)
End Sub

Private Sub cmboPerson_AfterUpdate()
  Call loadChecks
End Sub

Private Sub Form_Load()
  Call loadWeek(Me.calWeek.Value)
End Sub


Public Sub addDeleteCheck(dtmLessonDate As Date, strSlotID As String, blnSelected As Boolean)
  Dim intPersonID As Integer
  Dim rs As DAO.Recordset
  Dim strSqlPerson As String
  Dim strSqlDay As String
  Dim strSqlSlot As String
  Dim strSql As String
  If Trim(Me.cmboPerson & " ") = "" Then
    MsgBox "Enter a Customer"
    Exit Sub
  End If
  intPersonID = Me.cmboPerson.Value
  strSqlPerson = "CustomerID = " & intPersonID
  strSqlSlot = " AND SlotID = '" & strSlotID & "'"
  strSqlDay = " AND LessonDate = " & SQLDate(dtmLessonDate)
  
  Set rs = CurrentDb.OpenRecordset("tblBooking", dbOpenDynaset)
  strSql = strSqlPerson & strSqlSlot & strSqlDay
    rs.FindFirst (strSql)
      If blnSelected And rs.NoMatch Then
        Call addCheck(rs, intPersonID, dtmLessonDate, strSlotID)
      ElseIf Not (Me.chkMonMorn) And Not rs.NoMatch Then
        rs.Delete
      End If
    End Sub

Public Sub addCheck(rs As DAO.Recordset, thePersonID As Integer, theDate As Date, SlotID As String)
  rs.AddNew
   rs.Fields("CustomerID") = thePersonID
   rs.Fields("LessonDate") = theDate
   rs.Fields("SlotID") = SlotID
 rs.Update
End Sub

you will have to repeat below for wed, thurs, and fri

Private Sub chkTuesAft_AfterUpdate()
  Call addDeleteCheck(Me.txtBxTues, "Afternoon", Me.chkTuesAft.Value)
End Sub

Private Sub chkTuesMorn_AfterUpdate()
  Call addDeleteCheck(Me.txtBxTues, "Morning", Me.chkTuesMorn.Value)
End Sub

This works fine, but it was more complicated than I was thinking. Maybe someone has a better approach.

 

[Assassin0UK]

Ok I've now got that working now. I had a few problems to overcome which took me a while to suss out.

I firstly had to learn how your code worked. LOL

The first problem I had I finally tracked down to the DLookup in the checkValue Function. The criteria for the DLookup was looking for 'Morning' but my slot descriptions are held in a seperate table where i've also included the slot start and end times, costs etc. So its actually an integer value that link the two tables. I just had to edit the following lines to this.

  strSqlMorning = " AND SlotID = 1"
  strSqlAfternoon = " AND SlotID = 2"

another problem I found was when the form initially loads the txtbxMon etc are blank, the user can fill them in by selecting a date.

I've using a date entry text box (for now) which I grab and convert to the monday of that week. This then calls your code to fill in the dates. I've not got as far as doing a calendar control yet.

The above works fine and I get the message to select a customer if its blank, which it is when it first loads.

The second problem came if I firstly selected a name from the combo prior to setting the date. The username update triggers the code, but if the date hasn't already been set then the Dlookup criteria fails due to the 5 date boxes not yet having been filled in.

I got around this by firstly setting a default date in the date box, using =date() and also getting the after_Update on the name combo to also run the loadweek to ensure the values are updated prior to So on first pass if the user forgets to select a date, todays date is added and the dates for Mon -Fri of this week are automatically filled in.

I hope that makes sense.

I've just manually added a few entries to the bookings table and at the moment its not picking them up, so I'm just working through trying to suss out why its always returning false from the checkValue Function.

Anyway thanks for your time and I'm really impressed with the solution so far. I can't wait for the next installment.

Cheers

 

[MajP]

why its always returning false from the checkValue Function

That means the dlookup below:

checkValue = Not (Nz(DLookup("BookingID", "tblBooking", strSql)) = 0)

is not finding a record. There is a lot of names for fields, values, and tables that will have to match between what I did and your naming convention. Put in a
Msgbox strSql
to see if the Criteria makes sense and matches what you have.

 

[Assassin0UK]

thanks for the second half of the solution. I've just spotted it now.

I've been sending MsgBox 's up everywhere all night. LOL

It is returning NULL everytime. even for the records I've filled in. I've checked the table names and field names and it seems ok. I'm still working through debugging it.

 

[Assassin0UK]

sorted it now.

I had changed the format of the date to match the format in my table, dd-mm-yyyy, I put it back to the way you posted it yyyy-mm-dd and it started working.

Thats what I deserved for fiddling. LOL


Just before I found that I was looking what the NZ did and noticed on MS website the format they show is Nz(variant, [valueifnull])

they show a comma between the parameters. yet you use an = sign. Yours works though, changing it to a comma gives a compile error. - Typical Microsoft LOL I take it it should always be an equal sign then.

 

[MajP]

debug.print your strSql and then try to put it in by hand. Here is mine for Monday Am:

CustomerID = 63 AND SlotID = 'Morning' AND LessonDate = #2006-05-01#

Now try
dlookup("BookingID", "tblBooking", "CustomerID = 63 AND SlotID = 'Morning' AND LessonDate = #2006-05-01#")

 

[Assassin0UK]

Thanks for that Majp

I had managed to sort it out. see my post above yours.

 

[MajP]

With the NZ the second parameter is the return value if it is null. If you do not use a second parameter the default is 0. Therefore if there is no record

dlookup(fldname,tablename,criteria)
will return a null

nz(dlookup(fldname,tablename,criteria))
converts the null to zero

and
nz(dlookup(fldname,tablename,criteria)) = 0
is true since 0 = 0

The equal sign is outside the parentheses.

 

[Assassin0UK]

sorry I missed the 0 = 0 I assumed the =0 was the value if Null

Thanks for the explaination it makes sense now.

 

[Assassin0UK]

sorry for the delay in getting back to you.

I've added the second section of code and I don't seem to be able to get it to run now.

I get the following error now.

The expression After Update you entered as the event property setting produced the following error: User-defined type not defined.

* The expression may not result in the name of a macro, the name of a user-defined function, or [event procedure].
* There may have been am error evaluating the function, event, or macro.

The message is instant as soon as I try to do anything and it stops all code running even the stuff that was working before.

I've tried adding breakpoints but it fails before anything runs.

I've also tried commenting out sections and it starts to run from the point I disable the addCheck section so it looks like its something in there that its not happy about.


Thanks again

 

[PHV]

When in VBE (Ctrl+G) menu Tools -> References ...
Tick the Microsoft DAO 3.# Object Library

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Assassin0UK]

Thanks PHV thats sorted it

and a really big thank you to MajP. It works like a charm, and is just what I was after. Thanks