combo box issue

L

lmcc007

Well, I thought I had finally figured out how to get my form to work the way
I want it to. Meaning, on my form I want the following phone types to
display as defaults:

Business… (___) ___-____
Busines 2… (___) ___-____
Business Fax… (___) ___-____
Mobile… (___) ___-____

If I decide to select another phone type from the unbound combo box—such as
“cellâ€â€”I want it to display the phone type for the current record only.

The problem is that each time I select a different phone type for a company,
it changes all the phone types for all the companies (the entire table).

Here’s what I have done:

Tables

tblCompanies
CompanyID dbLong PrimaryKey Indexed
CompanyName dbText Indexed Required

tblPhoneNumbers
PhoneNumberID dbLong PrimaryKey Indexed
CompanyID dbLong ForiegnKey Indexed
PhoneType dbText Indexed
Phone dbText Indexed

tblPhoneTypes
PhoneType dbText PrimaryKey Indexed


Relations

Name: Reference1
Table: tblPhoneTypes
Foreign Table: tblPhoneNumbers
PK: PhoneType FK:phoneType

Name: tblCompaniestblPhoneNumbers
Table: tblCompanies
Foreign Table: tblPhoneNumbers
PK: CompanyID FK:CompanyID



What am I doing wrong? I have attached the database for your viewing.

I appreciate any help you can give. Thanks!
 
G

Gina Whipp

lmcc007,

To change the phone type for the current record the combo box must be bound
to that corresponding field in the table. You can still make the selection
form a list but it will only change the current record.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm
 
L

lmcc007

I have tried it that way. The reason it is setup this way because I have
four unbound Combo boxes. That is the way I want the form to look. Don't
like Access limited subform looks. It was previously set up with four
unbound text boxes with the Control Source equaling "Business," "Business 2,"
and so on. That did not work for me because it is not flexible--won't let me
change the phone type.
 
L

lmcc007

It works fine except for the problem stated.

Here the main form below:

frmCompanies (this is the main form)

Record Source = tblCompanies

Combo Box 1:
Name = txtBusiness (these were text boxes but I converted them to combo
and haven’t change the names yet)
Row Source = SELECT tblPhoneTypes.PhoneType FROM tblPhoneTypes ORDER BY
tblPhoneTypes.PhoneType;
Row Source Type = Table/Query
Bound Column = 1
Default Value = ="Business"
Column County = 1

Combo Box 2:
Name = txtBusiness2
Row Source = SELECT tblPhoneTypes.PhoneType FROM tblPhoneTypes ORDER BY
tblPhoneTypes.PhoneType;
Row Source Type = Table/Query
Bound Column = 1
Default Value = ="Business 2"
Column County = 1

Combo Box 3:
Name = txtBusinessFax
Row Source = SELECT tblPhoneTypes.PhoneType FROM tblPhoneTypes ORDER BY
tblPhoneTypes.PhoneType;
Row Source Type = Table/Query
Bound Column = 1
Default Value = "Business Fax"
Column County = 1

Combo Box 4:
Name = txtMobile
Row Source = SELECT tblPhoneTypes.PhoneType FROM tblPhoneTypes ORDER BY
tblPhoneTypes.PhoneType;
Row Source Type = Table/Query
Bound Column = 1
Default Value = ="Mobile"
Column County = 1


Subform/Subreport = tblPhoneNumbers
Source Object = frmtblPhoneNumbers
Link Master Fields = CompanyID;txtBusiness
Link Child Fields = CompanyID;PhoneType
Name = tblPhoneNumbers

Subform/Subreport = Child5
Source Object = frmtblPhoneNumbers
Link Master Fields = CompanyID;txtBusiness2
Link Child Fields = CompanyID;PhoneType
Name = Child5

Subform/Subreport = Child6
Source Object = frmtblPhoneNumbers
Link Master Fields = CompanyID;txtBusinessFax
Link Child Fields = CompanyID;PhoneType
Name = Child6

Subform/Subreport = Child7
Source Object = frmtblPhoneNumbers
Link Master Fields = CompanyID;txtMobile
Link Child Fields = CompanyID;PhoneType
Name = Child7
 
G

Gina Whipp

lmcc007,

You say the main form has a RecordSource of tblCompanies... well, the field
PhoneType is not in that table so in order to do what you want you would
have build a query that includes PhoneType in the RecordSource and then bind
the combo box to that field.

I am also not quite understanding why each phone number needs it's own
subform. Perhaps you should explain more of what you are trying to
accomplish... there might be a better way.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm
 
L

lmcc007

PhoneType is in tblPhoneNumbers as a FK.

Each phone number is in one table called tblPhoneNumbers joined together
using CompanyID. First you have to select a company and then enter a phone
number.

I want my form phone section to look and operate like MS Outlook Contact
Manager. All I want to do is be able to select a phone type and enter a
number, which is working except for the problem I stated. The four boxes
need to be dynamic (not sure I am using the right term) or changeable phone
types.

It's hard to explain when you can't attach the actual db.
 
G

Gina Whipp

I got it and have returned it with some adjustments. What you were trying
to do the way you were trying to di it will cause you more headache then it
would be worth and not to mention that would not be the way a programmer
would do it. The way I have done it requires...

1. A message to show if you plan to limit the number of records added.
2. Lock that company field unless you want people to edit the company name.

Other things I noted...
1. Trying to use the same subform 4 times on a main form is not possible or
you will get what you had. You would need to have 4 different subforms to
accomplish that task no matter what you are trying to do.
2. Be careful when you use a field in a combo box then later delete the
field from the underlying table.
3. Do NOT use look-up fields in tables, will only you issues. Do a search
in the newsgroup...

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm
 
L

lmcc007

This is just a test database; it is not the actual database. Many months ago
the actual database was set up with subforms--the way you have it--that's
simple--the computer will automatically generate it. And, I have tried many
different formats and ways, but still unhappy with the way it looks. I am
trying to get a particular look because I don't like the subforms.
 
L

lmcc007

Thanks Gina for all you help. I just got back from Barnes & Noble; they
didn't have the book suggested. But I did get a VB6 to read in the meantime
while I wait on my order to arrive.

I will take your advice and pull back and take it slow and put that project
 
L

lmcc007

Okay, more reading. I don't recall seeing any VBA books, only VB6 and
VB2008.

ARe you saying not to learn VB6, but find books on VBA?
 
L

lmcc007

So, you're saying return the VB6 books I bought and order the VBAs book?

I was reading the links you sent and so far it reads that VBA is smaller
version of VB6 or the same. Did I understand that correctly?
 
L

lmcc007

I just open my MS Access 2007 and then open VB; when I hit Help it says:

Microsoft VB6.5

VBA: Retail 6.5.1040
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Access 2007 parent-[PARTIAL] child table 8
Using a combo box to display multiple phone numbers 5

Top