Checkbox based on date

[dgodfrey]

I have a checkbox on my form called ActiveCheck that I want to be checked if
the information has not expired. I have a textbox with a short date that is
an expiration date for the record.

What I want is for the database to compare the current date to the
expiration date and uncheck the checkbox once the expiration date has passed.
I want the initial state of the checkbox to be unchecked and when a new
record is added, that record automatically checks the checkbox.

The purpose of this is a quick visual indicator of which records are active
and which or not when we do a search.

Alternatively, I could have a checkbox called "Expired" that would be
checked when the expiration date passes, and be unchecked otherwise. Either
would work.

***The real trick is, we are going to have some that will never expire, and
I have yet to figure out how to make that work. I had thought about doing a
never/ X# of years type of thing, but that doesn't seem to plausible. I
really need to take this into consideration with this checkbox thing. I am
even open to an entirely different method of flagging active/unexpired and
Inactive/Expired records...just as long as it is obvious to any user the
status of the record, no matter how they find it (search, query, report,
form, etc.) Any suggestions are much appreciated.

I have tried using Date() and Now in my conditional statement and I keep
getting an "Else with no If" error message:

Private Sub Expires_AfterUpdate()
If Me.Expires < Now Then Me.ActiveCheck = True
Else
Me.ActiveCheck = False
End If
End Sub

It is probably something simple I am missing.

Thanks,

Derek

 

[Allen Browne]

Derek, would it be an error if your expiry date was passed, but the check
box did not match? If not, you should not have both in your table.

Instead, create a query, and type an expression like this into the Field
row:
ActiveCheck: (([Expiry Date] Is Null) OR ([Expiry Date] >= Date()))

This field will be True (-1) if the Expiry Date is blank or future, or False
(0) if if the Expiry Date is past. Now use this query as the source for your
form, report, or whereever you need it. And if you have things that never
expire, just leave the Expiry Date blank.

The important thing here is that it will never be wrong. You can't say that
if you store the value in a table. This is actully a really important design
principle. More information about that:
Calculated fields
at:
http://allenbrowne.com/casu-14.html

 

[dgodfrey]

Great. The only case in which the checkbox would not match would be if the
user decides to keep it active, even after it expires, by say, a manual click?

Stupid newby question here...I want the indicator of active/inactive to show
up in both the form and the reports. How/Where do I call the query to insure
the checkbox is always correct when the information (form or report) is
opened?

One other "trick" I want to pull off with this if possible is to have the
expiration date box "hidden" if it never expires. Would it be best to use a
command button on this, and how would I make it work so the box is "empty"
and "hidden" without messing up the function of the checkbox?

Thanks again,

Derek

Derek, would it be an error if your expiry date was passed, but the check
box did not match? If not, you should not have both in your table.

Instead, create a query, and type an expression like this into the Field
row:
ActiveCheck: (([Expiry Date] Is Null) OR ([Expiry Date] >= Date()))

This field will be True (-1) if the Expiry Date is blank or future, or False
(0) if if the Expiry Date is past. Now use this query as the source for your
form, report, or whereever you need it. And if you have things that never
expire, just leave the Expiry Date blank.

The important thing here is that it will never be wrong. You can't say that
if you store the value in a table. This is actully a really important design
principle. More information about that:
Calculated fields
at:
http://allenbrowne.com/casu-14.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have a checkbox on my form called ActiveCheck that I want to be checked
if
the information has not expired. I have a textbox with a short date that
is
an expiration date for the record.

What I want is for the database to compare the current date to the
expiration date and uncheck the checkbox once the expiration date has
passed.
I want the initial state of the checkbox to be unchecked and when a new
record is added, that record automatically checks the checkbox.

The purpose of this is a quick visual indicator of which records are
active
and which or not when we do a search.

Alternatively, I could have a checkbox called "Expired" that would be
checked when the expiration date passes, and be unchecked otherwise.
Either
would work.

***The real trick is, we are going to have some that will never expire,
and
I have yet to figure out how to make that work. I had thought about doing
a
never/ X# of years type of thing, but that doesn't seem to plausible. I
really need to take this into consideration with this checkbox thing. I
am
even open to an entirely different method of flagging active/unexpired and
Inactive/Expired records...just as long as it is obvious to any user the
status of the record, no matter how they find it (search, query, report,
form, etc.) Any suggestions are much appreciated.

I have tried using Date() and Now in my conditional statement and I keep
getting an "Else with no If" error message:

Private Sub Expires_AfterUpdate()
If Me.Expires < Now Then Me.ActiveCheck = True
Else
Me.ActiveCheck = False
End If
End Sub

It is probably something simple I am missing.

Thanks,

Derek

 

[Allen Browne]

Responses in-line.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Great. The only case in which the checkbox would not match would be if the
user decides to keep it active, even after it expires, by say, a manual
click?

So there could be valid cases where the expiry date is past, but the record
should still be active? If so (and assuming you can't just handle that by
clearing the expiry date), then you do need both the yes/no field and the
date in your table.

However, this would mean nothing automatically expires, and since you cannot
merely mark ALL the records as expired as soon as the date is reached, you
can't automate this process. Sounds like quite a bit of manual maintenance
to keep this up to date.

Stupid newby question here...I want the indicator of active/inactive to
show
up in both the form and the reports. How/Where do I call the query to
insure
the checkbox is always correct when the information (form or report) is
opened?

The suggestion was to put this calculated control in a query. Then use the
query as the source for your report or form, or any place where you
previously used the table.

One other "trick" I want to pull off with this if possible is to have the
expiration date box "hidden" if it never expires. Would it be best to use
a
command button on this, and how would I make it work so the box is "empty"
and "hidden" without messing up the function of the checkbox?

Not sure the purpose here. If there is no expiry date, the date box is null,
so is there really much difference between blank and hidden?

If you did need to enter an expiry date where there wasn't one previously,
how could the user do that if the text box is hidden?

In Form view, you could use the Current event of the form to
programmatically hide it. This won't work in Continuous or Datasheet view
though.

 

[dgodfrey]

Let me explain what I am doing and maybe that will help. I am creating a
database where we are going to track people who have been either banned from
our property for whatever reason, charged with Trespass, or both.

The idea of being able to force a record as active even though expired was
merely to allow an override option. It should only be a rare occasion when
one would expire to begin with, and even rarer to have to override an expired
record...but life has a way of throwing theory out the window as we all know.
Some bans could be short term, while most are permanent.

So let us go back to the beginning and rethink this:

I will leave the date box there and NOT hide it. Makes sense and is no big
deal.

That being the case, if we need to keep the record active, we can merely
extend/change the expiry date or clear the expiry date box making it "does
not expire"...is that correct? If so, then I need to do the following:

1. Use the query as the control source for the Expiry date text box? The
thing is, I was planning on putting calendar controls in for all my date
boxes. How do I pull the query then? I am still very new to this and
programming is NOT one of my talents.

2. Put a command button on the form to clear the expiry date and update the
checkbox accordingly.

Doing this would make everything automatic, but still allow us to override a
record in unusual cases?

I am trying to allow for any possible case and I really appreciate the help.

I hope this clarifies what I need to do.

Thanks,

Derek

 

[Allen Browne]

Would this structure work, Derick?
tblPerson, with fields:
PersonID AutoNum primary key (PK)
BanStart Date/Time
BanEnd Date/Time
AlwaysBanned Yes/No

Surname Text
...

Then create a query, and type an expression like this into the Field row
(all one line):
IsBanned: ([AlwaysBanned]) OR ([BanEnd] > Date()) OR
(([BanStart] < Date()) AND ([BanEnd] Is Null))

This field returns true if any of these conditions is met:
- your AlwaysBanned field is True,
- the BanEnd field is future,
- an open-ended ban is in force (i.e. started but never ends.)

That should do the job.

You might also want to consider whether you could ever need to track
multiple bans for one person. If so, there is a one-to-many relation between
persons and bans, so you would use 2 tables. AlwaysBanned stays in the
person table, whereas BanStart and BanEnd go in the related table.

I'm not really clear whey the AlwaysBanned is needed. Perhaps that is
independent of the actual ban, i.e. the 2 decisions are made by different
people.

 

[dgodfrey]

Responses are inline:

Would this structure work, Derick?
tblPerson, with fields:
PersonID AutoNum primary key (PK)
BanStart Date/Time
BanEnd Date/Time
AlwaysBanned Yes/No

Surname Text
...

I believe that will work. In my main form/Table, I have a primary key
autonumber that is the number of the actual Ban...they are sequential each
time a new ban is issued/entered. In this person table, how is the person ID
related to the ban number, if at all?

Also, I see you have a blank row and a field that is ... (elipses). Are
these significant in some way? Or just a way of separating from the rest of
the message body in the forum?

Then create a query, and type an expression like this into the Field row
(all one line):
IsBanned: ([AlwaysBanned]) OR ([BanEnd] > Date()) OR
(([BanStart] < Date()) AND ([BanEnd] Is Null))

This field returns true if any of these conditions is met:
- your AlwaysBanned field is True,
- the BanEnd field is future,
- an open-ended ban is in force (i.e. started but never ends.)

That should do the job.

You might also want to consider whether you could ever need to track
multiple bans for one person. If so, there is a one-to-many relation between
persons and bans, so you would use 2 tables. AlwaysBanned stays in the
person table, whereas BanStart and BanEnd go in the related table.

I am not sure we would ever issue more than one ban on a particular person,
unless it was following an expired ban during a different incident. We might
possibly issue more than one trespass warning to a single person, or more
than one legal charge depending on what occurred. But just for argument
sake, how would I handle a situation like this? I believe the solution
presented here will work for a single ban, but I know multiple things can get
complicated, especially for a newbie.

I'm not really clear whey the AlwaysBanned is needed. Perhaps that is
independent of the actual ban, i.e. the 2 decisions are made by different
people.

That is dependant on legality and severity of offense as well.
I just want to make sure I hit every possible scenario, so we can get the
statistical data we need and make sure problem people don't come around and
cause problems.

Thanks again for your help. You are awesome.

 

[Allen Browne]

Replies interspersed.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I believe that will work. In my main form/Table, I have a primary key
autonumber that is the number of the actual Ban...they are sequential each
time a new ban is issued/entered. In this person table, how is the person
ID
related to the ban number, if at all?

From the rest of your reply, it seems you are tracking bans rather than
people. Therefore the BanID is p.k. rather than the PersonID suggested.
Makes sense.

Also, I see you have a blank row and a field that is ... (elipses). Are
these significant in some way? Or just a way of separating from the rest
of
the message body in the forum?

Blank line should not be there. (This stuff can happen in newsreaders.)

The elipsis just means "and whatever other fields you need."

I am not sure we would ever issue more than one ban on a particular
person,
unless it was following an expired ban during a different incident. We
might
possibly issue more than one trespass warning to a single person, or more
than one legal charge depending on what occurred. But just for argument
sake, how would I handle a situation like this? I believe the solution
presented here will work for a single ban, but I know multiple things can
get
complicated, especially for a newbie.

Would you ever need to look at a person's history, to see whether they have
had any previous bans? If so, it makes sense to have a table of persons
separate from the table of bans. Each person is listed just once in the
Person table, with the PersonID primary key. The Ban table then contains a
PersonID field, not the person's names. Since the ban refers to the Persons,
you have a watertight way of tracking when one person has multiple bans.

This idea (the one-to-many relationship) is the core concept of relational
databases.

 

[dgodfrey]

After studying this for a bit, it seems reasonable that I should make four
tables. Bolo (the ban) - Currently the only table I have and contains all the
information we need to pull from - should only contain incident information
and a narrative along with the checkboxes (Active, 50B, Trespass Warning) and
dates (the original question); Suspect - To contain Name, address, License #,
photo, physical description, contact information, etc.; Petitioner - Contact
info, Name, etc.; Vehicle - Vehicle description.

Is that the best way to do this database? Secondly, can I have all data
entry and editing done from one (Bolo/Ban) form with all the controls on it I
need?

The main form allows all information entry. I have a search form to search
by suspect name, petitioner name, or date range (may add other criteria
later). Si I only have two forms to work with. My user sits down and chooses
to add a Bolo, he types everything in, clicks "save" and it is stored
permanently. When we need to see if someone has a Bolo, we search with
whatever criteria and it pulls it up in a printer-friendly, read-only, format
with everything all on ONE page, including the photo...a single sheet Suspect
identification page with all the necessary information.

Is this possible with this set-up?

Thanks again,

Derek

 

[Allen Browne]

I suggest you do this as a form with a subform.

The main form is bound to the Suspects table, so there the user either finds
the existing suspect, or enters a new one.

The subform is bound to the Bolo table. Once they have the right person in
the main form, they enter the details of the ban in the subform.

 

[dgodfrey]

Ok. I realize we have strayed off-topic a bit here. I want to make sure I
have this right.

1. I have gone back and created a "petitioner" table and a "Suspect" table.
I used the wizard to do this. Each has an autonumber primary key. Both are
related to the "Bolo" table, each being the "one" and the "Bolo" table being
the "many" and it has been edited down to the necessary fields that are not
covered in the other tables.

Should the "petitioner" table also be related to the "suspect" table? If so,
which should be the "one" and which the "many"?

2. I created 2 forms using the wizard. One is a form using all fields from
the "Suspect" table, and the other uses all fields from the "Bolo" table. I
chose the "Justified" layout option to save space on the form.

3. I drug the "bolo" form onto the "suspect" form in design view to create
the subform as you suggested.

Assuming all of this was done correctly, my problem is that all this seems
to over-complicate my form design and is restricting me from laying out the
controls the way I'd like. The work flow does not seem to go smoothly, like
it did when I had everything on one form ("page"). For example, I have the
"narrative" memo box on the "bolo" subform, when I really want it on the
bottom of the main form. However, this information has to be tied to the
bolo, rather than the suspect, as it is incident specific. There are a few
other controls I would possibly like located differently. The other annoyance
is the subform has scrollbars showing and the navigation control. I know I
can hide the navigation control, but how to size without it being obvious it
is a subform?

Now, the million dollar question is this: Would it not be simpler to just
assign control source to the correct table and just lay everything out the
way I want? I did try that and I am getting #name in those text boxes that
reference the "suspect" or "petitioner" tables on my original form.

What am I missing? How do the SuspectID and PetitionerID keys/numbers fit
into matching the right bolo with the right suspect and petitioner? Can I
still search by last name? Also, the only autonumber I want displayed is the
Bolo # in the "bolo" table.

These are alot of silly newbie questions I know, but I really appreciate
your time and help and I am learning a load of stuff here.

Thanks,

Derek

 

[Allen Browne]

Responses in-line.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

1. I have gone back and created a "petitioner" table and a "Suspect"
table.
I used the wizard to do this. Each has an autonumber primary key. Both are
related to the "Bolo" table, each being the "one" and the "Bolo" table
being
the "many" and it has been edited down to the necessary fields that are
not
covered in the other tables.

Should the "petitioner" table also be related to the "suspect" table? If
so,
which should be the "one" and which the "many"?

No, that sounds. Your petitioner and suspect are not directly related: only
via an incident.

2. I created 2 forms using the wizard. One is a form using all fields from
the "Suspect" table, and the other uses all fields from the "Bolo" table.
I
chose the "Justified" layout option to save space on the form.

3. I drug the "bolo" form onto the "suspect" form in design view to create
the subform as you suggested.

Assuming all of this was done correctly, my problem is that all this seems
to over-complicate my form design and is restricting me from laying out
the
controls the way I'd like. The work flow does not seem to go smoothly,
like
it did when I had everything on one form ("page"). For example, I have
the
"narrative" memo box on the "bolo" subform, when I really want it on the
bottom of the main form. However, this information has to be tied to the
bolo, rather than the suspect, as it is incident specific. There are a
few
other controls I would possibly like located differently. The other
annoyance
is the subform has scrollbars showing and the navigation control. I know I
can hide the navigation control, but how to size without it being obvious
it
is a subform?

The main point of the structure is tracking which incidents the petitioners
and suspects have been involved in (their histories.) Over the years, one
suspect could be involved in multiple incicents. The main form lets you find
(or enter) the suspect. The subform then shows the incident(s) they have
been involved in.

Since there could be mutliple incidents, the subform does need a mechanism
for moving from incident to incident. The nav buttons are the obvious way to
do this.

If you don't need to track the incidents for a suspect, then you don't need
this structure. If you do want to track this history, then you do need this
kind of structure, and the form/subform is the easiest way to interface it.
So, when you move to the suspect's 2nd incident in the subform, the comments
about *that* incident show in the subform. This won't happen if you put the
comments back on the main form.

Developing the mind-set to clearly differentiate about these one-to-many
relations and what goes where is crucial. There is also a question about the
best interface, but that's secondary to the clear structure.

Now, the million dollar question is this: Would it not be simpler to just
assign control source to the correct table and just lay everything out the
way I want? I did try that and I am getting #name in those text boxes that
reference the "suspect" or "petitioner" tables on my original form.

No: it's not practical (nor useful) to split the data up properly into a
one-to-many relationship, and then try to treat the whole thing as if it
were a single entity in the way you interface it.

What am I missing? How do the SuspectID and PetitionerID keys/numbers fit
into matching the right bolo with the right suspect and petitioner? Can I
still search by last name? Also, the only autonumber I want displayed is
the
Bolo # in the "bolo" table.

There are multiple ways to interface SuspectID and PetitionerID without
having to show the AutoNumber values. One way is to use a combo box, where
the RowSource is something like this:
SELECT PetitionerID, Surname & ", " + Firstname AS PetitionerName
FROM tblPetitioner
ORDER BY Surname, FirstName, PetitionerID;
Then set the first column to zero-width, so it stores the hidden
PetitionerID value, but displays the name:
Column Count 2
Column Widths 0
Bound Column 1

 

[dgodfrey]

I am still a little unclear on that last bit:

There are multiple ways to interface SuspectID and PetitionerID without
having to show the AutoNumber values. One way is to use a combo box, where
the RowSource is something like this:
SELECT PetitionerID, Surname & ", " + Firstname AS PetitionerName
FROM tblPetitioner
ORDER BY Surname, FirstName, PetitionerID;
Then set the first column to zero-width, so it stores the hidden
PetitionerID value, but displays the name:
Column Count 2
Column Widths 0
Bound Column 1

I need to do this in my search form? I am also not sure how to set those
values you mention...do I do that in the control, the form, the table?

If I do not include those items on my main or subforms, is it going to
create a problem? I have deleted them from the form, but not the tables. I
have no desire nor need to display those IDs.

One more question. I have added text boxes for the Petitioner info on the
"bolo" subform and used =[Petitioners]![LastName] as the control source for
the Petitioner's Last Name text box, and similar expression for the others.
It is giving me "#Name?" in the textboxes on form view. Did I reference this
incorrectly, or do I need a sub-subform to make this work? I realize this is
still just the beginning, but I am getting it there slowly but surely with
your help and others on these forums.

Again, you are awesome and thanks for your kind patience and wonderful help.

Thanks again.

 

[dgodfrey]

Got this part fixed I think by using DLookup:

One more question. I have added text boxes for the Petitioner info on the
"bolo" subform and used =[Petitioners]![LastName] as the control source for
the Petitioner's Last Name text box, and similar expression for the others.
It is giving me "#Name?" in the textboxes on form view. Did I reference this
incorrectly, or do I need a sub-subform to make this work?

The rest I still need answered.
Thanks.

I am still a little unclear on that last bit:

There are multiple ways to interface SuspectID and PetitionerID without
having to show the AutoNumber values. One way is to use a combo box, where
the RowSource is something like this:
SELECT PetitionerID, Surname & ", " + Firstname AS PetitionerName
FROM tblPetitioner
ORDER BY Surname, FirstName, PetitionerID;
Then set the first column to zero-width, so it stores the hidden
PetitionerID value, but displays the name:
Column Count 2
Column Widths 0
Bound Column 1

I need to do this in my search form? I am also not sure how to set those
values you mention...do I do that in the control, the form, the table?

If I do not include those items on my main or subforms, is it going to
create a problem? I have deleted them from the form, but not the tables. I
have no desire nor need to display those IDs.

One more question. I have added text boxes for the Petitioner info on the
"bolo" subform and used =[Petitioners]![LastName] as the control source for
the Petitioner's Last Name text box, and similar expression for the others.
It is giving me "#Name?" in the textboxes on form view. Did I reference this
incorrectly, or do I need a sub-subform to make this work? I realize this is
still just the beginning, but I am getting it there slowly but surely with
your help and others on these forums.

Again, you are awesome and thanks for your kind patience and wonderful help.

Thanks again.

Responses in-line.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.



No, that sounds. Your petitioner and suspect are not directly related: only
via an incident.


The main point of the structure is tracking which incidents the petitioners
and suspects have been involved in (their histories.) Over the years, one
suspect could be involved in multiple incicents. The main form lets you find
(or enter) the suspect. The subform then shows the incident(s) they have
been involved in.

Since there could be mutliple incidents, the subform does need a mechanism
for moving from incident to incident. The nav buttons are the obvious way to
do this.

If you don't need to track the incidents for a suspect, then you don't need
this structure. If you do want to track this history, then you do need this
kind of structure, and the form/subform is the easiest way to interface it.
So, when you move to the suspect's 2nd incident in the subform, the comments
about *that* incident show in the subform. This won't happen if you put the
comments back on the main form.

Developing the mind-set to clearly differentiate about these one-to-many
relations and what goes where is crucial. There is also a question about the
best interface, but that's secondary to the clear structure.


No: it's not practical (nor useful) to split the data up properly into a
one-to-many relationship, and then try to treat the whole thing as if it
were a single entity in the way you interface it.


There are multiple ways to interface SuspectID and PetitionerID without
having to show the AutoNumber values. One way is to use a combo box, where
the RowSource is something like this:
SELECT PetitionerID, Surname & ", " + Firstname AS PetitionerName
FROM tblPetitioner
ORDER BY Surname, FirstName, PetitionerID;
Then set the first column to zero-width, so it stores the hidden
PetitionerID value, but displays the name:
Column Count 2
Column Widths 0
Bound Column 1

 

[Allen Browne]

Use the combo wherever you want it. If the main form is bound to the Suspect
table, then you don't need a combo for the SuspectID in the subform, but you
do need one for the PetitionerID.

The tables will store the ID numbers, but that is not the user interface.
You use forms for that, with combos on the forms where needed.

(Okay, you can actually put a combo in your table, by setting the
DisplayControl properties of the field in the lower pane of table design.
But I don't recommend you do that, as people get confused about what's
actually stored there.

 

[dgodfrey]

Response inline.

Use the combo wherever you want it. If the main form is bound to the Suspect
table, then you don't need a combo for the SuspectID in the subform, but you
do need one for the PetitionerID.

Ok. So I need to add a combo box to my subform bound to PetitionerID? Can I
set the "visible" property to no with no problems (it will still do what it
needs, but the user doesn't see it)?

The tables will store the ID numbers, but that is not the user interface.
You use forms for that, with combos on the forms where needed.

(Okay, you can actually put a combo in your table, by setting the
DisplayControl properties of the field in the lower pane of table design.
But I don't recommend you do that, as people get confused about what's
actually stored there.

That is cool, but if the above will work that will be fine.

 

[Allen Browne]

I don't understand how the user can associate the petitioner with the ban if
you hide the control.

 

[dgodfrey]

I don't want the user to see the autonumber, it will only confuse them. What
they see is the Petitioner's First Name, Last Name, Department, and phone
Extension (pulled from the Petitioner's table and bound with DLookup...each
item mentioned is its own text box on the Bolo subform). This data will be
displayed with the ban info, or be filled in at data entry time on a blank
form, along with the rest of the ban info. The typical scenario is going to
be that we get a complaint and we enter a new BOLO, including the suspect
info, as most cases will be a new suspect, rather than an existing
one...though there are repeat offenders that violate Trespass warnings and
will need a new BOLO done. BOLO stands for "Be on The Lookout" and would be
similar to a "wanted" poster, but with a slightly different purpose.

I hope this is making sense now? The user should think they are filling in
a single form, or viewing a completed form to be printed. They just click or
tab through the fields and everything is saved at the click of a button, then
they can later view or print what they entered. This is hopefully going to be
a piece of an Incident Management system. I may create other "modules" at a
later date.

Thanks again,

Derek

 

[Allen Browne]

I don't want the user to see the autonumber, it will only confuse them.
What
they see is the Petitioner's First Name, Last Name, Department, and phone
Extension (pulled from the Petitioner's table and bound with
DLookup...each
item mentioned is its own text box on the Bolo subform).

That's fine for display purposes. There is no need to show the PetitionerID
number.

I guess you must have a different form for *entering* a new petitioner, and
then associating that petitioner with a ban.

 

[dgodfrey]

I am entering the new petitioner information as part of the new ban
information all on the same subform. If that is not going to work, I can
change it...I have not had time to test it yet. WHile the information is
stored in separate tables, I do not need see any need to isolate the two sets
of information, as there is usually only one Petitioner (complainant) per
Incident and since there is one Bolo per Incident, the Petitioner may or may
not be different on a second Bolo, should there be more than one Incident.
The department will probably be the Petitioner on any subsequent Bolo, but
not necessarily. Am I confusing the issue further? I do not want the user to
have to keep track of an assigned number for anything...just names.
Thanks.