Error Handling (1 Viewer)

Juett · Nov 4, 2019

Hi folks,

I have an button function:

Code:

Private Sub Command266_Click()
Me.V1_SN = fnGetV1SN()

Which when clicked, triggers this code:

Code:

Public Function fnGetV1SN()
With CurrentDb.QueryDefs("Qry_AutoPop")
    .Parameters(0) = Me![Bath SN]
    fnGetV1SN = .OpenRecordset()(4)
End With
End Function

It works perfectly - It runs a query based on the value just entered in 'Bath SN', takes a value from the query result and populates the V1_SN field on the actvie form.

The query is based on a previous record that has the equal value of 'Bath SN'.

This is all fine, but on some occasions, there will not be a matching value in a previous record for 'Bath SN'. When this happens, I get the VBA error 3021 - No current record.

Is there a way to skip this particular error for this piece of code? I can't seem to stop the error from firing and opening VBA etc.

Uncle Gizmo · Nov 4, 2019

My guess is that ".OpenRecordset()(4)" occasionally returns a NULL value. If that's the case, then try:- Nz(.OpenRecordset()(4))

Micron · Nov 4, 2019

You must be getting the error on a line that you haven't shown? I can't see why you'd get that error unless you tried to do something against the recordset such as MoveNext. If that's the case, test for no records (.BOF and .EOF) are True. Trapping the error is somewhat of a hack when (if) you can prevent it in the first place.

theDBguy · Nov 4, 2019

Hi. This looks like it can be done using DLookup(), somehow. Just a thought...

Micron · Nov 4, 2019

I agree that it could be simpler. For one, I'd ditch the function. However, I think there's too much missing to draw such a conclusion. OP could be looping through a recordset - we just don't know what happens after those 2 initial lines.

Juett · Nov 5, 2019

Using Nz(.OpenRecordset()(4)) does not work.

The error is triggered on:

Code:

 fnGetV1SN = .OpenRecordset()(4)

The issue seems to be when the Query runs and there is no results to show, because there is not yet a previous matching record to get data from. But the code expects there to be, and it throws the error.

Gasman · Nov 5, 2019

So test for the error and leave the function.?, or test for BOF/EOF as one would normally do with a recordset.

Juett · Nov 5, 2019

Thanks for the advice, but I'm afraid I have no idea how to do either of those things. How can i work that into the function?

Gasman · Nov 5, 2019

Rather than reinvent the wheel, go with theDBguys suggestion and just use a Dlookup?

https://support.office.com/en-gb/article/dlookup-function-8896cb03-e31f-45d1-86db-bed10dca5937

or for the error handling

https://docs.microsoft.com/en-us/do...guage-reference/statements/on-error-statement

or (this might not pe perfect)

Code:

Public Function fnGetV1SN()
Dim rst As DAO.Recordset
Dim db As DAO.Database

Set db = CurrentDb()
Set rst = db.OpenRecordset("Qry_AutoPop")
If Not rst.EOF Then
    fnGetV1SN = rst!Fields(4)
Else
    fnGetVISN = 0
End If
rst.Close
db.Close
End Function

HTH

Juett · Nov 5, 2019

Thanks very much for your help - in the end both of your suggestions worked - the code you supplied, and also simply skipping the error (On Error Resume Next).

Micron · Nov 5, 2019

I had this sitting on a NotePad document so might as well make use of it.

.OpenRecordset()(4)
I believe the 4 refers to the recordset type, not the field position. Since the default is being used, it's not even necessary and probably doesn't help much unless you remember what all the numeric values represent. Also, it isn't enough to test for EOF alone.
As I said, why use a function to return a recordset to your sub instead of just opening the recordset in the sub?
I'd be inclined to do like (unsure because I don't usually modify query def parameters. Also would include an error handler)

Code:

Private Sub SomeUsefulNameHere_Click()
Dim rs As DAO Recordset
Dim db As CurrentDb

Set db = CurrentDb
'could have If block here to test if [Bath SN] is Null...

db.QueryDefs("Qry_AutoPop").Parameters(0) = Me![Bath SN]
Set rs = db.OpenRecordset("Qry_AutoPop")

'no idea what you have next, so maybe...
If Not (rs.BOF And rs.EOF) Then
' do stuff
End If

'clean up
On Error Resume Next
rs.Close
Set rs = Nothing
Set db = Nothing

End Sub

Gasman · Nov 5, 2019

Oops, I took it to be a flashy way of getting a particular field?

theDBguy · Nov 5, 2019

Gasman said:
Oops, I took it to be a flashy way of getting a particular field?

Same here...

Gasman · Nov 5, 2019

No, I just tested it. It will return the 5th field in the recordset, but I made a mistake as the fields start at 0 ?

Code:

Public Function fnGetV1SN()
With CurrentDb.QueryDefs("QryTransactions")
    .Parameters(0) = 15
    fnGetV1SN = .OpenRecordset()(4)
End With
End Function

? fngetV1SN()
Mr N.L.Davies:139567

Micron said:
I had this sitting on a NotePad document so might as well make use of it.

.OpenRecordset()(4)
I believe the 4 refers to the recordset type, not the field position. Since the default is being used, it's not even necessary and probably doesn't help much unless you remember what all the numeric values represent. Also, it isn't enough to test for EOF alone.
As I said, why use a function to return a recordset to your sub instead of just opening the recordset in the sub?
I'd be inclined to do like (unsure because I don't usually modify query def parameters. Also would include an error handler)

Micron · Nov 5, 2019

I can stop learning stuff for today.

4 happens to be the numeric 'constant' for the default recordset type. The () threw me as I've never used that syntax.

theDBguy · Nov 5, 2019

Gasman said:
No, I just tested it. It will return the 5th field in the recordset, but I made a istake as the fields start at 0 ?

Code:

Public Function fnGetV1SN() With CurrentDb.QueryDefs("QryTransactions") .Parameters(0) = 15 fnGetV1SN = .OpenRecordset()(4) End With End Function

? fngetV1SN()
Mr N.L.Davies:139567

Well, I still think DLookup() could have done the job, but it's always good to have options. Cheers!

Micron · Nov 5, 2019

FWIW I would do the lookup as well - assuming the recordset isn't being used for anything else, but we don't know- or do we?

theDBguy · Nov 5, 2019

Micron said:
FWIW I would do the lookup as well - assuming the recordset isn't being used for anything else, but we don't know- or do we?

Maybe, but the original code looks like this:

Juett said:
Code:

Private Sub Command266_Click() Me.V1_SN = fnGetV1SN()

Which when clicked, triggers this code:

Code:

Public Function fnGetV1SN() With CurrentDb.QueryDefs("Qry_AutoPop") .Parameters(0) = Me![Bath SN] fnGetV1SN = .OpenRecordset()(4) End With End Function

Which tells me the user clicks a button and a Textbox called V1_SN gets assigned a value looked up from a table or query with a criteria to match the current [Bath SN] value. I didn't see any other purpose for the recordset. In fact, I don't even see a recordset object, just a querydef object. I could be wrong, but that's what it looks like to me.

Micron · Nov 5, 2019

My point is, what comes after this:

Private Sub Command266_Click()

Me.V1_SN = fnGetV1SN()
?
?
End Sub

theDBguy · Nov 5, 2019

Micron said:
My point is, what comes after this:

Private Sub Command266_Click()

Me.V1_SN = fnGetV1SN()
?
?
End Sub

Not sure it matters because the function fnGetVISN() returns a String (probably a Variant actually), not a recordset. Am I missing something? Are you thinking of something else?

Error Handling (1 Viewer)

Registered User.

Nifty Access Guy

AWF VIP

I’m here to help

AWF VIP

Registered User.

Enthusiastic Amateur

Registered User.

Enthusiastic Amateur

Registered User.

AWF VIP

Enthusiastic Amateur

I’m here to help

Enthusiastic Amateur

AWF VIP

I’m here to help

AWF VIP

I’m here to help

AWF VIP

I’m here to help

Similar threads

Users who are viewing this thread