Refreshing a Sub form (1 Viewer)

Umpire

Member
Local time
Today, 15:34
Joined
Mar 24, 2020
Messages
120
I had a form with a Sub Form. When I entered a product Serial number on the main form, the Subform would update to show any prior entries for that serial number. No programing was needed. It worked great.

Then I added a second Subform for Windows License information. I added a routine to the AfterUpdate event for my serial number field that would ask for Windows license information only for certain part numbers. If the part didn't use a Windows license the Subform was passed by. That worked great.

But I lost the update to the original history SubForm. It no longer will update the history like it used to.

I believe it is because of my AfterUpdate event code.

While searching the forum, I found this bit of code:
With Me![SubFrmHistory] 'put in my subForm name
.Form.Requery
End With

I added this code to my after event code. I put it after the End If and before the End Sub. But I get an error that says Access can not find the field.

Where do I need to put this code (or other code) so that my History Sub Form updates like it used to?
 

Gasman

Enthusiastic Amateur
Local time
Today, 22:34
Joined
Sep 21, 2011
Messages
14,048
Not your subform name, but the subformcontrolname.
 

Umpire

Member
Local time
Today, 15:34
Joined
Mar 24, 2020
Messages
120
Not your subform name, but the subformcontrolname.
So I should put "SubForm-History1"?
I still get confused on Controls and their names. And when to refer to a field by its Name as called in the table and when to use its name on a form.
 

Attachments

  • Capture6-9-21.PNG
    Capture6-9-21.PNG
    9.5 KB · Views: 389

Gasman

Enthusiastic Amateur
Local time
Today, 22:34
Joined
Sep 21, 2011
Messages
14,048
Think about it logically.
You are using .form.requery
That form is a property of the subformcontrol
Now the wizard makes the name of the control the same as the form.
I always put ctl in front of that to identify which is which.
So make sure you use the subformcontrol name where you have subfrmhistory
 

Umpire

Member
Local time
Today, 15:34
Joined
Mar 24, 2020
Messages
120
Think about it logically.
You are using .form.requery
That form is a property of the subformcontrol
Now the wizard makes the name of the control the same as the form.
I always put ctl in front of that to identify which is which.
So make sure you use the subformcontrol name where you have subfrmhistory
Thanks for your help,
It works. I think. If I start with a blank form it works as expected. If I go back and change the S/N, lets say I noticed I have a typo in the number, It does not update the second time. Not sure why that is or if it is a problem or not. For the moment I can live with it.

I like the idea of adding ctl to control names. Still a little fuzzy on when to use one or the other. Time to dig back into my books.
(This self taught programing is hard.)
 

Gasman

Enthusiastic Amateur
Local time
Today, 22:34
Joined
Sep 21, 2011
Messages
14,048
No, I only do that for a subcontrol. For other controls I would use txt,cbo,cmd etc.

The experts advise to keep the field and control names different, using prefixes like above.
 

Gasman

Enthusiastic Amateur
Local time
Today, 22:34
Joined
Sep 21, 2011
Messages
14,048
No, I only do that for a subcontrol. For other controls I would use txt,cbo,cmd etc

Thanks for your help,
It works. I think. If I start with a blank form it works as expected. If I go back and change the S/N, lets say I noticed I have a typo in the number, It does not update the second time. Not sure why that is or if it is a problem or not. For the moment I can live with it.

I like the idea of adding ctl to control names. Still a little fuzzy on when to use one or the other. Time to dig back into my books.
(This self taught programing is hard.)
Show al the event code within code tags
 

Umpire

Member
Local time
Today, 15:34
Joined
Mar 24, 2020
Messages
120
Code:
Option Compare Database
Option Explicit

Private Sub Serial_Number_AfterUpdate()

With Me![SubFrm-History1]
  .Form.Requery
End With

If DCount("*", "tblLicense", "LicenseNeeded='" & Me.PN & "'") > 0 Then 'Part Number might have a Windows license
    If MsgBox("Does This part have a Windows License?", vbYesNo + vbQuestion, "Windows License Check") = vbYes Then 'Ask if it has a Windows License
    DoCmd.OpenForm "frmWindowsLicenseInfo" ' if it does have a Windows license, open the form to record it
    Else
    End If ' No license do nothing
Else
    'does not need a license
End If ' Do nothing


End Sub
 

Gasman

Enthusiastic Amateur
Local time
Today, 22:34
Joined
Sep 21, 2011
Messages
14,048
I would have expected that to work each time?
No need for those elses by the way?
 

Umpire

Member
Local time
Today, 15:34
Joined
Mar 24, 2020
Messages
120
Being new to VBA, I was not sure if I needed them or not. And it helped me follow my options as I wrote the code.

I will try again tomorrow with a fresh reboot on my PC. It does strange things sometimes.
Thanks for helping.
 

Users who are viewing this thread

Top Bottom