Append Query question

  • Thread starter Thread starter Saul
  • Start date Start date
S

Saul

I am creating a database in which tblEvents is related to
tblEventDetails in a 1:many join. EventDetail records can
be defined as "active" or "inactive". When a new Event
record is created and the user opens the EventDetails
form I would like the EventDetails form for the current
Event to include "active" event details from the
preceeding Event. I am trying to do this by creating an
append query that retrieves only the "active"
EventDetails records from the prior Event ONLY (i.e.,
EventID number 1 less than the current EventID number)
and then appends those records to the tblEventDetails
with the current Event number. Two question: 1. I can't
seem to get the query to pull up ONLY the prior EVENT
(tried using the "Latest" function). 2. Is there a better
way to accomplish this?

Thanks

Saul
 
Saul said:
I am creating a database in which tblEvents is related to
tblEventDetails in a 1:many join. EventDetail records can
be defined as "active" or "inactive". When a new Event
record is created and the user opens the EventDetails
form I would like the EventDetails form for the current
Event to include "active" event details from the
preceeding Event. I am trying to do this by creating an
append query that retrieves only the "active"
EventDetails records from the prior Event ONLY (i.e.,
EventID number 1 less than the current EventID number)
and then appends those records to the tblEventDetails
with the current Event number. Two question: 1. I can't
seem to get the query to pull up ONLY the prior EVENT
(tried using the "Latest" function). 2. Is there a better
way to accomplish this?
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

To get "prior" data you'd have to have something that indicates
chronological or ordinal position (event date or event number). You
could use a query like this:

PARAMETERS [Enter Event ID] Long;
SELECT *
FROM EventDetails
WHERE EventID = (SELECT MAX(EventID) FROM Events
WHERE EventID < [Enter Event ID])

English translation:

Get all Event detail records for the event that was immediately before
the user-entered event ID. This assumes the EventID is created in
ascending order, not random order.

Instead of the user-entered parameter you could use a reference to a
form:

Forms!FormName!EventIDControlName

- --
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQGOGzoechKqOuFEgEQJVFwCbBVaoClPKL7/XNALCW3q6Q3cVaSAAoLBn
inCVxR3AxVxcDQkbHqv9HXB1
=pxbR
-----END PGP SIGNATURE-----
 
Thank-you. That worked great. I now have a query that
reliably pulls up the "Active" EventDetail records from
the previous event. I would now like to set the EventID
for those records equal to the current EventID. Again the
purpose is so that "Active" EventDetail records from one
Event are "copied" into the next Event (i.e. now
associated with the latest EventID). When I append the
query as it is, the records are readded to the
EventDetails table with the original EventID number. The
current eventID number can be taken from Forms!
frmEvent.EventID but I can't figure out how to associate
this EventID with the EventDetails retrieved from the
query. Any further assistance is greatly appreciated.
-----Original Message-----
Saul said:
I am creating a database in which tblEvents is related to
tblEventDetails in a 1:many join. EventDetail records can
be defined as "active" or "inactive". When a new Event
record is created and the user opens the EventDetails
form I would like the EventDetails form for the current
Event to include "active" event details from the
preceeding Event. I am trying to do this by creating an
append query that retrieves only the "active"
EventDetails records from the prior Event ONLY (i.e.,
EventID number 1 less than the current EventID number)
and then appends those records to the tblEventDetails
with the current Event number. Two question: 1. I can't
seem to get the query to pull up ONLY the prior EVENT
(tried using the "Latest" function). 2. Is there a better
way to accomplish this?
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

To get "prior" data you'd have to have something that indicates
chronological or ordinal position (event date or event number). You
could use a query like this:

PARAMETERS [Enter Event ID] Long;
SELECT *
FROM EventDetails
WHERE EventID = (SELECT MAX(EventID) FROM Events
WHERE EventID < [Enter Event ID])

English translation:

Get all Event detail records for the event that was immediately before
the user-entered event ID. This assumes the EventID is created in
ascending order, not random order.

Instead of the user-entered parameter you could use a reference to a
form:

Forms!FormName!EventIDControlName

- --
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQGOGzoechKqOuFEgEQJVFwCbBVaoClPKL7/XNALCW3q6Q3cVa SAAoLBn
inCVxR3AxVxcDQkbHqv9HXB1
=pxbR
-----END PGP SIGNATURE-----

.
Click to expand...
 
Thank-you. That worked great. I now have a query that
reliably pulls up the "Active" EventDetail records from
the previous event. I would now like to set the EventID
for those records equal to the current EventID. Again the
purpose is so that "Active" EventDetail records from one
Event are "copied" into the next Event (i.e. now
associated with the latest EventID). When I append the
query as it is, the records are readded to the
EventDetails table with the original EventID number. The
current eventID number can be taken from Forms!
frmEvent.EventID but I can't figure out how to associate
this EventID with the EventDetails retrieved from the
query. Any further assistance is greatly appreciated.
Click to expand...
< SNIP previous posts >

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

That sounds kinda wacky, 'cuz you're able to get the events you want
displayed in your subform. That query I showed you will work for all
events.

But, if you really want to do that ....

You'll have to update the EventIDs in the subform w/ the master form's
EventID. You can use the subform's RecordsetClone, iterate thru the
subform's recordset & change the EventID = master's EventID.

== air code == [this would be run from the master form]

dim rs as dao.recordset

set rs = Me!subformControlName.form.recordsetclone

with rs
do while not .eof
.Edit
!EventID = Me!EventID
.Update
.MoveNext
loop
end with

rs.close

== end air code ==

You'd need error traps & change names to fit your situation.

- --
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQGSMrYechKqOuFEgEQICnwCdEPi6NFvfl1pjE7/s5X/v9+kTWvUAnj3V
vw+mCBdRAIkQxyqzBZ15ZAqc
=1TLB
-----END PGP SIGNATURE-----
 
Back
Top