Setting field value if a value exists in another table

[Rustle]

I work for a shelter and occasionally we have to ask a guest to leave
for some reason. We call this being "barred" from our services. When
a staff person opens the main data entry form, based on "tblGuest",
there is a subform on a tab where this information can be entered. If
a guest is barred, a record is placed using the subform in
"tblBarInformation". The fields in this table are "BarID",
"BarLink" (equal to GuestID from tblGuest), "BarDate", and
"BarExpireDate."

Basically, I want to create a field in tblGuest that indicates the
guest's status as Barred if there is a current bar record in
tblBarInformation. So far, I've created a query such as this:

Field: BarID BarLink BarCurrent: [BarExpireDate]
Totals: GroupBy Group By Last
Criteria: >=Date()

This gives me all the bars that are current as of today. I want to
use this information to update the guest table somehow if the guest
has a current bar. I can't trust staff people to do this as they
aren't always on top of things.

More simply, how can I set a value in one table if there is date value
in another table that is equal to today or in the future { >=
Date() }?

Could I could create some kind of update query that runs when the main
form is opened to that guests record and that would set the status in
Guest? Or, should I somehow run an event in the subform when data is
updated there?

Thanks!

Russ

 

[Access Developer]

You do not need to violate relational database design principles as you
suggest to accomplish your purpose. Determine the "barred status" in the
query that you use to populate the form used for registering the guests.
Use the TOP property on the query of tblBarInformation to pick up the latest
entry, or show it in a subform (where you have used the TOP property).

If you have inexperienced volunteers directly accessing tables, rather than
using a developed application, you have a far bigger problem than the one
about which you inquired.

 

[John Spencer]

First don't use LAST to the get the latest BarExpireDate. Use MAX to get the
latest date. Last returns the value of the last record found in the group.
It often will correspond to the latest date, but it is not guaranteed to do so.

Second you should not store a value in tblGuest that can be calculated and
therefore will always be in agreement with the data stored in tblBarInformation.

In a query you could use an exists clause to return true (-1) or false (0)

Field: Barred: Exists(SELECT * FROM tblBarInformation
WHERE tblBarInformation.Barlink = tblGuest.GuestID
GROUP BY BarLink
HAVING Max(BarExpireDate)>= Date())

You could get fancy with that and return the word "Barred" or a blank or null
value if you wished.

Field: Barred: IIF(Exists(SELECT * FROM tblBarInformation
WHERE tblBarInformation.Barlink = tblGuest.GuestID
GROUP BY BarLink
HAVING Max(BarExpireDate)>= Date()),"Barred",Null)

If you really feel that you have to store the information you will need to run
an update query using one of the VBA domain functions to generate the needed
data. ALSO, you will have to update the value when the date has passed, so
you will need to check every time you look at a record.

Alternative (Assuming that GuestID is a number field you could use an
expression like the following to return the BarExpireDate.
Field: Barred Until:
Dmax("BarExpireDate","tblBarInformation","BarExpireDate>=Date() and Barlink =
" & tblGuest.GuestID )

If GuestID is a text field then the expression would be
Dmax("BarExpireDate","tblBarInformation","BarExpireDate>=Date() and Barlink =
""" & tblGuest.GuestID & """")

John Spencer
Access MVP 2002-2005, 2007-2011

I work for a shelter and occasionally we have to ask a guest to leave
for some reason. We call this being "barred" from our services. When
a staff person opens the main data entry form, based on "tblGuest",
there is a subform on a tab where this information can be entered. If
a guest is barred, a record is placed using the subform in
"tblBarInformation". The fields in this table are "BarID",
"BarLink" (equal to GuestID from tblGuest), "BarDate", and
"BarExpireDate."

Basically, I want to create a field in tblGuest that indicates the
guest's status as Barred if there is a current bar record in
tblBarInformation. So far, I've created a query such as this:

Field: BarID BarLink BarCurrent: [BarExpireDate]
Totals: GroupBy Group By Last
Criteria:>=Date()

This gives me all the bars that are current as of today. I want to
use this information to update the guest table somehow if the guest
has a current bar. I can't trust staff people to do this as they
aren't always on top of things.

More simply, how can I set a value in one table if there is date value
in another table that is equal to today or in the future {>=
Date() }?

Could I could create some kind of update query that runs when the main
form is opened to that guests record and that would set the status in
Guest? Or, should I somehow run an event in the subform when data is
updated there?

Thanks!

Russ

 

[Bob Quintal]

m:

I work for a shelter and occasionally we have to ask a guest to
leave for some reason. We call this being "barred" from our
services. When a staff person opens the main data entry form,
based on "tblGuest", there is a subform on a tab where this
information can be entered. If a guest is barred, a record is
placed using the subform in "tblBarInformation". The fields in
this table are "BarID", "BarLink" (equal to GuestID from
tblGuest), "BarDate", and "BarExpireDate."

Basically, I want to create a field in tblGuest that indicates the
guest's status as Barred if there is a current bar record in
tblBarInformation. So far, I've created a query such as this:

Field: BarID BarLink BarCurrent:
[BarExpireDate] Totals: GroupBy Group By Last
Criteria: >=Date()

This gives me all the bars that are current as of today. I want
to use this information to update the guest table somehow if the
guest has a current bar. I can't trust staff people to do this as
they aren't always on top of things.

More simply, how can I set a value in one table if there is date
value in another table that is equal to today or in the future {

= Date() }?

Could I could create some kind of update query that runs when the
main form is opened to that guests record and that would set the
status in Guest? Or, should I somehow run an event in the subform
when data is updated there?

Thanks!

Russ

Actually, none of your suggestions is the correct one.
The status should simply be dLookup()ed whenever needed

You do not need the BarID in your query,Just put a textbox on your
main form, labeled "Barred Until" that contains
=dLookup("BarCurrent", "qryBarCurrent", "BarLink = " &me.guestid &")"
That will pull the data from the query onto the form.

Since your users should never be using the table directly it's not
necessary to have that date in tblGuest.

You can also use the dlookup() in queries and reports.