On Current event (1 Viewer)

kirkm · May 25, 2019

I have the following

Code:

   Do
            Set rst = Form_BBsubform.Form.RecordsetClone
            rst.FindFirst "Prefix = '" & targetPrefix & "'"
            If Not rst.NoMatch Then 'move to the next record
                Form_BBsubform.Form.Bookmark = rst.Bookmark
            End If

    DoStuff

            rst.MoveNext
            targetPrefix = rst!Prefix
    Loop Until rst!Year <> CStr(lstYear)

When this first runs the On Current event in Form_BBsubform is not executed.
Or rather doesn't excute until it has moved to the next (and subsequant) record(s),
I presume it's because I'm already on the first record.

How could I get around this so the On Current event runs the first time ?

Micron · May 25, 2019

Move last then first? Got to admit I would not expect OnCurrent to ever run because of moving through a recordset.

kirkm · May 25, 2019

I used a boolean and called the On Current event for the first loop. It works but may not be the best solution.

gemma-the-husky · May 25, 2019

when you open a from, you get open and load events, and then you get the current event for each record you navigate to.

However. Is you form bound? you may not get a current event firing at all for an unbound form.

if you have subforms then the main form won't fire a current event for things going on within the subform, and the order of events firing within the entire set of forms may also need to be considered.

It's not clear what you are doing, so it's hard to advise.

theDBguy · May 25, 2019

The Form's Current event fires after the form is loaded. Where are you executing this code and what is it supposed to do and what is in the Current event that you want to happen?

kirkm · May 26, 2019

Hi, I'll try and explain what I'm doing bearing in mind I'm an amateur here and most of what I do is trial and error, mixed with some good stuff I pick up!
The table is actually an Excel spreadsheet. I get a lot of criticism of this table (spreadsheet) layout so I better mention that first. Access is the maintenance tool and used instead of Excel to allow queries, and Save changes immediately (rather than a large Excel workbook every so often).
Checks on various fields confirm that record is okay. (DoStuff in Msg #1)
It starts from a button on the main Form, which has a datasheet view subform (bound to a query on the main table), .
This passes the first serial number 'targetprefix' and the recordset clone goes though each record. I can watch progress as the records move up on the screen.
Then I found this didn't run the wanted subform Form_Current until the next serial. Probably being already at the first one it wouldn't fire its Current event.
So how to make it do that was my question. What I ended up with was

Code:

Set rst = Form_BBsubform.Form.RecordsetClone
            rst.FindFirst "Prefix = '" & targetPrefix & "'"
            If Not rst.NoMatch Then 'move to this record
                Form_BBsubform.Form.Bookmark = rst.Bookmark
                If FirstRun = False Then FirstRun = True: Form_BBsubform.Form_Current
            End If

That seems to work whether good practice or not, so I may be all okay, but would improve it if necessary. Thanks for the interest.

Uncle Gizmo · May 26, 2019

The On Current event is NOT public. So how can you call a private subroutine from outside the Form? --- Besides, it's definitely not good practice to call the form events directly. Whether it's actually dangerous/damaging is debatable, however it's just not considered good practice. And really the main point is any code that you have in this On Current event can easily be placed in a separate function or subroutine and called from there, so it's immaterial whether it's good practice or not, it's just so easy to do it the right way!

kirkm · May 26, 2019

The On_Current event was used because this is a 'batch' operation, repeatedly calling the same thing as when a new record is selected. It worked for me so seemed 'right enough'. I've lost you a bit on being Public or Private. I'm all for doing it better... but any new function would still be called from Current event, wouldn't it? Hmmm thinking more that's what happens anyway as it calls various other functions.
Or maybe you mean don't use the Form at all ? It is convenient and shows something happening....

theDBguy · May 26, 2019

Hi. Can you also post the code you have in the Current event? Thanks.

kirkm · May 26, 2019

Yes, but I don't think this is what you want. If you want to see more it is a heck of a lot...

Code:

Sub Form_Current()

    If NoCurrent = True Then
        NoCurrent = False
        ResetDisplay
        Exit Sub
    End If

    If IsNull(Me.Prefix) = True Then Exit Sub

    ' Don't do anything when clicking a temporary entry
    If InStr(Me.Prefix, "_n") > 0 Then Exit Sub

    DisplayResources (Me.Prefix)
    ResetDisplay

    Dim thisRecord As New Record
    thisRecord.PopulateFromPrefix (Me.Prefix)

    Call Validate(thisRecord)
    Set MyCurrentEntry = thisRecord

End Sub

isladogs · May 26, 2019

Agree with previous moments about the use of form current.
If it does run, it will run each time there is a change.
Do you really need to run all that code repeatedly?

In an earlier post where you described the setting up of a recordset loop where you 'do stuff, you said you can watch progress whilst it loops through the records one by one. That implies it is slow. If your 'stuff' involves updating records, an update query would be simpler and much faster.

kirkm · May 26, 2019

I don't think is something normally done in Access. Yes it needs to repeatedly if checking a batch, or just once if a record is clicked in the subform.

It does a lot on things an update query might struggle with, and teh output is a text field I go through manually. Things like adding or removing spaces, fixing typos etc.

It a great aid and you know everything is spot-on when no errors

isladogs · May 26, 2019

kirkm said:
I don't think is something normally done in Access. Yes it needs to repeatedly if checking a batch, or just once if a record is clicked in the subform.

It does a lot on things an update query might struggle with, and teh output is a text field I go through manually. Things like adding or removing spaces, fixing typos etc.

It a great aid and you know everything is spot-on when no errors

What doesn't run normally in Access?

What does the recordset loop do that one or more update queries would struggle with? Recordsets are slow as each record is processed in turn. Queries run 'all at once'.
Either way, why run all the code repeatedly on form current?

You could also automate removing spaces using the replace function.

kirkm · May 26, 2019

Sorry. I meant to put 'don't think this is done normally...'
There's about 100 fields per records and data might have been typed in, copied in or from anywhere really, and it has to comply with certain rules. (They're not important here.) It may be possible to so some of this with queries...but confirmation would still be needed. There's 12 labels on my Form, all green, as each field/record is checked they stay green or turn red. So one click and I know that record is ok, or not. The batch thing allows a Range and any 'red' conditions are written to text file, or sometimes a msgbox.
This may be all garbage to anyone else but it does serve a worthwhile purpose here. Quite a few things I've picked up here have helped a lot.
The whole thing is about 49000 records but I never do all at once, although I could (over a few hours). Really the time taken isn't a biggie, only the results. (And it was fun creating it).

isladogs · May 26, 2019

Even though you had fun cresting it, I still think it could done be more efficiently.
If it were me, I would create a procedure to include one or more update sql statements (or queries) together with any other processing as necessary.
At the end the procedure would run a select query that would check for all records where one or more conditions are not met. These would be displayed as a datasheet or continuous form. Conditional traffic light formatting could then be used to highlight fields needing corrections in those records. You could either do the corrections on that form (assuming it is editable) or double click a record to navigate to that record for editing.
As I said it will be much faster even if that's not a primary concern for you.

But of course its your database and you are free to ignore such advice

kirkm · May 27, 2019

Yes, I've no doubt someone with more knowledge could make it more efficient. Were I starting again I might try your suggestions, but as it basically works now anything would be a big change. And not easy.. I did ask here about an update query but didn't get anything I could use. The data is best seen to decide what needs doing so any code would need to be pretty comprehensive. I wouldn't ignore any advice although some things like "Don't call Form events" are puzzling as what are they there for ?

Micron · May 27, 2019

things like "Don't call Form events" are puzzling as what are they there for?

They exist so that you can code something to happen when a form is in a specific state like current, load, open, active etc. You shouldn't be calling them from elsewhere as a rule, simply because it's unlikely that the form will be or should be in that state, plus it could be an event that normally occurs in conjunction with other events. You could also find that as a result, you lose variable assignments. Calling form control events or UDF's from elsewhere would be less problematic.

kirkm · May 27, 2019

Fair enough. I used Current Event because my line with Bookmark went there. This moves the Form to that record, yes ? I had existing code there already as it was somewhere I knew it'd execute from. (Observed by stepping)
So you're saying let Bookmark fire the On Current, but have no code there, and move what is there to a click event ?

Micron · May 27, 2019

I would have to review a lot to answer those questions specifically and to be honest, it's getting late here and the cast I have on my hand is getting real heavy while typing. However, I thought I had a decent answer for your last question, which is why I jumped back in. Hopefully others think I was at least close to being accurate.

In any event, if there is code that you would want to execute more than once, the best approach is to put it in a procedure that is visible to the form and call it from a form event such as Load. Then if you need to execute the repeatable part, call that procedure - not the form event.

kirkm · May 27, 2019

Sorry about the cast... on thinking I could change my batch operation so it doesn't Use Form_Current but where would I pick up the single event from, if not Form_Current?

On Current event (1 Viewer)

Registered User.

AWF VIP

Registered User.

Super Moderator

I’m here to help

Registered User.

Nifty Access Guy

Registered User.

I’m here to help

Registered User.

MVP / VIP

Registered User.

MVP / VIP

Registered User.

MVP / VIP

Registered User.

AWF VIP

Registered User.

AWF VIP

Registered User.

Similar threads

Users who are viewing this thread