Refresh my forms

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Bill_De - 04 Nov 2006 08:50 GMT

Hi I am in the final stages of finishing a database. I have my forms 5 total
set up in a tab control. In three of the forms I have text boxes that I do a
look up from other forms (I use this as a reference tool so I don't have to
keep going back and forth between forms).

What I am trying to do is find out if I can refresh one form in the tab
control while in another form in the same tab control. I know this process
works when in the same form. I have a drop down box that I am make a
selection from and then I have an update query that runs on the click
procedure. Then the refresh code refreshes the text box on the form that
corresponds to the data in the table.

What I did was created a command button to refresh the document and then
moved the code to another procedure and when I select an item everything
updates. Here is the procedure with the code then I will present my question:

Private Sub Combo 59_Click()
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70

Set db=Current Db()
set qdf= db.QueryDefs("Update Region Query")
qdf.Execute dbFailOnError
Me.Combo59.Requery

End Sub.

The DoCmd line refreshes the entire form. My question is: Is there code that
I can add to the DoCmd line that will refresh the other forms in the Tab
Control? If there is can someone tell me where I would enter it and what the
code would be? (write it out for me please). There are actually only two
forms out of the five that I would like to be able to refresh by clicking
just one command.

Thanks for your help

Bill_De

Reply to this Message

Allen Browne - 04 Nov 2006 09:10 GMT

You could force Form2 to update all its calculated controls with:
Forms!Form2.Recalc

If the control you want to update is in a subform, use this kind of thing
Forms!Form2.[MySub].Form.Recalc
substituting the name of your subform control for MySub.

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> Hi I am in the final stages of finishing a database. I have my forms 5
> total
[quoted text clipped - 38 lines]
>
> Bill_De

Reply to this Message

Bill_De - 04 Nov 2006 12:37 GMT

I understand this all except where this code would be entered. Can I enter it
in the procedure "on Click" in place of the DoCmd that I explained in my
origional question or would this have to go someplace else.
I am wondering if I could write the code as follows if this is correct
please let me know:

Private Sub Combo59_Click()

DoCmd, DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
Forms!Form2.[frmRegEvent].Form.Recalc

Set db=Current Db()
Set qdf=db.QueryDefs("Update Region Query")
qdf.Execute dbFailOnError
Me.Combo59.Requery

End Sub

I am thinking that this would update the form I am in with the DoCmd, and
update the 2nd form with the expression you just gave me.

One last thing before I let you go. If I wanted to update a 3rd form like
this would the code be Forms!Form3.[mySub].Form.recalc? and does it matter
what order the forms are in on the tab control.
My first form is Contacts (which I have no updates I want to do yet but may
want to add one)
My second form is fmsRegEvent (which I want to update with information from
the 3rd form.
My third form is fmsRegpay (which I would use the DoCmd to update).

In otherwords in the expression you gave me "Form2" does the number have to
correspond with the forms location?

Thanks for the help

Bill_De

> You could force Form2 to update all its calculated controls with:
> Forms!Form2.Recalc
[quoted text clipped - 45 lines]
> >
> > Bill_De

Reply to this Message

Allen Browne - 04 Nov 2006 13:25 GMT

I don't really understand what your code is meant to achieve, but yes,
presumably it goes into the same Click event, after the lines that cause the
Recalc to become necessary.

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

>I understand this all except where this code would be entered. Can I enter
>it
[quoted text clipped - 96 lines]
>> >
>> > Bill_De

Reply to this Message

Bill_De - 06 Nov 2006 22:14 GMT

I tried entering what you told me and I got a couple of error messages. I
should try to clarify what I have and am trying to do as this may not even be
possible.
I have 3 forms that open in a tab control. The first form is the primary
form "fmsContacts" I don't need anything to update on this form. I have two
sub forms "fmsReg" and "fmsRegpay". In the fms regpay I have a drop down that
allows me to choose the payment status ie. pending, paid, closed etc. I also
have a text box in fmsreg that does a DLOOKUP to get the payment status.
When I close out the forms and then reopen them the staus box with the
dlookup in fmsreg changes and shows the payment staus that I select in form
regpay.

What I am trying to accomplish is when I select a status in fmsregpay it
will update in fmsreg automatically.

I entered your suggestion: Forms!fmsReg.[Text34].Form.Recalc
I got the following error message: Run time error 2465 - Microsoft Access
can't find the Form 'fmsReg' referred to in a Macro Expression or VB Code.

I then change the expression to: Forms!fmsContacts!fmsReg.[Text34].Form.Recalc
I got the following error message: Run Time Error 438 - Object Doesn't
support this property or method.

I know that if I put a command button in a form to refresh the form the code
is as follows: DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5,, acMenuVer70

My thought and please let me know if I am on the right track or not is I am
not doing a recalculation I am trying to do a refresh so would I enter the
above code in place of the "recalc" in your expression? If I am on the right
track would I write the expression- Forms!fmsContacts!fmsReg.DoCmd.DoMenuItem
acFormBar, acRecordsMenu, 5,, acMenuVer70.???
Or is there a different expression I should write or write the above in a
different way.

Thanks for any advice you can give me on this.

Bill_De

> I don't really understand what your code is meant to achieve, but yes,
> presumably it goes into the same Click event, after the lines that cause the
[quoted text clipped - 100 lines]
> >> >
> >> > Bill_De

Reply to this Message

Allen Browne - 07 Nov 2006 09:15 GMT

The ".Form" bit is in the wrong place.
You force a recalculation of the form not the text box.
Try:
Forms!fmsContacts!fmsReg.Form.Recalc

The subform is not open in its own right, so the "Forms!fmsReg..." one would
not work.

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

>I tried entering what you told me and I got a couple of error messages. I
> should try to clarify what I have and am trying to do as this may not even
[quoted text clipped - 162 lines]
>> >> >
>> >> > Bill_De

Reply to this Message

Bill_De - 18 Nov 2006 10:25 GMT

Sorry I haven't gotten back to you been down sick for awhile.
I tried the code you wrote below and it worked just fine. I have now been
able to finish my database.

Just wanted to drop you a quick line to say thanks for all the help.

Bill_De

> The ".Form" bit is in the wrong place.
> You force a recalculation of the form not the text box.
[quoted text clipped - 170 lines]
> >> >> >
> >> >> > Bill_De

Reply to this Message

Allen Browne - 18 Nov 2006 10:51 GMT

Excellent.

Hope you're feeling on top of the world again soon.

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> Sorry I haven't gotten back to you been down sick for awhile.
> I tried the code you wrote below and it worked just fine. I have now been
[quoted text clipped - 3 lines]
>
> Bill_De

Reply to this Message