partial form completion and combo lists

  • Thread starter Thread starter Christopher Glaeser
  • Start date Start date
C

Christopher Glaeser

A form that contains some combo lists will be completed in several sessions,
filling in some data now, and more data later. What is good design practice
regarding combo lists and data integrity? Do you add an "Unknown" entry in
the combo list and establish it as a default? If so, should the "Unknown"
be the first index in the table for the combo list? I'm interested to know
how Access designers approach this problem.

Best,
Christopher
 
A form that contains some combo lists will be completed in several sessions,
filling in some data now, and more data later. What is good design practice
regarding combo lists and data integrity? Do you add an "Unknown" entry in
the combo list and establish it as a default? If so, should the "Unknown"
be the first index in the table for the combo list? I'm interested to know
how Access designers approach this problem.
Click to expand...

Unless there is some good reason to do otherwise, I'd just leave the
table field so that it is not Required, and leave the value NULL
(empty, blank, undefined) until the value is known (if ever!).

If this is confusing for users, you can set the Format property of a
field to show "Unknown" or "N/A" for Null values. As a rule it's rare
that you'ld actually have a text "Unknown" selection, though I've had
a few cases where that was needed.
 
Unless there is some good reason to do otherwise, I'd just leave the
table field so that it is not Required, and leave the value NULL
(empty, blank, undefined) until the value is known (if ever!).
Click to expand...

Thanks! To where should I start sending money?

Best,
Christopher
 
Unless there is some good reason to do otherwise, I'd just leave the
table field so that it is not Required, and leave the value NULL
(empty, blank, undefined) until the value is known (if ever!).
Click to expand...

I've tried implementing this, but an error is generated when attempting to
save the record. Basically, I have two tables with a one-to-many relation.
Let's call one of the tables tblColor with colors red, blue, green. Let's
call the second table tblThings with a fields Name and ColorID that is an
index into tblColor.

tblColor
ColorID (values are 1,2,3)
Color (values are red,blue,green)

tblThings
Name
ColorID (a combo box with relation to tblColor.ColorID).

If I create a new Thing record, but don't select a color, an error is
generated. The Required property for tblThings.ColorID is set to No. The
error message is "You can not add or change a record because a related
record is required in table "tblColor". Does that mean a field in tblColor
needs to be changed? If so, which field and why?

Best,
Christopher
 
PS: Partial form completion works fine when the combo boxes are genetaed
with the Wizard; I'm only having problems when I convert a text box to a
combo box and then edit the various properties. Apparently, I'm missing an
essential property, but I'm not sure which.

Best,
Christopher
 
PS: Partial form completion works fine when the combo boxes are genetaed
with the Wizard; I'm only having problems when I convert a text box to a
combo box and then edit the various properties. Apparently, I'm missing an
essential property, but I'm not sure which.

Best,
Christopher
Click to expand...

I have no idea either. Please post the following properties:
RowSource; Control Source; Bound Column; Column Count; Column Widths.
 
I have no idea either.

I just did another test with the Wizard, and now I can't get that to work.
Could it be that the form/table has two combo boxes/relations to the same
table?
Please post the following properties:
RowSource; Control Source; Bound Column; Column Count; Column Widths.
Click to expand...

Here are the properties for the Realtor and Escrow Officer combo boxes in
the Inspection Report form.

Name=RealtorID
RowSource=SELECT Contacts.ContactID, Contacts.FirstName FROM Contacts ORDER
BY Contacts.FirstName;
ControlSource=RealtorID
BoundColumn=1
ColumnCount=2
ColumnWidths=0";1"

Name=EscrowOfficerID
RowSource=SELECT Contacts.ContactID, Contacts.FirstName FROM Contacts ORDER
BY Contacts.FirstName;
ControlSource=EscrowOfficerID
BoundColumn=1
ColumnCount=2
ColumnWidths=0";1"

It works fine if I select both the Realtor and Escrow Officer, but if I
leave one or both empty for selection later, an error is generated when the
form is closed.

Best,
Christopher
 
I just did another test with the Wizard, and now I can't get that to work.
Could it be that the form/table has two combo boxes/relations to the same
table?
Click to expand...

If you're using the Lookup Wizard in your table - DON'T. It's a
HORRIBLE misfeature. It conceals the actual contents of your tables
behind a looked-up value and makes it really confusing to do anything
on a form.
Here are the properties for the Realtor and Escrow Officer combo boxes in
the Inspection Report form.

Name=RealtorID
RowSource=SELECT Contacts.ContactID, Contacts.FirstName FROM Contacts ORDER
BY Contacts.FirstName;
ControlSource=RealtorID
BoundColumn=1
ColumnCount=2
ColumnWidths=0";1"
Click to expand...

Ok, this should store the ContactID for Fred (the first person named
Fred in the Contacts table if there are more than one :-{( ) into the
RealtorID field...
Name=EscrowOfficerID
RowSource=SELECT Contacts.ContactID, Contacts.FirstName FROM Contacts ORDER
BY Contacts.FirstName;
ControlSource=EscrowOfficerID
BoundColumn=1
ColumnCount=2
ColumnWidths=0";1"

It works fine if I select both the Realtor and Escrow Officer, but if I
leave one or both empty for selection later, an error is generated when the
form is closed.
Click to expand...

This would appear to have nothing to do with the combo boxes. I
*SUSPECT* that what's happened is in the table definition of the
RealtorID and EscrowOfficerID fields in this form's table - Access
(most annoyingly!) assigns 0 as the Default property of numeric table
fields, even in cases like this where you want the field left NULL.

If this is the case, just remove the 0 from the table's Default
property.
 
If you're using the Lookup Wizard in your table - DON'T.

Good advice I'm sure. I'm referrng to four books on Access and all are a
bit thin on combo boxes. Having done the simple exercises, I'm now creating
all my own combo boxes in my project.
If this is the case, just remove the 0 from the table's Default
property.
Click to expand...

That was it! You are saving me hours if not days of frustration as I come
up to speed. Thanks!!!!

Best,
Christopher
 
Back
Top