Combo Box value / Text

[HelpMoses76]

Hi ,
I have a combo box with 2 columns . One has designation and one for salary level .

When I try to insert the designation into database it just puts the index value ..ie 1 ,2 ,3, 4,,,, of the item into the database not the designation.

Jr Analyst 50k
Sr Analyst 60k
Manager 100k
Sr. Manager 150k

When I chose Jr Analyst ..it puts 0 in the database table on the code for insert query
for Sr Analyst its put 1
for Manager it puts 2
for Sr. Manager it put 3.

How can I fix this ?

 

[June7]

That's what should happen. That's what keys are for. Don't duplicate data between tables.

 

[HelpMoses76]

I am assuming you are saying the keys then should be referenced in a query to pull the data ?

 

[Isaac]

While totally agreeing with June, I'd like to "add" an explanation of why what is happening is happening.
When you have a listbox or a combobox, they may have several columns. (let's say a 1st, 2nd and 3rd column). (whose "index"...cobName.columns(x) will be 0, 1 and 2).
When your form attempts to save the combobox's "Value" to the table, the "Value" is deemed to be whatever is in the bound column.
You can set that in the properties of the combobox.

If your combobox has 3 columns, containing the values "one", "two" and "three", and you set (or leave defaulted) the bound column to be the first one, then "one" will save to the table. If you set the bound column to be the 2nd one, then "two" will be saved to the table.

Recommended that you take these steps:
- make the first column be a key column, and, most likely, make its width 0, so that it's not displayed to users.
- make the bound column the first column (that key)
- so that the Key value gets saved to the table.

If this is really just a trivial attribute, say, "Color" which can take yellow, red or green, then I don't think Key values are probably a big deal here - save whatever column you want to the table.
But, if you were selecting, say, which Project (in a combo) to relate to an Employee, in which case PK and FK's ought to be fully utilized, then your combo would be Project Key (col 0) and Project Name (col1). bound column is col 0, the key, which gets saved to another table.
Not the best example, but to make the point.

 

[June7]

I just noticed you said result is from INSERT sql. It would be unusual for a key value to be 0. So, how is 0 getting saved? You are grabbing combobox row index instead of column value? Provide your code for analysis.

Why not combobox bound to field?

 

[HelpMoses76]

This is the code I am using

Private Sub cmdAddRow_Click()
MsgBox "Adding new row to database..."
'CurrentDb.Execute "INSERT into BR_Master([BRID], [Analyst], [Branch])VALUES (1, 'Kelly', 'PSPC');"
On Error GoTo trap

Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb()
Set rs = db.OpenRecordset("BR-DETAILS", dbOpenDynaset, dbAppendOnly)

rs.AddNew
rs!MasterID = txtID
rs!Partner = txtPartner
rs!AssetName = txtAsset
rs!AssetTag = txtTag
rs!CostCentre = txtCostCentre
rs!ServiceGroup = cmbService
rs!FACode = txtFACode
rs!BRID = txtBRID1
rs!PRJID = txtPRJID1
rs!ProjectIO = txtPrjIO
rs!CostType = cmbCostType
rs!EffortType = cmbEffortType
rs!Quantity = txtQty
rs!EffortCost = txtEffortCost
rs!FinancialYear = txtFY
rs!EffortDays = txtEffortDays
rs!Schedule = cmbSchedule
rs!ServiceCostTotal = txtSrvCostTotal
rs!ImplementationOPI = txtImpOPI
rs!CostingStatus = cmbCostingStatus
rs!EstimateClass = cmbEstClass
rs!Comments = txtComments

rs.Update
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing


MsgBox "BRID " & txtBRID1 & " 1 row added", vbOKOnly, "New row added!"



trap: If Err.Number = 3022 Then
MsgBox "Combination of BR and Project already exists.!"
End If





End Sub

 

[June7]

Why are you not using a bound form?

I don't see anything in code to save row index.

 

[HelpMoses76]

Can i share my database with you privately ?

 

[Isaac]

- In the recordset code, which line is going to the column where you see this problem?
- Do you have any code in the combobox's afterupdate event?

 

[HelpMoses76]

rs!EffortType = cmbEffortType
This one.
Also on the click event of the combox box I have a another text box being updated.

Me.txtEffortCost.Value = Round((cmbEffortType.Column(1) / 220), 2)

 

[June7]

You can attach db to post in thread, not me privately.

 

[HelpMoses76]

While totally agreeing with June, I'd like to "add" an explanation of why what is happening is happening.
When you have a listbox or a combobox, they may have several columns. (let's say a 1st, 2nd and 3rd column). (whose "index"...cobName.columns(x) will be 0, 1 and 2).
When your form attempts to save the combobox's "Value" to the table, the "Value" is deemed to be whatever is in the bound column.
You can set that in the properties of the combobox.

If your combobox has 3 columns, containing the values "one", "two" and "three", and you set (or leave defaulted) the bound column to be the first one, then "one" will save to the table. If you set the bound column to be the 2nd one, then "two" will be saved to the table.

Recommended that you take these steps:
- make the first column be a key column, and, most likely, make its width 0, so that it's not displayed to users.
- make the bound column the first column (that key)
- so that the Key value gets saved to the table.

If this is really just a trivial attribute, say, "Color" which can take yellow, red or green, then I don't think Key values are probably a big deal here - save whatever column you want to the table.
But, if you were selecting, say, which Project (in a combo) to relate to an Employee, in which case PK and FK's ought to be fully utilized, then your combo would be Project Key (col 0) and Project Name (col1). bound column is col 0, the key, which gets saved to another table.
Not the best example, but to make the point.

Also changing bound column to 2 fixed it . Thanks .

 

[Isaac]

Also changing bound column to 2 fixed it . Thanks .

Awesome. Glad to hear you got it working.

 

[HelpMoses76]

here it is

 

[June7]

If you don't want to use key then don't bother including in combobox RowSource.

 

[HelpMoses76]

Awesome. Glad to hear you got it working.

you sir are a genuis !