Pull value from another form

[mpaulbattle]

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]

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]

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

 

[mpaulbattle]

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]

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

 

[mpaulbattle]

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]

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

 

[pbaldy]

As I mentioned:

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

 

[mpaulbattle]

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]

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]

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?

 

[mpaulbattle]

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]

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