Null value in Combo Box

[Guest]

When selecting a record from a combo box it autofills two other fields
(phone, fax). Here is the code I'm using:

Private Sub cboDist_AfterUpdate()
Me!Caller_Phone_Number = Me!cboDist.Column(6)
Me!Caller_Fax_Number = Me!cboDist.Column(7)
End Sub

The problem I'm having is if the phone or fax field is blank I get an error.
How can I set the form to accept or continue on if the phone or fax is null?

 

[Jeff Boyce]

Are those two controls bound to underlying data fields? If so, why?! You
really wouldn't need to (re-)store phone and fax if you already have it
stored elsewhere (but you could still use your approach to display them).

If you inspect the [Caller_Phone_Number] control (and/or the underlying
field, if bound), does this field allow "blanks"? Note that a "blank" (to
human eyes) might be a zero-length string (""), or might be a null. If this
control is bound, does the underlying field definition allow zero-length
strings?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Jeff, thanks for your response.

I'm new to Access (this is my first project) and I'm not sure I understand
your questions. I'm not too familiar with the terminology yet.

Let me explain what I'm trying to do, perhaps it's not the best way???

I have two tables. One named Call_Logs the other named DistShipTo. I have
created a Call_Logs form. On this form our service department will enter data
from support calls they get from the field.

What I'm trying to accomplish with the combo box is this: When clicking on
the combo box they get a listing of Distributors (from DistShipTo table),
along with their shipto info. Once they choose the desired distributor the
Company name, phone and fax field from the combo box are auto filled into the
Call Logs form.

I think the only control that is bound is the company name, the other two
are not (I think)??

Is there a better way to autofill the fields?

Also, I changed the Allow Zero Length to YES in Caller_Phone_Number field in
the table to and the error seems to have gone away. So, I think your
suggestion/response was the answer.

Let me know if you think I should be doing this differently.

Are those two controls bound to underlying data fields? If so, why?! You
really wouldn't need to (re-)store phone and fax if you already have it
stored elsewhere (but you could still use your approach to display them).

If you inspect the [Caller_Phone_Number] control (and/or the underlying
field, if bound), does this field allow "blanks"? Note that a "blank" (to
human eyes) might be a zero-length string (""), or might be a null. If this
control is bound, does the underlying field definition allow zero-length
strings?

Regards

Jeff Boyce
Microsoft Office/Access MVP

When selecting a record from a combo box it autofills two other fields
(phone, fax). Here is the code I'm using:

Private Sub cboDist_AfterUpdate()
Me!Caller_Phone_Number = Me!cboDist.Column(6)
Me!Caller_Fax_Number = Me!cboDist.Column(7)
End Sub

The problem I'm having is if the phone or fax field is blank I get an
error.
How can I set the form to accept or continue on if the phone or fax is
null?

 

[Guest]

Here is a good lesson for you in Access or database design in general.
It appears, based on your description, when you are creating a call log
record and selected the distributor, you are duplicating multiple fields from
the DistShipTo table into the Call_Logs table. This is not correct database
design. Each record in DistShipTo should have a primary key. The Call_Logs
table should have a field of the same data type as the primary key field of
DistShipTo in which you store the value of the related DistShipTo primary
key. In other words, say you have primary key values of 1, 2, 3 for the 3
records in DistShipTo. Now you want to create a new record in Call_Logs.
You select the distributor whose primary key is 2. So, instead of copying
the detail info from DistShipTo, you store the value 2 in the foreign key
field in Call_Logs. Now, to get at the data, you use a query that joins the
two tables on the primary key of DistShipTo and the foreign key field of
Call_Logs. Less disc space, less work, and faster.

--
Dave Hargis, Microsoft Access MVP

Jeff, thanks for your response.

I'm new to Access (this is my first project) and I'm not sure I understand
your questions. I'm not too familiar with the terminology yet.

Let me explain what I'm trying to do, perhaps it's not the best way???

I have two tables. One named Call_Logs the other named DistShipTo. I have
created a Call_Logs form. On this form our service department will enter data
from support calls they get from the field.

What I'm trying to accomplish with the combo box is this: When clicking on
the combo box they get a listing of Distributors (from DistShipTo table),
along with their shipto info. Once they choose the desired distributor the
Company name, phone and fax field from the combo box are auto filled into the
Call Logs form.

I think the only control that is bound is the company name, the other two
are not (I think)??

Is there a better way to autofill the fields?

Also, I changed the Allow Zero Length to YES in Caller_Phone_Number field in
the table to and the error seems to have gone away. So, I think your
suggestion/response was the answer.

Let me know if you think I should be doing this differently.

Are those two controls bound to underlying data fields? If so, why?! You
really wouldn't need to (re-)store phone and fax if you already have it
stored elsewhere (but you could still use your approach to display them).

If you inspect the [Caller_Phone_Number] control (and/or the underlying
field, if bound), does this field allow "blanks"? Note that a "blank" (to
human eyes) might be a zero-length string (""), or might be a null. If this
control is bound, does the underlying field definition allow zero-length
strings?

Regards

Jeff Boyce
Microsoft Office/Access MVP

When selecting a record from a combo box it autofills two other fields
(phone, fax). Here is the code I'm using:

Private Sub cboDist_AfterUpdate()
Me!Caller_Phone_Number = Me!cboDist.Column(6)
Me!Caller_Fax_Number = Me!cboDist.Column(7)
End Sub

The problem I'm having is if the phone or fax field is blank I get an
error.
How can I set the form to accept or continue on if the phone or fax is
null?

 

[Jeff Boyce]

I'm still not clear on whether your DistShipTo table has fields for Cell and
FAX numbers. If so, it shouldn't!

If you have a way to connect the Call_Logs table to the DistShipTo table (a
common field, probably an ID), then you can use a query to join the two and
"get" the Cell and FAX numbers when you need them. No need to store them
twice...

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff, thanks for your response.

I'm new to Access (this is my first project) and I'm not sure I understand
your questions. I'm not too familiar with the terminology yet.

Let me explain what I'm trying to do, perhaps it's not the best way???

I have two tables. One named Call_Logs the other named DistShipTo. I have
created a Call_Logs form. On this form our service department will enter
data
from support calls they get from the field.

What I'm trying to accomplish with the combo box is this: When clicking on
the combo box they get a listing of Distributors (from DistShipTo table),
along with their shipto info. Once they choose the desired distributor the
Company name, phone and fax field from the combo box are auto filled into
the
Call Logs form.

I think the only control that is bound is the company name, the other two
are not (I think)??

Is there a better way to autofill the fields?

Also, I changed the Allow Zero Length to YES in Caller_Phone_Number field
in
the table to and the error seems to have gone away. So, I think your
suggestion/response was the answer.

Let me know if you think I should be doing this differently.

Are those two controls bound to underlying data fields? If so, why?!
You
really wouldn't need to (re-)store phone and fax if you already have it
stored elsewhere (but you could still use your approach to display them).

If you inspect the [Caller_Phone_Number] control (and/or the underlying
field, if bound), does this field allow "blanks"? Note that a "blank"
(to
human eyes) might be a zero-length string (""), or might be a null. If
this
control is bound, does the underlying field definition allow zero-length
strings?

Regards

Jeff Boyce
Microsoft Office/Access MVP

When selecting a record from a combo box it autofills two other fields
(phone, fax). Here is the code I'm using:

Private Sub cboDist_AfterUpdate()
Me!Caller_Phone_Number = Me!cboDist.Column(6)
Me!Caller_Fax_Number = Me!cboDist.Column(7)
End Sub

The problem I'm having is if the phone or fax field is blank I get an
error.
How can I set the form to accept or continue on if the phone or fax is
null?