Pull value from another form (1 Viewer)

mpaulbattle · Dec 1, 2017

I am trying to pull the the control source value from one form to another. According to my research it should be as easy as

=[Forms]![frmProvCountPerPractice]![NumOfProv]

Unfortunately it gives me the value of #Name on the form view.

Any idea why?

I found this on a google search Microsoft Office Support

Under the subject: Values in other controls

Ranman256 · Dec 1, 2017

That is all you do, but I dont think it will work in a text box.
it works in vb code.
it works in queries (without the = sign)

make sure your control has the correct name, NumOfProv
(you can test it in immediate window, ctl-G using a ? instead of =)
? [Forms]![frmProvCountPerPractice]![NumOfProv]

pbaldy · Dec 1, 2017

Is that form open? It has to be, though it can be hidden. That should work in a textbox.

mpaulbattle · Dec 1, 2017

pbaldy said:
Is that form open? It has to be, though it can be hidden. That should work in a textbox.

what do you mean by open?

pbaldy · Dec 1, 2017

Either double clicked on from the navigation pane or opened with OpenForm. Or being the form opened by default when the app opens.

mpaulbattle · Dec 1, 2017

pbaldy...no. the only form open is the one where i entered the expression. Should I add an unbound text box? the one i had in there belonged to another field that i removed from the table.

mpaulbattle · Dec 1, 2017

Well I tried adding the unbound and reentering the expression. Same result

Pat Hartman · Dec 1, 2017

@mpaulbattle,
Forms don't store data. Tables store data. While you can reach out and pick up a value from a control in an OPEN form, the key is that the form you are referencing MUST BE OPEN. Otherwise, it is just a form object unconnected to any data. The form is connected to data when it opens and the Form's Load event runs.

If it doesn't make sense that the other form will always be open, then you need to use some other method to get the data you need. A DLookup() might do what you need.

pbaldy · Dec 1, 2017

As I mentioned:

pbaldy said:
Is that form open? It has to be, though it can be hidden.

mpaulbattle · Dec 1, 2017

pbaldy...Now i understand, "it has to be". I was reading it as the reason it was not working. I will look for a different way to get the information

mpaulbattle · Dec 5, 2017

pbaldy said:
Is that form open? It has to be, though it can be hidden. That should work in a textbox.

Ok I created a subform, sfPracProvCnt, and added it to the footer of frmPracticeList.

2. changed the visible property of the subform to No.

3.Changed the control source for the Number Provider field on the frmPracticeList to: =[sfPracProvCnt].[Form]![NumOfProv]

When I open the frmPracticeList the field for NumProv shows the number 1 all the way down. If I click in the field for the records where numbers should be the correct number will show. If i click in the field on a record where I haven't completed adding active providers I get #Error (which I understand why).

Does anybody have an idea of why the number 1 shows for all the records upon the form loading? Should I add something to the onload event?

pbaldy · Dec 5, 2017

A reference to a textbox on a continuous/datasheet form will return the value from whatever record has focus, or the first record if focus hasn't been set elsewhere. What exactly are you trying to accomplish?

Pat Hartman · Dec 5, 2017

Adding a subform to a form and hiding it is an awful lot of trouble to go through to avoid using a DLookup(). Forms do NOT store data. Tables store data. Get the data from the table/query.

mpaulbattle · Dec 5, 2017

The overall objective is to count the number of active providers from tblProviders so the number is automatically adjusted and shown on the form Practice List which is based on tblPractices. I have the two tables linked by a third table tblProvPrac so we can associate each provider with their practice. Unfortunately when I create a query to include the expression that gives me the number of provider per practice, I am not able to update on the form.

pbaldy · Dec 5, 2017

Sounds like a DCount() or DSum() might be appropriate, or DLookup() if the data is already counted somewhere. Here's help on the syntax:

http://www.theaccessweb.com/general/gen0018.htm

Pull value from another form (1 Viewer)

mpaulbattle

Registered User.

Ranman256

Well-known member

pbaldy

Wino Moderator

mpaulbattle

Registered User.

pbaldy

Wino Moderator

mpaulbattle

Registered User.

mpaulbattle

Registered User.

Pat Hartman

Super Moderator

pbaldy

Wino Moderator

mpaulbattle

Registered User.

mpaulbattle

Registered User.

Attachments

pbaldy

Wino Moderator

Pat Hartman

Super Moderator

mpaulbattle

Registered User.

pbaldy

Wino Moderator

Similar threads

Users who are viewing this thread