Form Help (Access 2K)

[easyaccess]

I have a form that I would like to do the following in the VBA OnOpen or OnLoad (I'm not sure which would be appropriate) event:

Count the number of records in tblScheduledEmployees.lngScheduleID that have records that match the current field lngScheduledID on the form.

IF the count of records is LESS THAN the value of strManualNurseCount then I want to run a query (qryAddContractor) to add records UNTIL the count of records in tblScheduledEmployee EQUALS strManualNurseCount.
IF the count is EQUAL or GREATER THAN, then move to the next unique number in the recordset

I would like this loop to run for each unique number of the field lngScheduleID in the recordset on the form.

I have attached a copy of the database with the relevant form and tables.

[WendellB]

Apologies for the delay in responses - a number of us are involved in Memorial Day activities this weekend. I've taken a quick peek at your attachment, and will hopefully get back to you a bit later today. In the interim, it would help if you could give us a bit more general description of what you are trying to achieve with the appending of records so we can consider alternative approaches.

[easyaccess]

The ultimate result is when my Scheduler looks at this form he/she will be able to see each event in chronological order and there will be corresponding combo box (lngEmployeeID) in which an employee can be selected to staff the event based on the required number .

This form is one in which I would like to display all the records from the tblSchedule that requires scheduling of staff from the tblEmployees. I would like to have a recordset that would allow me to display each record in tblSchedule as many times as there is staff that needs to be scheduled. The field strManualNurseCount is indicative of how many staff each schedule should have staffed (and how many times that record should appear).

I am hoping that the form will be able to add records to tblScheduledEmployees based on counting the number of records that have been assigned in tblScheduledEmployees and if the numbers of records are less than required in strManualNurseCount then VBA code could be written to run in a loop until the required number of records are created (with the appropriate value in tblScheduledEmployees.lngScheduleID that corresponds to lngScheduleID in tblSchedule.

This code should step/loop through each record in the recordset of the form in which the field lngScheduleID is not a duplicate and perform this function.

The records in tblSchedule will be added in a incremental basis so the code would have to be run each time the form is opened. After the initial addition of records there should not have to be any additional records, but if there is I would like to account for that.

I hope all of this makes sense! <img src=/S/hairy.gif border=0 alt=hairy width=15 height=15>

[HansV]

See if the attached does what you want. The module basAddRecords contains a procedure AddRecords. This is called from the On Open event of the form, followed by a requery.

[easyaccess]

Thank you Wendell & Hans...I believe that what you have given me is what I need. I will give it a whirl tomorrow and let you know if I run into any problems.

You have my undying gratitude.

[WendellB]

Thanks - this sort of background really helps with understanding what you are trying to achieve, and it looks like Hans has aimed you in the right direction. Let us know how it turns out - scheduling applications based on Access can be very complex from a development perspective.

[easyaccess]

First let me again say "Thank You" for your quick and totally on point response. Hans, your solution works perfectly! I have two

[HansV]

1. You can change the line that calculates lngExist to
<code>
lngExist = DCount("*", "tblScheduledEmployees", "lngScheduleID=" & rstS!lngScheduleID & _
" AND NOT strEmployeeType = 'Registrar'")
</code>
2. How do you determine which record is the first for a specific lngScheduleID? There are no distinguishing fields.

[easyaccess]

1. Adding the criteria worked fine...I had to modify the form recordset to restrict the records to only those that have "RN" as an employee type and it works fine.

2. I have no particular way to distinguish which record is first. The form primarily sorts the records by the datDate field with a secondary sort by strClinicFlowNumber.
Is there someway that I can designate this?

[HansV]

As far as I can see, there is no good way to distinguish the "first" record for a specific lngScheduleID - they all have the same date and clinic flow number (since those fields are from tblSchedule, not from tblScheduledEmployees). And even if you could, the form you now have would become read-only if you displayed only one record per lngScheduleID. Why not create a form based on tblSchedule instead of on tblSchedule and tblScheduledEmployees? You can display the corresponding records from tblScheduledEmployees in a subform or in a popup form.

[easyaccess]

Thanks for the insight. It looks like I will have to skip that request. It was more cosmetic and does not effect the functionality. I appreciate your quick response and effort.