Assign Form Values From Table

[Russie]

Hi.

I have a cashbook balance (tbl_CBBalance) table set up like so:

Region CB_Balance
A 10
B 17
C 9
D etc..

I have a form with text box controls on it named so:

txtABalance
txtBBalance
tctCBalance

What I would like to do is to read the values from tbl_CBBalance into the respective text boxes when the form is loaded.

How would be the best way to achieve this? I have set tbl_CBBalances to be the control for my form but I only get 2 field options (Region & £) and not a listing of the regional values.

A star for a working answer. Thanks for anything.

Russie.

 

[OnTheFly]

Two possiblities depending on what you are trying to achieve. You can make the form a continuous form which will then show all the values.

Or you can create a Crosstab query to get the values of Region in the column headings rather than the row headings as they are in the table. (This is assuming, of course, that this is an .MDB and not a .ADP)

 

[Russie]

Thanks for the reply OnTheFly...there is a three table requirement in order to build a cross-tab query and my table only has 2 fields.

I've entered this into a the control Source of the text box but still get a #Name? error.

SELECT [tbl_CashbookBalance]![CashbookBalance]
FROM [tbl_CashbookBalance]
WHERE [tbl_CashbookBalance]![Region]="A";

Any ideas?

 

[Russie]

Thanks for the reply OnTheFly...there is a three field requirement in order to build a cross-tab query and my table only has 2 fields.

I've entered this into a the control Source of the text box but still get a #Name? error.

SELECT [tbl_CashbookBalance]![CashbookBalance]
FROM [tbl_CashbookBalance]
WHERE [tbl_CashbookBalance]![Region]="A";

Any ideas?

 

[OnTheFly]

If you only have the 2 fields, I would go with using the continuous form setting or even Data Sheet view on the form.

Not knowing exactly what you are trying to accomplish, beyond view the data in this particular table, I am afraid I am not being much help.

 

[Russie]

OnTheFly

Your efforts are appreciated and I think that my problem looks simple to solve, until you try and solve it.

My only solution is to open up a recordset of tbl_CBBalance and loop through to assign the values...but even this throws up other problems.

Does anyone else have an answer? How would you assign:

tbl_CBBalance.CashbookBalance Where Region=A to textbox txtACBBalance?