Problem querying cboxxx.Recordset on Form_Open

  • Thread starter Thread starter Max Yaffe
  • Start date Start date
M

Max Yaffe

Dear Group,

I'm having a problem with the following scenario and could use some
help.

I have an form (formPartInfo)with an empty record source. On it is a
combo box (cboPart) with a query (qryPartList) for a row source. I
initialize this form during the Form_Open event using code that looks
like:

Public Sub SetPart(myPart As String)

' Valid Part Number Test
Dim FindPart As String
FindPart = "Part = '" & myPart & "'"
Me.cboPart.Recordset.FindFirst FindPart
If (Me.cboPart.Recordset.NoMatch = True) Then
' Inform user & take corrective action
MsgBox ("Part " + myPart + " is not found)
Me.cboPart = Me.cboPart.ItemData(0)
myPart = Me.cboPart.Value
End If

'Continue on setting record source for form & subforms here
Me.RecordSource = PartQuery(myPart)
Me.sfVendor.Form.RecordSource = VendorQuery(myPart)
' and so forth.

The problem is that sometimes when I do the Valid Part Number Test,
Me.cboPart.Recordset is uninitialized and the test fails with an error
"Run-time error 91. Object or With Variable not set".
Then when I look at "Me.cboPart.Recordset" in the watch window, it is
set to "Nothing". However, if I then look at Me.cboPart in the watch
window, it is set correctly, after which, Me.cboPart.Recordset is set
correctly and the code continues to run!

I've tried this code running from Form_Open and from Form_Load with
the same results.

I guess the question is, when is MyForm.cboMyCombo.Recordset valid
after a form has started to open?

Thanks for your help.
Max
 
Thanks for your reply, John.

OnCurrent won't work here because I use this function to set the form
record source. That forces OnCurrent to be reissued which then resets
the record source which reissues OnCurrent,...ad rebooteum.

Any other ideas?
Max
 
Hi Max,

In that case I'd rewrite the valid part number test so it didn't depend
on the point reached by things over which I had no control. One
possibility would be to perform the test in the procedure that launchs
the form, and then pass something in OpenArgs to tell the form how to
behave.

Another would be to create your own RecordSet rather than using the
combobox's. Or you could just do

If DCount(...) Then

passing DCount the same query you're using for the combobox's RowSource.
 
Thanks, John.
That's essentially what I did although it seems a shame the obvious,
easy way wouldn't work. I rewrote the query using an SQL string so I
could effectively clone it. Here's my solution leaving out the
details:

Dim RS As Recordset
Set RS = DB.OpenRecordset(Me.cboPart.RowSource)

RS.FindFirst "Part = '" & myPart & "'"
If (RS.NoMatch = True) Then
' take appropriate corrective action
End If

RS.Close

Thanks for your help.
Max
 
Back
Top