Inserting into a single column (1 Viewer)

JoseO

Registered User
Joined
Jul 14, 2013
Messages
66
Hi,

Ever since I started using Access, I've been told to make use of Tables in order to populate my combo boxes. As such, rather than setting up, say, 4 tables to hold just one column of combo box choices each, I use one table and each column then supplies the combo box choices.

This works well the majority of the time but, I am in need to add choices to the first column only [Field1] independent of the other columns - is this possible?

So, while I only have 5 choices in Field1, Field4 has 70 choices. So, in Field1 from the 6th empty cell down to the the (New) record field, all of these cells in Field1 are empty.

My question: Is there a way to programmatically insert a text value (or any value for that matter) in the next empty cell of the first column only (Field1)? I am using an unbound form.

Thank you for any guidance/assistance.
 

Micron

AWF VIP
Joined
Oct 20, 2018
Messages
1,773
If I understand the question, the way to update existing fields (whether they have data or not) in an existing record is to use an UPDATE query or sql statement. You might have to provide a better indication of what you're table looks like. Maybe a pic?
 

JoseO

Registered User
Joined
Jul 14, 2013
Messages
66
Thank you Micron. Yes, I have tried UPDATE and INSERT. But when I try them with a WHERE clause it's essentially filling all Null fields in Field1 (first column). I've attached a picture.
 

Attachments

June7

AWF VIP
Joined
Mar 9, 2014
Messages
2,687
Personally, I would build 4 tables.

If you want to keep the column 'compact' and avoid gaps, your code would have to identify which record in Field1 is the 'next' record, in other words the first record with Null in that field. Assuming DDID field will always be positive and increasing, something like:

UPDATE tablename SET Field1 = "something" WHERE DDID = DMin("DDID", "tablename", "Field1 Is Null")

or

UPDATE tablename SET Field1 = "something" WHERE DDID IN (SELECT Min(DDID) FROM tablename WHERE Field1 Is Null)
 
Last edited:

theDBguy

I’m here to help
Joined
Oct 29, 2018
Messages
7,757
Hi Jose. Just my 2 cents, but you might as well bite the bullet and set up 4 tables with one column each. You're not really saving a whole lot of space by using only one table with 4 columns. One advantage of having separate tables is they're easier to maintain, as you have already found out. Also, they become portable. If you need to use the same set of choices in another project, you just export the table and you're done. Again, just one person's humble opinion. Cheers!
 

Micron

AWF VIP
Joined
Oct 20, 2018
Messages
1,773
I'll go along with those sentiments, but if you keep it to 1 table don't worry about the gaps. The combo won't care. The pic isn't exactly what I thought you were dealing with.
 

Galaxiom

Super Moderator
Staff member
Joined
Jan 20, 2009
Messages
11,830
Look at it as data. Data comes in records. The value in each field of a record should normally have some relationship to the values on other fields of the same record. This is not the case when having the fields as the RowSource of independent combos.

A normalized single table RowSource structure has a column for the ID of the combo box and a separate record for each value in each combo. This is especially important when the combo reqiures both an ID for the value that will be stored and a displayed text. Trying to keep multiple fields aligned in the select by field model would be a nightmare.

Put composite indexes on ComboID, ValueID as well as ComboID,DisplayText to ensure there are no duplicate combinations.

The rowsource query is in the form of:
Code:
SELECT ValueID, DisplayText FROM tblComboRowSources WHERE ComboID = whatever
Adding new values is simple

Code:
INSERT INTO tblComboRowSources (ComboID,ValueID, DisplayText)
VALUES (whatever, thenewID, "thenewdisplaytext")
whatever identifies the combo.
 
Last edited:

The_Doc_Man

Happy Retired Curmudgeon
Joined
Feb 28, 2001
Messages
15,642
Got to side with Galaxiom and the others who recommend separate tables for combo boxes. And G's explanation (technical violation of normalization by having unrelated columns adjacent to each other) is about what I would have said.

However, there is a solution to your problem that might work if you bloody-bedamned have to keep it as uneven columns... in the .RowSource for the combo box, include a WHERE clause that requires that the text column cannot be empty or null.

I.e. build the row source for the combo just like you normally would. But then go back into the .RowSource property, which should contain a simple-minded SELECT query. Add a WHERE clause such that for the combo that uses ColumnB, you can't pick a blank.

Code:
SELECT IDCol, ColumnB FROM ComboMultiTable [COLOR="Red"]WHERE NZ( ColumnB, "" ) <> ""[/COLOR] ;
Theoretically, you shouldn't do this for databases where you really wanted everything to be normalized. But from a pragmatic viewpoint, this will keep you from tripping over the unevenly populated columns.
 

JoseO

Registered User
Joined
Jul 14, 2013
Messages
66
Thank you, thank you June7, Dbguy, Micron, Galaxiom, and Doc-man

Not only do I appreciate the SQL samples but, more so, the educating part of each of your posts. I want to learn good DB design and implementation and, having pros like each one of you guide me and instruct me, is a huge blessing!

I will go with the general consensus of having a separate table for each combo box. This is awesome! Thanks again!!!
 

June7

AWF VIP
Joined
Mar 9, 2014
Messages
2,687
Glad we could help. Maybe I will muddy the waters a little. Yes, I voted for 4 tables but have to say I have done something sort of like this. I have a table with 2 fields and never more than 1 record. Table is not a source for comboboxes. I just use it to save the version number of db in one field and the other field has a date for a process that is run periodically. Neither value has anything to do with the other. Technically, should be 2 tables. I will admit to violating a few rules of normalization in this db. It is a balancing act between normalization and ease of data entry/output. "Normalize until it hurts, denormalize until it works."
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom