Show Vendor Name

[Don]

I have a form that shows purchase order records and one of the fields is
vendor Number. What I would like to do is have the vendor name that is in the
vendor file show and of course match the vendor. Behind the form is a query
that is updatable. However, when I try to link the vendor table to the PO
table by vendor number and put vendor name into the query, I can no longer
add any records to the form. It does not seem to make any difference how I
join the tables (other that get a bad record set) I still cannot add records.
I thought about some sort of unbound text box that would look at the vendor
number on the form and return the vendor name. What do I need to do to make
this work?

 

[Don]

I also tried to use the following code to return the vendor name but it only
works in a combo box:

SELECT Vendortbl.[VENDOR_NAME], Vendortbl.[VENDOR_NUMBER]

I need it to work in an unbound text box.

 

[Beetle]

Set the control source of the unbound textbox to;

DLookup("[VendorName]", "tblVendors", "[VendorID] = " & VendorID)

You'll need to modify the naming to fit your application. if you get stuck
you can find info on the DLookup function in your Access help file.
--
_________

Sean Bailey

I also tried to use the following code to return the vendor name but it only
works in a combo box:

SELECT Vendortbl.[VENDOR_NAME], Vendortbl.[VENDOR_NUMBER]

I need it to work in an unbound text box.
--
Thanks,

Dennis

I have a form that shows purchase order records and one of the fields is
vendor Number. What I would like to do is have the vendor name that is in the
vendor file show and of course match the vendor. Behind the form is a query
that is updatable. However, when I try to link the vendor table to the PO
table by vendor number and put vendor name into the query, I can no longer
add any records to the form. It does not seem to make any difference how I
join the tables (other that get a bad record set) I still cannot add records.
I thought about some sort of unbound text box that would look at the vendor
number on the form and return the vendor name. What do I need to do to make
this work?

 

[John W. Vinson]

I have a form that shows purchase order records and one of the fields is
vendor Number. What I would like to do is have the vendor name that is in the
vendor file show and of course match the vendor. Behind the form is a query
that is updatable. However, when I try to link the vendor table to the PO
table by vendor number and put vendor name into the query, I can no longer
add any records to the form. It does not seem to make any difference how I
join the tables (other that get a bad record set) I still cannot add records.
I thought about some sort of unbound text box that would look at the vendor
number on the form and return the vendor name. What do I need to do to make
this work?

The simplest solution might be to use a Combo Box on the form bound to the
vendor number, but displaying the vendor name. Do your users habitually refer
to vendors by name, or by number? or are both human-meaningful?

 

[Don]

Using the combo box how can I get the box to update when the record changes
in the form and how can I get the combo box to show the vendor name without
having to drop the combo box and select the name ? Granted it is the only
name in the combo box but to see it you have to drop the combo box and select
it.
This is the select query I used to get the returned value:

SELECT Vendortbl.[VENDOR_NAME], Vendortbl.[VENDOR_NUMBER]

 

[John W. Vinson]

Using the combo box how can I get the box to update when the record changes
in the form and how can I get the combo box to show the vendor name without
having to drop the combo box and select the name ? Granted it is the only
name in the combo box but to see it you have to drop the combo box and select
it.
This is the select query I used to get the returned value:

SELECT Vendortbl.[VENDOR_NAME], Vendortbl.[VENDOR_NUMBER]

That's not a select query - or it's just part of one.

What is the RowSource property of the combo box?
What is its Bound Column property?
What is its ColumnWidth property?
What is its Control Source?

 

[Don]

Sorry John I guess I did not copy the whole query:

SELECT Vendortbl.VENDOR_NAME, Vendortbl.VENDOR_NUMBER
FROM Vendortbl
WHERE (((Vendortbl.VENDOR_NUMBER)=[Forms]![POEntry]![VENDOR_NUMBER]));

What is the RowSource property of the combo box? Not sure what property
means but the above is the query in the RowSource.

What is its Bound Column property? (1)

What is its ColumnWidth property? No setting

What is its Control Source? Unsure. This is an unbound combo box and the
only Source is the above query.
--
Thanks,

Dennis

Using the combo box how can I get the box to update when the record changes
in the form and how can I get the combo box to show the vendor name without
having to drop the combo box and select the name ? Granted it is the only
name in the combo box but to see it you have to drop the combo box and select
it.
This is the select query I used to get the returned value:

SELECT Vendortbl.[VENDOR_NAME], Vendortbl.[VENDOR_NUMBER]

That's not a select query - or it's just part of one.

What is the RowSource property of the combo box?
What is its Bound Column property?
What is its ColumnWidth property?
What is its Control Source?

 

[John W. Vinson]

Sorry John I guess I did not copy the whole query:

SELECT Vendortbl.VENDOR_NAME, Vendortbl.VENDOR_NUMBER
FROM Vendortbl
WHERE (((Vendortbl.VENDOR_NUMBER)=[Forms]![POEntry]![VENDOR_NUMBER]));

What is the RowSource property of the combo box? Not sure what property
means but the above is the query in the RowSource.

What is its Bound Column property? (1)

What is its ColumnWidth property? No setting

What is its Control Source? Unsure. This is an unbound combo box and the
only Source is the above query.

Try setting the ColumnCount to 2. If you want the user to see both the
vendorname and the vendor number with the combo dropped down, set the
ColumnWidths property to something like

1.0;0.5

or some other appropriate widths to display both fields. The combo when not
dropped down will display the first nonzero width field.

What is the PURPOSE of this combo? If you just want to display the vendor name
for the selected record's vendor number, don't use a combo at all; instead use
a textbox with a control source

=DLookUp("[Vendor_Name]", "VendorTbl", "[Vendor_Number] = " & [Vendor_Number])

 

[Don]

I have tried DLookUp as Beetle suggested and each time the field reads
#Error. I have used the Access help and cannot seem to figure out why it does
not work. I have changed the table names (example from VENDOR_NUMBER to
VendorNumber) thinking that may make a difference but it does not. The
expression I have with the table column name updates looks like:

=DLookUp("[VendorName]","Vendortbl","[VendorNumber] = " & [VendorNumber])

And it still does not work.
What else can I check to make DLookUp work?
--
Thanks,

Dennis

Sorry John I guess I did not copy the whole query:

SELECT Vendortbl.VENDOR_NAME, Vendortbl.VENDOR_NUMBER
FROM Vendortbl
WHERE (((Vendortbl.VENDOR_NUMBER)=[Forms]![POEntry]![VENDOR_NUMBER]));

What is the RowSource property of the combo box? Not sure what property
means but the above is the query in the RowSource.

What is its Bound Column property? (1)

What is its ColumnWidth property? No setting

What is its Control Source? Unsure. This is an unbound combo box and the
only Source is the above query.

Try setting the ColumnCount to 2. If you want the user to see both the
vendorname and the vendor number with the combo dropped down, set the
ColumnWidths property to something like

1.0;0.5

or some other appropriate widths to display both fields. The combo when not
dropped down will display the first nonzero width field.

What is the PURPOSE of this combo? If you just want to display the vendor name
for the selected record's vendor number, don't use a combo at all; instead use
a textbox with a control source

=DLookUp("[Vendor_Name]", "VendorTbl", "[Vendor_Number] = " & [Vendor_Number])

 

[John W. Vinson]

I have tried DLookUp as Beetle suggested and each time the field reads
#Error. I have used the Access help and cannot seem to figure out why it does
not work. I have changed the table names (example from VENDOR_NUMBER to
VendorNumber) thinking that may make a difference but it does not. The
expression I have with the table column name updates looks like:

=DLookUp("[VendorName]","Vendortbl","[VendorNumber] = " & [VendorNumber])

And it still does not work.
What else can I check to make DLookUp work?

Just changing the fieldnames makes no sense. You need to have the fieldname in
the DLookUp exactly match the fieldname in the table. As far as Access is
concerned, the three strings "Vendor Number", "VendorNumber" and
"Vendor_Number" are just three completely different unrelated strings.

What is the name of your table?
What is the name of the vendor name field?
What is the name of the vendor number field?

 

[Don]

What is the name of your table? The name of the Vendor table is Vendortbl.
The name of the table benind the form is PONum

What is the name of the vendor name field? VendorName.
What is the name of the vendor number field? VendorNumber.

I understand and agree with what you are saying. However, all of the field
names match the names in the DLookUp expression. Each time I have done the
expression the names mached and it did not work. Out of frustration, I
changed the table's field names and the expression field names to match.
There is however a problem causing the expression not to work. Is there a
setting I am missing?

--
Thanks,

Dennis

I have tried DLookUp as Beetle suggested and each time the field reads
#Error. I have used the Access help and cannot seem to figure out why it does
not work. I have changed the table names (example from VENDOR_NUMBER to
VendorNumber) thinking that may make a difference but it does not. The
expression I have with the table column name updates looks like:

=DLookUp("[VendorName]","Vendortbl","[VendorNumber] = " & [VendorNumber])

And it still does not work.
What else can I check to make DLookUp work?

Just changing the fieldnames makes no sense. You need to have the fieldname in
the DLookUp exactly match the fieldname in the table. As far as Access is
concerned, the three strings "Vendor Number", "VendorNumber" and
"Vendor_Number" are just three completely different unrelated strings.

What is the name of your table?
What is the name of the vendor name field?
What is the name of the vendor number field?

 

[John W. Vinson]

What is the name of your table? The name of the Vendor table is Vendortbl.
The name of the table benind the form is PONum

What is the name of the vendor name field? VendorName.
What is the name of the vendor number field? VendorNumber.

I understand and agree with what you are saying. However, all of the field
names match the names in the DLookUp expression. Each time I have done the
expression the names mached and it did not work. Out of frustration, I
changed the table's field names and the expression field names to match.
There is however a problem causing the expression not to work. Is there a
setting I am missing?

Is there a VendorNumber field in PONum, or a VendorNumber control on the form?

 

[Don]

Is there a VendorNumber field in PONum, or a VendorNumber control on the form?

Yes both.
The table PONum has a field VendorNumber and the POEntry form has a control
with a control source of VendorNumber and the name of the control is
VendorNumber.

Reacting to your question it occurs to me that I may not be able to name
fields and controls the same name as Access may get confused. So I changed
the control name of the VendorNumber field on the form to VendorNum. I then
tried the following expression:

=DLookUp("[VendorName]","[Vendortbl]","[VendorNumber]=" & [VendorNum])

and received the same results (#Error). I studied the Help and the following
is the Help example:

=DLookup("[ContactName]","[Suppliers]","[SupplierID]=" &
Forms!Products!SupplierID)
Expression looks ok?

Not knowing what else to do I changed around the expression to:

=DLookUp("[VendorName]","[Vendortbl]","[VendorNum]=" & [VendorNumber])

This returned the vendor name. However, the same name remains in the field
for every record regardless of the VendorNumber.

 

[John W. Vinson]

Is there a VendorNumber field in PONum, or a VendorNumber control on the form?

Yes both.
The table PONum has a field VendorNumber and the POEntry form has a control
with a control source of VendorNumber and the name of the control is
VendorNumber.

Reacting to your question it occurs to me that I may not be able to name
fields and controls the same name as Access may get confused. So I changed
the control name of the VendorNumber field on the form to VendorNum. I then
tried the following expression:

=DLookUp("[VendorName]","[Vendortbl]","[VendorNumber]=" & [VendorNum])

That would be the correct expression.

and received the same results (#Error). I studied the Help and the following
is the Help example:

=DLookup("[ContactName]","[Suppliers]","[SupplierID]=" &
Forms!Products!SupplierID)
Expression looks ok?

Not knowing what else to do I changed around the expression to:

=DLookUp("[VendorName]","[Vendortbl]","[VendorNum]=" & [VendorNumber])

This returned the vendor name. However, the same name remains in the field
for every record regardless of the VendorNumber.

Unless there's ALSO a field named VendorNum in the table this should certainly
not work. My guess is that it's somehow returning True as a criterion.

Is VendorNumber in the table a Text field? If so you need syntactially
required quotemarks:

=DLookUp("[VendorName]","[Vendortbl]","[VendorNumber]='" & [VendorNum] & "'")

Exaggerated for readability (don't do it this way):

"[VendorNumber]=' " & [VendorNum] & " ' "

 

[Don]

John,

That was it! The field was a text field and needed the quotation marks.
--
Thanks,

Dennis

Is there a VendorNumber field in PONum, or a VendorNumber control on the form?

Yes both.
The table PONum has a field VendorNumber and the POEntry form has a control
with a control source of VendorNumber and the name of the control is
VendorNumber.

Reacting to your question it occurs to me that I may not be able to name
fields and controls the same name as Access may get confused. So I changed
the control name of the VendorNumber field on the form to VendorNum. I then
tried the following expression:

=DLookUp("[VendorName]","[Vendortbl]","[VendorNumber]=" & [VendorNum])

That would be the correct expression.

and received the same results (#Error). I studied the Help and the following
is the Help example:

=DLookup("[ContactName]","[Suppliers]","[SupplierID]=" &
Forms!Products!SupplierID)
Expression looks ok?

Not knowing what else to do I changed around the expression to:

=DLookUp("[VendorName]","[Vendortbl]","[VendorNum]=" & [VendorNumber])

This returned the vendor name. However, the same name remains in the field
for every record regardless of the VendorNumber.

Unless there's ALSO a field named VendorNum in the table this should certainly
not work. My guess is that it's somehow returning True as a criterion.

Is VendorNumber in the table a Text field? If so you need syntactially
required quotemarks:

=DLookUp("[VendorName]","[Vendortbl]","[VendorNumber]='" & [VendorNum] & "'")

Exaggerated for readability (don't do it this way):

"[VendorNumber]=' " & [VendorNum] & " ' "