Checking if Recordset has data (1 Viewer)

[SeanDelere]

Hello again all,

I have a form which has a subform which is based on a Query.

Updating a TextBox on the main form does a requery and displays the results in the subform.

I am trying to continue if there are records present or open a second form if the recordset is empty.

This code used to work but I have changed something and now I get the error "Object Variable or With Block Variable not set" I don't know what I changed to break the code.

Private Sub TextSupCodeSearch_AfterUpdate()
Dim exist, result1, result2
Me.Requery
TextSupCodeSearch.SetFocus

Me.Recordset.MoveFirst
exist = Me.Recordset.ProductID

If exist = "" Then

result1 = MsgBox("Enter New Product into Database?", 36, "Not Found in Database")

If result1 = 6 Then
DoCmd.OpenForm "NewProdForm"

Else
End If

Else
result2 = MsgBox("Please add or update the order in the Table", 64, "Product already exists in Database")

End If
End Sub

 

[Oldsoftboss]

If exist = "" Then

Try

If IsNull(exist) then

or

If Me!subformname!Recordset.RecordCount < 1 Then


HTH

Dave

 

[SeanDelere]

I should have pointed out that error occurs at the line

Me.Recordset.MoveFirst

I suspect it has something to do with not knowing which Recordset to work with

 

[Oldsoftboss]

If there are no records, then you cant move to the first!

Use error handling to your advantage.

To start, in the error handling line put Msgbox Err.Number & " - " Err.Description

This will then tell you the error No.

Then change the error handling routine to

Select Case Err.Number

Case 123(The Above Error No)
MsgBox "No Record"
Resume Next
Case Else

End Select

 

[adaytay]

An alternative to the suggestion from OldSoftBoss would be to do the following:

Private Sub TextSupCodeSearch_AfterUpdate()
    Dim exist, result1, result2
    
    Me.Requery
    TextSupCodeSearch.SetFocus
    
    On Error Resume Next
    Me.Recordset.MoveFirst
    If CBool(Err.Number) Then
        'No records, so do something here
        Exit Sub
    End If
    exist = Me.Recordset.ProductID
    
    If exist = "" Then
    
        result1 = MsgBox("Enter New Product into Database?", 36, "Not Found in Database")
    
        If result1 = 6 Then
            DoCmd.OpenForm "NewProdForm"
        End If
    
    Else
    result2 = MsgBox("Please add or update the order in the Table", 64, "Product already exists in Database")
    
    End If
End Sub

Then, if an error is triggered (ie no records) then it gets captured by cbool(err.number) and the conditional IF statement runs - I put "Exit Sub" in there just purely as an example.

HTH,

Ad

 

[SeanDelere]

Thank you very much for the two suggestions - I will try them both and get back to you.