Enter Multiple Records Into One Field

[ILoveAccess via AccessMonster.com]

I need to keep track of what Models my customers are interested in. My
customers are usually interested in more than one model. How can I keep
track in one field on a form of the multiple Models they are interested in
without using a multi-select list box. I don't want to use a multi-select
list box because I don't know what the models are ahead of time so I can't
type them into the list box ahead of time.

Thanks!

 

[Rick Brandt]

I need to keep track of what Models my customers are interested in.
My customers are usually interested in more than one model. How can
I keep track in one field on a form of the multiple Models they are
interested in without using a multi-select list box. I don't want to
use a multi-select list box because I don't know what the models are
ahead of time so I can't type them into the list box ahead of time.

Thanks!

You don't. Fields should never contain more than one piece of data.

What you are describing is a one-to-many realtionship (one customer, many
models) and therefore requires two tables with a one-to-many relationship
between them. The usual method for data entry in such tables is then with a
form and subform.

With a subform for ModelsInterested you will be able to enter any number of
models per customer.

 

[Alex White MCDBA MCSE]

This is normally solved via a master detail arrangement, e.g.

1. customer record

many model records per customer

so you will need a model table that is related to the customer table

e.g.

tblCustomer Table

Customer_ID autonumber
.... more fields


tblModel Table
Model_ID autonumber
Customer_ID numeric (linked to the tblCustomer table)
Model_Description text



should do what you need

 

[Rick B]

You don't. A field should only contain one peice of data. You should build
a one-to-many relationship (or actually a many-to-many)

tblCustomer
CustomerNumber
FirstName
LastName
etc.


tblVehicles
StockNumber
VIN
YR
Make
Model
Coler
etc.


tblCustomerWants
CustomerNumber
StockNumber

 

[ILoveAccess via AccessMonster.com]

This is ending up to be a subform within a subform. Here is how I have
everything set up:

"tblCustomers"(contains customer info)----- "LeadID"(AutoNumber)
"tblHistory"(contains contact history) -------"HistoryID" (Number)
"ModelNo" (Number)
"tblModels" (contains model interest) ------- "ModelID"(Autonumber)
"HistoryNo" (Number)
"Model" (Text)

"frmCustomers" and subform "frmsubHistory" (Linked by LeadID and HistoryID)

"frmsubHistory" and subform "frmModels" (How should I link these?)

I've tried to link them many different ways and I just keep having problems!
Some of the problems I've had:

1.) (linking by HistoryID and HIstoryNo)
It duplicates info (ex. if Mr. Doe requests info from me and he is
interested in Model VX, I type VX in the Model subform. He requests info
from me again a few days later but he is interested in a different model.
The next "History" record for him repeats the number automatically. If I
change the model, then the model changes in all his "History" records.

2.) (Linking by ModelID and ModelNo)
When I enter the model in the Model subform,then click to the next
"History" record, then go back to the record I just entered the model in.
The model isn't saved. I checked and the models do store in tblMOdels like
they are supposed to. They are just not saving in the Models subform!

3.) (linking by HistoryID and ModelID)
It doesn't even let me enter a model number. I receive the error message
saying "You can't assign a value to this object"

 

[ILoveAccess via AccessMonster.com]

This is ending up to be a subform within a subform. Here is how I have
everything set up:

"tblCustomers"(contains customer info)----- "LeadID"(AutoNumber)
"tblHistory"(contains contact history) -------"HistoryID" (Number)
"ModelNo" (Number)
"tblModels" (contains model interest) ------- "ModelID"(Autonumber)
"HistoryNo" (Number)
"Model" (Text)

"frmCustomers" and subform "frmsubHistory" (Linked by LeadID and HistoryID)

"frmsubHistory" and subform "frmModels" (How should I link these?)

I've tried to link them many different ways and I just keep having problems!
Some of the problems I've had:

1.) (linking by HistoryID and HIstoryNo)
It duplicates info (ex. if Mr. Doe requests info from me and he is
interested in Model VX, I type VX in the Model subform. He requests info
from me again a few days later but he is interested in a different model.
The next "History" record for him repeats the number automatically. If I
change the model, then the model changes in all his "History" records.

2.) (Linking by ModelID and ModelNo)
When I enter the model in the Model subform,then click to the next
"History" record, then go back to the record I just entered the model in.
The model isn't saved. I checked and the models do store in tblMOdels like
they are supposed to. They are just not saving in the Models subform!

3.) (linking by HistoryID and ModelID)
It doesn't even let me enter a model number. I receive the error message
saying "You can't assign a value to this object"

 

[ILoveAccess via AccessMonster.com]

I figured it out! I created a subform within a subform! Here is how
everything is set up:

MainForm: frmLeads
SubForm of frmLeads: frmHistory
SubForm of frmHistory: frmModels
-----------------------------------------
tblLeads
LeadNo(Autonumber,PrimaryKey)


tblHistory
HistoryID (Autonumber,PrimaryKey)
HistoryNo (Number)

tblModels
ModelID (Number)

Relationships:
LeadNo---HistoryNo
HistoryID---ModelID