Refresh Main Form after Subform Click

hey guys, I have two forms... one main form and one subform.  I have the following code:

If MsgBox("Are you sure this information is accurate?  You cannot edit changes once they are submitted to the Control Sheet.", vbOKCancel) = vbOK Then
        DoCmd.GoToRecord , , acNext
        Forms!frmControlProd.Requery
        Exit Sub
Else:
    Exit Sub
End If

For some reason, this refreshes the main form just fine, but instead of making the subform go to a new record (as I wanted with DoCmd) it sends it to the first record.  Is there something I can add to make the subform go to a new record as well?
LVL 2
JeffGMACIAsked:
Who is Participating?
 
rockiroadsConnect With a Mentor Commented:
i have to give the form focus


        DoCmd.GoToRecord , , acNext
        Me.Parent.Requery
        Me.Parent.<<nameofsubformcontrol>>.SetFocus
        DoCmd.GoToRecord , , acNewRec


of course, u can use Forms!... instead


0
 
rockiroadsCommented:
try this if u want the subform to go to a new record

DoCmd.GoToRecord , , acNewRec
0
 
rockiroadsCommented:
note, the other way to refresh the parent, without specifying the form name is

Me.PArent.Requery
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
JeffGMACIAuthor Commented:
It still goes to the first record in the table that the subform is linked to if I use that.... i'm wondering if I need to set the focus back to the subform and then go to the new record after the refresh
0
 
JeffGMACIAuthor Commented:
Well, the Parent command worked as well but it still sets the subform's record to the first record and not a new one
0
 
rockiroadsCommented:
the setfocus might do the trick

try setting focus to a field in your subform

somefield.SetFocus

then do the acNewRec
0
 
JeffGMACIAuthor Commented:
well, that set them BOTH to new records!  haha I've done everyone but the one I want :)
0
 
rockiroadsCommented:
LOL!!!
hang on
0
 
JeffGMACIAuthor Commented:
So weird!  If I don't requery it goes to the next record just fine.  
0
 
rockiroadsCommented:
When u go into addmode in the parent, its obviously gone into addmode in the subform as well
0
 
JeffGMACIAuthor Commented:
Ah ha, that did it.  I see what was happening now, thanks for the help!
0
 
JeffGMACIAuthor Commented:
I was getting an error but I had renamed my subform and forgot to update it in the code, thanks for the assistance!
0
 
rockiroadsCommented:
Cool :)


as u can see, I prefer to use Me.Parent or Me.<<nameofsubformcontrol>>

when working in forms vba, its just my style. Especially as I have a habit at times to rename my forms.

Not so keen on using Forms!frmName etc, but u can still use that if u want.
0
 
JeffGMACIAuthor Commented:
Yeah, I like the Me also because I change them or forget that I changed them too.
0
 
JeffGMACIAuthor Commented:
Hey Rockiroads,
I had a weird thing happen with this.  The code we worked on works when the Main form lives in isolation (meaning, I just open it and enter.)

However, when I set a command to open it from ANOTHER form (and filter for a specific record) it gums back up.  The update part works fine, but it won't go to the new record in the subform, even though it will let me manually advance to a new record.  It's so weird, too, becuase when I break down the code into eithe:

Refresh main form
or
Advance to new record in subform

it will work for either one, but not both together!  it still works fine for both when I just open the form standalone and play with it.  Do you see anything I could be missing?

Jeff
0
 
rockiroadsCommented:
perhaps the use of

me.parent.subform

is that what u r using?

if so,try this then

forms!formname etc
0
 
JeffGMACIAuthor Commented:
Yeah, that was the first thing I did... I switched it all to absolute names:

Forms!frmControlProd!sfEntry   etc.  I think this project is doomed haha
0
 
JeffGMACIAuthor Commented:
Well, I figured out a way to trick it, sort of.  Instead of refreshing the whole form, I only wanted to refresh about a dozen text boxes.  So I told the subform, on refresh, to populate the values of the 12 UNBOUND text boxes with a function.   Before, the text box values were bound to a function.  This way the main form doesn't get refreshed all together, but the necessary parts do update when they're supposed to.


Thanks for all your help!
0
All Courses

From novice to tech pro — start learning today.