combo box afterupdate issue

[Lhuffst]

I have a combo box that displays all my contracts fine. There are a couple of times when you select a value that a different record actually shows up and I dont' know why. It is an unbound combobox with the following record source

SELECT tblContracts.FldContractNum, tblContracts.fldSubdivisionName, tblContracts.fldContractId FROM tblContracts;

The first time in, the list of contract show properly and in most cases the combo box works properly. However, if I select 92-9305, it ends up displaying 03-3641. The only thing that is duplicated is the fldsubdivisionname but I am bound to fldcontractid as the boundcolumn. I don't know what to do to fix this issue and hoping for some help. Thanks lhuffst

 

[dhookom]

Are there duplicate fldContractIDs? What do you see with this query:

SELECT fldContractID, Count(*) as NumOF
FROM tblContracts
Having Count(*) >1;

What are the other significant properties of the combo box?

Duane
Hook'D on Access
MS Access MVP

 

[Lhuffst]

zero records

 

[dhookom]

What have you done to trouble-shoot?

Duane
Hook'D on Access
MS Access MVP

 

[Lhuffst]

I looked at the query and made sure that duplicates were not there.
I did debug statements to make sure that the correct values went with the correct column.

Other than that, I don't know what to check. Originally on afterupdate, I requery a different combo box and thought that might be causing the problem so I commented out the afterupdate and still get the same results.

 

[dhookom]

Apparently there is code and something that you might want to share as well as answer my previous question.

Duane
Hook'D on Access
MS Access MVP

 

[Lhuffst]

Sorry thought I had answered your question.
CboContracts combobox

Rowsource type = table/query
Rowsource = SELECT DISTINCT tblContracts.FldContractNum, tblContracts.fldSubdivisionName, tblContracts.fldContractId FROM tblContracts; 
Bound Column = 2
After Update Event:
 txtid = cboContracts.Column(2)
   Debug.Print cboContracts.Column(0)
    Debug.Print cboContracts.Column(1)
    Debug.Print cboContracts.Column(2)

    Me.cboBoxNumbers.Requery

Since clicking on the 92-9305 contract in the drop down box displays the 03-3641 contract, I made a query to see the differences.

[blue]
fldcontractid fldcontractnum fldsubdivisionname
397 03-3641 tantallon on the potomac
9182 92-9305 tantallon on the potomac
[/blue]
I am not sure why it is picking up the first record in the database because the mainid (fldcontractid) is unique as is the contractnum. How do I avoid this?
Thanks

 

[dhookom]

It looks like your combo box is bound to the fldSubdivisionName field which has duplicates.

Duane
Hook'D on Access
MS Access MVP

 

[Lhuffst]

ok thanks. I thought you counted columns as 0,1,2. I'll make that change and retest. Thanks

 

[dhookom]

It's a bit confusing since the columns are numbered from 0 in code.

Duane
Hook'D on Access
MS Access MVP