access vba question 1

[danomaniac]

Ok, here’s one.

In an access 2000 database, I have a report that is based on a query. Within the report, I transfer the value of one of the query fields to a variable via VBA. This works fine so long as the query returns at least one record. If no records match the criteria, then when I reference the field in VBA, I get a run-time error 2427: “You entered an expression that has no value.”

any ideas?

Thanks

 

[gcole]

After the export, test the value. If it is null, assign it something (0 or "&quot.

 

[KyleS]

You've got two choices, you could create a recordset and check to make sure it's not empty:

Dim rst as DAO.Recordset

set rst = currentdb.OpenRecordset("QUERYNAME&quot

if rst.recordcount = 0 Then
DO whatever you want here
End If

rst.close
set rst = nothing


Or you can use error handling:


On Error Goto Error_Handler

YOUR CODE HERE

Exit_Sub_Open:
Exit Sub

Error_Handler:
If Err.Number = 2427 then
Exit Sub
Else
Err.Description
Resume Exit_Sub_Open

End Sub Kyle :

 

[danomaniac]

Kyle,
Thanks Very Much! I ended up using the error handling. I trapped the error and assigned a value of 0 to the variable. Worked perfect. Thanks again.

Dan

 

[KyleS]

No problem, glad I could help! Kyle :