Re: Name not showing ID is

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: René (Ren_at_discussions.microsoft.com)
Date: 06/28/04 

Date: Mon, 28 Jun 2004 09:30:01 -0700

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

"René" wrote:

> 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! :(
>
>
> "Ken Snell" wrote:
>
> > 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>
> >
> > "René" <Ren@discussions.microsoft.com> wrote in message
> > news:F0C05B05-A302-429B-BB82-648A660F6C92@microsoft.com...
> > > 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" wrote:
> > >
> > > > 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>
> > > >
> > > > "René" <Ren@discussions.microsoft.com> wrote in message
> > > > news:79F4BA5A-5C8B-45CB-8B56-5E784B5BA3B8@microsoft.com...
> > > > > 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" wrote:
> > > > >
> > > > > > If your table's fields show as combo boxes when you open the table
> > in
> > > > > > data*** 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 data*** 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>
> > > > > >
> > > > > > "René" <Ren@discussions.microsoft.com> wrote in message
> > > > > > news:2F3721A2-8A4E-4D05-A16D-081FC706253B@microsoft.com...
> > > > > > > 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" wrote:
> > > > > > >
> > > > > > > > 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>
> > > > > > > >
> > > > > > > > "René" <Ren@discussions.microsoft.com> wrote in message
> > > > > > > > news:44468B80-A7F8-4C1F-831F-A88307831365@microsoft.com...
> > > > > > > > > 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
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >

Quantcast