Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 11-04-2019, 07:27 AM   #1
Juett
Newly Registered User
 
Join Date: Jul 2019
Posts: 27
Thanks: 2
Thanked 0 Times in 0 Posts
Juett is on a distinguished road
Error Handling

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.

Juett is offline   Reply With Quote
Old 11-04-2019, 07:36 AM   #2
Uncle Gizmo
Nifty Access Guy
 
Uncle Gizmo's Avatar
 
Join Date: Jul 2003
Location: Newbury Berks UK
Posts: 10,376
Thanks: 556
Thanked 949 Times in 898 Posts
Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough
Send a message via Skype™ to Uncle Gizmo
Re: Error Handling

My guess is that ".OpenRecordset()(4)" occasionally returns a NULL value. If that's the case, then try:- Nz(.OpenRecordset()(4))
__________________
Code:
                 |||||
               @(~‘^‘~)@
-------------oOo---U---oOo-------------
|                                     |
|      Uncle Gizmo              |
|                                     |
|                                     |
| Get $20 worth of "Nifty Code"       |
|      
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
| | Ooo | |_________________ooO____( )________| ( ) ) / \ ( (_/ \_)
Uncle Gizmo is offline   Reply With Quote
Old 11-04-2019, 08:17 AM   #3
Micron
AWF VIP
 
Join Date: Oct 2018
Location: Ontario, Canada
Posts: 1,247
Thanks: 10
Thanked 232 Times in 220 Posts
Micron has a spectacular aura about Micron has a spectacular aura about
Re: Error Handling

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.

__________________
Sometimes I just roll my eyes out loud...
Windows 10; Office 365 (Access 2016)
Micron is offline   Reply With Quote
Old 11-04-2019, 08:20 AM   #4
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,653
Thanks: 58
Thanked 1,460 Times in 1,441 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Error Handling

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

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 11-04-2019, 08:30 AM   #5
Micron
AWF VIP
 
Join Date: Oct 2018
Location: Ontario, Canada
Posts: 1,247
Thanks: 10
Thanked 232 Times in 220 Posts
Micron has a spectacular aura about Micron has a spectacular aura about
Re: Error Handling

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.
Micron is offline   Reply With Quote
Old 11-05-2019, 01:16 AM   #6
Juett
Newly Registered User
 
Join Date: Jul 2019
Posts: 27
Thanks: 2
Thanked 0 Times in 0 Posts
Juett is on a distinguished road
Re: Error Handling

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.
Juett is offline   Reply With Quote
Old 11-05-2019, 01:40 AM   #7
Gasman
Enthusiastic Amateur
 
Gasman's Avatar
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 4,563
Thanks: 442
Thanked 847 Times in 818 Posts
Gasman is a jewel in the rough Gasman is a jewel in the rough Gasman is a jewel in the rough
Re: Error Handling

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

__________________
Access novice. Sometimes trying to give something back.
Access 2007

Please, please use code tag # when posting code snippets

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is offline   Reply With Quote
Old 11-05-2019, 02:08 AM   #8
Juett
Newly Registered User
 
Join Date: Jul 2019
Posts: 27
Thanks: 2
Thanked 0 Times in 0 Posts
Juett is on a distinguished road
Re: Error Handling

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?
Juett is offline   Reply With Quote
Old 11-05-2019, 02:24 AM   #9
Gasman
Enthusiastic Amateur
 
Gasman's Avatar
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 4,563
Thanks: 442
Thanked 847 Times in 818 Posts
Gasman is a jewel in the rough Gasman is a jewel in the rough Gasman is a jewel in the rough
Re: Error Handling

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

https://support.office.com/en-gb/art...b-bed10dca5937

or for the error handling

https://docs.microsoft.com/en-us/dot...rror-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
__________________
Access novice. Sometimes trying to give something back.
Access 2007

Please, please use code tag # when posting code snippets

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by Gasman; 11-05-2019 at 02:32 AM.
Gasman is offline   Reply With Quote
The Following User Says Thank You to Gasman For This Useful Post:
Juett (11-05-2019)
Old 11-05-2019, 03:53 AM   #10
Juett
Newly Registered User
 
Join Date: Jul 2019
Posts: 27
Thanks: 2
Thanked 0 Times in 0 Posts
Juett is on a distinguished road
Re: Error Handling

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).
Juett is offline   Reply With Quote
Old 11-05-2019, 07:28 AM   #11
Micron
AWF VIP
 
Join Date: Oct 2018
Location: Ontario, Canada
Posts: 1,247
Thanks: 10
Thanked 232 Times in 220 Posts
Micron has a spectacular aura about Micron has a spectacular aura about
Re: Error Handling

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
__________________
Sometimes I just roll my eyes out loud...
Windows 10; Office 365 (Access 2016)
Micron is offline   Reply With Quote
Old 11-05-2019, 08:35 AM   #12
Gasman
Enthusiastic Amateur
 
Gasman's Avatar
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 4,563
Thanks: 442
Thanked 847 Times in 818 Posts
Gasman is a jewel in the rough Gasman is a jewel in the rough Gasman is a jewel in the rough
Re: Error Handling

Oops, I took it to be a flashy way of getting a particular field?
__________________
Access novice. Sometimes trying to give something back.
Access 2007

Please, please use code tag # when posting code snippets

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is offline   Reply With Quote
Old 11-05-2019, 08:50 AM   #13
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,653
Thanks: 58
Thanked 1,460 Times in 1,441 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Error Handling

Quote:
Originally Posted by Gasman View Post
Oops, I took it to be a flashy way of getting a particular field?
Same here...
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 11-05-2019, 09:25 AM   #14
Gasman
Enthusiastic Amateur
 
Gasman's Avatar
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 4,563
Thanks: 442
Thanked 847 Times in 818 Posts
Gasman is a jewel in the rough Gasman is a jewel in the rough Gasman is a jewel in the rough
Re: Error Handling

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


Quote:
Originally Posted by Micron View Post
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)
__________________
Access novice. Sometimes trying to give something back.
Access 2007

Please, please use code tag # when posting code snippets

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by Gasman; 11-05-2019 at 09:45 AM.
Gasman is offline   Reply With Quote
Old 11-05-2019, 09:40 AM   #15
Micron
AWF VIP
 
Join Date: Oct 2018
Location: Ontario, Canada
Posts: 1,247
Thanks: 10
Thanked 232 Times in 220 Posts
Micron has a spectacular aura about Micron has a spectacular aura about
Re: Error Handling

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.

Micron is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Run time error 3101 - Error handling code djossh Modules & VBA 14 04-10-2012 05:52 AM
Custom Error-Handling (Run-Time Error '490') gnarpeggio Modules & VBA 3 02-09-2011 04:20 PM
How to refer to a run time error number in error handling catbeasy Modules & VBA 6 07-21-2009 07:55 AM
Error Handling: Ignore Access Error Myriad_Rocker Modules & VBA 2 05-30-2007 06:45 AM
Break on error even tho error handling is turned on DataMiner Modules & VBA 3 12-29-2005 11:53 AM




All times are GMT -8. The time now is 03:27 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World