Bound column number being saved to table

[KerryL]

I can't figure out why a few of my combo boxes are saving the bound column number to the table instead of the data (from the selection table).

For example, on the data entry form users can add up to 3 Layoff Reasons using separate combo boxes whose selections come from tblLayoffReason. A selection in the first combo box works fine; the selection is added to the table. But anything selected in the 2nd & 3rd combo boxes cause the bound column number to be saved to the table instead of the selection data.

These are the 3 combo boxes on frmAddData:
cboLayfReas1
cboLayfReas2
cboLayfReas3

Properties of each cbo:
Column Count = 2
Column Heads = No
Col Width = 0";2"
Bound Column = 1


tblLayoffReason contains the selections available for the combo boxes:

ID Reason
1 Not Reported
2 Company Sold
3 Bankruptcy
4 Financial Trouble
5 Operations Transferred


Iif the user selects "Company Sold" for cboLayfReas1, "Company Sold" ends up in the table. If the user then selects "Bankruptcy" for cboLayfReas2, what is placed in the table is the number "3." Consequently, when users go into edit that record later it shows "3", and all reports show a "3" for Layoff Reason 2 as well. Obviously, I need the actual reason (Bankruptcy) to be displayed.

If the combo box properties are identical, why is the bound column number being saved to the table instead of the text from column 2 of the selection table?

 

[Remou]

The bound column should always be saved to the table, 'bound' as in bound to a field in a table. It is quite normal to save a number to the table and then base reports on a query that links the 'main' table and the 'look-up' table. Such a query will allow you to show the description of the number field, that is 'Not reported', 'Bankruptcy' etc.

 

[KerryL]

If that's the case, then I'm confused as to why 2 combo boxes with exactly the same properties (and using the same lookup table) are saving different things to the table. One is saving the explanation and one is saving the bound column, and each have the same properties.


FWIW, I think I now understand why it's preferable to save the bound column to the table and link the main & lookup tables in a query. For one, it will allow me to make changes to the lookup table without having to edit multiple records.

 

[Remou]

Can you double-check the combo that is saving the description to make sure that one, the row source is the same (ID, Reason) and two, the bound column is 1?

 

[KerryL]

The properties for all 3 of them are identical:

Row Source = tblLayoffReason
Column Count = 2
Column Heads = No
Col Width = 0";2"
Bound Column = 1


I just double-checked them again -- I don't understand it. Now I've got erroneous data in the table.

Do you think you might be able to figure out what's going on if you look at the db? It's small--I could email it to you.

 

[Tarnish]

FWIW, I think I now understand why it's preferable to save the bound column"

Not to nitpick but a "bound" column is 'saved' by definition. You don't make a decision to save a bound column (versus saving a different column). It is bound, therefore it is saved. It is saved only because it is bound.

T

 

[Remou]

Have you got a site you can upload to? Emails are very much frowned upon.

 

[KerryL]

Tarnish,
If the bound column is saved by default then I'm stumped as to why a couple of the cbo's on my form are saving the text (from column 2 of the lookup tables) instead of the number from the bound column. Especially if the bound column isn't the description column.


Remou,
No, I don't have a site to upload it to.

 

[Remou]

Have you any code on this form? If so, please post it.

I must agree with Tarnish, I have never encountered a situation where anything but the bound column was saved to the table.

 

[KerryL]

Yeah, there's some code but it doesn't really have anything to do with the cbo's that are acting funny.

Right now I'm going back through each cbo individually. I did find one other one that had a Bound Column = 2. Then I imported the form(s) from an older version to see how the data was saved to the table. Still verifying that.

I guess I'm a little confused on how to retrieve the correct descriptions from the lookup tables (for reports and queries) if the only thing that ends up in the main table is a number. Especially if a report or query has the main table for its record source.


FWIW, here's the code for the ADD form:
=============================================



Option Compare Database

Private Sub ckWOM_AfterUpdate()

If Me.ckWOM = True Then
Me.lblMtgDates.Visible = True
Me.txtEmplOrMtg1.Visible = True
Me.txtEmplOrMtg2.Visible = True
Me.txtEmplOrMtg3.Visible = True
Else
Me.lblMtgDates.Visible = False
Me.txtEmplOrMtg1.Visible = False
Me.txtEmplOrMtg2.Visible = False
Me.txtEmplOrMtg3.Visible = False

End If

End Sub

Private Sub cmdClearForm_Click()
On Error GoTo Err_cmdClearForm_Click

DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70

Exit_cmdClearForm_Click:
Exit Sub

Err_cmdClearForm_Click:
MsgBox Err.Description
Resume Exit_cmdClearForm_Click

End Sub

Private Sub cmdReturnToMainMenu_Click()
On Error GoTo Err_cmdReturnToMainMenu_Click

DoCmd.Close

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Switchboard"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdReturnToMainMenu_Click:
Exit Sub

Err_cmdReturnToMainMenu_Click:
MsgBox Err.Description
Resume Exit_cmdReturnToMainMenu_Click

End Sub

Private Sub County_AfterUpdate()

Me.MWA = County.Column(1)
Me.Region = County.Column(2)

End Sub

Private Sub cmdSaveNotice_Click()
On Error GoTo Err_cmdSaveNotice_Click

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
DoCmd.GoToRecord , , acNext
DoCmd.GoToControl ("Tab0")

Exit_cmdSaveNotice_Click:
Exit Sub

Err_cmdSaveNotice_Click:
MsgBox Err.Description
Resume Exit_cmdSaveNotice_Click

End Sub

Private Sub Name_Change()

Me!txtNoticeNo = 1 + Nz(DMax("NoticeNo", "tblWARNData"), 0)
Me.EntryDate = Date

End Sub

Private Sub RRMtgDate_AfterUpdate()
If Not Me.RRMtgDate Then
Me.NoRRMtgReas.Visible = False
Else
Me.NoRRMtgReas.Visible = True
End If
End Sub

 

[Tarnish]

Kerry,

Are you sure the values are actually saved to the table as opposed to what's shown in the combo box?

You can show one thing in the combo box and save a different thing using the columnwidths property.

T

 

[Remou]

You also need to check the row source to see how many fields are referenced in the SQL, table or query.

 

[Tarnish]

Maybe the sql in the rowsources are different such that one combo box selects the "ID" first but the other combo box selects some other field first, and the ID second?

Just spitballing here...

T

 

[KerryL]

To verify what's been saved to the table, I open the table itself and look at the record I just added or edited.

Tell me if I understand this correctly:

cboLayoffReas1
Row Source = tblLayoffReason
Column1 = ReasID
Column2 = ReasDescr
Column Count = 2
Column Heads = No
Col Width = 0";2"
Bound Column = 1

If I use cboLayoffReas1 to select the description in row #3, then "3" will be saved to the table because the bound column is Column1. Correct?

If I were to change Bound Column to "2" then the description would be saved to the table. Right?

 

[Remou]

Yes, that is what I would expect.

 

[KerryL]

So, as you mentioned before, the bound column is the one bound to the table. The one whose data is saved in the record.

That's what confuses me about the cbo's whose bound column is #1 but the description (from col 2) ends up in the table.

I think I'll recreate those cbo's and see what happens. I'll let you know whap happens with brand new combo boxes that are bound to the ID column.

BTW, thanks for your thoughts--I appreciate you hanging in there with me.

 

[KerryL]

I created a new cbo (used the wizard) and the properties are exactly the same as this...

cboLayoffReas1
Row Source = tblLayoffReason
Column1 = ReasID
Column2 = ReasDescr
Column Count = 2
Column Heads = No
Col Width = 0";2"
Bound Column = 1

with the following exception:

Row Source = SELECT [tblLayoffReason].[LayfReasID], [tblLayoffReason].[Reason] FROM tblLayoffReason;


It's saving the ReasID in the table instead of the description text from Column2.

I don't understand why the mini query is necessary, but at least I know what I need to do to fix it. Now I will need to figure out how to retrieve the Description for queries and reports. I still dont get that.

 

[PHV]

I still dont get that
Have a look here:

http://www.devshed.com/c/a/MySQL/Understanding-SQL-Joins

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Remou]

You can create a query something like this:

[tt]SELECT tblMain.LayfReasID, tblLayoffReason.Reason
FROM tblMain LEFT JOIN LayfReasID ON tblMain.LayfReasID = tblLayoffReason.LayfReasID;[/tt]

 

[Tarnish]

Kerry,

The "mini query" solves this problem:

Often, what is best to STORE in a table is NOT the best thing for the user to SEE as an option from which to choose.

For example, the best, most succinct reference (and the ONLY one that's truly UNIQUE) to a person in your database is that person's contactID field (or whatever you name it). That one field in his record can be used to look up his record and find out everything else about him that you know. Any other field (first name, last name, age, race, etc...will not be UNIQUE.....meaning two or more contacts may share the same entry for that field). Even an address may not be unique because, over time, people move and/or people move in with each other (and different family members, etc etc).

SO, usually what you want to store is that unique field, which is often a number. However, if you populate a combo box with some random number, how is your user suppose to know which contact is represented by each number in the dropdown list? Well, I guess they could STOP entering the record and go try to find the ID number in your contacts table (if they have access to it), and then come back. But that's hardly user-friendly.

So, there's often this conflict: What's good for the goose (the table) is NOT always good for the gander (the user).

"Mini query" to the rescue. "Mini query" steps in and creates a "mini record" which includes what the table wants to store (ID#) and what the user wants to see (Name) and makes it all fit inside the combo box. Then, with a couple of setting adjustments, the user picks the "mini record" based on the "Name" that's displayed (and that's all he sees), but "mini query" passes on the related "ID" to the table so table is happy too!

Does that make any sense or did I get too carried away?

T