Export list/show/select on the form.

  • Thread starter Thread starter LeeTV
  • Start date Start date
L

LeeTV

Hi,
I'm using the Event Management database (Microsoft product) to track
students that have taken First Aid/CPR courses etc.... In the database I have
had to add one or two extra fields and customize it to my needs. One of the
main fields that I've had to add is in the Registration table and is a
"CourseExprationDate" date field. Most people that take a course have to come
back before the expiration date and take either a renewal/full course over
again. In order to track who is expiring and mail merge to an email I'm
trying to show a list on the form (in a list box) based on a query that will
show me the people that are expiring--then from that list I can select the
people that I wish to send the expiration reminder email to.

The problem that I'm having is that the registration table is now a few
years old and a lot of people have already renewed their credential.
Therefore, people are getting emails when they have already renewed.

The query that I have for the list box is as follows: (SQL below)

SELECT Attendees.AttendeeID, Attendees.AttendeeTitle,
Attendees.AttendeeFirstName, Attendees.AttendeeMiddleName,
Attendees.AttendeeLastName, Attendees.AttendeeCredentials, Attendees.Address,
Attendees.Address2, Attendees.City, Attendees.PostalCode,
Attendees.StateOrProvince, Attendees.PhoneNumber, Attendees.CellPhoneNumber,
Attendees.FaxNumber, Attendees.EmailName, [Event Types].EventType,
Registration.Role, Registration.CourseExpirationDate,
DLookUp("RegistrationID","Registration","attendeeid = [attendeeid] and
courseexpirationdate < now()") AS Renewed
FROM ((Attendees LEFT JOIN Registration ON Attendees.AttendeeID =
Registration.AttendeeID) LEFT JOIN Events ON Registration.EventID =
Events.EventID) LEFT JOIN [Event Types] ON Events.EventTypeID = [Event
Types].EventTypeID
WHERE (((Registration.Role)="student") AND
((Registration.CourseExpirationDate) Like "*2009*"));

You may gather from this that I’m attempting to use the "Renewed" field to
populate the ReigstrationID that they use has renewed their credential under.
(The EventTypeID is the field that will help differentiate if it was a
different level of course (I.e. 1=General Public CPR/2=Healthcare provider
CPR), I've tried to use a DLookup to find if there is a new record that has
the same coursetypeID and not expired. The DLookup function doesn't work and
I’ve tried many ways to configure the criteria to populate the correct value.
I’ve been able to modify the above DLookup fxn (the one above didn’t give me
any results and gave errors as to wanting to find fields/tables that already
existed). When I took out most of the dlookup criteria I was able to get it
to look up the reg id, but every row in the recordset was set to the same
value.

I'm coming to the conclusion that the DLookup function is probably not the
best method to use for this problem. I'm getting a feeling that 1) I’m
completely going about this the wrong way, or 2) I’m going to have to
populate the list box via VBA and not try to use a query. If both are
possible I'd be very interested, for educational purposes, to see how both
would be constructed.

I would appreciate any help that anyone maybe able to offer for this
solution. Please post to the newsgroup so all can benefit.

Thanks in advance.
Lee
 
the form is unbound and the purpose of the list box is to show the list of
students that are expiring that have not renewed.
All ID Fields are numeric.

the solution you gave me, gives the registration id number of the same row.
how can i get it to show the row of a renewed course (if there is one)?
thanks
lee


June7 via AccessMonster.com said:
The Select query is in the RowSource property of the listbox? The DLookUp
needs to use attendeeid value from the form's recordset? Try this syntax for
the DLookUp, test it in a textbox.
DLookUp("RegistrationID","Registration","attendeeid = '" & [attendeeid] & "'
And
courseexpirationdate < Now()") As Renewed
Is attendeeID text or numeric? If numeric, don't need the apostrophe
delimiters
Hi,
I'm using the Event Management database (Microsoft product) to track
students that have taken First Aid/CPR courses etc.... In the database I have
had to add one or two extra fields and customize it to my needs. One of the
main fields that I've had to add is in the Registration table and is a
"CourseExprationDate" date field. Most people that take a course have to come
back before the expiration date and take either a renewal/full course over
again. In order to track who is expiring and mail merge to an email I'm
trying to show a list on the form (in a list box) based on a query that will
show me the people that are expiring--then from that list I can select the
people that I wish to send the expiration reminder email to.

The problem that I'm having is that the registration table is now a few
years old and a lot of people have already renewed their credential.
Therefore, people are getting emails when they have already renewed.

The query that I have for the list box is as follows: (SQL below)

SELECT Attendees.AttendeeID, Attendees.AttendeeTitle,
Attendees.AttendeeFirstName, Attendees.AttendeeMiddleName,
Attendees.AttendeeLastName, Attendees.AttendeeCredentials, Attendees.Address,
Attendees.Address2, Attendees.City, Attendees.PostalCode,
Attendees.StateOrProvince, Attendees.PhoneNumber, Attendees.CellPhoneNumber,
Attendees.FaxNumber, Attendees.EmailName, [Event Types].EventType,
Registration.Role, Registration.CourseExpirationDate,
DLookUp("RegistrationID","Registration","attendeeid = [attendeeid] and
courseexpirationdate < now()") AS Renewed
FROM ((Attendees LEFT JOIN Registration ON Attendees.AttendeeID =
Registration.AttendeeID) LEFT JOIN Events ON Registration.EventID =
Events.EventID) LEFT JOIN [Event Types] ON Events.EventTypeID = [Event
Types].EventTypeID
WHERE (((Registration.Role)="student") AND
((Registration.CourseExpirationDate) Like "*2009*"));

You may gather from this that I’m attempting to use the "Renewed" field to
populate the ReigstrationID that they use has renewed their credential under.
(The EventTypeID is the field that will help differentiate if it was a
different level of course (I.e. 1=General Public CPR/2=Healthcare provider
CPR), I've tried to use a DLookup to find if there is a new record that has
the same coursetypeID and not expired. The DLookup function doesn't work and
I’ve tried many ways to configure the criteria to populate the correct value.
I’ve been able to modify the above DLookup fxn (the one above didn’t give me
any results and gave errors as to wanting to find fields/tables that already
existed). When I took out most of the dlookup criteria I was able to get it
to look up the reg id, but every row in the recordset was set to the same
value.

I'm coming to the conclusion that the DLookup function is probably not the
best method to use for this problem. I'm getting a feeling that 1) I’m
completely going about this the wrong way, or 2) I’m going to have to
populate the list box via VBA and not try to use a query. If both are
possible I'd be very interested, for educational purposes, to see how both
would be constructed.

I would appreciate any help that anyone maybe able to offer for this
solution. Please post to the newsgroup so all can benefit.

Thanks in advance.
Lee
Click to expand...
Click to expand...
 
i think i figured it out:
the dlookup is now as follows: RenewedRegID:
DLookUp("RegistrationID","Registration","AttendeeID = " &
[registration].[AttendeeID] & "and EventTypeMasterID = " &
[registration].[EventTypeMasterID] & " and CourseExpirationDateFull > now()
and not registrationid = " & [Registration].[Registrationid])

i've not found any errors yet, but i'm checking. if anyone can see any flaws
to it please post.

thanks.
lee



June7 via AccessMonster.com said:
The Select query is in the RowSource property of the listbox? The DLookUp
needs to use attendeeid value from the form's recordset? Try this syntax for
the DLookUp, test it in a textbox.
DLookUp("RegistrationID","Registration","attendeeid = '" & [attendeeid] & "'
And
courseexpirationdate < Now()") As Renewed
Is attendeeID text or numeric? If numeric, don't need the apostrophe
delimiters
Hi,
I'm using the Event Management database (Microsoft product) to track
students that have taken First Aid/CPR courses etc.... In the database I have
had to add one or two extra fields and customize it to my needs. One of the
main fields that I've had to add is in the Registration table and is a
"CourseExprationDate" date field. Most people that take a course have to come
back before the expiration date and take either a renewal/full course over
again. In order to track who is expiring and mail merge to an email I'm
trying to show a list on the form (in a list box) based on a query that will
show me the people that are expiring--then from that list I can select the
people that I wish to send the expiration reminder email to.

The problem that I'm having is that the registration table is now a few
years old and a lot of people have already renewed their credential.
Therefore, people are getting emails when they have already renewed.

The query that I have for the list box is as follows: (SQL below)

SELECT Attendees.AttendeeID, Attendees.AttendeeTitle,
Attendees.AttendeeFirstName, Attendees.AttendeeMiddleName,
Attendees.AttendeeLastName, Attendees.AttendeeCredentials, Attendees.Address,
Attendees.Address2, Attendees.City, Attendees.PostalCode,
Attendees.StateOrProvince, Attendees.PhoneNumber, Attendees.CellPhoneNumber,
Attendees.FaxNumber, Attendees.EmailName, [Event Types].EventType,
Registration.Role, Registration.CourseExpirationDate,
DLookUp("RegistrationID","Registration","attendeeid = [attendeeid] and
courseexpirationdate < now()") AS Renewed
FROM ((Attendees LEFT JOIN Registration ON Attendees.AttendeeID =
Registration.AttendeeID) LEFT JOIN Events ON Registration.EventID =
Events.EventID) LEFT JOIN [Event Types] ON Events.EventTypeID = [Event
Types].EventTypeID
WHERE (((Registration.Role)="student") AND
((Registration.CourseExpirationDate) Like "*2009*"));

You may gather from this that I’m attempting to use the "Renewed" field to
populate the ReigstrationID that they use has renewed their credential under.
(The EventTypeID is the field that will help differentiate if it was a
different level of course (I.e. 1=General Public CPR/2=Healthcare provider
CPR), I've tried to use a DLookup to find if there is a new record that has
the same coursetypeID and not expired. The DLookup function doesn't work and
I’ve tried many ways to configure the criteria to populate the correct value.
I’ve been able to modify the above DLookup fxn (the one above didn’t give me
any results and gave errors as to wanting to find fields/tables that already
existed). When I took out most of the dlookup criteria I was able to get it
to look up the reg id, but every row in the recordset was set to the same
value.

I'm coming to the conclusion that the DLookup function is probably not the
best method to use for this problem. I'm getting a feeling that 1) I’m
completely going about this the wrong way, or 2) I’m going to have to
populate the list box via VBA and not try to use a query. If both are
possible I'd be very interested, for educational purposes, to see how both
would be constructed.

I would appreciate any help that anyone maybe able to offer for this
solution. Please post to the newsgroup so all can benefit.

Thanks in advance.
Lee
Click to expand...
Click to expand...
 
Back
Top