Problem querying cboxxx.Recordset on Form

Problem querying cboxxx.Recordset on Form_Open

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Max Yaffe - 05 Mar 2005 15:51 GMT

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

Reply to this Message

John Nurick - 08 Mar 2005 06:13 GMT

Hi Max,

Have you tried the form's Current event?

>Dear Group,
>
[quoted text clipped - 40 lines]
>Thanks for your help.
>Max

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

Reply to this Message

Max Yaffe - 08 Mar 2005 17:27 GMT

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,
>
[quoted text clipped - 44 lines]
>>Thanks for your help.
>>Max

Reply to this Message

John Nurick - 08 Mar 2005 21:32 GMT

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 for your reply, John.
>
[quoted text clipped - 53 lines]
>>>Thanks for your help.
>>>Max

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

Reply to this Message

Max Yaffe - 09 Mar 2005 07:55 GMT

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

>Hi Max,
>
[quoted text clipped - 68 lines]
>>>>Thanks for your help.
>>>>Max

Reply to this Message