Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

MS Access VBA run-time error

Posted on 2011-03-14
16
Medium Priority
?
230 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 MVP, Access and Data Platform) 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

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

Cancel future meetings from user mailboxes in Office 365 using Remove-CalendarEvents
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

926 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