Four Report Problems

[znibk]

Four Report Problems: (So you know my Control Source for the Report, it is a
Query.)
1)Sometimes, the FieldID AutoNumber, ie,â€45,†shows in the Report rather
than the entered information, ie, “Client.†I pull the Field Name from the
Field List into the Report. Incidentally, when I run the Query, “Clientâ€
shows, not “45.†If I change the text box to a combo box, sometimes the
problem is eliminated, sometimes not. But, using a Combo Box leads to another
problem:

2) I want to use the Field Name, ie [Whoes] in the “Whoes Footerâ€. Inside
the Text Box =â€Summary of “&[Whoes] & " (" & Count(*) & " " &
IIf(Count(*)=1,"detail record","detail records") & ")"
However, I cannot use the Combo Box (needed to view data input) inside the
Text Box.

3) I have “0.00†appearing throughout the report. The Default Value is set
to “0,†with the Standard Format and 2 decimal places. I tried the following
two expressions in the field text box and get an Invalid Control Source and
Circular Reference message when I try to get out of the text box. I tried
=IIF(Is Null([Client Check Amount]),†“,[Client Check Amount]) and when that
did not work, the comment you’d made earlier about the Null field, I put
=IIF(Nz(Client Check Amount],0),†“,[Client Check Amount])

4) The “Description Footer†and the “Whoes Footer†are appearing in the
Report above the Headers at the top of the page! (This is a new problem) I’ve
compared previous reports, the properties, etc. and can’t seem to find the
problem.

Incidentally, I don’t write code and have limited knowledge of expressions
or simple calculations.

I really hope someone has a lot of patience and can help me out. As in the
past, I’ll be extremely grateful.

 

[Marshall Barton]

Four Report Problems: (So you know my Control Source for the Report, it is a
Query.)
1)Sometimes, the FieldID AutoNumber, ie,”45,” shows in the Report rather
than the entered information, ie, “Client.” I pull the Field Name from the
Field List into the Report. Incidentally, when I run the Query, “Client”
shows, not “45.” If I change the text box to a combo box, sometimes the
problem is eliminated, sometimes not. But, using a Combo Box leads to another
problem:

2) I want to use the Field Name, ie [Whoes] in the “Whoes Footer”. Inside
the Text Box =”Summary of “&[Whoes] & " (" & Count(*) & " " &
IIf(Count(*)=1,"detail record","detail records") & ")"
However, I cannot use the Combo Box (needed to view data input) inside the
Text Box.

3) I have “0.00” appearing throughout the report. The Default Value is set
to “0,” with the Standard Format and 2 decimal places. I tried the following
two expressions in the field text box and get an Invalid Control Source and
Circular Reference message when I try to get out of the text box. I tried
=IIF(Is Null([Client Check Amount]),” “,[Client Check Amount]) and when that
did not work, the comment you’d made earlier about the Null field, I put
=IIF(Nz(Client Check Amount],0),” “,[Client Check Amount])

4) The “Description Footer” and the “Whoes Footer” are appearing in the
Report above the Headers at the top of the page! (This is a new problem) I’ve
compared previous reports, the properties, etc. and can’t seem to find the
problem.

1) That's why experienced folks really hate those lookup
fields in a table (i.e. what you see is not what you get).

Change the table field from a lookup to a text box so you
can see what's really there. Then modify the report's
record source query to join the table to the clients table
and get the client name from there.

2) Try changing the name of the text box that's getting the
circular reference to something else such as
txtClientCheckAmt.

3) Don't use an expression to format a value. Just set the
text box's Format property to a custom format. If you want
nonzero numbers to have 2 decimal places and nothing for
zero amounts. then the format property could be like:
0.00;;""
Lookup Fprmat Property in VBA Help for details and links to
related options.

4) If that's what you are relly getting, it would be very
strange. The GROUP header/footer sections should appear at
appropriate points throughout the report.

The only thing that appears before the first page header
section is the report header section. Did you inadvertantly
put that sutff in the wrong section?

 

[tina]

please don't multi-post. for more information see
http://home.att.net/~california.db/tips.html#aTip10.
i also noticed that you posted these questions over 5 hours *after* you
already got answers to the first 3 of them from a separate post in the
..reports ng, from MVP Duane Hookom. please check previous threads before
posting questions again.

 

[znibk]

Tina,

Believe me, I did not want to multi-post. I could not remember how I posted
the original question or on which forum. I spent a long time looking for my
original post, then the 2nd one did not show up, so I thought, okay, I'd try
a third time. These are my first attemps to post questions on-line. I am
really sorry for any inconvenience these multi-post caused you or anyone else.

Incidently, I discovered that if I write my question in Word and then copy
and paste it here, it works better because I was taking so long to write the
question or reply that the website was kicking me off. When I would click
"Refresh" everything disappeared so I did not know if it was posted or not.
Again, sorry.

And, do you know how do I get the "Notify me of replies" to work? I've
filled out all that information and waited patiently for something to show in
my "In Box," and when it did not, tried the "notify Microsoft . . ."

Thanks for your help Tina, I'll try the websites you listed in your reply.

please don't multi-post. for more information see
http://home.att.net/~california.db/tips.html#aTip10.
i also noticed that you posted these questions over 5 hours *after* you
already got answers to the first 3 of them from a separate post in the
..reports ng, from MVP Duane Hookom. please check previous threads before
posting questions again.

Four Report Problems: (So you know my Control Source for the Report, it is a
Query.)
1)Sometimes, the FieldID AutoNumber, ie,"45," shows in the Report rather
than the entered information, ie, "Client." I pull the Field Name from the
Field List into the Report. Incidentally, when I run the Query, "Client"
shows, not "45." If I change the text box to a combo box, sometimes the
problem is eliminated, sometimes not. But, using a Combo Box leads to another
problem:

2) I want to use the Field Name, ie [Whoes] in the "Whoes Footer". Inside
the Text Box ="Summary of "&[Whoes] & " (" & Count(*) & " " &
IIf(Count(*)=1,"detail record","detail records") & ")"
However, I cannot use the Combo Box (needed to view data input) inside the
Text Box.

3) I have "0.00" appearing throughout the report. The Default Value is set
to "0," with the Standard Format and 2 decimal places. I tried the following
two expressions in the field text box and get an Invalid Control Source and
Circular Reference message when I try to get out of the text box. I tried
=IIF(Is Null([Client Check Amount])," ",[Client Check Amount]) and when that
did not work, the comment you'd made earlier about the Null field, I put
=IIF(Nz(Client Check Amount],0)," ",[Client Check Amount])

4) The "Description Footer" and the "Whoes Footer" are appearing in the
Report above the Headers at the top of the page! (This is a new problem) I 've
compared previous reports, the properties, etc. and can't seem to find the
problem.

Incidentally, I don't write code and have limited knowledge of expressions
or simple calculations.

I really hope someone has a lot of patience and can help me out. As in the
past, I'll be extremely grateful.

 

[znibk]

Hey Marshall,

Thank you for the reply.

The 0.00;;"" worked perfectly to solve my Problem 3.

My Problem 4 really was happening, but when I double checked my
ControlSource, I discovered that it was set to the table not the query. When
I changed to the query like I thought I had, everything worked perfectly.

I'm still working to solve Problems 1 by changing the field type to "Text,"
rather than using the Lookup Wizad. I have done that and have gotten a
variety of results.
Field1, I just typed the names of the banks in the Lookup tab. I chose
combo box, value list, and typed “ABC.†The “ABC†shows in the table
datasheet when the drop down arrow is clicked, however, it will not appear on
the datasheet when chosen.
Fields2, 3 and 4 the FieldID, ie “45†shows rather than “Client†on the
datasheet. When the down arrow is clicked, the FieldID will appear on the
datasheet, an improvement over the first, but not what I need! Here is how I
have these three fields set:
Display Control: Combo Box; Row Source Type: Table/Query; Row Source;
tblname; Bound Column: 1; Column Count: 1; Column Header: Yes; Column Width:
1.5â€

I’ve had these problems in the past and have tried extremely hard to not
have to use the Autonumber as a FieldID, but this time, I was not fortunate
enough to avoid it and have had nothing but problems. I really hope you can
help me understand what I am doing wrong.

Four Report Problems: (So you know my Control Source for the Report, it is a
Query.)
1)Sometimes, the FieldID AutoNumber, ie,â€45,†shows in the Report rather
than the entered information, ie, “Client.†I pull the Field Name from the
Field List into the Report. Incidentally, when I run the Query, “Clientâ€
shows, not “45.†If I change the text box to a combo box, sometimes the
problem is eliminated, sometimes not. But, using a Combo Box leads to another
problem:

2) I want to use the Field Name, ie [Whoes] in the “Whoes Footerâ€. Inside
the Text Box =â€Summary of “&[Whoes] & " (" & Count(*) & " " &
IIf(Count(*)=1,"detail record","detail records") & ")"
However, I cannot use the Combo Box (needed to view data input) inside the
Text Box.

3) I have “0.00†appearing throughout the report. The Default Value is set
to “0,†with the Standard Format and 2 decimal places. I tried the following
two expressions in the field text box and get an Invalid Control Source and
Circular Reference message when I try to get out of the text box. I tried
=IIF(Is Null([Client Check Amount]),†“,[Client Check Amount]) and when that
did not work, the comment you’d made earlier about the Null field, I put
=IIF(Nz(Client Check Amount],0),†“,[Client Check Amount])

4) The “Description Footer†and the “Whoes Footer†are appearing in the
Report above the Headers at the top of the page! (This is a new problem) I’ve
compared previous reports, the properties, etc. and can’t seem to find the
problem.

1) That's why experienced folks really hate those lookup
fields in a table (i.e. what you see is not what you get).

Change the table field from a lookup to a text box so you
can see what's really there. Then modify the report's
record source query to join the table to the clients table
and get the client name from there.

2) Try changing the name of the text box that's getting the
circular reference to something else such as
txtClientCheckAmt.

3) Don't use an expression to format a value. Just set the
text box's Format property to a custom format. If you want
nonzero numbers to have 2 decimal places and nothing for
zero amounts. then the format property could be like:
0.00;;""
Lookup Fprmat Property in VBA Help for details and links to
related options.

4) If that's what you are relly getting, it would be very
strange. The GROUP header/footer sections should appear at
appropriate points throughout the report.

The only thing that appears before the first page header
section is the report header section. Did you inadvertantly
put that sutff in the wrong section?

 

[znibk]

Tina,

I still have not ben able to find the thread that was probably my original
questions which you found at "..reports ng, from MVP Duane Hookom." If you
don't mind, please tell me the Subject name I used so that I can look at his
response.

Again, I'm sorry for the multi-posts.

please don't multi-post. for more information see
http://home.att.net/~california.db/tips.html#aTip10.
i also noticed that you posted these questions over 5 hours *after* you
already got answers to the first 3 of them from a separate post in the
..reports ng, from MVP Duane Hookom. please check previous threads before
posting questions again.

Four Report Problems: (So you know my Control Source for the Report, it is a
Query.)
1)Sometimes, the FieldID AutoNumber, ie,"45," shows in the Report rather
than the entered information, ie, "Client." I pull the Field Name from the
Field List into the Report. Incidentally, when I run the Query, "Client"
shows, not "45." If I change the text box to a combo box, sometimes the
problem is eliminated, sometimes not. But, using a Combo Box leads to another
problem:

2) I want to use the Field Name, ie [Whoes] in the "Whoes Footer". Inside
the Text Box ="Summary of "&[Whoes] & " (" & Count(*) & " " &
IIf(Count(*)=1,"detail record","detail records") & ")"
However, I cannot use the Combo Box (needed to view data input) inside the
Text Box.

3) I have "0.00" appearing throughout the report. The Default Value is set
to "0," with the Standard Format and 2 decimal places. I tried the following
two expressions in the field text box and get an Invalid Control Source and
Circular Reference message when I try to get out of the text box. I tried
=IIF(Is Null([Client Check Amount])," ",[Client Check Amount]) and when that
did not work, the comment you'd made earlier about the Null field, I put
=IIF(Nz(Client Check Amount],0)," ",[Client Check Amount])

4) The "Description Footer" and the "Whoes Footer" are appearing in the
Report above the Headers at the top of the page! (This is a new problem) I 've
compared previous reports, the properties, etc. and can't seem to find the
problem.

Incidentally, I don't write code and have limited knowledge of expressions
or simple calculations.

I really hope someone has a lot of patience and can help me out. As in the
past, I'll be extremely grateful.

 

[tina]

Report Problems--Three Questions

Tina,

I still have not ben able to find the thread that was probably my original
questions which you found at "..reports ng, from MVP Duane Hookom." If you
don't mind, please tell me the Subject name I used so that I can look at his
response.

Again, I'm sorry for the multi-posts.

please don't multi-post. for more information see
http://home.att.net/~california.db/tips.html#aTip10.
i also noticed that you posted these questions over 5 hours *after* you
already got answers to the first 3 of them from a separate post in the
..reports ng, from MVP Duane Hookom. please check previous threads before
posting questions again.

Four Report Problems: (So you know my Control Source for the Report,

it is
a

Query.)
1)Sometimes, the FieldID AutoNumber, ie,"45," shows in the Report rather
than the entered information, ie, "Client." I pull the Field Name from the
Field List into the Report. Incidentally, when I run the Query, "Client"
shows, not "45." If I change the text box to a combo box, sometimes the
problem is eliminated, sometimes not. But, using a Combo Box leads to another
problem:

2) I want to use the Field Name, ie [Whoes] in the "Whoes Footer". Inside
the Text Box ="Summary of "&[Whoes] & " (" & Count(*) & " " &
IIf(Count(*)=1,"detail record","detail records") & ")"
However, I cannot use the Combo Box (needed to view data input) inside the
Text Box.

3) I have "0.00" appearing throughout the report. The Default Value is set
to "0," with the Standard Format and 2 decimal places. I tried the following
two expressions in the field text box and get an Invalid Control

Source
and

Circular Reference message when I try to get out of the text box. I tried
=IIF(Is Null([Client Check Amount])," ",[Client Check Amount]) and

when
that

did not work, the comment you'd made earlier about the Null field, I put
=IIF(Nz(Client Check Amount],0)," ",[Client Check Amount])

4) The "Description Footer" and the "Whoes Footer" are appearing in the
Report above the Headers at the top of the page! (This is a new

problem) I
've

compared previous reports, the properties, etc. and can't seem to find the
problem.

Incidentally, I don't write code and have limited knowledge of expressions
or simple calculations.

I really hope someone has a lot of patience and can help me out. As in the
past, I'll be extremely grateful.

 

[znibk]

Tina,

Sorry to tell you, but that does not come up on my computer. I copied and
pasted what you typed so I would not make any mistakes. I also tried to find
it in the general question section. Don't know why it doesn't come up but it
does not. In each forum, I get the same results: "Your query for 'Report
Problems--Three Questions' did not return results."

And, I still have not been able to solve the problem of the text box
returning the FieldID when I use it in the table. I was really exact in my
reply to Marshall, in the event you have any suggestions.

Report Problems--Three Questions

Tina,

I still have not ben able to find the thread that was probably my original
questions which you found at "..reports ng, from MVP Duane Hookom." If you
don't mind, please tell me the Subject name I used so that I can look at his
response.

Again, I'm sorry for the multi-posts.

please don't multi-post. for more information see
http://home.att.net/~california.db/tips.html#aTip10.
i also noticed that you posted these questions over 5 hours *after* you
already got answers to the first 3 of them from a separate post in the
..reports ng, from MVP Duane Hookom. please check previous threads before
posting questions again.


Four Report Problems: (So you know my Control Source for the Report, it is
a
Query.)
1)Sometimes, the FieldID AutoNumber, ie,"45," shows in the Report rather
than the entered information, ie, "Client." I pull the Field Name from the
Field List into the Report. Incidentally, when I run the Query, "Client"
shows, not "45." If I change the text box to a combo box, sometimes the
problem is eliminated, sometimes not. But, using a Combo Box leads to
another
problem:

2) I want to use the Field Name, ie [Whoes] in the "Whoes Footer". Inside
the Text Box ="Summary of "&[Whoes] & " (" & Count(*) & " " &
IIf(Count(*)=1,"detail record","detail records") & ")"
However, I cannot use the Combo Box (needed to view data input) inside the
Text Box.

3) I have "0.00" appearing throughout the report. The Default Value is set
to "0," with the Standard Format and 2 decimal places. I tried the
following
two expressions in the field text box and get an Invalid Control Source
and
Circular Reference message when I try to get out of the text box. I tried
=IIF(Is Null([Client Check Amount])," ",[Client Check Amount]) and when
that
did not work, the comment you'd made earlier about the Null field, I put
=IIF(Nz(Client Check Amount],0)," ",[Client Check Amount])

4) The "Description Footer" and the "Whoes Footer" are appearing in the
Report above the Headers at the top of the page! (This is a new problem) I
've
compared previous reports, the properties, etc. and can't seem to find the
problem.

Incidentally, I don't write code and have limited knowledge of expressions
or simple calculations.

I really hope someone has a lot of patience and can help me out. As in the
past, I'll be extremely grateful.

 

[John W. Vinson]

Tina,

Sorry to tell you, but that does not come up on my computer. I copied and
pasted what you typed so I would not make any mistakes. I also tried to find
it in the general question section. Don't know why it doesn't come up but it
does not. In each forum, I get the same results: "Your query for 'Report
Problems--Three Questions' did not return results."

The Microsoft webpage is (oddly) far less useful for searching than the
comptetition, Google Groups:

http://groups.google.com/groups?as_...81&as_maxd=29&as_maxm=7&as_maxy=2007&safe=off

And, I still have not been able to solve the problem of the text box
returning the FieldID when I use it in the table. I was really exact in my
reply to Marshall, in the event you have any suggestions.

The FieldID is *WHAT IS IN YOUR TABLE*. It's showing the ID because that is
what's there. The Lookup Wizard is concealing that fact from you.

The solution is to create a Query joining your table to the Lookup table, and
getting the field you want from the joined table in the query.

John W. Vinson [MVP]

 

[znibk]

Good Evening John,

Please don't yell at me, I'm really trying to get this database together.
It's been three years since I've worked with Access, and am feeling stupid
enough anyway.

I understand that what my tblExpense sees when I use the Lookup Wizard is
the FieldID. That is why I went back, as suggested by Tina and Wayne, as I
understood their directions, and inserted rows in the Table, gave each Field
a similar Field Name and set my Field Name to "Text." On the "Lookup Tab"
(this is not the Lookup Wizard) at the bottom of the page, it is the tab
behind General in the Design View of a Table. I set all the properties as
follows: Display Control: Combo Box; Row Source Type: Table/Query; Row
Source; tblname; Bound Column: 1; Column Count: 1; Column Header: Yes; Column
Width: 1.5â€

Since I did not get that to work, I am trying the creation of a Query; here
is what happens when I do that. I created a Query in Design View with the
following two tables in it. tblExpense (the table I want the data to be
entered into) and my tblBusiness (the table which the 30 Businesses). I
linked the Field Name Business in the tblExpense with the Field Name Business
in the tblBusiness (now both text fields) in the Query. When I ran the Query,
I get 77 recordsets. If I joined the FieldID’s in the tables, I got 17
mismatched records,(the records returned from the tblBusiness are not the
same as the ones returned under the tblExpense.

If I use the table in which I used the Lookup Wizard and join the Field Name
in that tblExpenseLW with the FieldID in the tblBusiness, I receive a
recordset of 73, which is actually the number of records which I have
entered. Under the side of the tblExpenseLW with the Field Name Business, the
name of the business is returned. Under the tblBusiness, the FieldID is
returned. (Is that supposed to be that way?)

I have yet to make a join or add Field Names from the tables into the Query
with 30 recordsets, the number of businesses I have.

Quite simply, I do not know/understand what I am not doing right or what I’m
doing wrong!!!

I hope what I’ve answered makes sense to you or someone, and I can be
helped. I really need to enter more data.

 

[znibk]

Tina,

I am now able to see the first post I entered and could not find, "Report
Problems--Three Questions." Thanks for whatever part you played in making it
accessable to me.

Report Problems--Three Questions

Tina,

I still have not ben able to find the thread that was probably my original
questions which you found at "..reports ng, from MVP Duane Hookom." If you
don't mind, please tell me the Subject name I used so that I can look at his
response.

Again, I'm sorry for the multi-posts.

please don't multi-post. for more information see
http://home.att.net/~california.db/tips.html#aTip10.
i also noticed that you posted these questions over 5 hours *after* you
already got answers to the first 3 of them from a separate post in the
..reports ng, from MVP Duane Hookom. please check previous threads before
posting questions again.


Four Report Problems: (So you know my Control Source for the Report, it is
a
Query.)
1)Sometimes, the FieldID AutoNumber, ie,"45," shows in the Report rather
than the entered information, ie, "Client." I pull the Field Name from the
Field List into the Report. Incidentally, when I run the Query, "Client"
shows, not "45." If I change the text box to a combo box, sometimes the
problem is eliminated, sometimes not. But, using a Combo Box leads to
another
problem:

2) I want to use the Field Name, ie [Whoes] in the "Whoes Footer". Inside
the Text Box ="Summary of "&[Whoes] & " (" & Count(*) & " " &
IIf(Count(*)=1,"detail record","detail records") & ")"
However, I cannot use the Combo Box (needed to view data input) inside the
Text Box.

3) I have "0.00" appearing throughout the report. The Default Value is set
to "0," with the Standard Format and 2 decimal places. I tried the
following
two expressions in the field text box and get an Invalid Control Source
and
Circular Reference message when I try to get out of the text box. I tried
=IIF(Is Null([Client Check Amount])," ",[Client Check Amount]) and when
that
did not work, the comment you'd made earlier about the Null field, I put
=IIF(Nz(Client Check Amount],0)," ",[Client Check Amount])

4) The "Description Footer" and the "Whoes Footer" are appearing in the
Report above the Headers at the top of the page! (This is a new problem) I
've
compared previous reports, the properties, etc. and can't seem to find the
problem.

Incidentally, I don't write code and have limited knowledge of expressions
or simple calculations.

I really hope someone has a lot of patience and can help me out. As in the
past, I'll be extremely grateful.

 

[tina]

I understand that what my tblExpense sees when I use the Lookup Wizard is

the FieldID. That is why I went back, as suggested by Tina and Wayne, as I
understood their directions, and inserted rows in the Table, gave each Field
a similar Field Name and set my Field Name to "Text." On the "Lookup Tab"
(this is not the Lookup Wizard) at the bottom of the page, it is the tab
behind General in the Design View of a Table. I set all the properties as
follows: Display Control: Combo Box; Row Source Type: Table/Query; Row
Source; tblname; Bound Column: 1; Column Count: 1; Column Header: Yes; Column
Width: 1.5"

i didn't tell you to do any of that. in my last post, i specifically said:
"don't rename the
field - just go to the Lookup tab and change the DisplayControl property to
Textbox. that gets rid of the Lookup, as we suggested."

hth

 

[John W. Vinson]

Good Evening John,

Please don't yell at me, I'm really trying to get this database together.
It's been three years since I've worked with Access, and am feeling stupid
enough anyway.

Please accept my apologies, znibk. I'm not yelling at you so much as yelling
at Microsoft for putting you into this quandry.

I understand that what my tblExpense sees when I use the Lookup Wizard is
the FieldID. That is why I went back, as suggested by Tina and Wayne, as I
understood their directions, and inserted rows in the Table, gave each Field
a similar Field Name and set my Field Name to "Text." On the "Lookup Tab"
(this is not the Lookup Wizard) at the bottom of the page, it is the tab
behind General in the Design View of a Table. I set all the properties as
follows: Display Control: Combo Box; Row Source Type: Table/Query; Row
Source; tblname; Bound Column: 1; Column Count: 1; Column Header: Yes; Column
Width: 1.5”

As noted elsethread, that was not what was suggested. You're tinkering with
the nature of the Lookup field. What Tina and others have suggested is
*getting rid of the lookup field altogether*, and using just a plain vanilla
textbox in the table design, showing the FieldID as a number.

Since I did not get that to work, I am trying the creation of a Query; here
is what happens when I do that. I created a Query in Design View with the
following two tables in it. tblExpense (the table I want the data to be
entered into) and my tblBusiness (the table which the 30 Businesses). I
linked the Field Name Business in the tblExpense with the Field Name Business
in the tblBusiness (now both text fields) in the Query. When I ran the Query,
I get 77 recordsets. If I joined the FieldID’s in the tables, I got 17
mismatched records,(the records returned from the tblBusiness are not the
same as the ones returned under the tblExpense.

You should be linking the numeric FieldID in the Expense table with the
primary key FieldID in tblBusiness.

If I use the table in which I used the Lookup Wizard and join the Field Name
in that tblExpenseLW with the FieldID in the tblBusiness, I receive a
recordset of 73, which is actually the number of records which I have
entered. Under the side of the tblExpenseLW with the Field Name Business, the
name of the business is returned. Under the tblBusiness, the FieldID is
returned. (Is that supposed to be that way?)

Exactly. tblExpenseLW contains a numeric FieldID. tblBusiness contains the
name of that business. In order to produce a Report displaying the business
name in conjunction with other data, you create a Query joining the two
tables; pull the business name from the table where that piece of information
resides (tblBusiness); and pull other information from the table where *it*
resides (tblExpenseLW). That's how relational databases work!

I have yet to make a join or add Field Names from the tables into the Query
with 30 recordsets, the number of businesses I have.

I'm sorry, but this is confusing me. Why on Earth would you want 30
recordsets?? If you want to see data about a business, use a criterion on your
query to select a business! You don't need 30 recordsets, and you don't need
30 queries!

On a Form for entering data, you would use tblExpenseLW as the Recordsource of
the form. On the form, you would have a Combo Box bound to the Business field;
this combo would store the numeric business ID, while displaying the business
name. You may have the impression that the only way to create such a combo box
is to use the Lookup Wizard - that's understandable, since Microsoft goes to
some effort to give that impression; but it is NOT the case.

John W. Vinson [MVP]

 

[Marshall Barton]

Hey Marshall,

Thank you for the reply.

The 0.00;;"" worked perfectly to solve my Problem 3.

My Problem 4 really was happening, but when I double checked my
ControlSource, I discovered that it was set to the table not the query. When
I changed to the query like I thought I had, everything worked perfectly.

I'm still working to solve Problems 1 by changing the field type to "Text,"
rather than using the Lookup Wizad. I have done that and have gotten a
variety of results.
Field1, I just typed the names of the banks in the Lookup tab. I chose
combo box, value list, and typed “ABC.” The “ABC” shows in the table
datasheet when the drop down arrow is clicked, however, it will not appear on
the datasheet when chosen.
Fields2, 3 and 4 the FieldID, ie “45” shows rather than “Client” on the
datasheet. When the down arrow is clicked, the FieldID will appear on the
datasheet, an improvement over the first, but not what I need! Here is how I
have these three fields set:
Display Control: Combo Box; Row Source Type: Table/Query; Row Source;
tblname; Bound Column: 1; Column Count: 1; Column Header: Yes; Column Width:
1.5”

I’ve had these problems in the past and have tried extremely hard to not
have to use the Autonumber as a FieldID, but this time, I was not fortunate
enough to avoid it and have had nothing but problems. I really hope you can
help me understand what I am doing wrong.

I use autonumber fields in most tables without having a
problem so I don't relate to why you are avoiding them. If
it's because you are following the theory of using natural
keys, that's fine, but the argument against surrogate keys
is mostly a theoretical issue so I wouldn't get bent out of
shape over it.

I suspect that any other comments I could make at this point
would only add to the confusion. John has already explained
how to avoid the dreaded lookup field in you table and you
should continue that discussion.

 

[znibk]

Tina,

We seem to have gotten of on the wrong foot. I apologize for whatever part I
played. All I would like to say is that I received posts which said "’tina’
wrote:†but nothing followed.

All I have seen is the first post you said to not "multi-post," and one
which gave me the name of my first post, "Report Problems--Three Questions."
If you wrote any of the post that is below in any of your posts, I did not
see it. I've gone back through and re-read what I've seen and I still don't
see it. As I said above, all that is see is “‘tina’ wrote:â€

It would have been extremely helpful, and saved me so much time, oh so much
time, if I'd seen what you wrote below, “. . . i specifically said: ‘don't
rename the field - just go to the Lookup tab and change the DisplayControl
property to Textbox. that gets rid of the Lookup, as we suggested.’†before.
However, since I did not, John’s step-by-step instructions really helped me;
it is possible that had I seen your post before, I would have done exactly
what I did, as I would not have understood that I could (should) leave the
“Data Type†in the Table Design view to “Number.â€

Thank you so very much for your help. I hope you have a great week.

 

[znibk]

John,

Apology accepted.

Thank you so very much for the step by step directions on what to do. I’ve
done that before when I used “Text†as my “Primary Key.†I would never have
thought to set my “Data Type†to Number and leave the “Lookup†tab set to
“Text Box.†Now, thanks to your instructions, I won’t be afraid of the
“AutoNumber†as the “Primary Key.†And, I understand what had been
suggested.

I still have some formatting on my Form, but everything else seems to be
working. I will add that in the “Combo Box†“Property†field, my “Drop Down
Arrow†did not show the choices it should have, so I did some editing. (I
hope I did the correct thing. If not, please tell me before I go any
further.) So that the “Drop Down Arrow†on the “Combo Box†would work, I
chose the correct “Table†for the field, also, I changed the “Column Countâ€
to “2†and set the â€Bound Column†to “1†and the “Column Widths†to “0†for
the first column, the “Bound Column,†and the “Column Width†to what I needed
for “Field Name†in my “Combo Box.†(Please tell me I did the right thing and
I’m not back to “Zero†again).

New question: In the tables, I have chosen “Sort Ascending†on the alpha
column, but unfortunately, when the “Drop Down Arrow†is clicked on my “Combo
Box(es),†the numeric values are sorting which really makes a mess of some of
my lists and it difficult to choose the data I need. John, is there a way to
set “Sort Ascending†so the, ie names of businesses etc. will appear to be in
alphabetical order?

The answer to my question/statement about the 30 recordsets, I really don’t
know what I meant. Something I thought I was supposed to get when I ran one
of the query combinations I’d come up with, not understanding completely what
had been said about the before mentioned “Text Box.†But, you really solved
the problem when you said the query with the Field Name from the
tblExpensesLW and the FieldID from tblBusiness was the correct choice of
Field Names for the query. Yea, for a change.

John, I really appreciate your help and your patience, and that of Marshall
(his solution to my multiple “0.00s†worked great and his reassurance about
using the Autonumer as the Primary Key) and Tina’s. Thank you all again so
much. God Bless each of you for the help you provide us.

 

[znibk]

John,

If I need to make corrections, and need to see, ie the business name, must
this be done on the form, since I do not have the use of the "Drop Down
Arrows" in either the table or the query, or should I have a "Drop Down
Arrow" in those places and I've made a mistake?

Thanks again.

 

[John W. Vinson]

John,

Apology accepted.

Thank you so very much for the step by step directions on what to do. I’ve
done that before when I used “Text” as my “Primary Key.” I would never have
thought to set my “Data Type” to Number and leave the “Lookup” tab set to
“Text Box.” Now, thanks to your instructions, I won’t be afraid of the
“AutoNumber” as the “Primary Key.” And, I understand what had been
suggested.

Great. Just remember - tables are for data storage, not for data display or
interaction with the data. It doesn't matter that the table shows a
meaningless number, since you'll not be looking at the table for meaning
anyway! That's what the Form is for.

I still have some formatting on my Form, but everything else seems to be
working. I will add that in the “Combo Box” “Property” field, my “Drop Down
Arrow” did not show the choices it should have, so I did some editing. (I
hope I did the correct thing. If not, please tell me before I go any
further.) So that the “Drop Down Arrow” on the “Combo Box” would work, I
chose the correct “Table” for the field, also, I changed the “Column Count”
to “2” and set the ”Bound Column” to “1” and the “Column Widths” to “0” for
the first column, the “Bound Column,” and the “Column Width” to what I needed
for “Field Name” in my “Combo Box.” (Please tell me I did the right thing and
I’m not back to “Zero” again).

Sounds perfect.

New question: In the tables, I have chosen “Sort Ascending” on the alpha
column, but unfortunately, when the “Drop Down Arrow” is clicked on my “Combo
Box(es),” the numeric values are sorting which really makes a mess of some of
my lists and it difficult to choose the data I need. John, is there a way to
set “Sort Ascending” so the, ie names of businesses etc. will appear to be in
alphabetical order?

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.

The answer to my question/statement about the 30 recordsets, I really don’t
know what I meant. Something I thought I was supposed to get when I ran one
of the query combinations I’d come up with, not understanding completely what
had been said about the before mentioned “Text Box.” But, you really solved
the problem when you said the query with the Field Name from the
tblExpensesLW and the FieldID from tblBusiness was the correct choice of
Field Names for the query. Yea, for a change.

John, I really appreciate your help and your patience, and that of Marshall
(his solution to my multiple “0.00s” worked great and his reassurance about
using the Autonumer as the Primary Key) and Tina’s. Thank you all again so
much. God Bless each of you for the help you provide us.

Glad to be of help, and sorry about the testiness!

John W. Vinson [MVP]

 

[John W. Vinson]

If I need to make corrections, and need to see, ie the business name, must
this be done on the form, since I do not have the use of the "Drop Down
Arrows" in either the table or the query, or should I have a "Drop Down
Arrow" in those places and I've made a mistake?

The data in the Business table should be edited using a maintenance form bound
to the business table itself. It is not necessary to have "drop down arrows" -
or, more in tune with Access jargon, Combo Boxes - in either the table or in
the query.

John W. Vinson [MVP]

 

[znibk]

John,

Thank you again. I followed your directions and created the query in the Row
Source. Had to do it twice, but it worked. Thank you.

Please keep an eye on this thread. If I have any more problems, I'd like to
just post them here so you can help me out. And, again, thank you so very
much.