Error in module

[Dangerous]

Hi, I'm using code to go to the next record and warn that i'm on the last record so it doesn't proceed to a new record.

The code I'm using works if I put it in the 'OnClick' event but if I try to use the same code in a module it fails.

The code is.

Private Sub Next_Click()

With Recordset
If .AbsolutePosition = .RecordCount - 1 Then
'you are on the last record
MsgBox "Sorry, this is the last Record.", vbInformation
Else
'you are on some other record
DoCmd.GoToRecord , , acNext
End If
End With

End Sub

I am using a 'OnClick' event to call it of

Private Sub Next_Click()

navigate.GotoNext() 'navigate (module name) . gotonext (sub name)

It causes a run-time error 424 object required. On Debug the error is highlighted in the line 'If .AbsolutePosition = .RecordCount - 1 Then'

Why does it work as an event proceedure and not from a module and how do I fix it?

 

[arnelgp]

you must create a public function in a module and must add a
form parameter to it:

public function fnNextRecord(frm As Form)
With frm.RecordSet
     If Not .Eof Then 
          .MoveNext

          If .Eof Then 
               .MovePrevious
               MsgBox "Sorry This is the last record.
          End If

     Else
          MsgBox "Sorry This is the last record.
     End If
End With
End Sub

 

[Dangerous]

Thanks for your reply Arnelgp


I've tried that code but can't test it as I'm just a beginner and not sure what you mean by a form parameter, exactly where to put the function or how to call it from an 'OnClick' event assigned to a button.

could you explain to me how to do those things.

 

[Dangerous]

Everything I tried failed. I must be doing it all wrong.

 

[bob fitz]

I know it's only a typo but the the function needs to end with End Function rather than End Sub

 

[bob fitz]

Thanks for your reply Arnelgp


I've tried that code but can't test it as I'm just a beginner and not sure what you mean by a form parameter, exactly where to put the function or how to call it from an 'OnClick' event assigned to a button.

could you explain to me how to do those things.

Delete all the code that you have between the lines:

Private Sub Next_Click()
and
End Sub

and replace with:

fnNextRecord Me

Copy the code that arnelgp gave you but change the last line of his code which was:
End Sub
with
End Function

 

[The_Doc_Man]

And it's a minor point, but for future reference: When you copy code and want to highlight it, don't choose Yellow. Pick red or brown or purple or something that doesn't require us to have eyestrain to read it. AND I am NOT chastising. Just suggesting.

 

[Dangerous]

Bob Fitz That worked perfect. The function was spot on (I had already changed the end sub with end function) as soon as it was invoked correctly.

I am trying to find out what the ME stands for/does. Can you explain (simply lol)?

I am learning slowly hence why I am now playing with MODULES, previously I have the same code assigned to several buttons on one form (with different additional code) and then all of the subs assigned to buttons on a few more forms so things were all messy and hard to read.
They are now down to a few simpler blocks with a neater 'OnClick' or whatever call so a whole lot easier for me to understand but I still don't understand the ME part.

My database works the way I want it to but as it builds I am finding ways to tidy it up, improve it or expand it's capabilities.

Thanks for your help and also thanks to ArnelGP for the function.

 

[Dangerous]

And it's a minor point, but for future reference: When you copy code and want to highlight it, don't choose Yellow. Pick red or brown or purple or something that doesn't require us to have eyestrain to read it. AND I am NOT chastising. Just suggesting.

No problem The_Doc_Man, I used yellow as that was the highlight colour used by Access Debug and I was trying to stick with their method. I normally use red so next time ..........................

 

[bob fitz]

I am trying to find out what the ME stands for/does. Can you explain (simply lol)?

First of all, I'm no Access guru or teacher of VBA, so take nothing I say as gospel truth.
My understanding is that the Me tells the function where to look for the Recordset

Your original code could/should have been written with Me preceding the fullstops that you have in
.AbsolutePosition = .RecordCount
e.g.
Me.AbsolutePosition = Me.RecordCount

 

[isladogs]

If you were referring to fnNextRecord Me, the Me refers to the current form.
If you read arnel's function you should see why that's the case

 

[The_Doc_Man]

Technically speaking, Me does NOT exactly refer to the current form. It more precisely refers to the form associated with the form's Class Module where the Me reference occurred. In other words, to the form associated with the currently executing routine IF that routine is in a form's Class Module.

There is a small but very important point here: When you use Me in a routine in a General Module, even if you called that routine from a form's Class Module, Me in the General Module is treated as a de-referenced object i.e. it points to Nothing (if it exists at all, which it might not.)

Note, however, that if you passed a form reference to a subroutine called from the form's Class Module, you CAN use Me as the ACTUAL argument for a form reference. To avoid confusion, it is not considered wise to use Me as the FORMAL argument in the subroutine's definition.

Note also that Me works as I described in the class module of a report. Again, it references the report

There are a few other special words in various Office utilities. For example, ActiveWorkbook and ActiveSheet have special meanings in Excel VBA. ActiveDocument has special meaning in Word VBA. Me is just another example of that concept for Access. Somehow those lucky dogs with the Access team got the short keyword for their shortcut.

 

[isladogs]

Doc
Whilst I agree with what you have written, my comment was in the context of arnel's function used on a button click within a form.
In that case, Me does refer to the current form as I stated.

Similarly I use automatic form resizing code in almost all my apps.
Each form includes the following line in its Form_Load event: ResizeForm Me

 

[Dangerous]

As I said the function Arnelgp worked perfectly with the correct function call provided by Bob Fitz.

Having just spent the last few hours writing & re-writing all the 'On Click' events and tidying up the work adding comments & indents in both the modules etc. everything is working as it should and very few of the sections of code are repeated across the forms (calling code excluded).

I would like to thank everyone for their input.

 

[Dangerous]

My next question is, in my database I've taken various steps to prevent accidentally adding a new record.

Up to now if I did I added the record giving the 1st field the description of blank and then edited later when a new record was entered.

This is because it's better for me to maintain the Auto-Number Key sequence whereas Access doesn't allow you to reuse the key field number of the deleted record.

This also means that I've had to disable DELETE and use a Clear Record button which clears all fields except the photo attachment field, It assigns 'blank' to the description field.

The code I'v uses a list of commands - [Description].Value = "" - repeated for every field which makes for a long messy looking block of code.

Is there a way to make it neater AND clear the photo attachment filed (most records have 2 photos and a few have 3).

TIA

 

[Solo712]

As I said the function Arnelgp worked perfectly with the correct function call provided by Bob Fitz.

Having just spent the last few hours writing & re-writing all the 'On Click' events and tidying up the work adding comments & indents in both the modules etc. everything is working as it should and very few of the sections of code are repeated across the forms (calling code excluded).

I would like to thank everyone for their input.

Hi, you might consider little advanced organization in your programming. The code that arnold sent has a great potential: it can be used as a general utility library and called by any program that needs this function. The advantage of putting this type of code into a general class module which you can copy from one program to another. Then, when you have button to rifle through records all you have to do is write

fnNextRecord Me

or

fnPreviousRecord Me

into the button click event. You will never have to worry about how to navigate through a record set. Here is the full set of navigation functions:

Public Sub fnFirstRecord(frm As Access.Form)
   If frm.NewRecord Or frm.Dirty Then Exit Sub
   frm.Recordset.MoveFirst
End Sub
'---------------------------------------
Public Sub fnLastRecord(frm As Access.Form)
    If frm.NewRecord Or frm.Dirty Then Exit Sub
    '
    frm.Recordset.MoveLast
End Sub
'-----------------------------------------
Public Sub fnNextRecordd(frm As Access.Form)
    If frm.NewRecord Or frm.Dirty Then Exit Sub
    
    With frm.Recordset
         If Not .EOF Then
              .MoveNext
              If .EOF Then
                  .MovePrevious
             End If
         End If
    End With
End Sub
'----------------------------------------------------
Public Sub fnPreviousRecord(frm As Access.Form)
   If frm.NewRecord Or frm.Dirty Then Exit Sub
   With frm.Recordset
         If Not .BOF Then
              .MovePrevious
              If .BOF Then
                  .MoveNext
             End If
         End If
    End With
End Sub
'------------------------------------

In case you are wondering, I disable the buttons while writing a new record or when the current record is being edited. You will find those restrictions useful.
Have fun with it!

Best,
Jiri

 

[Dangerous]

I'm just getting used to writing code in modules, not looked in to class modules yet.

I'm starting to figure out some of the code but the syntax is way beyond me at the moment. I can do limited stuff but at first I couldn't get even the simplest one line command to work as I had no idea where to put it.

Looking at you code I understand most of what it does and guess what other bits mean/do. I guess frm means form so frm.NewRecord is when entering data in to a new record but frm.Dirty, is that when an existing record is being edited?

To be honest I'm a bit past the age of paying for a training course in Access VBA or VB etc. I don't have the time or need to do that. This is the first Access Database I've attempted and may well be the last.

I have done several in Lotus Approach and I did do quite a few in a dos based database program. With that I set up one for a firm that used it for several years (program cost £50 at the time) till he needed/wanted to incorporate barcode scanner etc. The £13,000 cost of a professional 'tailor made' package could do all that but the main feature he needed the rep asked me how I was able to do it as they couldn't.

All that knowledge is pretty much useless as it doesn't translate to VBA. With VBA I can't even find a list of commends (or whatever they are called) so stringing them together to do anything is near impossible for me.

From what you have said, I get the impression that a general module stays in the database but a class module stays within Access, is that right?

 

[Pat Hartman]

While I don't want to discourage you from learning new stuff, you might for now, consider letting Access be Access and do the things that it does such as scrolling and saving records and you concentrate on doing the things that Access cannot do for you such as to implement business rules. Access will do everything that needs to be done to manage displaying and updating and moving through a set of records. It is up to you to provide validation code in the Form's BeforeUpdate event so you can ensure that only valid data gets saved. That is absolutely critical. Are you doing that? Because if you are not, you are wasting your time futzing with scrolling and should concentrate on data validation instead. Once the app is structured to save only valid data, you can move your focus to bending Access to your will if you don't like the automatic methods. I've been using Access a long time and while Access is pretty flexible, it does not necessarily work as I would prefer. Rather than attempt to bend it to my will, I accept the fact that I am using a RAD tool for a reason and I should let the tool work as it wants to. If I want to have complete control over everything and write 10 times the amount of code I currently write, I can use some other platform and do everything from scratch myself.

 

[Dangerous]

Pat Hartman.

I have been building this database for a while now and where data must be entered in a valid format I have used the built in Access rules to do that, input masks etc. For the majority of fields the data can be just about anything so no validation is needed for it.

It's up and running with 62 genuine records and 9 false/blank records (entered by accident before preventing the navigation buttons going past the last record). These records are being added slowly at the moment but I expect then to increase a lot quicker before long.

The main things the database will be used for is to filter & scroll through the results. FilterByForm is an ideal way to do it as the search criteria can be anywhere from 1 to 20 data fields with absolutely no set pattern of what fields or what data are to be used.

As I have said, this is my first time with Access and I have produced a database that does what I originally wanted as a base level. I'm now seeing things can be done better and I have realised the limitations of my initial requirements and have found that in some instances I need VBA to add the new functionality I now want.

What I am building is for personal use (although others may use it) and may be the only one I build hence why I don't want full a full and possibly costly training course but I'm prepared to put in the time to learn a limited amount so I can do the best I can with it.

 

[The_Doc_Man]

And Colin (re post 13), your example showed a case of using Me as the actual argument of a subroutine as I suggested, so we are on the same page.

It's a fine point that I felt needed clarifying. Me is NOT always the current form if it is not used in the context of the code in that form's Class Module. Which gives me cause to believe it might be a compile-time assignment as opposed to a run-time assignment. Which would explain why Me has no meaning in a general module. The other possibility is that it is derived from the Class Module's .Parent (the form) and that parent only exists for the Class Module, not the General Module.