Four Report Problems

[znibk]

John,

Am I hopeless? My expense table etc worked so well, I thought I'd grasp the
consept of the Autonumber FieldID. But, here's what happened when I tried to
do the same to one of my banking tables. In the tblLCB, I entered Bank and
Contributors as a Number, leaving them as a Text Box. I went to the design
the query. I put the Field Name LCBank from tblLCB and the BankID from
tblBank; the Field Name LCContributor from tblLCB and the ContID from
tblContributor. I draw a blank all the way across.

I did my qryExp just like that and everything worked great. Question is--did
everything in my qryExp work because all the ckecks were drawn from the same
bank? Or am I supposed to make a separate query for each FieldName from the
data table and the FieldID from the source table and I just got lucky before?
(Although, it should have worked because both the contributions were made to
the same bank)

Please rescue me again. Thanks

 

[znibk]

John,

You wrote, > Base the combo box, not on your Table, but on a sorted Query of
the table.

To do so, select the combo box in form design view and view its Properties.
Click the ... icon by the Row Source property; Access will grumble about it,
but accept the offer to make a query. Specify the sort order in that query,
and you'll see the companies sorted alphabetically.

And I did this for my Expense Form and everything worked perfectly. Now, I'm
trying to do it on my Income Form and when I view my edits in the Form View,
the information on the Combo Boxes is blank. Where, oh Where, have I gone
wrong?

I'll keep trying until I hear from you, maybe I'll luck into finding out
what I'm doing wrong. (Oh yes, I do have the property for "Visible," set to
"Yes.")

 

[John W. Vinson]

John,

Am I hopeless? My expense table etc worked so well, I thought I'd grasp the
consept of the Autonumber FieldID. But, here's what happened when I tried to
do the same to one of my banking tables. In the tblLCB, I entered Bank and
Contributors as a Number, leaving them as a Text Box. I went to the design
the query. I put the Field Name LCBank from tblLCB and the BankID from
tblBank; the Field Name LCContributor from tblLCB and the ContID from
tblContributor. I draw a blank all the way across.

Please open the query in SQL view (View... SQL from design mode) and post the
SQL here. I'm not at all sure what you're doing.

The concept is that: data can be stored in multiple tables. Each table should
store only its own data. The Tables are *for the purpose of storing data* and
that's IT.

It is not necessary that the table *visibly show* data from another table. The
bank's name is not and should not be in tblLCB. The contributor's name is not
and should not be in tblLCB. What should be in tblLCB is a number, a Foreign
Key to the tables which *DO* contain the bank's name or the contributor's
name.

You don't LOOK at the table for routine use of the database; it sounds like
you're still trying to do so. Tables aren't designed or intended for humans to
interact with the data! That's what Forms are for. If you base a Form on
tblLCB, you can put a Combo Box control on that form; the combo's Row Source
(where it gets its data *from*) would be based on the table of Banks (I don't
know what yours is called, but it's the lookup table for banks). The Control
Source of the combo would be the BankID in tblLCB - that's where your
selection is stored. The combo's Column Count and Column Widths properties
would be set so that what the computer sees is the meaningless, numeric
BankID; what the user sees on screen is the human-meaningful bank name. That
leaves both the computer and the user satisfied, because they each see what's
meaningful to them!

If you're creating a Report, you would generally not use a combo box on the
report. Instead you would create a Query joining tblLCB to the Banks table,
joining by BankID; and to the Contributors table, joining by ContributorID.
You can then select the bank name from the Banks table in the query, and the
contributor's name from the Contributors table. You have access to all of the
fields in all of the tables in the query, for the purpose of the report. This
multitable query may not be updateable (so you don't want to use it on a Form
where you want to update data), but it will contain all of the information
that you need for the Report.

I did my qryExp just like that and everything worked great. Question is--did
everything in my qryExp work because all the ckecks were drawn from the same
bank? Or am I supposed to make a separate query for each FieldName from the
data table and the FieldID from the source table and I just got lucky before?
(Although, it should have worked because both the contributions were made to
the same bank)

Since I cannot see qryExp - and I have no idea what you mean by "each
FieldName" in this context - I'm not sure. See if the explanation above makes
sense, and post the SQL view of your query and an example of what is in the
table, what you're seeing, and what you want to see if you still need help.

John W. Vinson [MVP]

 

[znibk]

John,

I've done the tblLCB and tblBank, as I should. In the tblLCB, I see the
FieldID for Bank, FieldID for Contributor, etc. What I was trying to ask is
if in the query where I have put the FieldID for tblLCB, and the FieldName
Bank, can I also have, tblContributor, tblRefDate, etc, all which have the
FiedID in the tblLCB and pull the FieldName from each of those tables into
the query, or should I have individual queries for each individual join? What
I've done is to put the tblLCB Field ID and the FieldName from each of the
identifying tables. Hope that is right.

Don't think I need to open up the SQL view because I THINK I just figured
out what I've been doing wrong ALL morning, more than a dozen times, over and
over again. So, I'm glad you wrote so that I had to write out step by step,
what I'd been doing on my form. I'll try that again. What I was NOT doing is
pulling both the FieldID and the FieldName from the tblBank into the query
when I involked the Query builder on the Row Source. I set everything
correctly but, for some reason, it will not work if I don't have both in
query so that I actually have 2 columns with column 1 being the bound column.
DUUU. No wonder I was pull a blank combo box every time. Nothing was there!

 

[John W. Vinson]

And I did this for my Expense Form and everything worked perfectly. Now, I'm
trying to do it on my Income Form and when I view my edits in the Form View,
the information on the Combo Boxes is blank. Where, oh Where, have I gone
wrong?

Well, tell me where you've gone... <g>

Please post the following properties:

The Form's Recordsource (post the SQL)
The combo's RowSource (ditto)
.... ColumnCount
.... Bound Column
.... Control Source
.... ColumnWidths

John W. Vinson [MVP]

 

[John W. Vinson]

No wonder I was pull a blank combo box every time. Nothing was there!

"Ex nihil nihil veniat" I think is the old saying... <g>

John W. Vinson [MVP]