pre-checking for multiple record entry, and date control question. (1 Viewer)

[hardin4019]

Hi Guys,
2 separate questions this time. I know the rest of the universe has moved on to a newer version, but bare in mind, I am using MS Access 97.

1. I am creating an attendance DB, I have forms for entering employee info, attendance info, editing attendance info, and running reports.

In the attendance info box form, you select EmployeeName, AbscenceType, a Date, etc, and I have a button that saves the record and goes on to a new record. What would be the best way to do some checking against the selected EmployeeName, AbscenceType, and Date against the table that holds the data on Attendance. IE, I want to make sure that you don't enter John Doe for Being tardy twice in one day, or mark him abscent and tardy, etc. You get the idea....

2. I have in the Attendance form I am using "Calendar control 8.0" for the Date control. I can only select a single date. If someone wants to schedule a vacation, I would like to be able to choose a beginning and ending date, then I am guessing create a loop that fills in data in the table from start date to end date with all the needed employee info that would already be supplied from the form. Where as right now, I use the form to manually enter each day of a person's vacation.

Ideas? Different date control? Make AbscenceType "Vacation" change the date control to a different subform? Also would need rather in depth help with the code, as I'm not up on my VB in any way shape or form.

 

[highandwild]

1. I am creating an attendance DB, I have forms for entering employee info, attendance info, editing attendance info, and running reports.

In the attendance info box form, you select EmployeeName, AbscenceType, a Date, etc, and I have a button that saves the record and goes on to a new record. What would be the best way to do some checking against the selected EmployeeName, AbscenceType, and Date against the table that holds the data on Attendance. IE, I want to make sure that you don't enter John Doe for Being tardy twice in one day, or mark him abscent and tardy, etc. You get the idea....

If the selection is by combo box I would force the user to select the date first and then, on the On Change Event of the Date combo box, set the Row Source of the EmployeeName combo box to present a list of employees that have no entries in the table for that date. The AbsenceType combo box can then have a Row Source which presents a list using data from an AbsenceType table as by this stage any Absence Type is valid.

I always like to prevent the user from selecting an incorrect combination of values rather than having to cope with checking against the rules after the data has been selected.

Do you have a data picker form?

 

[boblarson]

On Change is a dangerous event to use if you are changing the row source with each keystroke. I would wait until the After Update event of the combo box. And you can check in the form's BEFORE UPDATE event to validate that the selected combination doesn't exist (using a DCount).

You could also set a MULTI-FIELD index to the table so that a single combination of EmployeeID, Date, and AbsenceType could not be put in more than once. I'd give a link to my website with how to do up a multi-field index but it is currently down for some reason and I can't do anything about it until I get home.

 

[hardin4019]

If the selection is by combo box I would force the user to select the date first and then, on the On Change Event of the Date combo box, set the Row Source of the EmployeeName combo box to present a list of employees that have no entries in the table for that date. The AbsenceType combo box can then have a Row Source which presents a list using data from an AbsenceType table as by this stage any Absence Type is valid.

I always like to prevent the user from selecting an incorrect combination of values rather than having to cope with checking against the rules after the data has been selected.

Do you have a data picker form?

Hi Highandwild,
I agree, and I am working toward the same thing, preventing double data entry. At this stage in the game, this is a single form, each is independently driven on its own (table/query lookup). As for a data picker, I'm not sure I follow. For me, and this DB, there is not wrong combination of person, type of abscence, and date. Unless of course we go back to the original concern of the same person and the same date twice. The way my form is laid out, you choose person, type, then date.

Even if I choose people in the person, type, date order... Wouldn't I be safe to have something like VB code or an IIF statement in the calender control / on change that looks to see that that person with that date isn't already there?

I need an example of the VB or SQL code to make this work if you think I am headed in the right direction.

 

[highandwild]

Hi Guys,
2 separate questions this time. I know the rest of the universe has moved on to a newer version, but bare in mind, I am using MS Access 97.

2. I have in the Attendance form I am using "Calendar control 8.0" for the Date control. I can only select a single date. If someone wants to schedule a vacation, I would like to be able to choose a beginning and ending date, then I am guessing create a loop that fills in data in the table from start date to end date with all the needed employee info that would already be supplied from the form. Where as right now, I use the form to manually enter each day of a person's vacation.

Ideas? Different date control? Make AbscenceType "Vacation" change the date control to a different subform? Also would need rather in depth help with the code, as I'm not up on my VB in any way shape or form.

Do you know about cartesian products?

1. Have a text box on the form for the start and end dates and populate this in any way you like. This could be a combo box or date picker.
2. Set up a table that just contains a single date field. This could be used to select the absence date on the form.
3. Set up a query with the Date table and the Absence table. Do not join the tables. Apply the date range from Form as the criteria for the Dates table.
4. Select the fields from the Absence table and apply a criteria which will just select the required record from the Absence table.
5. When run the query you should have a record for each date within the start-end date range and this can then be appended to the required table.

Let me know where you are in understanding this? This is easily possible without much VBA and can be built up and tested in stages. I'm around a while.

 

[highandwild]

Hi Highandwild,

Even if I choose people in the person, type, date order... Wouldn't I be safe to have something like VB code or an IIF statement in the calender control / on change that looks to see that that person with that date isn't already there?

I need an example of the VB or SQL code to make this work if you think I am headed in the right direction.

A question for you. As this is recording absences I assume that the record will be created the same day as the absence or within a few days of that date. Is this a good assumption?

A combo box should suffice instead of a calender control. The Row Source of the combo box can be used to prevent invalid dates being presented to the user.

 

[hardin4019]

Hi again,
No, sorry, I'm not familiar with the Cartesian Products. Yes, attendance should be entered within say +/- 5 days of the incident.

As for the Cartesian Products steps you listed. I get lost in #2, setting up another table. Would you want to use like a temporary table or something not permanent?

Also, I know I got lost in there somewhere, so this may be a dumb question, but I'll go ahead and ask. Would that cause issues with having multiple people gone on the same day? I have a work force of 100 + employees at some times.

 

[hardin4019]

Sorry Bob, I nearly missed your post. Please post a link when your site comes back up. It sounds vary useful.

 

[highandwild]

Hi again,
No, sorry, I'm not familiar with the Cartesian Products. Yes, attendance should be entered within say +/- 5 days of the incident.

As for the Cartesian Products steps you listed. I get lost in #2, setting up another table. Would you want to use like a temporary table or something not permanent?

Also, I know I got lost in there somewhere, so this may be a dumb question, but I'll go ahead and ask. Would that cause issues with having multiple people gone on the same day? I have a work force of 100 + employees at some times.

See attached database. Look at the query and see that it will create a record for each company in Buenos Aires and for each date in the date range specified in the criteria. This technique can be used instead of using VBA to loop through a date range and create records one at a time.
The criteria could look at values on your form.

 

[highandwild]

Whoops!! Here is the database.

 

[highandwild]

Hi again,
Would that cause issues with having multiple people gone on the same day? I have a work force of 100 + employees at some times.

The append query could be linked to the target table and set up to only append records that do not already exist in the target table.

 

[hardin4019]

I'm using MS Access 97 so it doesn't recognize .mdb. Can you save it as access 97 format?

 

[highandwild]

How do I do that, any idea?

 

[boblarson]

How do I do that, any idea?

Which version are you using?

 

[highandwild]

Version 2003

 

[boblarson]

Actually, I just did it from my 2003. TOOLS > DATABASE UTILITIES > CONVERT > ACCESS 97.

 

[highandwild]

Thanks Bob

I've never had to do that before.

 

[hardin4019]

Hi Bob and Highandwild,

Ok, I got the DB and see what you mean and all looks good, and I understand it now.

I get how I could use this to generate dates for a combo box, but is there an easy and effective way to automatically add the dates to this combo box?

And now, if I use this to choose a beginning and end date, what suggestions do you have about taking this and turning it into either multiple entries in the attendance table?

Also, any other ideas on how to do the error checking for the same person multiple times on a single day?

Thanks guys, its all starting to come together!