Continuous Form Auto-Fill

[Matt_Lavoie]

I'm creating a database in Access 2007. Every day, the same 30+ company vehicles are operated and the number of hours they were running needs to be recorded. My form shows the current date at the top, and shows a blank continuous form at the beginning of a new day. There are "Next/Prev Day" buttons at the bottom to flip to previous days to view what was filled in that day.

When beginning each day, I don't want a supervisor to have to re-select over 30 vehicle names from 30 combo boxes and potentially skip some by accident. I would prefer that the form automatically fill in the combo boxes with one of each vehicle.

The fleet of vehicles changes a few times a year, so the list of vehicles is in another table that the combo box gets values from.

Form:
frmVehicleHours

Tables:
tblVehicleHours
--> AutoNumber, Date, VehicleNumber, HoursRan

tblVehicleList
--> VehicleNumber

This is a simplified version of the problem, but if I can manage this it will be integrate into the database.

Thanks in advance for any ideas,
-Matt

 

[pbaldy]

At the appropriate point in your process, you can execute an append query that gets the vehicle numbers from tblVehicleList and the date from the form and appends records for that date. You'd probably want to test to make sure records for that date didn't already exist.

 

[Matt_Lavoie]

Thanks for the reply pbaldy! This gave me a great start.
I looked into append queries and managed to come up with something.

On Form_Open I put:

Dim AutoFill As Database
    Set AutoFill = CurrentDb
    AutoFill.Execute " INSERT INTO tblVehicleHours (VehicleNumber) " _
        & " SELECT tblVehicleList.VehicleNumber " _
        & " FROM tblVehicleList;" 
    AutoFill.Close

This inserts the correct numbers into the table as new records. Now I need to attach today's date to those numbers so that they show up on today's continuous form.

I wasn't sure where to put Date in the above code since it isn't coming from a table which is what FROM seems to be wanting.

Then I found a discussion on another forum specifically talking about appending dates to tables, but they formatted it slightly differently, so I then tried that:

Dim AutoFill As Database
    Set AutoFill = CurrentDb
    AutoFill.Execute " INSERT INTO tblVehicleHours (Date, Vehicle)" _
        & " VALUES (#" & Date & "#, tblVehicleList.VehicleName);"
    AutoFill.Close

This gave me a syntax error in the INSERT INTO statement. I tried formatting it with just the date parts in there and it still gave me the same error.

I'm really trying here, I'm new to this. I'll worry about the part where I test for existing records later, right now I'm trying to win this battle. Any ideas?

 

[pbaldy]

No, you don't want a VALUES clause, which only inserts a single record. For today's date it would look like:

    AutoFill.Execute " INSERT INTO tblVehicleHours (VehicleNumber, DateField) " _
        & " SELECT tblVehicleList.VehicleNumber, Date() " _
        & " FROM tblVehicleList;"

To use a particular date, you'd concatenate the value in as your example showed.

 

[Matt_Lavoie]

Thank you pbaldy! I was still getting a syntax error with your code, but then I realized that I was calling my date field Date - which is a reserved word in Access and was making it unhappy when I used it in the code. I just put brackets around Date to let it know that it is a field name.

For anyone who finds this thread in the distant future with the same problem, this is what my code looks like, INCLUDING the part where it checks to see if any records already exist for today AND the part where it refreshes the continuous form at the end to display the newly inserted values.

If Not DCount("*", "tblVehicleHours", "[Date] = #" & pdteTodaysDate & "# ") > 0 Then
    Dim AutoFill As Database
    Set AutoFill = CurrentDb
    AutoFill.Execute " INSERT INTO tblVehicleHours ( VehicleNumber, [Date]) " _
        & " SELECT VehicleList.VehicleNumber, Date() " _
        & " FROM VehicleList;"    
    AutoFill.Close
End If
 
Me.RecordSource = pstrVehicleHoursSQL

That last bit with the SQL goes to a module which queries the table for records with today's date, thus updating the values on the continuous form with the new records I just added with today's date.

 

[pbaldy]

Glad you got it sorted out. I was at a party last night so wasn't able to respond to your initial question.