Problem Setting Text Property of Combo Box

[duluter]

I'm having a problem setting the text property of a combo box in vba.

I get a "Run-time error 91: Object variable or With block variable not set" when I run my procedure, and the debugger highlights whatever line follows the line on which I set the combo box text property.

On my form, the user uses the "Edit Farm" button to bring up a dialog box to edit the farm details, like the farm's name and acreage, etc. After the user makes the changes in the dialog box, I want the updated farm info to appear on the main form. I query the database for the appropriate records and populate the labels on the form. But the farm name is in a combo box, not a label, so I use the Text property to set the farm name in the combo box, after repopulating the combo box with the updated list of farm names.

If I comment out the line on which I set the Text property, the procedure runs, so I'm pretty sure it's a problem with setting the Text property, but I can't figure out why there's a problem, and why the debugger doesn't highlight that line, rather than the next line in the procedure.

Here's my procedure:

Private Sub cmdEditFarm_Click()

Dim rsf as ADODB.RecordSet

'Open the Edit Farm dialog to get the new farm info from the 'user.
DoCmd.OpenForm "frmEditFarm", , , , , acDialog

'Clear the farms selection box. Need to do this because the 'user might have renamed a farm, and that needs to get 'reflected in the combo box list.
Call clearComboBox(Me.cboFarms)

'Repopulate the farms selection box.
Set rsf = New ADODB.RecordSet

'Get the names and IDs of all the farms registered to the selected client.
'(Note that ClientIDF is a global variable that's already been set 'to the current client ID.)
rsf.Open "SELECT pkFarmID, FarmName FROM tblFarm WHERE ClientID = " & ClientIDF & " ORDER BY FarmName", CurrentProject.Connection, adOpenStatic, adLockOptimistic

If rsf.RecordCount = 0 Then

rsf.Close
Exit Sub

End If

Do Until rsf.EOF

Me.cboFarms.AddItem (rsf!pkFarmID & ";" & rsf!FarmName)
rsf.MoveNext

Loop

rsf.Close

'Clear the farm data labels.
Me.lblFarmDataCounty.Caption = ""
Me.lblFarmDataAcreage.Caption = ""
Me.lblFarmDataDescription.Caption = ""
Me.lblFarmDataState.Caption = ""

'Get all the data associated with the farm that's just been 'edited so we can repopulate the data labels on the form.
'(Note that FarmIDF is a global variable that has already been 'set to the selected farm ID.)
rsf.Open "SELECT FarmName, FarmDescription, Acreage, Abbreviation, CountyName FROM tblCtlState INNER JOIN (tblCtlCounty INNER JOIN tblFarm ON tblCtlCounty.pkCountyID = tblFarm.PrimaryCountyID) ON (tblCtlState.pkStateID = tblCtlCounty.StateID) AND (tblCtlState.pkStateID = tblFarm.PrimaryStateID) WHERE pkFarmID = " & FarmIDF, CurrentProject.Connection, adOpenStatic, adLockOptimistic

If rsf.RecordCount = 0 Then

rsf.Close
Set rsf = Nothing
Exit Sub

End If

'Set data labels.
Me.cboFarms.SetFocus
Me.cboFarms.Text = Nz(rsf!FarmName, "") 'MY PROBLEM LINE.
Me.cmdManageWorkOrders.SetFocus

Me.lblFarmDataCounty.Caption = Nz(rsf!CountyName, "")
Me.lblFarmDataAcreage.Caption = Nz(rsf!Acreage, "")
Me.lblFarmDataDescription.Caption = Nz(rsf!FarmDescription, "")
Me.lblFarmDataState.Caption = Nz(rsf!Abbreviation, "")

rsf.Close
Set rsf = Nothing

End Sub
​

Actually, I lied a little earlier. In this case, the debugger highlights the second line after the Text property line--setting the focus to a different control gets through. If I were to delete all of the lines between the Text property line and the "rsf.close" line, the debugger highlights the "rsf.close" line with the same error.

What's going on?

 

[WayneRyan]

duluter,

The .Text property is only valid when the control has the focus and is being
edited.

Change --> Me.cboFarms.Text
To --> Me.cboFarms or Me.cboFarms.Value or Me.cboFarms.Column(0)

I put the last part in if you have a multi-column combo.

Wayne

 

[duluter]

Thanks for the reply, Wayne.

But I just built a quick form to check my Text property syntax, and everything worked ok. On my quick form, I put a combobox and a command button. In the click event of the command button, I wrote:

me.combo0.setfocus
me.combo0.text = "This"

and the procedure ran okay and the combobox's text property updated.

Are you sure there's something wrong with my Text property syntax in my original example?

 

[duluter]

UPDATE: I just tried saving the farm name in a string variable, closing the recordset, and then changing the Text property of the combobox (it's now the last thing I do in the procedure) and it runs. Weird. Was it not working because the recordset was still open before?