Create new records from a continuous form

[DocBrown]

I've searched this newgroup and can't see an answer to my particular question.

I have a Form based on a query containing two tables, linked as a one to
many relation on an ID key. Coding in the form takes data from a unbound text
box and generates a form filter so the continuous form displays only for the
desired records from the secondary table.

The purpose of this form is to update and maybe create new records in the
secondary table. The updates work ok, but when I go to a new record in the
detail section, the bound controls in the header go blank and the record can
not be created because the ID field, which is displayed in the header is not
valid.

The Query is:
SELECT VolunteerLog.LogRecID, Volunteers.FirstName, Volunteers.LastName,
VolunteerLog.VolunteerID, VolunteerLog.LogDate, VolunteerLog.TimeIn,
VolunteerLog.TimeOut, Volunteers.SchoolName
FROM Volunteers LEFT JOIN VolunteerLog ON Volunteers.VolunteerID =
VolunteerLog.VolunteerID
ORDER BY VolunteerLog.LogRecID;

The fields from the Volunteers table are displayed in the header, the
VolunteerLog fields are in the continuous detail section.

Is this the correct approach? What is the way to create new records in the
VolunteerLog table?

Thanks!
John

 

[Dale Fye]

Make a copy of your database.

Normally, I would do this with a subform. Take the copy of the form you
have, remove the header stuff, then save it (I usually use the same name as
the main form, but add "_sub_xxx" to the name).

Now, go back to the copy of your original form. Remove everything from the
detail section. Move the ID textbox from the header into the detail section.
Leave your filter in the header. What you should have now is your filter
and the ID textbox. Open the form and make sure that it works, displaying
the ID when you move from record to record.

Once that is working, add a subform control to the details section of this
form. Set its ControlSource property to the subform you saved in the earlier
step, and set the Link Master and Link Child field values to the ID field.
Now, when you move from one record to the next in the main form, the subform
should filter properly, and because the ID fields are linked, when you add a
new record to the subform, it will already contain the ID value from the main
form.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[DocBrown]

dale,

Thanks for the info. Yes, using a subform works perfectly!

Of course, I did have to do a few other items of clean up to get it to work.
For example, remove the VBA code from the new copied subform which is no
longer valid. Change some form properties to get the look I want, etc.

Thanks
John

 

[Dale Fye]

Glad it is working.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.