Confused

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Access 97. I have a form that is used to schedule carrier appointments to
our facility. Among other bits of information collected, the user must enter
the carrier name as well as the contact name, phone and fax number.

Each carrier could have as many as 10 different people scheduling
appointments. Currently the user is selecting the carrier code from a lookup
list, then typing the contact name and phone numbers in separate fields.

What I am trying to do it set it up so that the user selects the carrier
code, then gets a drop down list of the contact names associated only with
that carrier (many times they are just first names, and I would rather not
have the user scroll through an entire list of names) to choose the correct
one. Once the contact name is selected, I would like the next box to fill in
with the contact's fax number.

Can this be done? And if so, can it be done easily (my VB skills are
virtually non-existent)?

Thanks in advance for you help and assistance.
 
I'm still not getting it. When I said I knew virtually nothing about code, I
meant it. :-)

The first combo box (Combo93 - Text label: Carrier) is a lookup to
tbl_CarrierListing. The tables fields are:

Carrier SCAC
Carrier Name

The second combo box (Combo153 - Text label: Carrier Contact) needs to
check the carrier SCAC in the first combo box and return information from
tbl_CarrierInfo. The fields in this table are:

Carrier SCAC
Contact Name
Fax Number

The third combo box (Combo155 - Text label: Carrier Fax) needs to return the
fax number.

When I use the code supplied, I get an error message:

Runtime Error "2465"
Can't find the field cbxCombo93 referred to in your expression
 
Why do you not store all of the info in one table? Since you already have
Carrier Name in tbl_CarrierListing, why not have the Fax Number in there also.
You should put related info in the same table. If you need parts of the info
you can use a query.
If you place Fax Number in the tbl_CarrierListing, one combo box will do what
you want. Just bind the column with the fax in your combo. If you do this,
you eliminate two lookups and thus your form becomes more efficient for the
user.
Even the way you are attempting to do it, the third combo is not necessary
since the second one "returns" the fax.

I'm still not getting it. When I said I knew virtually nothing about code, I
meant it. :-)

The first combo box (Combo93 - Text label: Carrier) is a lookup to
tbl_CarrierListing. The tables fields are:

Carrier SCAC
Carrier Name

The second combo box (Combo153 - Text label: Carrier Contact) needs to
check the carrier SCAC in the first combo box and return information from
tbl_CarrierInfo. The fields in this table are:

Carrier SCAC
Contact Name
Fax Number

The third combo box (Combo155 - Text label: Carrier Fax) needs to return the
fax number.

When I use the code supplied, I get an error message:

Runtime Error "2465"
Can't find the field cbxCombo93 referred to in your expression
Try this:
http://www.mvps.org/access/forms/frm0028.htm
Click to expand...
[quoted text clipped - 18 lines]
Click to expand...
 
The problem with storing the info in one table is the contact names. Each
carrier could have as many as 10 different contact names and numbers.

Carrier SCAC is a primary key in both tables.

jahoobob via AccessMonster.com said:
Why do you not store all of the info in one table? Since you already have
Carrier Name in tbl_CarrierListing, why not have the Fax Number in there also.
You should put related info in the same table. If you need parts of the info
you can use a query.
If you place Fax Number in the tbl_CarrierListing, one combo box will do what
you want. Just bind the column with the fax in your combo. If you do this,
you eliminate two lookups and thus your form becomes more efficient for the
user.
Even the way you are attempting to do it, the third combo is not necessary
since the second one "returns" the fax.

I'm still not getting it. When I said I knew virtually nothing about code, I
meant it. :-)

The first combo box (Combo93 - Text label: Carrier) is a lookup to
tbl_CarrierListing. The tables fields are:

Carrier SCAC
Carrier Name

The second combo box (Combo153 - Text label: Carrier Contact) needs to
check the carrier SCAC in the first combo box and return information from
tbl_CarrierInfo. The fields in this table are:

Carrier SCAC
Contact Name
Fax Number

The third combo box (Combo155 - Text label: Carrier Fax) needs to return the
fax number.

When I use the code supplied, I get an error message:

Runtime Error "2465"
Can't find the field cbxCombo93 referred to in your expression
Try this:
http://www.mvps.org/access/forms/frm0028.htm
Click to expand...
[quoted text clipped - 18 lines]
Thanks in advance for you help and assistance.
Click to expand...
Click to expand...
Click to expand...
 
Put this:
base one combo on another
in the search field in AccessMonster and look at the first item.
The problem with storing the info in one table is the contact names. Each
carrier could have as many as 10 different contact names and numbers.

Carrier SCAC is a primary key in both tables.
Why do you not store all of the info in one table? Since you already have
Carrier Name in tbl_CarrierListing, why not have the Fax Number in there also.
Click to expand...
[quoted text clipped - 37 lines]
Click to expand...
 
Back
Top