Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 231
  • Last Modified:

MS Access VBA run-time error

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
Blueice13085
Asked:
Blueice13085
  • 8
  • 7
1 Solution
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:


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
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
And what is the error you are getting ??

mx
0
 
Blueice13085Author Commented:
Run-time 3021
no current record
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
"no current record"
Then try what I posted ... you need to test for Zero records before doing the MoveLast ...

mx
0
 
HainKurtSr. System AnalystCommented:
how about adding this on top

on error resume next
0
 
Blueice13085Author Commented:
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
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
"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
 
Blueice13085Author Commented:
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
 
Blueice13085Author Commented:
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
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
ok ... so, does that work for you ?

mx
0
 
Blueice13085Author Commented:
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
 
Blueice13085Author Commented:
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
 
Blueice13085Author Commented:
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
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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
 
Blueice13085Author Commented:
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
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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!

  • 8
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now