Hi everybody-

I inherited a database that somehow uses a bound combo box as a record
selector. Let me give you some background. The form is based on data
from 2 tables. The first table, Person, records info about a person.
The second table, Case, records information about a person's case,
almost like a human resources database.

The primary key of the table, person, is his/ her social security
number. The primary key of the other table, case, is just caseid, a
number.

The first step is to enter info about a person into a basic form based
only on the person table, let's say form 1. If a person has a case
then data about that case is entered into a different form. This form,
let's say form 2, is the form in question.

This form, form 2, uses a query to retrieve all the info from both
tables, person and case. The top of this form displays the person's
info, and the bottom of the form displays info about the case.

If a person has a case, then the user clicks a command button to open
form 2 to enter data about the case. When the user first opens this
form, it is blank. So to select the appropriate person, the user
selects the person's ss# from a combo box to populate all of the
fields on the form that are associated with info about the person.
Then the user can enter data about the case into the appropriate
fields.

I can't seem to figure out how the record selector box works while
being bound to the ss# field of the person table.

The person info on this form used to be populated with a macro after
the ss# was selected from the combo box. But I thought it was better
to inclu

Megan you need 2 tables as follows. Note that ssn is not a reliable key
field so I've changed as below"

tblPerson
PersonID(primary key - Autonumber)
fName
lname
etc..

tblCases
CaseID(primary key - Autonumber)
PersonID(foreign key - Long Integer)
other case fields

Now design a Main form (I'll call it frmMain) and use the person table/query
as the recordsource for the form. Add fields to the form set it to Single
form view. In the tool box make sure the wand with stars button is
selected(wizard button) then click the combo box option to place a combo box
on your form. The wizard will open and select the option to Find a record
on your form....Select the fields you want to display in the combo box.
Close and save form.
Design another form based on the cases table/query and add the fields you
want. Set the view to either Continuous or datasheet view. Close and save
the form(I'll call it frmSub).
Open the frmMain in design view and move it to the side so that you can see
the Object browser. Click on the frmSub name in the object browser and drag
and drop it in the detail section of your frmMain. Click/select the frmSub
(just click the outer shell of the frmSub so that sizing handles are
visible. Now select view/properties from the main menu. On the all tab
make sure that link master & child fields is set to PersonID. If not type
it in. Save the form.

Now open the form and select a Person from the combo box. After you do you
will be taken to that Person record. If that person has Cases assigned to
them they will be shown on your subform. Now you can add cases for the
person you have selected on the main form and each time you do so that case
will automatically be assigned the PersonID and will forever be linked to
that Person.

Hope this helps!

--
Reggie

----------
"Megan" <meganrobertson22@hotmail.com> wrote in message
news:5c14c12b.0408011207.4dd9618c@posting.google.c om...[color=blue]
> Hi everybody-
>
> I inherited a database that somehow uses a bound combo box as a record
> selector. Let me give you some background. The form is based on data
> from 2 tables. The first table, Person, records info about a person.
> The second table, Case, records information about a person's case,
> almost like a human resources database.
>
> The primary key of the table, person, is his/ her social security
> number. The primary key of the other table, case, is just caseid, a
> number.
>
> The first step is to enter info about a person into a basic form based
> only on the person table, let's say form 1. If a person has a case
> then data about that case is entered into a different form. This form,
> let's say form 2, is the form in question.
>
> This form, form 2, uses a query to retrieve all the info from both
> tables, person and case. The top of this form displays the person's
> info, and the bottom of the form displays info about the case.
>
> If a person has a case, then the user clicks a command button to open
> form 2 to enter data about the case. When the user first opens this
> form, it is blank. So to select the appropriate person, the user
> selects the person's ss# from a combo box to populate all of the
> fields on the form that are associated with info about the person.
> Then the user can enter data about the case into the appropriate
> fields.
>
> I can't seem to figure out how the record selector box works while
> being bound to the ss# field of the person table.
>
> The person info on this form used to be populated with a macro after
> the ss# was selected from the combo box. But I thought it was better
> to inclu[/color]

"Reggie" <NoSpam_webmaster@NoSpam_smittysinet.com> wrote in message
news:O-qdnW3yZPKZ95DcRVn-tA@comcast.com...[color=blue]
> Megan you need 2 tables as follows. Note that ssn is not a reliable key
> field so I've changed as below"
>[/color]

What is unreliable about a social security number? IMO it makes an excellent
key..The exception is the case where the ssn might not be known when the
record is created.

John, the reason I said that is because it seems I've seen in several
posting to these news groups that though rare there have been cases of
people with duplicate ssn's. The other point you made sort of backs my
preference of using an autonumber key field, because if your relationships
are correct you could not add a record without knowing the ssn before.
Using the autonumber allows you to add it, continue your work, and come back
to update the ssn once you find out what it is. But again, this is only my
opinion and preference. Use what you want of course.

--
Reggie

----------
"John Winterbottom" <assaynet@hotmail.com> wrote in message
news:2n59c9Fscl10U1@uni-berlin.de...[color=blue]
> "Reggie" <NoSpam_webmaster@NoSpam_smittysinet.com> wrote in message
> news:O-qdnW3yZPKZ95DcRVn-tA@comcast.com...[color=green]
> > Megan you need 2 tables as follows. Note that ssn is not a reliable key
> > field so I've changed as below"
> >[/color]
>
> What is unreliable about a social security number? IMO it makes an[/color]
excellent[color=blue]
> key..The exception is the case where the ssn might not be known when the
> record is created.
>
>[/color]

"John Winterbottom" <assaynet@hotmail.com> wrote in message
news:2n59c9Fscl10U1@uni-berlin.de...[color=blue]
> "Reggie" <NoSpam_webmaster@NoSpam_smittysinet.com> wrote in message
> news:O-qdnW3yZPKZ95DcRVn-tA@comcast.com...[color=green]
> > Megan you need 2 tables as follows. Note that ssn is not a reliable key
> > field so I've changed as below"
> >[/color]
>
> What is unreliable about a social security number? IMO it makes an[/color]
excellent[color=blue]
> key..The exception is the case where the ssn might not be known when the
> record is created.[/color]

From what I've heard, there have been cases of duplicated SSN numbers.
Couple that with SSN fraud, and I'd have to agree with Reggie that it's not
a reliable key.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

In message <9XoPc.10$Qgc.6@news04.bloor.is.net.cable.rogers.c om>,
Douglas J. Steele <NOSPAM_djsteele@NOSPAM_canada.com> writes[color=blue]
>"John Winterbottom" <assaynet@hotmail.com> wrote in message
>news:2n59c9Fscl10U1@uni-berlin.de...[color=green]
>> "Reggie" <NoSpam_webmaster@NoSpam_smittysinet.com> wrote in message
>> news:O-qdnW3yZPKZ95DcRVn-tA@comcast.com...[color=darkred]
>> > Megan you need 2 tables as follows. Note that ssn is not a reliable key
>> > field so I've changed as below"
>> >[/color]
>>
>> What is unreliable about a social security number? IMO it makes an[/color]
>excellent[color=green]
>> key..The exception is the case where the ssn might not be known when the
>> record is created.[/color]
>
>From what I've heard, there have been cases of duplicated SSN numbers.
>Couple that with SSN fraud, and I'd have to agree with Reggie that it's not
>a reliable key.[/color]

If there is a legal requirement for the person to give you their SSN
before you create the database record then using it as a key should work
almost all the time.

The system could be broken in a number of different ways. The person
might not have an SSN (like me, for instance.)

They could object to giving you their SSN and give you a random number
instead.

There could be a duplicate number issued, although this is rare.



--
Bernard Peek
London, UK. DBA, Manager, Trainer & Author. Will work for money.

"Bernard Peek" <bap@shrdlu.com> wrote in message
news:Adoc82ah+hDBFw26@shrdlu.com...[color=blue][color=green]
> >
> >From what I've heard, there have been cases of duplicated SSN numbers.
> >Couple that with SSN fraud, and I'd have to agree with Reggie that it's[/color][/color]
not[color=blue][color=green]
> >a reliable key.[/color]
>
> If there is a legal requirement for the person to give you their SSN
> before you create the database record then using it as a key should work
> almost all the time.[/color]


And when it doesn't work you really want to know about it right away. No
good hiding the problem. At the very least you need a unique constraint so
that duplicates are highlighted, and you can then:

- contact the person concerned and get the right ssn, or
- re-check the data entry, or
- call the FBI

In fact, if you have a legal requirement to store ssn's you should be
verifying them with the feds anyway. And if you don't have this legal
requirement you shouldn't be storing them.
The worst thing to do is just allow duplicates and only discover the problem
years down the road. In the business world this is the stuff of lawsuits.
One of the reasons I hate autonumber keys so much is that it allows
developers to be lazy and to avoid making these sorts of decisions when they
build a data model.

In message <2n6tlhFtbh1jU1@uni-berlin.de>, John Winterbottom
<assaynet@hotmail.com> writes

[color=blue]
>One of the reasons I hate autonumber keys so much is that it allows
>developers to be lazy and to avoid making these sorts of decisions when they
>build a data model.[/color]

It's a point that gets made repeatedly in comp.databases.theory, often
by me. If you use autonumbers it's the designer's responsibility to
ensure that there is always a 1:1 relationship between keys and whatever
type of thing the table is supposed to keep track of.

I expect that quite a few people are going to get tired of hearing me
say that. Unfortunately in a newsgroup like this there are always new
people dropping in, and so the message needs repeating.


--
Bernard Peek
London, UK. DBA, Manager, Trainer & Author. Will work for money.