Append Query Question

[floyd33 via AccessMonster.com]

Not sure if queries is the right place for this. Please redirect me if not.


I have three tables that interact on this process: tblTimeandEvents,
tblTimeandEventsAdd, and tblEvent.

The main table for the entire db is tblEvent. TblTimeandEvents is a table
that stores a set of goals for each eventID in tblEvent (there is an eventID
in tblTimeandEvents). TblTimeandEvents is the source behind subfrmTandE on
frmMeeting. I have an append query that takes the data from
tblTimeandEventsAdd (preloaded) and adds it to tblTimeandEvents per EventID,
on click of a command button.

TblTimeandEvents has a field named DueDate, which needs to calculate from the
StartDate field of tblEvent + the NumberDays field from tblTimeandEventsAdd.
This NumberDays field is just a positive or negative number that needs to
help calculate a due date on subfrmTimeandEvents.

Currently, the user is clicking the button to add tblTimeandEventsAdd to each
program and the lack of a visible Due Date field is really bothering them...
they'd like to know automatically what it should be. I wasn't the original
creator of this db/form, so I'm kind of at a loss on how to proceed. Any
ideas on how to resolve this?

 

[Daryl S]

Floyd33 -

I understand you want to see the Due Date on your form with the
tblTimeandEventsAdd data. If they can only use this form for records that
are already in the tblEvent, then you can add a text box and use a DLOOKUP to
populate/calculate it. You could also show the StartDate field from the
tblEvent, and calculate days between the StartDate and the DueDate, which
might make the users more comfortable. To do this:

Add a text field txtStartDate on the tblTimeandEventsAdd form. This will be
unbound and not updateable by the user. The control source would be (assumes
EventID is numeric, and the control name for the EventID on the form is
txtEventID):
DLOOKUP("[StartDate]","[tblEvent]","[EventID] = " & Me.txtEventID)

Add another text field txtNumberDays on the tblTimeandEventsAdd form. This
will also be unbound and not updateable by the user. It should be formatted
as a number with no decimal places. The control source would be:
=nz(Me.txtDueDate,0) - Me.txtStartDate

You will have to use your control names instead of my made-up ones in the
code samples above. If you have issues with it, repost what code you have
and what the results are..

 

[floyd33 via AccessMonster.com]

I will give this a try in the morning and report back! Thank you so much!

Floyd33 -

I understand you want to see the Due Date on your form with the
tblTimeandEventsAdd data. If they can only use this form for records that
are already in the tblEvent, then you can add a text box and use a DLOOKUP to
populate/calculate it. You could also show the StartDate field from the
tblEvent, and calculate days between the StartDate and the DueDate, which
might make the users more comfortable. To do this:

Add a text field txtStartDate on the tblTimeandEventsAdd form. This will be
unbound and not updateable by the user. The control source would be (assumes
EventID is numeric, and the control name for the EventID on the form is
txtEventID):
DLOOKUP("[StartDate]","[tblEvent]","[EventID] = " & Me.txtEventID)

Add another text field txtNumberDays on the tblTimeandEventsAdd form. This
will also be unbound and not updateable by the user. It should be formatted
as a number with no decimal places. The control source would be:
=nz(Me.txtDueDate,0) - Me.txtStartDate

You will have to use your control names instead of my made-up ones in the
code samples above. If you have issues with it, repost what code you have
and what the results are..

Not sure if queries is the right place for this. Please redirect me if not.

[quoted text clipped - 18 lines]

creator of this db/form, so I'm kind of at a loss on how to proceed. Any
ideas on how to resolve this?

 

[floyd33 via AccessMonster.com]

Okay, so I'm not having much luck, Daryl. Here's what I've done, with real
field names.

This is the append query that adds data from tblTandEAdd to tblTandE.
TblTandE is a subform represented on the frmMeeting (main form).

INSERT INTO tblTandE ( Subject, Role, NumberDays, BillingInformation,
MeetingID, Categories )
SELECT tblTandEAdd.Subject, tblTandEAdd.Role, tblTandEAdd.NumberDays,
tblTandEAdd.BillingInformation, tblMeeting.MeetingID, tblMeeting.
ProgramNumber
FROM tblTandEAdd, tblMeeting
WHERE (((tblMeeting.MeetingID)=[Forms]![frmMeeting]![MeetingID]) AND (
(tblMeeting.ProgramNumber)=[Forms]![frmMeeting]![ProgramNumber]));

I feel like I should be calculating the Due Date field in this append query
somehow, but the DueDate field does not appear in tblTandEAdd, only in
tblTandE.

Here is the view of the subform after I added what you suggested. I'm not
sure if I subbed out field names correctly, but here goes. I've also added
design view image.

http://img.photobucket.com/albums/v354/floyd33/subfrmTandE-1.jpg
http://img.photobucket.com/albums/v354/floyd33/TE.jpg

Floyd33 -

I understand you want to see the Due Date on your form with the
tblTimeandEventsAdd data. If they can only use this form for records that
are already in the tblEvent, then you can add a text box and use a DLOOKUP to
populate/calculate it. You could also show the StartDate field from the
tblEvent, and calculate days between the StartDate and the DueDate, which
might make the users more comfortable. To do this:

Add a text field txtStartDate on the tblTimeandEventsAdd form. This will be
unbound and not updateable by the user. The control source would be (assumes
EventID is numeric, and the control name for the EventID on the form is
txtEventID):
DLOOKUP("[StartDate]","[tblEvent]","[EventID] = " & Me.txtEventID)

Add another text field txtNumberDays on the tblTimeandEventsAdd form. This
will also be unbound and not updateable by the user. It should be formatted
as a number with no decimal places. The control source would be:
=nz(Me.txtDueDate,0) - Me.txtStartDate

You will have to use your control names instead of my made-up ones in the
code samples above. If you have issues with it, repost what code you have
and what the results are..

Not sure if queries is the right place for this. Please redirect me if not.

[quoted text clipped - 18 lines]

creator of this db/form, so I'm kind of at a loss on how to proceed. Any
ideas on how to resolve this?

 

[Daryl S]

Floyd -

In your DLOOKUP statement you must be able to show the EventID from the
form. I don't see [EventID] on your form, so you may want to add it (even if
it is hidden so the users don't see it). Then use the Me.MeetingID in the
DLOOKUP statement where I had Me.txtEventID in the original code. Use the
name of the control you add (it would default to MeetingID). I assume the
field is also called MeetingID in the tblMeeting.

Get that working first. Then, your new due date would be that field
(me.txtStartDate if that is the name of the control with the label Text210)
plus Me.[NumberDays].

Hope that helps!

--
Daryl S

Okay, so I'm not having much luck, Daryl. Here's what I've done, with real
field names.

This is the append query that adds data from tblTandEAdd to tblTandE.
TblTandE is a subform represented on the frmMeeting (main form).

INSERT INTO tblTandE ( Subject, Role, NumberDays, BillingInformation,
MeetingID, Categories )
SELECT tblTandEAdd.Subject, tblTandEAdd.Role, tblTandEAdd.NumberDays,
tblTandEAdd.BillingInformation, tblMeeting.MeetingID, tblMeeting.
ProgramNumber
FROM tblTandEAdd, tblMeeting
WHERE (((tblMeeting.MeetingID)=[Forms]![frmMeeting]![MeetingID]) AND (
(tblMeeting.ProgramNumber)=[Forms]![frmMeeting]![ProgramNumber]));

I feel like I should be calculating the Due Date field in this append query
somehow, but the DueDate field does not appear in tblTandEAdd, only in
tblTandE.

Here is the view of the subform after I added what you suggested. I'm not
sure if I subbed out field names correctly, but here goes. I've also added
design view image.

http://img.photobucket.com/albums/v354/floyd33/subfrmTandE-1.jpg
http://img.photobucket.com/albums/v354/floyd33/TE.jpg

Floyd33 -

I understand you want to see the Due Date on your form with the
tblTimeandEventsAdd data. If they can only use this form for records that
are already in the tblEvent, then you can add a text box and use a DLOOKUP to
populate/calculate it. You could also show the StartDate field from the
tblEvent, and calculate days between the StartDate and the DueDate, which
might make the users more comfortable. To do this:

Add a text field txtStartDate on the tblTimeandEventsAdd form. This will be
unbound and not updateable by the user. The control source would be (assumes
EventID is numeric, and the control name for the EventID on the form is
txtEventID):
DLOOKUP("[StartDate]","[tblEvent]","[EventID] = " & Me.txtEventID)

Add another text field txtNumberDays on the tblTimeandEventsAdd form. This
will also be unbound and not updateable by the user. It should be formatted
as a number with no decimal places. The control source would be:
=nz(Me.txtDueDate,0) - Me.txtStartDate

You will have to use your control names instead of my made-up ones in the
code samples above. If you have issues with it, repost what code you have
and what the results are..

Not sure if queries is the right place for this. Please redirect me if not.

[quoted text clipped - 18 lines]

creator of this db/form, so I'm kind of at a loss on how to proceed. Any
ideas on how to resolve this?