Displaying Total from a Different Source to A Form

[osimini1]

i have a table called tblLocation with LocationID as the primary Key and a query called qryLocationTotal with LocationID and TotalMembers.Both the table and the query has LocationID as linkage. I designed a form called frmLocation with tblLocation as the source table. In frmLocation I added an unbound field called Total Member. How can I display the TotalMember value in unbound field of frmLocation once the frmLocationID equal to = that of qryLocationTotal. I have spent one day trying to figure this out. I have done this in the past but can not remember how. Please I will appreciate any help that I can get.

Thanks in advance

 

[kjv1611]

Well, I'd think there are a couple of ways, off the top of my head - my very sleepy, allergy-striken head... [Ah, Spring!]

Oh, the issue at hand....

One way would be to (I think) set your "field" as a combo box or list box with only one item. Then set the row source / data source for that control to a the query you mentioned, and hit the "build" button... and just add in your additional requirement.... set a critieria (build) where it has to equal the value in the form's other field of reference..

Other method would be using VBA... assuming the other field is changed, and right then is when you want the value updated...

The first method, I'm fairly certain will be the easiest.

 

[dhookom]

If you want to use a combo box, set these properties:
[tt]
Control Source: LocationID
Row Source: SELECT LocationID, TotalMembers FROM qryLocationTotal
Bound Column: 1
Column Count: 2
Column Widths: 0,0.5
Locked: Yes
Enabled: No
[/tt]

Duane
Hook'D on Access
MS Access MVP

 

[osimini1]

I am still not able to get the TotalMembers number display in the form. I followed the steps that dhookhom suggested yet stll no show. Any suggestion or is there any other way to have this accomplished - How can I display the TotalMember from the query in unbound field of frmLocation once the frmLocationID equals to that of qryLocationTotal.

I will highly appreciate any suggetions or direction on how to accomplish this.

thanks

 

[dhookom]

What does display in the combo box?

Duane
Hook'D on Access
MS Access MVP

 

[osimini1]

Thanks for all the help. I used combo box as suggested. Combo box displays nothing on the form, but if clicked drop down button you will see the numbers.

 

[dhookom]

Did you set the control source to the locationID field?

Duane
Hook'D on Access
MS Access MVP

 

[osimini1]

Yes I did. It was set to the query locationID just as below:

Control Source: LocationID
Row Source: SELECT LocationID, TotalMembers FROM qryLocationTotal
Bound Column: 1
Column Count: 2
Column Widths: 0,0.5
Locked: Yes
Enabled: No

 

[dhookom]

If it was locked and disabled, how could you "if clicked drop down button you will see the numbers"?

What do you see in a text box bound to LocationID?

Duane
Hook'D on Access
MS Access MVP