Insert New Records Using Nested Loops

[ThomasLafferty]

Hello Gurus!
Here's the scenario...
I need to insert a block of dates into a table called Driver Paperwork using the click event of an unbound form called frmInsertPaperworkDates. The dates to be inserted are entered by the user in two unbound textboxes:

txtboxBegDate
txtboxEndDate

The event I plan to use to insert the dates into my table is the click event named of a command button named btn_OK_Close_frmInsertPaperworkDates

Here's my code so far which correctly gives the dates to use and the number of dates to insert and displays them in a message box:

[COLOR=blue]Option Compare Database
Dim[/color] intJobTitle [COLOR=blue]As Integer
Dim [/color]dtStDate
[COLOR=blue]Dim [/color]dtEnDate
[COLOR=blue]Dim [/color]intDateInterval
[COLOR=blue]Dim [/color]SQL [COLOR=blue]As String[/color]

[COLOR=blue]Private Sub[/color] btn_OK_Close_frmInsertPaperworkDates_Click()
[COLOR=blue]On Error GoTo[/color] Err_btn_OK_Close_frmInsertPaperworkDates_Click

[COLOR=green]'set variables for dates to feed to SQL string[/color]
            dtStDate = Me.txtboxBegDate.Value
            dtEnDate = Me.txtboxEndDate.Value
            intDateInterval = DateDiff("d", dtStDate, dtEnDate)

MsgBox "You have chosen the following:" _
& Chr$(13) & "Start Date: " & dtStDate _
& Chr$(13) & "End Date: " & dtEnDate _
& Chr$(13) & "Total days: " & intDateInterval, _
vbInformation, "Dates to insert"


    [COLOR=green]'DoCmd.Close[/color]

Exit_btn_OK_Close_frmInsertPaperworkDate:
    [COLOR=blue]Exit Sub[/color]

Err_btn_OK_Close_frmInsertPaperworkDates_Click:
    MsgBox Err.Description
    [COLOR=blue]Resume[/color] Exit_btn_OK_Close_frmInsertPaperworkDate
    
[COLOR=blue]End Sub[/color]

My plan for inserting the dates is to use a nested For Next loop somewhere in my code shown above. I will probably do something like this:

[COLOR=green]'*****Outer Loop will go here for varying driver number*****

'*****Inner Loop to generate sequential dates*****[/color]
[COLOR=blue] For [/color]dteDatesToInsert = 1 [COLOR=blue]To[/color] intDateInterval
     dtStDate = dtStDate + 1  [COLOR=green]'increase by 1 day each time loop executes[/color]
[COLOR=blue]Next[/color] intDateInterval

[COLOR=green]'*****End Outer Loop will go here*****[/color]

The output to my table should look something like this:

Driver       Date
  23        4/1/2006
  19        4/1/2006
  22        4/1/2006
  23        4/2/2006
  19        4/2/2006
  22        4/2/2006

Here's where it gets a bit hairy. The numbers in the Driver field are not sequential and will not always be the ones shown above. In fact, I plan to specify criteria on my unbound form about which ones will be chosen, and the criteria resides in a related table: Driver Information which is related to Driver Paperwork by a one-to-many:

[Driver Information].[DriverID] = [Driver Paperwork].[Driver]

Here's what I don't yet know how to do:
1. How do I determine the total number of times for the Outer Loop which I will use to vary the Driver number? Run a query and count the records? If so, how do I do this without showing the resulting datasheet?
2. How do I vary the non-sequential driver number? Read the numbers into an array of some kind first?
3. How do I feed this information to my SQL string variable so I can use it to run an append query?
4. How do I check to see if the specified dates already exist for the Driver numbers chosen to prevent bloating the database?
5. How do I prevent the user from specifying a start date later than the end date?

Sorry about the long-winded post, I just want to be thorough.

Tom

 

[VicRauch]

Answers 1 and 2: Rather than using a For/Next loop, it appears you will need to read a recordset somewhere, I'm just not sure how to build the recordset as I don't know all your tables, fields, and relationships. I'm assuming you do. So, put together the SQL you need to create a query of just the drivers you want. Then, using that SQL, create a recordset. As you read through the recordset, when you get to rs.EOF, you are finished with your "outer" loop. (You might need to read up on Recordsets and how to code for them).
Answer 3: While you are reading the RecordSet from Answers 1 and 2 above, with "rs" set as the recordset object variable, the driver number would be referenced by rs!DriverNumber where "DriverNumber" is the field in the table that holds the driver number.
Answer 4: Either use the date and driver number as the Primary Key for this table you are writting to, or do a DLookup with the Driver number and Date before trying to add that record.
Answer 5: Using DateDiff, you can subtract the StartDate from the EndDate. If the number is negative, tell the user "STOP IT!" or whatever error message you would like to use.

Good Luck!
Ask more if you need to.
Vic

 

[ThomasLafferty]

Vic -
Thanks for the info. The select query below pulls the appropriate drivers:

SELECT DISTINCT [Driver Information].DriverID, [Driver Information].[Job Title]
FROM [Driver Information]
WHERE ((([Driver Information].Active)=Yes) AND (([Driver Information].[Job Title])=[Forms]![frmInsertPaperworkDates]![optgrpSelectWorkGroupint]));

This pulls a record set that looks like below:

[b]DriverID	Job Title[/b]
[u]   20	   HH Driver
   22	   HH Driver
   23	   HH Driver
   24	   HH Driver[/u]

The Job Title field is actually numeric as it is looked up from another table.

In any event, the record set returned from the Driver Information table shown above presently returns 4 records. For each of those records, I need to be able to insert a block of consecutive dates in the related table called Driver Paperwork, and the user will specify the range of dates to use in a form by typing the starting and ending dates into textboxes.

As mentioned above the relationship between the tables is as follows:

[Driver Information].[DriverID] = [Driver Paperwork].[Driver]

Assuming that the user enters a start date of 6/1/2006 and an end date of 6/5/2006, I need to append a data set like the one shown below to my Driver Paperwork table.

[b]Driver	Date[/b]
[u]20	   6/1/2006
20	   6/2/2006
20	   6/3/2006
20	   6/4/2006
20	   6/5/2006
22	   6/1/2006
22	   6/2/2006
22	   6/3/2006
22	   6/4/2006
22	   6/5/2006
23	   6/1/2006
23	   6/2/2006
23	   6/3/2006
23	   6/4/2006
23	   6/5/2006
24	   6/1/2006
24	   6/2/2006
24	   6/3/2006
24	   6/4/2006
24	   6/5/2006[/u]

You mentioned that I should read up on record sets, and I agree. Can you think of a decent resource written in basic terms for the lay person with not much experience (preferably free on the Internet)?

Again, I appreciate what you've done so far, and if you need more information in order to advise me, let me know.

Thanks!

Tom

 

[VicRauch]

Tom,

Try this: (I have not tested this actual code, but I have done this type of thing many times. I know the concept works.)

Given start and end date fields of names: StartDate and EndDate and then the output table of [Driver Paperwork].

Dim IncrementDate as Date/Time
Dim SqlTxt as String
IncrementDate = StartDate
While IncrementDate <= EndDate
	SqlTxt = "INSERT INTO [Driver Paperwork] ([Driver], [Date]) " & _
		SELECT [Driver], #" & IncrementDate & "# AS [DateTime] " & _
		FROM qryDriverInformation;"
	DoCmd.SetWarnings = False
	DoCmd.RunSql SqlTxt
	DoCmd.SetWarnings = True
	IncrementDate  = DateAdd("d", 1, IncrementDate)
WEnd

This code will take the list of drivers from your query (I named qryDriverInformation here) and add one record for each driver in the query and include the date you need. Add one to the date and do it again until it has done it through the end date requested.

HTH,
Vic

 

[ThomasLafferty]

This will get me where I need to go... Thanks a million!

Tom