Lots of references in one field... in a form... its complicated! HELP!

[ollygregory]

I am working on a database for a property company, developing a table
within their existing database to store clients.

On one of the forms, there is a field to show the references of the
properties in which they are interested (references tend to look like
this - 10092).

There is a hyperlink on this form, that when clicked, runs a macro that
opens another form showing the relevant page for the property dependant
on the reference number that is in the 'Reference' field.... with me so
far???

This all works perfectly, as long as there is only ONE reference in the
field. Unfortunately for me, most clients tend to be interested in more
than one property. What I need to able to do is record several
reference numbers in that one field, seperated by commas, like this
perhaps...

10292, 90567, 10293

...and for when I click on the link to run the macro a form opens
showing the 3, 4 or 5 records relevant to the references entered in the
reference field on the client form.

I have yet to work out how to store the reference numbers so that this
works. Nothing i've done seems to work. I've looked through lots of
other threads and cant find anything relating to my problem!

Answers on a postcard please, all responses VERY VERY welcome!!

Thanks!

 

[DL]

You would usually have a Client Tbl, containing a ClientID key, then a
Property Tbl, which apart from PropertyID contains also a ClientID field.
You then have a relationship

 

[Steve Schapel]

Olly,

It sounds like you have a Reference field in the Clients table. Since
there is a one-to-many relationship between clients and references, you
should really have a separate table for this data. I would remove the
Reference field from the Clients table, and make a table named
References with these fields:
ClientID
PropertyReference
Then, the example of 10292, 90567, 10293 would end up being stored as 3
separate records in the References table, rather than 3 pieces of
information in one field in one Client record. As for the user
interface, you can make a continuous view form, with just the
PropertyReference field shown on there with a textbox, and place this as
a subform onto your Clients form.
From here you can link to the relevant record on the Properties form
for any one of the Properties referenced for that Client. Or, if you
really wanted to have all Properties that a Client is interested in
shown at the same time, you could have the Properties form based on a
Query that includes the References table, and a criteria that refers to
the the ClientID on the current record on the Clients form. Hope that
makes enough sense to point you in a useful direction.

 

[ollygregory]

As complicated as that is it?? :-(

Hmmm... i'm thinking that a subform is probably the way to go... but
think i need to research that slightly more first. Is that going t
help with with my entering several references at one time problem???

Many thanks for your responses so far!!

 

[Steve Schapel]

Olly,

As complicated as that is it?? :-(

No. That's not complicated. It's standard procedure. And despite
initial appearances, less complicated than what you were initially
proposing. Access is a relational database. One of the main purposes
for using a programme like Access is to take advantage of its power in
managing related data. But to do so requires adhering to relational
principles, one of which is atomicity, i.e. one piece of information in
one place at one time.

Hmmm... i'm thinking that a subform is probably the way to go... but I
think i need to research that slightly more first. Is that going to
help with with my entering several references at one time problem???

Absolutely. Guaranteed.

Many thanks for your responses so far!!

You're welcome

 

[John Vinson]

This all works perfectly, as long as there is only ONE reference in the
field. Unfortunately for me, most clients tend to be interested in more
than one property. What I need to able to do is record several
reference numbers in that one field, seperated by commas, like this
perhaps...

10292, 90567, 10293

..and for when I click on the link to run the macro a form opens
showing the 3, 4 or 5 records relevant to the references entered in the
reference field on the client form.

YOu're using a relational database. Use it relationally!

Storing multiple values in one field - or one record for that matter -
is Bad Design and unnecessary.

If you have a Many to Many relationship (even from the Property table
to itself) you need ANOTHER TABLE. In this case, I'd use a References
table with two (maybe more but at least two) fields: ReferencingID and
ReferencedID. This table would have THREE records - all with this
record's propertyID, and one with 10292, another with 90567, the third
with 10293.

You can use a continuous Subform to enter and display them.

John W. Vinson[MVP]

 

[ollygregory]

Again, thanks to all of you for all your help, but i am still so stuck!

I have, as previously advised, deleted the reference field on th
Client page and created a whole new table called 'Reference ID' whic
has on it fields for

Reference
Client ID

and that is all!!

I then created a form where the Client table was the main form and th
Reference ID table was the subform.... this is right? yes?

I then set it to be displaed in Columnar format and not in a datashee
or anything else. Am I still on the right track? Also, would anyon
like to enlighten me on what a continuous view form is and how
implement one? If i do in fact require one??

That is what I'm curently up to... and.... I may be missing somethin
here, but i dont see where I'm supposed to enter my line of references
I wondered if I did infact need it in datasheet view but that didn'
seem to help much either! And what more, I continually get an erro
message about duplicated values!!

Where am i going wrong???

I fully admit in my adsent mindedness I have probably missed out som
major and vital part of the process, but I could just do with someon
to point it out to me but I certainly dont seem to be able to come u
with it on my own!!

As always, all responses GREATLY welcomed.

Thanks again, Olly

 

[John Vinson]

Again, thanks to all of you for all your help, but i am still so stuck!

I have, as previously advised, deleted the reference field on the
Client page and created a whole new table called 'Reference ID' which
has on it fields for

Reference
Client ID

and that is all!!

Well, your names are a bit confusing. Normally a *field* will be named
with an ID as part of the name, not a Table.

I then created a form where the Client table was the main form and the
Reference ID table was the subform.... this is right? yes?
Correct.

I then set it to be displaed in Columnar format and not in a datasheet
or anything else. Am I still on the right track? Also, would anyone
like to enlighten me on what a continuous view form is and how i
implement one? If i do in fact require one??

It's more convenient in this case. Open the Form that you're using as
a subform in design view; view its Properties; and find the "Default
View" property. Access generally sets this to Datasheet if you use the
subform wizard. That's ok - but changing it to Continuous Forms gives
you more control and flexibility, and it's what I prefer.

You'll need to move all the textboxes and other controls up to the top
of the Detail section, side by side, and snug the bottom of the detail
section up beneath the controls. When you open the form, you'll now
see one reference per row, for as many rows as you have references.

That is what I'm curently up to... and.... I may be missing something
here, but i dont see where I'm supposed to enter my line of references.

YOU DON'T.

Instead of a line of references separated by commas, you enter *one
reference per line*, for as many lines as you need.

I wondered if I did infact need it in datasheet view but that didn't
seem to help much either! And what more, I continually get an error
message about duplicated values!!

Where am i going wrong???

I don't know, since I don't clearly understand what you're doing.
What's the Primary Key of your ReferenceID table? It should be BOTH
the ClientID and Reference fields - ctrl-click them both in table
design view and click the Key icon. Maybe you made the Reference the
primary key - that would mean that you could enter one and only
instance of a given reference into the table, no matter how many
clients might be referencing it.


John W. Vinson[MVP]

 

[ollygregory]

Okie dokie... I had, as you so rightly pointed out, set my primary key
on the Reference table (i've called it just 'Reference' now, as opposed
to 'Reference ID'- much simpler) to be both reference AND client ID, as
instructed.

I'm still having some problems though...

Firstly although it'll let me enter information into the Reference
textboxes as soon as I close the form... all information has
disappeared, and I'm left with just one text box with no reference in
it!

Do i need the client ID from the reference table on the form/sub form
as well? Else, even though theres a relationship, how is it supposed to
know whick record it's affliated with.

I imagine that these questions are blatantly obvious but this has
almost completely gone over my head now, and i'm running out of time!!


Please help!!!!!!

Cheers

 

[John Vinson]

Okie dokie... I had, as you so rightly pointed out, set my primary key
on the Reference table (i've called it just 'Reference' now, as opposed
to 'Reference ID'- much simpler) to be both reference AND client ID, as
instructed.

Ok. This prevents having the same client getting the same reference
entered twice, while allowing each client to have many references and
each reference applying to multiple clients.

I'm still having some problems though...

Firstly although it'll let me enter information into the Reference
textboxes as soon as I close the form... all information has
disappeared, and I'm left with just one text box with no reference in
it!

Since I have NO idea how your form is structured, or what the
"textbox" is, I'm not sure what's wrong.

Do i need the client ID from the reference table on the form/sub form
as well? Else, even though theres a relationship, how is it supposed to
know whick record it's affliated with.

You should have a Form based on the Client table, with a Subform based
on the Reference table. The Master/Child Link Fields should be
ClientID; the subform should have a control - combo box is probably
simplest - to select which references apply to this client. See the
Orders form in the Northwind sample database for an example.

If this isn't communicating, please post back and indicate:

- The names and Primary Keys of the tables involved
- The "real life" meaning of the table; what does it represent (I
really don't know what a "reference" means in this case, for example)
- The Forms you're using (including subforms), with their names and
Recordsource properties
- If you have a Subform, its master and child link field properties
- The names, Control Sources, and (for combo boxes) Rowsource of the
relevant controls on the forms

John W. Vinson[MVP]

 

[ollygregory]

Ok...

Let me try and explain myself a little better...

There are 2 tables involved-

Client Database ... and...
Reference

The Primary key on the Client Database table is 'Client ID' and on the
reference table, as I said, it is both 'Client ID' and 'Reference'

The Client table has been set up to handle all of the client data for
an estate agency company. 'Reference' is the reference of a property.
Each property is given it's own unique 5 digit reference. There are
currently around 300 properties on the system. The idea of the form, is
for a user of the system to be able to make a list of all the references
that a client may be interested in viewing (this could be anything from
1 to 20) and then to run a macro which is linked with the 'House
Details' table and retrieve all records dependant on the references
entered on the client form.

I therefore have a form called 'Clients' which has a subform of
'Reference'

- If you have a Subform, its master and child link field properties

I am unsure exactly what this is, or how I define it. I fear this may
be a large part of my problem.

The control source of the Client ID on the form is called 'Client ID'
and of the Reference field on the subform, the control source is simply
'Reference' (and as you suggested, a command box)

Since I have NO idea how your form is structured, or what the
"textbox" is, I'm not sure what's wrong.

When I say 'textbox' in this instance I am referring to the command box
linked to reference on the subform. While I can enter data on opening
the form, as soon as I close it, the data has vanished, as if it is not
being stored within the table.

Many, many thanks for all of your help so far, and I look forward to
your response on this one!

 

[John Vinson]

Ok...

Let me try and explain myself a little better...

There are 2 tables involved-

Client Database ... and...
Reference

I think there should be THREE tables involved, unless each Reference
refers to one and only one client. Do you not have a table with one
record for each property, with its own five-digit reference ID? Or is
it the case that once a client is interested in viewing a property, no
other client is allowed to view it? Surely not!

When you have a Many (clients) to Many (references) relationship, you
need a third table to resolve the relationship. YOu're describing the
Reference table as having the ClientID and the Reference - but this
would only be appropriate for a ONE to many relationship, in which a
given Reference record pertains to one and only one client.

The Primary key on the Client Database table is 'Client ID' and on the
reference table, as I said, it is both 'Client ID' and 'Reference'

The Client table has been set up to handle all of the client data for
an estate agency company. 'Reference' is the reference of a property.
Each property is given it's own unique 5 digit reference. There are
currently around 300 properties on the system. The idea of the form, is
for a user of the system to be able to make a list of all the references
that a client may be interested in viewing (this could be anything from
1 to 20) and then to run a macro which is linked with the 'House
Details' table and retrieve all records dependant on the references
entered on the client form.

No Macro would be needed. Just a Subform.

I therefore have a form called 'Clients' which has a subform of
'Reference'


I am unsure exactly what this is, or how I define it. I fear this may
be a large part of my problem.

Open the Client form in design view. Select the Subform control - the
box that contains the Reference subform. View its Properties. Two of
them are the Master Link Field and CHild Link Field - they are what
link the mainform to the subform. In this case they should both say
ClientID.

The control source of the Client ID on the form is called 'Client ID'
and of the Reference field on the subform, the control source is simply
'Reference' (and as you suggested, a command box)

A combo box I presume? There's no such thing as a "command" box...!

When I say 'textbox' in this instance I am referring to the command box
linked to reference on the subform. While I can enter data on opening
the form, as soon as I close it, the data has vanished, as if it is not
being stored within the table.

It sounds like the Subform may not be bound to the right table, or it
might not be bound to any table at all. Click the little box at the
upper-left corner of the subform in form design view, and view that
form's properties. What is the Recordsource property of the subform?

Many, many thanks for all of your help so far, and I look forward to
your response on this one!

John W. Vinson[MVP]

 

[ollygregory]

No need to post any more help, I got there in the end!

It was, as pointed out below an inaccuracy within my child/master link,
but after solving that it now works perrrrrrrrrrrrfectly.

Thank you to everyone for all of your help