Multicolumn Combobox property issue

[AmanKaur123]

Hi All

When I try to retrieve the value from access table and store it in multicolumn combo box then the rowsource property works fine and populate the combobox with the data.

but in actual I want to display the value in the combobox so I don't have to select it from the combobox and it appears automatically.
Also I can't use textbox to replace combobox.

The single column combobox works absolutely fine as below:

Me.CboOption = rs.Fields("Dept")

What change will I need to make in the following code to achieve what I want.

Me.CboBDForms.RowSource = "SELECT * FROM tblTC_Type WHERE (((tblTC_Type.[role])=[cborole].[value] and tblTC_Type.[Dept]='BD' and ID=" & rs.Fields("tblTC_Type.ID") & "));"

ANy help will be much appreciated.

Thanks

[Micron]

the first works because you're dealing with a single value. In the second, you're returning anywhere from 1 to 255 fields (by using the *) and as many records as there are that match the criteria. You'd need to return only one record, and set the column value to match the position from your recordset field. Something like

With Me.CboOption
 .Column(0) = rs.Fields("Dept")
 .Column(1) = rs.Fields("Shift")
 .Column(2) = rs.Fields("Planner")
End With

This assumes that you don't want the combo to have more than one row, as your post suggests, which is very odd. Not sure why you don't just use textboxes. Note that you usually don't need to refer to the value property of a control. Most often, it is the default. Note also that combo columns and rows are zero based.

[AmanKaur123]

Thanks Micron for the reply but I am getting Error message "Object required" . The combobox named cboBDForms exist and is 2 column combobox.

strsql = "SELECT ID,[SignOff_Form] FROM tblTC_Type WHERE role='" & Me.[cboRole] & "' and Dept='" & Me.CboOption & "' and ID=" & Me.txtDummy & ""
Set rs = CurrentDb.OpenRecordset(strsql)
If Me.CboOption = "BD" Then
Me.lblBDForms.Visible = True
Me.CboBDForms.Visible = True

Me.CboBDForms.Column(0) = rs.Fields(0)  'Object required error message
Me.CboBDForms.Column(1) = rs.Fields(1)   'Object required error message

[jwhite]

Shouldn't you be referencing the field names in rs.Fields(), like you exhibited in your first post?

[Micron]

rs.names or numbers won't matter unless you move the results around via sql edits.
Without seeing your module, my guess is that you don't have Option Explicit at the top of every module (it is a vb editor property setting) and you have not declared your recordset object name as rs, but as something else - maybe rst. To test that guess, insert Msgbox rs.fields(0) before the red line. Hopefully you will get the same error after eliminating the combo from the equation.

Those who don't set/check the 'require variable declaration' option get what they deserve - or so I'm told.

[AmanKaur123]

Micron, All the variables are already defined and I have already used Option Explicit. When I try to display the values of recordset variables then it works fine but when I try to store in combobox then the error message "Object required" comes up. The combobox named "CboBDForms" has the following properties set:
Column count = 2
Column Widths =0cm;2cm

strsql1 = "SELECT ID,[SignOff_Form] FROM tblTC_Type WHERE role='" & Me.[cboRole] & "' and Dept='" & Me.CboOption & "' and ID=" & Me.txtDummy & ""
Set rs = CurrentDb.OpenRecordset(strsql1)
If Me.CboOption = "BD" Then
Me.lblBDForms.Visible = True
Me.CboBDForms.Visible = True
MsgBox rs.Fields(0)
MsgBox rs.Fields(1)
'Me.CboBDForms.RowSource = "SELECT ID,[SignOff_Form] FROM tblTC_Type WHERE role='" & Me.[cboRole] & "' and tblTC_Type.[Dept]='BD' and ID=" & Me.txtDummy & ";"
With CboBDForms
    .Column(0) = rs.Fields(0)
    .Column(1) = rs.Fields(1)
End With

[andy49]

try cboBDForms.setfocus before the "with" line might help

But surely you cant set the value of a combobox

[Micron]

In your first code sample, you omitted that the combo might have a rowsource, which AFAIK, means you cannot alter the values returned to it. If it even could work, you'd have to tell it what row to place values. You'll have to explain why you're trying to do this, since a combo is meant for displaying values as per the list rows and columns that are returned to it. Why you are trying to inject values from a recordset is a puzzle. Like andy49 suggests, I doubt you can do this, nor should you want to.

The present error would be because you have gone too far down into the reference, meaning you're trying to work with the combo without identifying the parent form. If you look at what I posted versus what you did, they are not the same-
With Me.CboOption
With CboBDForms
If you fix that, I expect you'll generate an error related to Property Get and Property Let anyway, so I suggest you explain what the process is (related to this form) and what you want to have happen. I think there is a problem with your approach.