Assign recordset to combo problem

[cheer]

There are two columns under the combo box (name: cboDefectGroup) . When I assign a recordset to the combo box as below, no data can be displayed. Below is my VBA

cboDefectGroup.Value = rsTemp2("Description")

 

[missinglinq]

That's not assigning a recordset to the combobox, it's trying to assign an entire recordset to the Value Property of the cbo!

I'm guessing what you're trying to do is have the combobox display the recordset when dropped down, i which case you need to assign the recordset to the RowSource of the cbo.

cboDefectGroup.RowSource = rsTemp2("Description")

 

[DCrake]

The problem is that you will only get the first record in the recordsource. It won't enuerate through the recordset to fully populate the combo values.

David

 

[cheer]

That's not assigning a recordset to the combobox, it's trying to assign an entire recordset to the Value Property of the cbo!

I'm guessing what you're trying to do is have the combobox display the recordset when dropped down, i which case you need to assign the recordset to the RowSource of the cbo.

cboDefectGroup.RowSource = rsTemp2("Description")

No, above is not what I am looking for.

There are 2 combo box on my interface. The 1st combo box consists of 1 column and the 2nd combo box consists of 2 columns as I describe earlier. All columns have been filled up with value through the RowSource

When user search back the old record, I have to create a recordset to extract data from query and then assign individual recordset value to all controls (like textbox, combox box, etc) on interface.

Question, why I can easily assign the recordset value to the 1st combo box and not to the 2nd combo box ?

1st combox box (1 column) : cboCompanyName.value = rsTemp2("CompanyName") => work fine

2nd combox box (2 columns with 1 column invisible) : cboDefectGroup.value= rsTemp2("Description") => no error message and no value displayed on combo box

What is the right way to assign recordset value to the 2nd combo box with 2 columns ?

 

[boblarson]

It sounds like you are trying to assign a value to a combo but you are assigning the WRONG value. If you look, your combo probably has an ID field AND a description field. The ID field is probably the bound field and so you are essentially trying to assign a description to a numeric field. You might try changing to this:

cboDefectGroup.Column(1) = rsTemp2("Description")

 

[cheer]

It sounds like you are trying to assign a value to a combo but you are assigning the WRONG value. If you look, your combo probably has an ID field AND a description field. The ID field is probably the bound field and so you are essentially trying to assign a description to a numeric field. You might try changing to this:

cboDefectGroup.Column(1) = rsTemp2("Description")

You have expressed exactly my problem.

However, error message alert: Object required.

I attach the full source code at below.

 

[boblarson]

Try adding

Me.

to the code:

Me.cboDefectGroup.Column(1) = rsTemp2("Description")

 

[cheer]

Try adding

Me.

to the code:

Me.cboDefectGroup.Column(1) = rsTemp2("Description")

Same as before. Error alert.

 

[boblarson]

Why are you trying to use RECORDSETS when it appears you are trying to get to a certain value. A DLookup would be better in this situation.

 

[cheer]

Why are you trying to use RECORDSETS when it appears you are trying to get to a certain value. A DLookup would be better in this situation.

I have replaced above code with following

cboDefectGroup = DLookup("Description", "Defect", "DefectCode='" & rsTemp1("DefectCode") & "'")
cboDefectGroup.Requery

At the debug time, I can see there is a value for cboDefectGroup . However, I cannot see any value on screen. Any further advice?

 

[cheer]

I attach here the combo box property for better illustration

 

[boblarson]

Make sure that the COLUMN COUNT in the combo box is set to 2 and if you have Access 2003 Service Pack 3 make sure that there is no formatting on the fields at table level.

Also, you don't need to requery the combo after setting the value, in fact it could cause you a problem.

 

[cheer]

Make sure that the COLUMN COUNT in the combo box is set to 2 and if you have Access 2003 Service Pack 3 make sure that there is no formatting on the fields at table level.

Also, you don't need to requery the combo after setting the value, in fact it could cause you a problem.

The column count is 2, Access version 2000.
I can't upgrade to Access 2003 due to limitation in license.
How can I resolve this although the column count is already set as 2?

 

[cheer]

Above case is closed after I implement the below

a) Use the unbound combo box
b) Populate value at run time via the GotFocus event . Read more at here http://www.access-programmers.co.uk/forums/showpost.php?p=908190&postcount=6
c) Remain the application of the recordset

 

[boblarson]

Above case is closed after I implement the below

a) Use the unbound combo box
b) Populate value at run time via the GotFocus event . Read more at here http://www.access-programmers.co.uk/forums/showpost.php?p=908190&postcount=6
c) Remain the application of the recordset

You still didn't answer my question about B) - why do you need to populate at runtime since the query had no where clause.

 

[cheer]

You still didn't answer my question about B) - why do you need to populate at runtime since the query had no where clause.

1) I wish to have the latest /updated value
2) In order for (C) to run, this is the only way to create unbound control but load all value at run time through the GotFocus event.

 

[boblarson]

1) I wish to have the latest /updated value
2) In order for (C) to run, this is the only way to create unbound control but load all value at run time through the GotFocus event.

That is INCORRECT! The latest value can come simply by requerying the combo box. You do NOT need to set the recordset every time, especially if it is the SAME.

And it is NOT the only way to create an unbound control but load the values through the Got Focus event. Just put

Me.ComboBoxNameHere.Requery

in the got focus event.

It works just fine. IF you had to change the row source based on a WHERE clause then that would be different. But since the row source doesn't change, you should just set it in design view and then REQUERY the control!

 

[cheer]

That is INCORRECT! The latest value can come simply by requerying the combo box. You do NOT need to set the recordset every time, especially if it is the SAME.

And it is NOT the only way to create an unbound control but load the values through the Got Focus event. Just put

Me.ComboBoxNameHere.Requery

in the got focus event.

It works just fine. IF you had to change the row source based on a WHERE clause then that would be different. But since the row source doesn't change, you should just set it in design view and then REQUERY the control!

Wrong or right not so important in life!!! What is MORE important is the system currently work the way I am looking for . Output always overtake the concept.

Anyway, thanks for your above sharing. Requery method really do not required in my case. See ya!

 

[boblarson]

No, you can do whatever you want to do - that is for sure. But I will liken your situation to someone who is trying to heat a house with a blow torch instead of a furnace. It will get the job done but it makes the system work harder.