Solved Clearing Recordset property value in Combobox

[DalynSoft]

I am using this code to set the recordset for a combo box:

Set rstTemp = basRunDataObject("dbo.spcboNodeSubPortPartnerPort " & Forms!frmAANodeDetail!sfrNodeSub.Form!txtID & ",0", adCmdText)
Set Me!cboPartnerPortIDNo.Recordset = rstTemp
rstTemp.Close
Set rstTemp = Nothing

The function basRunDataObject returns an ADODB recordset.

After certain processes on the form I want to clear the recordset property so that there are no records showing in the combo box (the user needs to fill other fields so determine the new set of records to appear in the combobox).

Set Me!cboPartnerPortIDNo.Recordset = Null, and Set Me!cboPartnerPortIDNo.Recordset = "" both produce errors.

Is there a simple way to clear the Recordset property? I could run the stored procedure again to return no records but this involves a trip to the server and if there was a local solution this would be more efficient.

 

[cheekybuddha]

Try

Set Me!cboPartnerPortIDNo.Recordset = Nothing

 

[arnelgp]

With Me!cboPartnerPortIDNo
   .Value=""
   .RowSource = ""
End With

 

[Mike Krailo]

Set Me!cboPartnerPortIDNo.Recordset = Null, and Set Me!cboPartnerPortIDNo.Recordset = "" both produce errors.

What errors exactly are you getting?

 

[The_Doc_Man]

both produce errors.

Such as? I'm going to guess "No Current Record" is one of the errors you might get.

One thing you might do depends on whether you have error trapping in place where you do that adjustment of the recordset.

IF you have no error trapping for that routine, you can use ON ERROR RESUME NEXT and then diddle with the .Recordset property. It is a general rule that when changing a .Recordset property, you will immediately do a .Requery, which is why you might get a recordset error.

Particularly if the next thing you were going to do anyway was to EXIT the routine, that would silence the errors. Whether that recordset change also displays empty records? At least you can do a Me.Repaint after changing the recordset to assure that effect.

If that routine was NOT going to immediately exit (I.e. has more to do), you would have to consider re-asserting the previous error handler afterwards. Or changing the order of the other things to make the recordset-diddling last.

 

[Gasman]

It is a general rule that when changing a .Recordset property, you will immediately do a .Requery,

I *thought* changing the recordset carried out an implicit requery?

 

[The_Doc_Man]

I *thought* changing the recordset carried out an implicit requery?

Yes. Did my post not come out that way? Diddle the recordset and it changes everything. As a side effect, it also changes the .RecordSource if you do that on a form. And a couple of other properties that current escape me.

 

[Gasman]

t is a general rule that when changing a .Recordset property, you will immediately do a .Requery,

No, I read that as you *should* ?

 

[cheekybuddha]

Yes. Did my post not come out that way? Diddle the recordset and it changes everything. As a side effect, it also changes the .RecordSource if you do that on a form. And a couple of other properties that current escape me.

This is a combobox recordset, not a form's recordset, so does not affect a form's .RecordSource

 

[MajP]

Set Me!cboPartnerPortIDNo.Recordset = Null, and Set Me!cboPartnerPortIDNo.Recordset = "" both produce errors.

Of course they give an Error. You can not set an Object to null or a String value. That is not even correct syntax. A combos recordset is not a string or a variant it is either a DAO or ADO recordset. So setting it to nothing will work as per reply #2.

See demo

Private Sub cmdClear_Click()
  Set Me.cmboOne.Recordset = Nothing
End Sub

Private Sub cmdSetRecordset_Click()
 LoadComboRecordset
End Sub

Private Sub Form_Load()
  LoadComboRecordset
End Sub

Public Sub LoadComboRecordset()
  Dim RS As New ADODB.Recordset
  Dim strSql As String
 
  strSql = "SELECT LastName from Employees" ' where LastName = 'King'"
  Set RS = New ADODB.Recordset
  With RS
    .ActiveConnection = CurrentProject.Connection
    .CursorLocation = adUseClient
    .CursorType = adOpenStatic
    .LockType = adLockReadOnly
    .Open strSql
 End With
 Set Me.cmboOne.Recordset = RS
End Sub

 

[The_Doc_Man]

This is a combobox recordset, not a form's recordset, so does not affect a form's .RecordSource

When I looked up MS Help to verify, it suggested that the equivalent... .RowSource, I guess, would be affected if you diddled with its .Recordset. And vice-versa, of course. My bad for using .RecordSource when it should have referenced .RowSource.

This is the reference I used.

ComboBox object (Access)

Office VBA reference topic

learn.microsoft.com

The thought occurs to me that if you change the .RowSource to "" you would get the empty recordset, but if you were so inclined, you could just put a constant string that can't return anything, like SELECT field list FROM table WHERE 0=1; - which you would do ONLY because that makes for a highly predictable result, namely an empty recordset. Erasing the query from the .RowSource would have the same effect as trying to change the .Recordset to Nothing.

 

[MajP]

The thought occurs to me that if you change the .RowSource to "" you would get the empty recordset, but if you were so inclined, you could just put a constant string that can't return anything, like SELECT field list FROM table WHERE 0=1; - which you would do ONLY because that makes for a highly predictable result, namely an empty recordset. Erasing the query from the .RowSource would have the same effect as trying to change the .Recordset to Nothing

Easy enough test to show what you are saying is wrong.

Simply modify my example db. Instead of setting the recordset to nothing, set the rowsource to whatever you want. It does nothing.

Private Sub cmdClear_Click()
  'Set Me.cmboOne.Recordset = Nothing
  Me.cmboOne.RowSource = " SELECT LastName from Employees where 0 = 1"
 'or Me.cmboOne.Rowsource = ""
End Sub

Neither of these will do anything if the recordset is assigned through code. This is the same in DAO too. If you assign the recordset in code changing the rowsource has no effect.

FYI,
I addressed this before, and there are some nuances.

How do you clear a listbox?

@moke123, there are times where this makes coding easier. Imagine you have a form where you use the built in access features in data sheet view to filter and sort the records. You can then simply pass the reocordset to a listbox without trying to rebuild a Sql string based on the FilterBy and...

www.access-programmers.co.uk

 

[June7]

Why use Recordset? Why not just set RowSource with SQL statement?

 

[cheekybuddha]

Why use Recordset? Why not just set RowSource with SQL statement?

The source is coming from a SP, so you would need to use a stored QueryDef since a straight SQL statement wouldn't work.

So, it might suit the OP better to keep the object in code rather than have another QueryDef cluttering up the queries section.

 

[The_Doc_Man]

Simply modify my example db. Instead of setting the recordset to nothing, set the rowsource to whatever you want. It does nothing.

Interestingly enough, the article I linked was for the ComboBox.Recordset, and on re-reading, I see that they copied/pasted some text from the Form.Recordset discussion (in the part about Transactions in that article.) It was misleading. I will defer to you this time, but note that MS changed focus inside the article, which I now consider as useless.

 

[DalynSoft]

Try

Set Me!cboPartnerPortIDNo.Recordset = Nothing

Wow, leave the group for a night and replies abound. Thanks everyone for your time. David's suggestion was the one that worked.