?
Solved

MS Access VBA run-time error

Posted on 2011-03-14
16
Medium Priority
?
233 Views
Last Modified: 2012-05-11
to start this is being used on a continuous form
when using the code below on a new record where the form is blank  i get a run time error
how ever this works great when there is at least one record

Private Sub START_MILES_GotFocus()
Dim LResponse As Integer
    If Nz(Me![Start Miles], 0) = 0 Then
        LResponse = MsgBox("Are you sure you want to add Start Miles And Start Time =" & "" & Time() & "?", vbYesNo, "Current Date and Time" & Now())
    If LResponse = vbYes Then
        With Me.RecordsetClone
            .MoveLast    ' got the the last record on the Continuous form ...
            Me![Start Miles] = Nz(![End Miles], 0)    ' get the value from the RecordsetClone
        End With
            [START TIME] = Now()
    ElseIf LResponse = vbNo Then
        Me.END_MILES.SetFocus
    End If
    End If

Open in new window


Is there something that i am missing?, can i use this and have it look up a table instead of using the form? same happens for the second code below


Private Sub Form_BeforeInsert(CANCEL As Integer)
Dim LResponse1 As Integer
    If Nz(Me![INV / KEY TAG], 0) = 0 Then
        LResponse1 = MsgBox("Are you sure you want to add Update Invoice / Key tag?", vbYesNo, "Current Date and Time" & Now())
    If LResponse1 = vbYes Then
        With Me.RecordsetClone
            .MoveLast    ' got the the last record on the Continuous form ...
            Me![INV / KEY TAG] = Nz(![INV / KEY TAG], 0) + 1    ' get the value from the RecordsetClone
        End With
    ElseIf LResponse1 = vbNo Then
        Me.END_MILES.SetFocus
    End If
    End If
End Sub

Open in new window

0
Comment
Question by:Blueice13085
  • 8
  • 7
16 Comments
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft Access MVP) earned 2000 total points
ID: 35131287


Private Sub START_MILES_GotFocus()
Dim LResponse As Integer
    If Nz(Me![Start Miles], 0) = 0 Then
        LResponse = MsgBox("Are you sure you want to add Start Miles And Start Time =" & "" & Time() & "?", vbYesNo, "Current Date and Time" & Now())
    If LResponse = vbYes Then
        With Me.RecordsetClone
             If .RecordCount = 0 Then
                    ' No Records ' ***
                    ' Do whatever - Probably Exit HERE
             End If
            ' Only go here if RecordCount > 0
            .MoveLast    ' got the the last record
on the Continuous form ...
            Me![Start Miles] = Nz(![End Miles], 0)    ' get the value from the RecordsetClone
        End With
            [START TIME] = Now()
    ElseIf LResponse = vbNo Then
        Me.END_MILES.SetFocus
       
    End If
    End If
0
 
LVL 75
ID: 35131294
And what is the error you are getting ??

mx
0
 

Author Comment

by:Blueice13085
ID: 35131312
Run-time 3021
no current record
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
LVL 75
ID: 35131364
"no current record"
Then try what I posted ... you need to test for Zero records before doing the MoveLast ...

mx
0
 
LVL 61

Expert Comment

by:HainKurt
ID: 35131827
how about adding this on top

on error resume next
0
 

Author Comment

by:Blueice13085
ID: 35131828
is there a way i can use a input box and type the millage in the input box? in this area u posted

 If .RecordCount = 0 Then
                    ' No Records ' ***
                    ' Do whatever - Probably Exit HERE
             End If 

Open in new window

0
 
LVL 75
ID: 35131897
"on error resume next"
Not really a good idea because that can mask other errors as well.  It's very easy to check for zero records and is what should be done.

"is there a way i can use a input box and type the millage in the input box? in this area u posted"

Not quite following.  What would that do ?

But certainly you could use an Input box in this area if that is what you mean ?

mx

0
 

Author Comment

by:Blueice13085
ID: 35132037
Dim LResponse As Integer
Dim SMiles As String
    If Nz(Me![Start Miles], 0) = 0 Then
        LResponse = MsgBox("Are you sure you want to add Start Miles And Start Time =" & "" & Time() & "?", vbYesNo, "Current Date and Time" & Now())
    If LResponse = vbYes Then
             If .RecordCount = 0 Then
                SMiles = InputBox("Please Emter Start Miles for your 1st call.", "Enter Start Miles", 1)
                 Me.START_MILES = SMiles
              Else
                With Me.RecordsetClone
                    ' Only go here if RecordCount > 0
                    .MoveLast    ' got the the last record on the Continuous form ...
                    Me![Start Miles] = Nz(![End Miles], 0)    ' get the value from the RecordsetClone
                End With
                 [START TIME] = Now()
             End If
           
    ElseIf LResponse = vbNo Then
        Me.END_MILES.SetFocus
       
    End If
    End If
0
 

Author Comment

by:Blueice13085
ID: 35132051
I need there to be a start miles entered or else i will not be able to start this sense the code is ongotfocus and beforeinsert
0
 
LVL 75
ID: 35132075
ok ... so, does that work for you ?

mx
0
 

Author Comment

by:Blueice13085
ID: 35132113
trying to do something like this

Dim LResponse As Integer
Dim SMiles As String
    If Nz(Me![Start Miles], 0) = 0 Then
        LResponse = MsgBox("Are you sure you want to add Start Miles And Start Time =" & "" & Time() & "?", vbYesNo, "Current Date and Time" & Now())
    If LResponse = vbYes Then
             If .RecordCount = 0 Then
                SMiles = InputBox("Please Emter Start Miles for your 1st call.", "Enter Start Miles", 1)
                 Me.START_MILES = SMiles
              ElseIf .RecordCount > 0 Then
                With Me.RecordsetClone
                    ' Only go here if RecordCount > 0
                    .MoveLast    ' got the the last record on the Continuous form ...
                    Me![Start Miles] = Nz(![End Miles], 0)    ' get the value from the RecordsetClone
                End With
                 [START TIME] = Now()
             End If
            
    ElseIf LResponse = vbNo Then
        Me.END_MILES.SetFocus
       
    End If
    End If

Open in new window


but i get a compile error, which means something isnt right somewhere it also hights
If .RecordCount = 0 Then
0
 

Author Comment

by:Blueice13085
ID: 35132127
Changed a few things
Dim LResponse As Integer
Dim SMiles As String
    If Nz(Me![Start Miles], 0) = 0 Then
        LResponse = MsgBox("Are you sure you want to add Start Miles And Start Time =" & "" & Time() & "?", vbYesNo, "Current Date and Time" & Now())
    If LResponse = vbYes Then
             If .RecordCount = 0 Then
                SMiles = InputBox("Please Emter Start Miles for your 1st call.", "Enter Start Miles", 1)
                 Me.START_MILES = SMiles
              Else
                With Me.RecordsetClone
                    ' Only go here if RecordCount > 0
                    .MoveLast    ' got the the last record on the Continuous form ...
                    Me![Start Miles] = Nz(![End Miles], 0)    ' get the value from the RecordsetClone
                End With
                 [START TIME] = Now()
             End If
            
    ElseIf LResponse = vbNo Then
        Me.END_MILES.SetFocus
       
    End If
    End If

Open in new window

0
 

Author Comment

by:Blueice13085
ID: 35132137
still get the  compile error, which means something isnt right somewhere it also hights
If .RecordCount = 0 Then

First time it put in what i typed into the input box then gave me the compile error
0
 
LVL 75
ID: 35132156
You seem to have removed the With / With End construct

If so then change

If .RecordCount = 0 Then

to

IF Me.RecordsetClone.RecordCount = 0
0
 

Author Comment

by:Blueice13085
ID: 35132262
Seems that these two (2) codes are working fine now thank you DatabaseMX

Dim LResponse As Integer
Dim SMiles As String
    If Nz(Me![Start Miles], 0) = 0 Then
        LResponse = MsgBox("Are you sure you want to add Start Miles And Start Time =" & "" & Time() & "?", vbYesNo, "Current Date and Time" & Now())
    If LResponse = vbYes Then
             If Me.RecordsetClone.RecordCount = 0 Then
                SMiles = InputBox("Please Emter Start Miles for your 1st call.", "Enter Start Miles", 1)
                 Me.START_MILES = SMiles
                 [START TIME] = Now()
              Else
                With Me.RecordsetClone
                    ' Only go here if RecordCount > 0
                    .MoveLast    ' got the the last record on the Continuous form ...
                    Me![Start Miles] = Nz(![End Miles], 0)    ' get the value from the RecordsetClone
                End With
                 [START TIME] = Now()
             End If
            
    ElseIf LResponse = vbNo Then
        Me.END_MILES.SetFocus
       
    End If
    End If

Open in new window




Private Sub Form_BeforeInsert(CANCEL As Integer)
Dim LResponse1 As Integer
Dim SInvoice As String
    If Nz(Me![INV / KEY TAG], 0) = 0 Then
        LResponse1 = MsgBox("Are you sure you want to add Update Invoice / Key tag?", vbYesNo, "Current Date and Time" & Now())
    If LResponse1 = vbYes Then
             If Me.RecordsetClone.RecordCount = 0 Then
                SInvoice = InputBox("Please Emter Start Invoice / Key tag for your 1st call.", "Enter Start Invoice #", 0)
                 [INV / KEY TAG] = SInvoice
              Else
                With Me.RecordsetClone
                    ' Only go here if RecordCount > 0
                    .MoveLast    ' got the the last record on the Continuous form ...
                    Me![INV / KEY TAG] = Nz(![INV / KEY TAG], 0) + 1  ' get the value from the RecordsetClone
                End With
                 
             End If
            
    ElseIf LResponse1 = vbNo Then
      Me.END_MILES.SetFocus
       
    End If
    End If
End Sub

Open in new window

0
 
LVL 75
ID: 35132284
ok.  you are welcome ...

mx
0

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Read this tutorial to learn how to fix repeating password error prompts when setting up Gmail IMAP with Microsoft Outlook. The entire process is described with step by step, illustrated instructions. Enjoy...
With the functions here, you can parse, convert, and format back and forth between feet and inches and fractions and decimal inches - for normal as well as extreme values and with extreme precision.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

569 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question