selecting options from a form

[John C.]

I have a database with customer information (name, address etc.) and
cellphone information (cell #, rate plan, date purchased etc.) These tables
are linked via custID so that one customer can have multiple phones. They are
in a form with a subform. I am trying to create an additonal form or a
pop-up type form that will allow me to select 1 or more data services that
that cell # subscribes to. example: Joe's Lanscaping has a 2 phones one
that subscribes to text messaging, web services and navigation service and
the other that does not. I want to be able to select these services when I
enter Joe's information. The data services are listed in another table along
with other information. Can anyone HELP!

 

[Beetle]

I think the first question would be how are your tables set up. It sounds
like you have a M:M relationship between data services and cell phones (a
phone can have many services and a service can be used by many phones), so
you would typically have a junction table to define the relationship. Is this
what you currently have?

 

[John C.]

I've never set up a many to many relationship.

I think the first question would be how are your tables set up. It sounds
like you have a M:M relationship between data services and cell phones (a
phone can have many services and a service can be used by many phones), so
you would typically have a junction table to define the relationship. Is this
what you currently have?

 

[Beetle]

It might look something like this;

tblCustomers
**********
CustID (PK)
LastName
FirstName
Other fields related to customer

tblCellPhones
**********
PhoneID (PK)
CustID (FK to tblCustomers)
Other fields related to phone

tblDataServices
************
ServiceID (PK)
ServiceName
other fields related to services

tblPhoneServices
*************
PhoneServiceID (PK)
PhoneID (FK to tblCellPhones)
ServiceID (FK to tblDataServices)

tblPhoneServices would be the junction table that handles the relationship
between phones and services. It would likely just store ID numbers that
relate back to the other tables unless you have some other data that relates
specifically to the combination of a phone and it's services.

Then (just an idea here) you would add another subform (popup or embedded -
it's up to you) that would have maybe a combo box to select which services
apply to that phone and that info would be stored in tblPhoneServices.

Again, i'm just throwing out some ideas. I don't know your db so I can't
tell you exactly what to do.

HTH

 

[John C.]

pk is primary key and not sure what fk is?

It might look something like this;

tblCustomers
**********
CustID (PK)
LastName
FirstName
Other fields related to customer

tblCellPhones
**********
PhoneID (PK)
CustID (FK to tblCustomers)
Other fields related to phone

tblDataServices
************
ServiceID (PK)
ServiceName
other fields related to services

tblPhoneServices
*************
PhoneServiceID (PK)
PhoneID (FK to tblCellPhones)
ServiceID (FK to tblDataServices)

tblPhoneServices would be the junction table that handles the relationship
between phones and services. It would likely just store ID numbers that
relate back to the other tables unless you have some other data that relates
specifically to the combination of a phone and it's services.

Then (just an idea here) you would add another subform (popup or embedded -
it's up to you) that would have maybe a combo box to select which services
apply to that phone and that info would be stored in tblPhoneServices.

Again, i'm just throwing out some ideas. I don't know your db so I can't
tell you exactly what to do.

HTH

 

[Beetle]

Foreign Key. The table in the *many* side of the relationship has a foreign
key field to store the PK value from the table in the *one* side of the
relationship (I'm probably oversimplifying it a little, but hopefully you get
the idea)