If Me.RecordsetClone.RecordCount = 0 Then Cancel

[nickdawes28]

Hi,

I'm trying to achieve the following-

ButtonA on MainForm to open FormA. When ButtonA is clicked, an inputbox prompts the user for a value and filters the records on FormA based on that value.

I have a Select Case function on the On Open event of FormA, which inspects a field value on FormA and makes a few form design changes based on that value.

Case Is = "Ropes"
Me.lblM.Visible = True
Me.lblLength.Caption = "Length"
Me.cmbEquipmentSubType.RowSource = "Dynamic, Low-Stretch, Hauling"
Me.cmbManufacturer.RowSource = "Mammut, Petzl, Sterling"

This works fine when the user enters a valid input, except I am now trying to introduce some error handling and would like to know how best to handle this situation. If the value the user inputs does not match any record in FormA's recordset, I would expect it to open a blank page and I could handle this using the following:

If Me.RecordsetClone.RecordCount = 0 Then Cancel = True

However I am getting an error from my Select Case function, stating that the expression I am checking does not contain a value. This is expected, as no records are returned therefore the field will not contain a value, however I do not understand why the form processes my Select Case statement and does not cancel as soon as it realises there are no records.

Hope somebody can help!

 

[JHB]

Can't you use EOF instead?

If Me.RecordsetClone.EOF Then Cancel = True

 

[Old Man Devin]

A possible alternative would be just have something like

If Not Me.RecordsetClone.Recordcount = 0 Then
     'STUFF
Else
   'Whatever should happen if nothing was returned
End If

So you'd have that if statement encompassing everything in your on open. Might be worth a go if the Cancel isn't happening and you can't find out why, just to save time.