Solved Understanding Events code not responding (1 Viewer)

slharman1

Member
Local time
Today, 17:43
Joined
Mar 8, 2021
Messages
456
I have this code in an after update event for a combo box on a form.
But when I put the same code in the load event of the form the txtJobName and txtDescription text boxes do not populate
When the form loads it goes to the last record to see if two table fields are populated before doing anything else. If they are both populated, this code doesn't run, if neither have data, this code doesn't run - but if one field has data and the other does not, then this same code runs in an else if statement.

Code:
Private Sub cboShopItemID_AfterUpdate()
    
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim shopitem As String
    Dim order
    Dim SQL As String
    shopitem = Me.cboShopItemID
    Set db = CurrentDb
    
    
    SQL = "SELECT [description] FROM qryWorkOrdersShop where WOItems = '" + Me.cboShopItemID + "'"
    Set rst = db.OpenRecordset(SQL)
    
    
    order = Left(shopitem, 5)
    Me.txtJobName.Value = DLookup("JobName", "tblOrders", "OrderNumber =" & order)
    rst.MoveFirst
    Me.txtDescription.Value = rst!Description
    rst.Close
    Set rst = Nothing
                
End Sub
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 06:43
Joined
May 7, 2009
Messages
16,393
is cboShopItemID, Bound to a Field?
can you not move it to the Current event of the form?
Load event only fires Once, while current event, when you
move to each records.
 

slharman1

Member
Local time
Today, 17:43
Joined
Mar 8, 2021
Messages
456
I have this code in an after update event for a combo box on a form.
But when I put the same code in the load event of the form the txtJobName and txtDescription text boxes do not populate
When the form loads it goes to the last record to see if two table fields are populated before doing anything else. If they are both populated, this code doesn't run, if neither have data, this code doesn't run - but if one field has data and the other does not, then this same code runs in an else if statement.
Ok so maybe this will help you guys steer me in the right direction.
I wrote the private sub below
When I type updateJobDesc in the cboShopItemID_AfterUpdate procedure in my form it works as it should
When I type the same thing in the Form_Load procedure in a Else If statement it doesn't work.
What am I missing?

Code:
Private Sub updateJobDesc()
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim shopitem As String
    Dim order
    Dim SQL As String
    shopitem = Me.cboShopItemID
    Set db = CurrentDb
    
    
    SQL = "SELECT [description] FROM qryWorkOrdersShop where WOItems = '" + Me.cboShopItemID + "'"
    Set rst = db.OpenRecordset(SQL)
    
    
    order = Left(shopitem, 5)
    Me.txtJobName.Value = DLookup("JobName", "tblOrders", "OrderNumber =" & order)
    rst.MoveFirst
    Me.txtDescription.Value = rst!Description
    rst.Close
    Set rst = Nothing
End Sub
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 17:43
Joined
Feb 28, 2001
Messages
22,778
Code in the OnLoad event runs (and finishes) before bound fields are filled in (which is the OnCurrent event). Are the fields that give you trouble both bound fields?
 

slharman1

Member
Local time
Today, 17:43
Joined
Mar 8, 2021
Messages
456
is cboShopItemID, Bound to a Field?
can you not move it to the Current event of the form?
Load event only fires Once, while current event, when you
move to each records.
See #3
Yes cboShopItemID is bound to a field.
The form is designed no to page through records, the user an can either complete the unfinish data field on the form or create a new record.
The form is to capture a time stamp for starting and stopping work on a particular job.
so if they are not moving through records would the current event still be a valid choice?
Thanks
 

slharman1

Member
Local time
Today, 17:43
Joined
Mar 8, 2021
Messages
456
Code in the OnLoad event runs (and finishes) before bound fields are filled in (which is the OnCurrent event). Are the fields that give you trouble both bound fields?
The fields I am trying to populate ar enot bound but the code looks for data in a bound field to populate the unbound fields. the fields are basically there to provide the user more info about the record they are working with.
Starting to sound like the on current event is where it might need to go.
Thanks
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 06:43
Joined
May 7, 2009
Messages
16,393
you better google Sequence of Events on Access Form
to fully understand which events fire when you open the form.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 17:43
Joined
Feb 28, 2001
Messages
22,778
The fields I am trying to populate ar enot bound but the code looks for data in a bound field to populate the unbound fields. the fields are basically there to provide the user more info about the record they are working with.
Starting to sound like the on current event is where it might need to go.
Thanks

I would concur that OnCurrent is more likely to help you. Even though the fields in question are NOT bound, you say they depend on bound fields. If so, you cannot guarantee that the fields are visible to you (or to code) until the OnCurrent event. The OnLoad event is probably too soon.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 18:43
Joined
Feb 19, 2002
Messages
36,284
The on Current event runs once for each record. As you move focus from one record to another, the Current event runs for the new record when it gets the focus. The Load event runs once and only once when a form is loaded so it is never appropriate for thys type of functionality.

You might still need code in the AfterUpdate event of the combo to force the code to run otherwise you won't see the effects until you leave the record and come back to it.
 

slharman1

Member
Local time
Today, 17:43
Joined
Mar 8, 2021
Messages
456
I will either go to the last record on the subform or add new record to the subform from the load event of the main form.
Thanks All, I will get to work on this and report beck later.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 23:43
Joined
Sep 12, 2006
Messages
14,835
^^^^
AS Pat says, the current event is the one. The open, and load events fire once each, when the form opens and gets populated. There is also a similar activate event. All events have different uses, but it sounds like you need the current event.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 18:43
Joined
Feb 19, 2002
Messages
36,284
I don't understand your fixation on the Load event. The load event of the form runs ONCE to load the RecordSource. What you want to do is to run code to move focus in the subform to a new record. That needs to be done for each record in the main form, not just once. Otherwise, the action of the form will be inconsistent since the action would happen when the form opens but not when the user scrolls to a new record.
 

slharman1

Member
Local time
Today, 17:43
Joined
Mar 8, 2021
Messages
456
I don't understand your fixation on the Load event. The load event of the form runs ONCE to load the RecordSource. What you want to do is to run code to move focus in the subform to a new record. That needs to be done for each record in the main form, not just once. Otherwise, the action of the form will be inconsistent since the action would happen when the form opens but not when the user scrolls to a new record.
Once again Pat, you have taught me well, once ArnelGP sent me to to learn about sequence of form events (which I did) and I remembered your good advice on my last post about telling you guys what the objective is and not try to speak your language (technical words I don’t understand) I scrapped my code and started over.
Now code is simplified and done in short order.
Once again, You are correct, my code is in the current event on the sub form, triggered every time a new record is loaded.
Thanks goes out to all of you guys willing to help people like me.
 

slharman1

Member
Local time
Today, 17:43
Joined
Mar 8, 2021
Messages
456
What you want to do is to run code to move focus in the subform to a new record.

What you want to do is to run code to move focus in the subform to a new record. That needs to be done for each record in the main form, not just once. Otherwise, the action of the form will be inconsistent since the action would happen when the form opens but not when the user scrolls to a new record.
I can't seem to get the subform to a new record from the main forms current event, please guide me.
I think I am setting the focus to the sub, could it be setting some properties to enabled=no that is causing it?
Please just look at the second part of the If statement (first ElseIf), I will clean up the first and third parts once I can get past this.

Code:
Private Sub Form_Current()

        If Me!SfrmTimePunch!TimeIn = 0 And Forms!frmTimeClockEmployee!SfrmTimePunch!TimeOut = 0 Then
            Forms!SfrmTimePunch.TimeIn.Enabled = True
            Forms!SfrmTimePunch.TimeOut.Enabled = False
            Forms!SfrmTimePunch.cboShopItemID.Enabled = True
            Forms!SfrmTimePunch.cboShopItemID.SetFocus
            Forms!SfrmTimePunch.cboShopItemID = Null
            'DoCmd.GoToRecord , , acNewRec
            Exit Sub
        ElseIf Me!SfrmTimePunch!TimeIn > 0 And Forms!frmTimeClockEmployee!SfrmTimePunch!TimeOut > 0 Then
            Me!SfrmTimePunch.Form!TimeIn.Enabled = True
            Me!SfrmTimePunch.Form!TimeOut.Enabled = False
            Me!SfrmTimePunch.Form!cboShopItemID.Enabled = True
            Me!SfrmTimePunch.SetFocus
            DoCmd.GoToRecord , , acNewRec
            Forms!frmTimeClockEmployee!SfrmTimePunch.Form!cboShopItemID.SetFocus
            Exit Sub
        ElseIf Forms!frmTimeClockEmployee!SfrmTimePunch!TimeIn > 0 And Forms!frmTimeClockEmployee!SfrmTimePunch!TimeOut = 0 Then
            Forms!SfrmTimePunch.TimeIn.Enabled = False
            Forms!SfrmTimePunch.TimeOut.Enabled = True
            Forms!SfrmTimePunch.cboShopItemID.Enabled = False
            Forms!SfrmTimePunch.cmdClockOut.SetFocus
            DoCmd.GoToRecord , , acNewRec
        Exit Sub
        End If

End Sub
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 17:43
Joined
Feb 28, 2001
Messages
22,778
To be unambiguous, let's consider these shortcuts.

If you are running code in the main form and want to adjust something on the main form, you refer to it as Me.something

If you are running code in the main form and want to adjust something in a sub form, you had to have put a SubForm control on the main form and you had to name it. Let's say you used MySFC for the control used to hold the sub form. Then, to adjust something on the sub form from code running in the main form you use Me.MySFC.Form.something and doing it that way is unambiguous.

Your DoCmd.GoToRecord has omitted a critical component. As shown in the link I provided, you need to tell DoCmd the name of the object if it is not the "Me" object. I.e. if you want to affect the sub form, you must specify the sub form. By default, DoCmd will operation on the current object, which in this case is Me, not the sub forum, if you are running this from the main form trying to affect the sub form.

 

slharman1

Member
Local time
Today, 17:43
Joined
Mar 8, 2021
Messages
456
To be unambiguous, let's consider these shortcuts.

If you are running code in the main form and want to adjust something on the main form, you refer to it as Me.something

If you are running code in the main form and want to adjust something in a sub form, you had to have put a SubForm control on the main form and you had to name it. Let's say you used MySFC for the control used to hold the sub form. Then, to adjust something on the sub form from code running in the main form you use Me.MySFC.Form.something and doing it that way is unambiguous.

Your DoCmd.GoToRecord has omitted a critical component. As shown in the link I provided, you need to tell DoCmd the name of the object if it is not the "Me" object. I.e. if you want to affect the sub form, you must specify the sub form. By default, DoCmd will operation on the current object, which in this case is Me, not the sub forum, if you are running this from the main form trying to affect the sub form.

I read the link several times and couldn’t get the code to add new record. I ended up setting the data entry property to yes and that worked. Will this be ok and to standards?
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 17:43
Joined
Feb 28, 2001
Messages
22,778
Oh, please don't misunderstand. We talk about standards, but everyone probably has a slightly different viewpoint on what is "standard" for their shop. If you get it to work reliably AND it remains simple, then until it breaks somehow, it will be "OK and within standards." There are purists among us who will object, but the truth is simple... if it works, doesn't confuse you, and doesn't break anything else, use it.

By the way... if that behavior is what you want but not all of the time, it is OK to set or clear DataEntry as needed. I.e. it can be dynamic.
 

slharman1

Member
Local time
Today, 17:43
Joined
Mar 8, 2021
Messages
456
Oh, please don't misunderstand. We talk about standards, but everyone probably has a slightly different viewpoint on what is "standard" for their shop. If you get it to work reliably AND it remains simple, then until it breaks somehow, it will be "OK and within standards." There are purists among us who will object, but the truth is simple... if it works, doesn't confuse you, and doesn't break anything else, use it.

By the way... if that behavior is what you want but not all of the time, it is OK to set or clear DataEntry as needed. I.e. it can be dynamic.
Thanks for the input, I am continuing to learn more and more as I progress.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 23:43
Joined
Sep 12, 2006
Messages
14,835
Setting DataEntry to true shouldn't make a difference unless there is some issue with your code.

What dataentry=true does is set up the form to just capture new records, and not show existing records.
dataentry=false shows existing records, but still allows you to add new ones.

So there should be no difference.
 

Users who are viewing this thread

Top Bottom