error 2427 from zero record query on sub form (1 Viewer)

[cochese]

That subject's a mouthful. I'll try my best here...

I have Form A, which has a sub form on it (Form B). Form B has only one textbox which displays the result of a query. Form C is launched from Form A, and some of the information from Form A populates a textboxes on Form C. This is where the problem occurs.

Form B (the sub form with the query result) populates one textbox on Form C. This works as long as the query returns a record - if it does not I get error 2427 when I try to populate the textbox on Form C.

I can't test if the textbox on Form B is null, because doing so causes the error. This all involves a form and not a report so I can't use On NoData event. The only thing I can think to do is check if the query has any records, and if not skip the VBA code that would populate the textbox on Form C from Form B. I can't figure this out though.

I would want to check the query during the load event of Form C. If the query has no records, I still want to continue, I just don't want to try to populate one of the textboxes on Form C (it can be empty).

Thank you.

 

[bwellbor]

You could try using the DLookUp function. If it returns Null, you know there aren't any records.

If IsNull(DLookUp("[FieldName]","QueryName")) Then
'Your Code Here
End If

You could also try some error handling in your On Load event.

On Error GoTo ErrorHandler
'Your Code Here

ExitProcedure:
Exit Sub

ErrorHandler:
If Err.Number = 2427 Then
'Your Code Here
Else
Resume ExitProcedure
End If

 

[vbaInet]

Use IsError() before you call Forms!SubformName!ControlName. If IsError doesn't work, use this -->

http://access.mvps.org/access/forms/frm0022.htm

 

[cochese]

Use IsError() before you call Forms!SubformName!ControlName. If IsError doesn't work, use this -->

http://access.mvps.org/access/forms/frm0022.htm

I couldn't get iserror() to work, but isnumeric() did the job. Thank you.

 

[vbaInet]

You're welcome!