Yes it does make sense. This is a very common thing to do and it not really
difficult. What you want to do is make your combo box a 2 column combo. You
can set it so the user sees only the customer name. The customer code can be
hidden. Start by modifying the query that is the row source of the combo to
include both fields.
Row Source: SELECT DISTINCT CustomerCode, CustomerName FROM Customers
WHERE CustomerName Is Not Null ORDER BY CustomerName;
Set the combo's Column Count property to 2
Set the combo's Column Widths to 0";2"
The 0" makes the CustomerCode hidden. You may need to adjust the 2" so the
customer name displays like you want it.
Set the combo's Bound Column to 1.
You will still want to use the Customer Code to look up records. When you
reference the control as:
Me.Rcd_Frm The Customer Code will be returned.
Note the column reference for combo boxes is zero based so you can access
both fields like this:
Me.Rcd_Frm.Column(0) returns the Customer Code. It is the same as Me.Rcd_Frm
Me.Rcd_Frm.Column(1) returns the Customer Name.
The most common way to navigate to a selected customer record is to use the
combo box's After Update event:
With Me.RecordsetClone
.FindFirst "[customercode] = " & Me.Rcd_Frm
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
--
Dave Hargis, Microsoft Access MVP
Since some of the customers name can be long, I am storing the
CustomerCode (3 digits) and not the CustomerName in the Rcd_Frm field.
This works fine on data input. The problem is when I go to look at the
data. With over 800 cutomers, I do not recognize the name by the
codenumber. So I was trying to look up and display the CustomerName
from the CustomerCode. Does that make sense?
I do appreciate your help.
The problem is you are trying to set the control source of the combo to the
value in the combo. This is a circular reference. This is not going to work
at all.
What is it you want to do? Perhaps we can offer a suggestion if we know
your objective.
--
Dave Hargis, Microsoft Access MVP
:
In the table Customers, the fields are -
CustomerCode (Number,long integer)
CustomerName (Text, 50)
On the form frm3PHPad_Edit2, I have -
Rcd_Frm (Text, 30, combo box)
I have the combo box set up as -
Name: Rcd_Frm
Control SOurce: =DLookUp("CustomerName","Customers","[CustomerCode] =
" & [Rcd_Frm])
Row Source Type: Table/Query
Row Source: SELECT DISTINCT Customers.CustomerName FROM Customers
WHERE (((Customers.CustomerName) Is Not Null)) ORDER BY
Customers.CustomerName;
I just noticed the size for the fields CustomerName (50) and Rcd_Frm
(30) are not equal. Could that be part of the problem?
Thanks for your help.
Is it possible the control on the form and the field in the recordset have
the same name? Access can get confused over this. Since I don't know your
names, let me lay out what goes in the DLookup, and you can see if there is a
problem:
=DLookup("NameofFieldInTable","NameOfTable","NameOfFieldToFilterON" and
Criteria to Search For)
I still need to know where the code is.
--
Dave Hargis, Microsoft Access MVP
:
That didn't work either. The form and database didn't close. In view,
the field remained blank. I did get the error "This control has a
reference to itself." Any other suggestions?
Try it like this:
=DLookUp("CustomerName","Customers","[CustomerCode] = " & [Rcd_Frm])
--
Dave Hargis, Microsoft Access MVP
:
Thanks for the quick reply but something is not right. If I put your
DLookUp code in the Control Source property for the combo box and then
click on Form view, not only does the form shut down but the database
shuts down. If I delete the DLookUp code and leave the field blank or
put Rcd_Frm I get either a null field or the numeric code
respectively.
Any more thoughts on what is causing that? I do not have any code
associated with the form (OnOpen, OnLoad, etc.)
The reference to the form control needs to be outside the quotes.
=DLookUp("CustomerName","Customers","[CustomerCode] = [Forms]!
[frm3PHPad_Edit2]![Rcd_Frm]")
Should be:
=DLookUp("CustomerName","Customers","[CustomerCode] = " & [Forms]!
[frm3PHPad_Edit2]![Rcd_Frm])
--
Dave Hargis, Microsoft Access MVP
:
I have a table of Customers that contain 2 fields - a CustomerCode
(numeric) and CustomerName (text).
I have a form named 3PHPad, which has a combo box named Rcd_Frm (text)
which stores the CustomerCode information. When I am inputting the
data, I store the CustomerCode to save space. However, on other forms
I would like to show the CustomerName. I am trying to use the
following DLookUp on the control source property -
=DLookUp("CustomerName","Customers","[CustomerCode] = [Forms]!
[frm3PHPad_Edit2]![Rcd_Frm]")
This works in the intermediate window correctly but when I paste it
into the control source, it does not work.
Any thoughts? Thanks in advance.
