Solved

MS Access VBA run-time error

Posted on 2011-03-14
16
227 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
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with 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 56

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

Industry Leaders: 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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
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…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

617 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