Name not showing ID is

[Guest]

I have set up on my contact form combo boxes for both clients and employees. It is working great on my form after I set the width for that column to 0",

I went through the report wizard to set up a report to show those contacts and what I am now seeing is the ID #'s instead of the names on my report. How do I fix this???

Thanks in advance for your help

 

[Ken Snell]

Are you using lookup fields in your table? Likely you are. Get rid of them.
They mask what you actually store in the table -- in this case, the ID#, not
the name of the contact.

Then set up the query to correctly pull the contact name based on the ID of
the contact.

--

Ken Snell
<MS ACCESS MVP>

I have set up on my contact form combo boxes for both clients and

employees. It is working great on my form after I set the width for that
column to 0",

I went through the report wizard to set up a report to show those contacts

and what I am now seeing is the ID #'s instead of the names on my report.
How do I fix this???

 

[Guest]

Sorry, I'm not quite understanding. The table is set up as comment id, and the rest of the fields are stored from combo boxes. I'm kinda a newbie here, so if you could please give me a little more detail I would greatly appreciate

 

[Ken Snell]

If your table's fields show as combo boxes when you open the table in
datasheet view, then you are using the lookup fields feature on those
fields. That means that the table is showing you a representation of what is
stored in the table, not the actual value itself. For example, if you have a
field for holding an EmployeeID, which is an autonumber (numeric) field, and
the table, when open in datasheet view, shows an actual employee name in
that field instead of the number, then you have a lookup field.

This misleads you when doing queries to forgetting that the field does not
contain the actual name but rather contains a number that represents the
employee, and that the employee name actually is stored in another table
that is linked to this table via the EmployeeID field.

Thus, you become confused because you "think" the employee name is in the
field...but it really is a number. Thus, your report shows a number and not
a name, just as the table is designed.

--

Ken Snell
<MS ACCESS MVP>

Sorry, I'm not quite understanding. The table is set up as comment id,

and the rest of the fields are stored from combo boxes. I'm kinda a newbie
here, so if you could please give me a little more detail I would greatly
appreciate

 

[Guest]

OK here goes

I opened up the table and the id #'s are stored in the table. (I was told that I should always use the id's versus the names due to unique values) I did go into design view and look into the fields to see that under the look up tab it says text in each of those fields. Am I to delete those?

I understood what you said about thinking that the id would equate to the name, but as I said I was under the understanding that I was to use id's not actual names in my tables. That being said, I still am not understanding how to transfer the name into my report.

 

[Ken Snell]

Ahhhhhhh --

Ok - good, you've set up your table correctly. So, let's now focus on how to
write the query for your report's recordsource.

You correctly are using the EmployeeID as the "value" of the combo box. What
you now want is to use a query as the report's recordsource that includes
both the ID and the employee name field(s).

Something like this, assuming that you are storing the employee's name in
separate fields in the same table:

SELECT TableName.EmployeeID, TableName.EmployeeFirstName & " "
& TableName.EmployeeMiddleName & " " & TableName.EmployeeLastName
AS EmployeeFullName
FROM TableName;

In your report, bind a textbox to the EmployeeFullName field from the query
and that will show the full name in that textbox.

When you open the report, you can filter on the EmployeeID field through the
use of the fourth argument in the DoCmd.OpenReport method:
DoCmd.OpenReport "ReportName", , , "EmployeeID=" & Me.ComboBoxName.Value

Am I on the right track for your setup?

--

Ken Snell
<MS ACCESS MVP>

OK here goes

I opened up the table and the id #'s are stored in the table. (I was told

that I should always use the id's versus the names due to unique values) I
did go into design view and look into the fields to see that under the look
up tab it says text in each of those fields. Am I to delete those?

I understood what you said about thinking that the id would equate to the

name, but as I said I was under the understanding that I was to use id's not
actual names in my tables. That being said, I still am not understanding
how to transfer the name into my report.

 

[Guest]

We'll get this yet

When I originally set up the combo box, I used the wizard. It had me choose the fields that I wanted for the box. I choose the CustomerID and Client Name. The customerID is all that shows up in the table, so I do not believe that I have the client name stored in the same table, unless because it is the second column, it is there and I just can't see it (????)

How do I bind a textbox to the Employee/Client Name field?

Sorry about this, but I am feeling really stupid right now!

I am going to need to transfer the data before tomorrow morning if possible. Do you think that this is something that once it clicks well be an easy fix?

 

[Ken Snell]

I'm more confused, I'm afraid.

Let's go all the way back to the beginning. What are the tables that you
have? What are the fields in each table? What is the row source that is
being used for the combo box on the form? What is the recordsource that is
being used for the report?

--

Ken Snell
<MS ACCESS MVP>

We'll get this yet

When I originally set up the combo box, I used the wizard. It had me

choose the fields that I wanted for the box. I choose the CustomerID and
Client Name. The customerID is all that shows up in the table, so I do not
believe that I have the client name stored in the same table, unless because
it is the second column, it is there and I just can't see it (????)

How do I bind a textbox to the Employee/Client Name field?

Sorry about this, but I am feeling really stupid right now!

I am going to need to transfer the data before tomorrow morning if

possible. Do you think that this is something that once it clicks well be
an easy fix?

 

[Guest]

Sorry Ken, I seem to have that effect on people

I have a Customer table, a employee table, Staff table (list of office employees) Review table (list of review items) and I just added a comment table

The comment table is as follows:

commentid
date -- default date()
Staff - combo box fill in from Staff table
Client Name -- combo box fill in from Customer table
Employee Name -- combo box fill in from Employee table
Reason -- combo box fill in from from Reveiw table
contact -- memo field for documentation
CustomerID -- just added for linking
EmployeeID -- just added for linking

On my documentation form is the fields and combo boxes named above. The combo boxes (set up by wizard) were set to have two columns // CustomerID and Client Name etc. you choose the client/employee name in the drop down on the documentation form.

The report is being drawn off the Comment table mentioned above. I have one report that is generated by date for the employees and the other for the clients. The report looks great with the exception of all the numbers in place of names in the Client/Employee name and staff name

What I was trying to create was a type of contact log similar to that in the Northwind dbase. For the life of me I couldn't figure that on out!

 

[Bob]

The query that drives your combo box should be setup with
the client name in the first column and the ID in the
second column. Bind column number 2 but set the width of
column 2 to 0".

-----Original Message-----
I woke up this morning with one of those "Duh" thoughts I

should have set the combo box to save the id number to the
id field and just had two columns so that you could choose
by the visible name, and then query together the comment
table and the customer/employee table. Correct????

If that sounds like what I should have done give me some

pointers so as I don't skip anything in the fix, if not
redirect me (again)

Thanks

(list of office employees) Review table (list of review
items) and I just added a comment tablenamed above. The combo boxes (set up by wizard) were set
to have two columns // CustomerID and Client Name etc.
you choose the client/employee name in the drop down on
the documentation form.mentioned above. I have one report that is generated by
date for the employees and the other for the clients. The
report looks great with the exception of all the numbers
in place of names in the Client/Employee name and staff
namesimilar to that in the Northwind dbase. For the life of
me I couldn't figure that on out!

 

[Ken Snell]

Still not quite clear, but it may be that your combo box's Row Source and
related properties need adjusting.

Set the Row Source Type to query.

Set the Row Source to this SQL statement:
SELECT Customer.ClientID, Customer.ClientName FROM Customer ORDER BY
Customer.ClientName;

Set the Bound Column to 1.

Set the Column Count to 2.

Set the Column Widths to 0";2"

Set the List Width to 2".

Set the Control Source to the field in the form's RecordSource that is to
store the ClientID value. Do this only if you're using the form for data
entry.

That will mean that the value of the combo box is the ClientID, and only the
client name will be displayed in the dropdown list. Then use the value of
the combo box as the "value" for filtering a report.

--

Ken Snell
<MS ACCESS MVP>

I woke up this morning with one of those "Duh" thoughts I should have set

the combo box to save the id number to the id field and just had two columns
so that you could choose by the visible name, and then query together the
comment table and the customer/employee table. Correct????

If that sounds like what I should have done give me some pointers so as I

don't skip anything in the fix, if not redirect me (again)

Thanks

employees) Review table (list of review items) and I just added a comment
tablecombo boxes (set up by wizard) were set to have two columns // CustomerID
and Client Name etc. you choose the client/employee name in the drop down
on the documentation form.one report that is generated by date for the employees and the other for the
clients. The report looks great with the exception of all the numbers in
place of names in the Client/Employee name and staff name