Combox uses 'wrong' value list (1 Viewer)

[Geirr]

Hi.

I've seen your video and again I've checked everything here, in special if I've made any typo errors in any references or choices.
The fun part I discovered late last night that for all three combo boxes, I can not use the 'build' option in the Row Source for the boxes, It's deactivated on all three for some reason...

I woold like to send you all the form with this issues, but it's a part of a rather big system with several of forms, navigation form, linked to a several tables in a sql server database. I don't want to spend too much time chasing ghosts, or waste your time, but thank you all for input.
I will remove the three combo boxes, copy the form to a dummy database, an import it back and start over with three combo boxes with query as row source type.

Best regards,
Geirr.

 

[isladogs]

Hi.

I've seen your video and again I've checked everything here, in special if I've made any typo errors in any references or choices.
The fun part I discovered late last night that for all three combo boxes, I can not use the 'build' option in the Row Source for the boxes, It's deactivated on all three for some reason...

I woold like to send you all the form with this issues, but it's a part of a rather big system with several of forms, navigation form, linked to a several tables in a sql server database. I don't want to spend too much time chasing ghosts, or waste your time, but thank you all for input.
I will remove the three combo boxes, copy the form to a dummy database, an import it back and start over with three combo boxes with query as row source type.

Best regards,
Geirr.

I understand why you don’t want to spend a lot of time on this or waste our time (thanks!) but, if there is a bug, providing a simple repro db will significantly assist with tracking it down. If I can reproduce this as a bug, I will forward it to the Access team

 

[Mike Krailo]

Did you read the explanation of what is happening behind the scenes?

No, I didn't see the explanation.

 

[isladogs]

The article mentioned in post#15 has a link to another article with the explanation

 

[Ken Sheridan]

In fact I do not like value list, I'm much more happy with using table query on a table as row source, so I might reconsider the whole thing, and see if that will be a better solution.

Codd would be right behind you on that. The Information Principle (Codd's Rule #1) requires that all data be stored as single values at column positions in rows in tables, and in no other way. A value list RowSource encodes data as part of the object definition. The only time I'd consider using a value list is where the values are an immutable set in the external reality, e.g. days of the week or months of the year.

 

[Gasman]

Codd would be right behind you on that. The Information Principle (Codd's Rule #1) requires that all data be stored as single values at column positions in rows in tables, and in no other way. A value list RowSource encodes data as part of the object definition. The only time I'd consider using a value list is where the values are an immutable set in the external reality, e.g. days of the week or months of the year.

Or like Male and Female? roflmao

 

[DHookom]

For the same reason @Ken Sheridan limits value lists to immutable values, I rarely use Yes/No fields. I have run into too my projects where values aren’t that black and white so a “kinda/maybe” needs to be added as a choice.

 

[Pat Hartman]

I've attached a link to a mini-app I first developed in COBOL/DB2 40 years ago. It is almost unchanged since then except that this version is Access/ACE. In my production apps, it is Access/SQL Server.

Before I developed the mini-app, all the "lookup" tables were hardcoded in the COBOL code which is essentially what the Value Lists are in Access. Moving the lists to a two table version makes it ever so much easier to manage them and for some, I even let the user add/change but never delete items.

The original version was a single self-referencing table. I converted it to a table of tables and a table of table values when I started posting it here because novices had trouble understanding the self-referencing version.

Table Maintenance Mini-app

When you have several lookup tables in an application, it becomes a pain to create separate tables and maintenance forms to manage the contents. This sample is an application that I first developed over 40 years ago, long before Access was a twinkle in Bill's eye. It was constructed using...

www.access-programmers.co.uk

 

[Ken Sheridan]

Or like Male and Female? roflmao

Not so straightforward these days.

 

[Ken Sheridan]

For the same reason @Ken Sheridan limits value lists to immutable values, I rarely use Yes/No fields. I have run into too my projects where values aren’t that black and white so a “kinda/maybe” needs to be added as a choice.

Codd supported 3-way logic, TRUE, FALSE and MAYBE, though Date did not, asserting that this was an inferred support for NULL, which Date strongly opposed, maintaining that the database relational model disallowed NULL as it required each column position in each row to contain a value of type a, where a is the attribute type represented by the column. NULL is not a value of course, but the absence of a value.

 

[isladogs]

Not allowing Nulls with Boolean fields was arguably one of the biggest design mistakes that the Access team made way back with version 1 in 1992.
This issue can lead to write conflict errors with data from other RDBMS such as SQL Server. Unfortunately, structural decisions such as that will never be changed as doing so would potentially affect millions of Access apps.

 

[Ken Sheridan]

Not allowing Nulls with Boolean fields was arguably one of the biggest design mistakes that the Access team made way back with version 1 in 1992.
This issue can lead to write conflict errors with data from other RDBMS such as SQL Server. Unfortunately, structural decisions such as that will never be changed as doing so would potentially affect millions of Access apps.

For that I'd use a column of Integer data type constrained with a ValidationRule of (>=-1 And <=0) Or Is Null. For the bound control a check box with a TripleState property of True could be used.

 

[isladogs]

That’s also my solution

 

[June7]

This rule also worked in my test of combobox: In (-1,0) Or Is Null