Automatically Create Records in Subform

[hootie318]

I have searched, but found no reference to my exact question. I have a list of drivers I need to create a checklist for each day. The Parent form is the day and the subform is the list of drivers. What I would like to do is have a command button automatically enter all the drivers that are active (denoted by a chechbox) into the tabular subform.

 

[hootie318]

anyone????

 

[RuralGuy]

You might try describing your tables and then describe what business rule you are trying to resolve.

 

[hootie318]

OK

I have a table that keeps all of our buisness days (tblDispatchDay) which is the source for the form (frmChecklists). I have a subform (frmDrivers). Each driver has a checkbox designating them active. What I would like to do is have a command button that fills in the subform that autofills all the active drivers linked the the date on the subform. I have it set up where I can select each one but I would like it to autofill.

 

[RuralGuy]

You can do it all with the query for the SubForm. Only select "Active" drivers for the subform in the query and then use the date as the LinkChild/MasterField.

 

[hootie318]

I could do that, but the the query would only return data that had already been entered. I am entering the data each day through this form. When I select a driver in the subform it links the that particualr driver to the current date on the main form. Bassically I want a command button to fill the subform with all driver that are active and link it with the date on the main form. Switch to the next day and fill in again, and so on.

 

[RuralGuy]

So what you are saying is you want to add a record to the SubForm's table that includes each driver from the driver table that is marked active and the current date from the MainForm, is that correct?

 

[hootie318]

thats it exactly!

 

[hootie318]

Anyone have any ideas?

 

[RuralGuy]

How about using an Append query to accomplish the task?

 

[hootie318]

I tried that but it wouldnt link the date, I couldnt figure what I was doing wrong.

 

[RuralGuy]

What was the SQL for your Append query?

 

[hootie318]

I wasnt using SQL. I was trying to do it with just adding the filed names. I am not familiar with SQL yet.

 

[RuralGuy]

How were you creating the Append query? Were you using the Query builder? If so, you can switch to SQL view and copy the SQL so we can see it.

 

[hootie318]

INSERT INTO Checklists ( ChecklistID, DriverID, DateID )
SELECT Checklists.ChecklistID, Checklists.DriverID, [Forms]![Checklist]![DateID] AS Expr1
FROM Drivers INNER JOIN (DispatchDay INNER JOIN Checklists ON DispatchDay.DateID = Checklists.DateID) ON Drivers.DriverID = Checklists.DriverID
WHERE (((Drivers.active)=-1));

 

[RuralGuy]

Give it a try without the join to the DispatchDay table. Just delete the DispatchDay table from the query. It should give you all of the active drivers.