Solved

MS Access VBA run-time error

Posted on 2011-03-14
16
226 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 7
16 Comments
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 500 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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 
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 53

Expert Comment

by:Huseyin KAHRAMAN
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

734 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