Schedulig question

[Steve Goodrich]

We have a database built in Access 97 that is used to book people into our
building,

There are two date fields on the form, Date from & Date To. There are
queries/Reports set up so you can view any days visitors.

This works great if the visitor is expected between two set dates –e.g.
1/3/10 to 31/3/10



On occasions a visitor will need booking in on Monday of each week for two
months or Mon & Tues for six months.



I have a duplicate button on my form which is useful for say two or three
entries, but not very convenient for twenty entries.



My question is this: Is there a way the user could select the days of the
week the visitor is expected, after the two date fields have been completed.



So if the visitor was due every Monday in March and April, the user could
select:

Date from 1/3/10. Date To 30/4/10, then be prompted for the days of the
week, so the user could select Mon only.



Hope this makes sense



Steve

 

[PieterLinden via AccessMonster.com]

We have a database built in Access 97 that is used to book people into our
building,

There are two date fields on the form, Date from & Date To. There are
queries/Reports set up so you can view any days visitors.

This works great if the visitor is expected between two set dates –e.g.
1/3/10 to 31/3/10

On occasions a visitor will need booking in on Monday of each week for two
months or Mon & Tues for six months.

I have a duplicate button on my form which is useful for say two or three
entries, but not very convenient for twenty entries.

My question is this: Is there a way the user could select the days of the
week the visitor is expected, after the two date fields have been completed.

So if the visitor was due every Monday in March and April, the user could
select:

Date from 1/3/10. Date To 30/4/10, then be prompted for the days of the
week, so the user could select Mon only.

Hope this makes sense

Steve

Steve,

Here's the way I solved the problem... (if you want me to e-mail the database
to you, I can.)
1. created a table VisitDates (VisitorID, VisitDate)
2. created an UN-bound form, with the following controls
- cboVisitorID (combobox populated from Visitor table)
- txtStartDate
- txtEndDate
- checkboxes for Mon-Fri (chkMon...chkFri)
- a button to create records, cmdCreateApptDates

Here's the code...


Option Compare Database
Option Explicit

'-----------------------------------------------------------------------------
----------
' Procedure : cmdCreateApptDates_Click
' Author : Pieter
' Date : 3/24/2010
' Purpose : Grabs start date and end date of a date range from the form
' then populates the table VisitDates with the (VisitorID,
VisitDate)
' for every date between the given range
'-----------------------------------------------------------------------------
----------
'
Private Sub cmdCreateApptDates_Click()

Dim strSQL As String
Dim lngVisitorID As Long

dtStart = Me.txtStartDate
dtEnd = Me.txtEndDate
lngVisitorID = Me.cboVisitorID.Column(0)

For dtThis = dtStart To dtEnd

Select Case Weekday(dtThis)
Case 1, 7 'weekend
' do not add a record for weekend dates

Case 2 ' Monday
If Me.chkMon = -1 Then ' -1 = True/Checked
strSQL = "INSERT INTO VisitDates(VisitorID, VisitDate)
VALUES (" & lngVisitorID & ", #" & dtThis & "#)"
DBEngine(0)(0).Execute strSQL
End If

Case 3 ' Tues
If Me.chkTue = -1 Then
strSQL = "INSERT INTO VisitDates(VisitorID, VisitDate)
VALUES (" & lngVisitorID & ", #" & dtThis & "#)"
DBEngine(0)(0).Execute strSQL
End If
Case 4 'Weds
If Me.chkWeds = -1 Then
strSQL = "INSERT INTO VisitDates(VisitorID, VisitDate)
VALUES (" & lngVisitorID & ", #" & dtThis & "#)"
DBEngine(0)(0).Execute strSQL
End If
Case 5
If Me.ChkThurs = -1 Then
strSQL = "INSERT INTO VisitDates(VisitorID, VisitDate)
VALUES (" & lngVisitorID & ", #" & dtThis & "#)"
DBEngine(0)(0).Execute strSQL
End If
Case 6
If Me.chkFri = -1 Then
strSQL = "INSERT INTO VisitDates(VisitorID, VisitDate)
VALUES (" & lngVisitorID & ", #" & dtThis & "#)"
DBEngine(0)(0).Execute strSQL
End If
End Select

Next dtThis

On Error GoTo cmdCreateApptDates_Click_Error
Exit Sub

cmdCreateApptDates_Click_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure
cmdCreateApptDates_Click of VBA Document Form_Form11"
End Sub

HTH
Pieter

 

[Steve Goodrich]

Pieter,

Thanks for your reply, I'm very much a novice at Access but I'll try and
work through your suggestion, May have to post again for more help!!

If you could send me the database it would help my understanding,

Many thanks,

Steve