Reading in data from forms

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am just making my way through a data base program. I am using access to
schedule out events based on dates and days of occurence. I wrote a loop so
that the schedule would ignore weekends, however, I have a table of holidays
I also want it to ignore. I want to read in the dates into an array
Holiday(x) and check those against schedule dates. I am having problems
determining exactly how to go from an existing open form, open up the
"Holidays" table, read in the dates and store in an arryan and then use them.



While in a "Schedule" form I am using the AfterUpdate for field on the form.
Whenever a user changes a date in the list, the program runs down a list of
dates on the "schedule" form and updates them. Just before the updates, I
need to read in the holiday date values so that I can ignore those dates.
Inside the private sub "aferupdate" I think I want to do the following (sorry
for the code typos).

Docmd.openform "Holidays",,acnormal
Docmd.gotorecord acfirst
X=1
Do while Holidays.EOF = False
HolidayDateVariable(X) = HolidayDate.value
X=X +1
Docmd.gotorecord acnext
Loop

1. Can I do this from inside a private sub?
1a. I am having problems with the program recognizing "HolidayDate.value" so
I assume the answer to 1. is no. If so, can I run the whole thing from
outside in a module?
2. How do I then refocus on back to my original "Schedule" form?
3. Is it better to read in the holiday data from a form or the data table
itself?
 
Ryan said:
1. Can I do this from inside a private sub?
1a. I am having problems with the program recognizing "HolidayDate.value" so
I assume the answer to 1. is no. If so, can I run the whole thing from
outside in a module?
2. How do I then refocus on back to my original "Schedule" form?
3. Is it better to read in the holiday data from a form or the data table
itself?
Click to expand...

3.

Use a form to enter/view data, but don't use it to try and validate
other data against.

In your example, rather that looping through individual records on a
form, I would use the DLookup() function to check if a date exists in
the Holiday table. This way you never have to leave the Schedule form.
In fact, you could also check against the Schedule table to make sure
that date doesn't exist on the schedule either -- and deal with it
appropriately.

Dim varExistingDate as Variant

varExistingDate = DLookup("[HolidayID],"[tblHolidays]","[HolidayDate]
= #" & [ScheduleDate] & "#")

If Not IsNull(varExistingDate) Then
' A date exists on the holiday table. I had Dlookup return the
HolidayID, that way you can exactly know exactly what date conflicts,
and if you wanted, use that to display to the end user
Else
' The date doesn't exist.
End If
 
Here is a function I use to return the number of working days. It counts
only Monday through Friday and ignores holidays that are in the table.

Function CalcWorkDays(dtmStart As Date, dtmEnd As Date) As Integer
'Calculates the number of working days between two dates
'dtmStart - the first day to include in the range
'dtmEnd - the last day to include in the range
'Returns the number of working days between the two dates
'Both dates are counted if they are working days

Dim intTotalDays As Integer ' Counter for number of days
Dim dtmToday As Date ' To increment the date to compare

intTotalDays = DateDiff("d", dtmStart, dtmEnd) + 1 'Start with total days
'Add one to include
First Day
dtmToday = dtmStart 'Initiate compare date
Do Until dtmToday > dtmEnd
If Weekday(dtmToday, vbMonday) > 5 Then 'It is Saturday or
Sunday
intTotalDays = intTotalDays - 1 'Take one day away
for Weekend day
ElseIf Not IsNull(DLookup("[Holdate]", "Holidays", _
"[Holdate] = #" & dtmToday & "#")) Then 'It is a holiday
intTotalDays = intTotalDays - 1 'Take one day away
for the Holiday
End If
dtmToday = DateAdd("d", 1, dtmToday) 'Add a day for next
compare
Loop 'Until dtmToday > dtmEnd All days have been
compared
CalcWorkDays = intTotalDays 'Return the value
End Function
 
Thanks Duncan. I did not know that Dlookup existed. That should work
perfectly for my program.

Duncan Bachen said:
Ryan said:
1. Can I do this from inside a private sub?
1a. I am having problems with the program recognizing "HolidayDate.value" so
I assume the answer to 1. is no. If so, can I run the whole thing from
outside in a module?
2. How do I then refocus on back to my original "Schedule" form?
3. Is it better to read in the holiday data from a form or the data table
itself?
Click to expand...

3.

Use a form to enter/view data, but don't use it to try and validate
other data against.

In your example, rather that looping through individual records on a
form, I would use the DLookup() function to check if a date exists in
the Holiday table. This way you never have to leave the Schedule form.
In fact, you could also check against the Schedule table to make sure
that date doesn't exist on the schedule either -- and deal with it
appropriately.

Dim varExistingDate as Variant

varExistingDate = DLookup("[HolidayID],"[tblHolidays]","[HolidayDate]
= #" & [ScheduleDate] & "#")

If Not IsNull(varExistingDate) Then
' A date exists on the holiday table. I had Dlookup return the
HolidayID, that way you can exactly know exactly what date conflicts,
and if you wanted, use that to display to the end user
Else
' The date doesn't exist.
End If
Click to expand...
 
Back
Top