Lookup field returns wrong value

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a form to create service orders for our repair dept. There are 6 combo
box's to enter part numbers and prices used in the repair of each product.
Part numbers, descriptions, prices, etc come from a separate "PART_2" table.
As part numbers are unique, that field is set as primary key. The lookup
field is set to enter part number and price. This works great for most parts,
but not for those parts that have the same price as some other parts.

When I enter a part number or select one from the list, the lookup field
changes my entry to the first part number on the alphabetical list with the
same price as the one I selected.

I have the AutoCorrect set to "No"

This is the entry for "Row Source" on the Part Number Combo Box:
SELECT [PART_2].PRICE, [PART_2].ID FROM [PART_2] ORDER BY [ID];

Everything I try to monkey with either has no effect or makes it worse and I
have spent way too many hours on this already. Would greatly appreciate help!
 
It can happen if the combo is bounded to the price field (the first column),
so you actually selecting a price instead of an item, so the combo jump to
the first price record that has that price.
If that the case bound the combo to the second column.
Set the BoundColumn Property to 2
 
Changing Bound Column to 2 and the "Control Source" for the Part Number field
to Part Number instead of Price will yield the correct part number, but now
it gives no price in the next field.

Ofer said:
It can happen if the combo is bounded to the price field (the first column),
so you actually selecting a price instead of an item, so the combo jump to
the first price record that has that price.
If that the case bound the combo to the second column.
Set the BoundColumn Property to 2

--
\\// Live Long and Prosper \\//


Denise said:
I have a form to create service orders for our repair dept. There are 6 combo
box's to enter part numbers and prices used in the repair of each product.
Part numbers, descriptions, prices, etc come from a separate "PART_2" table.
As part numbers are unique, that field is set as primary key. The lookup
field is set to enter part number and price. This works great for most parts,
but not for those parts that have the same price as some other parts.

When I enter a part number or select one from the list, the lookup field
changes my entry to the first part number on the alphabetical list with the
same price as the one I selected.

I have the AutoCorrect set to "No"

This is the entry for "Row Source" on the Part Number Combo Box:
SELECT [PART_2].PRICE, [PART_2].ID FROM [PART_2] ORDER BY [ID];

Everything I try to monkey with either has no effect or makes it worse and I
have spent way too many hours on this already. Would greatly appreciate help!
Click to expand...
Click to expand...
 
How did you assign the price to the next field?

On the after update event of the combo, write
Me.PriceFieldName = Me.ComboName

--
\\// Live Long and Prosper \\//


Denise said:
Changing Bound Column to 2 and the "Control Source" for the Part Number field
to Part Number instead of Price will yield the correct part number, but now
it gives no price in the next field.

Ofer said:
It can happen if the combo is bounded to the price field (the first column),
so you actually selecting a price instead of an item, so the combo jump to
the first price record that has that price.
If that the case bound the combo to the second column.
Set the BoundColumn Property to 2

--
\\// Live Long and Prosper \\//


Denise said:
I have a form to create service orders for our repair dept. There are 6 combo
box's to enter part numbers and prices used in the repair of each product.
Part numbers, descriptions, prices, etc come from a separate "PART_2" table.
As part numbers are unique, that field is set as primary key. The lookup
field is set to enter part number and price. This works great for most parts,
but not for those parts that have the same price as some other parts.

When I enter a part number or select one from the list, the lookup field
changes my entry to the first part number on the alphabetical list with the
same price as the one I selected.

I have the AutoCorrect set to "No"

This is the entry for "Row Source" on the Part Number Combo Box:
SELECT [PART_2].PRICE, [PART_2].ID FROM [PART_2] ORDER BY [ID];

Everything I try to monkey with either has no effect or makes it worse and I
have spent way too many hours on this already. Would greatly appreciate help!
Click to expand...
Click to expand...
Click to expand...
 
I'm kinda new at this so I used the Wizard to create the Combo box. When the
Control Source was set to Price and the Bound Column was set to 1, the price
was entered in the second field but that was causing the problem with the
default price. I tried your suggestion for the "after update" entry and now I
get an error that the macro Me does not exist.

Ofer said:
How did you assign the price to the next field?

On the after update event of the combo, write
Me.PriceFieldName = Me.ComboName

--
\\// Live Long and Prosper \\//


Denise said:
Changing Bound Column to 2 and the "Control Source" for the Part Number field
to Part Number instead of Price will yield the correct part number, but now
it gives no price in the next field.

Ofer said:
It can happen if the combo is bounded to the price field (the first column),
so you actually selecting a price instead of an item, so the combo jump to
the first price record that has that price.
If that the case bound the combo to the second column.
Set the BoundColumn Property to 2

--
\\// Live Long and Prosper \\//


:

I have a form to create service orders for our repair dept. There are 6 combo
box's to enter part numbers and prices used in the repair of each product.
Part numbers, descriptions, prices, etc come from a separate "PART_2" table.
As part numbers are unique, that field is set as primary key. The lookup
field is set to enter part number and price. This works great for most parts,
but not for those parts that have the same price as some other parts.

When I enter a part number or select one from the list, the lookup field
changes my entry to the first part number on the alphabetical list with the
same price as the one I selected.

I have the AutoCorrect set to "No"

This is the entry for "Row Source" on the Part Number Combo Box:
SELECT [PART_2].PRICE, [PART_2].ID FROM [PART_2] ORDER BY [ID];

Everything I try to monkey with either has no effect or makes it worse and I
have spent way too many hours on this already. Would greatly appreciate help!
Click to expand...
Click to expand...
Click to expand...
 
When you locate the cursor on the after update property of the combo, on the
right there is a button with three dots, press the button, and select code
view.
Enter the code there, between the

Sub
' code
End Sub

--
\\// Live Long and Prosper \\//
BS"D


Denise said:
I'm kinda new at this so I used the Wizard to create the Combo box. When the
Control Source was set to Price and the Bound Column was set to 1, the price
was entered in the second field but that was causing the problem with the
default price. I tried your suggestion for the "after update" entry and now I
get an error that the macro Me does not exist.

Ofer said:
How did you assign the price to the next field?

On the after update event of the combo, write
Me.PriceFieldName = Me.ComboName

--
\\// Live Long and Prosper \\//


Denise said:
Changing Bound Column to 2 and the "Control Source" for the Part Number field
to Part Number instead of Price will yield the correct part number, but now
it gives no price in the next field.

:

It can happen if the combo is bounded to the price field (the first column),
so you actually selecting a price instead of an item, so the combo jump to
the first price record that has that price.
If that the case bound the combo to the second column.
Set the BoundColumn Property to 2

--
\\// Live Long and Prosper \\//


:

I have a form to create service orders for our repair dept. There are 6 combo
box's to enter part numbers and prices used in the repair of each product.
Part numbers, descriptions, prices, etc come from a separate "PART_2" table.
As part numbers are unique, that field is set as primary key. The lookup
field is set to enter part number and price. This works great for most parts,
but not for those parts that have the same price as some other parts.

When I enter a part number or select one from the list, the lookup field
changes my entry to the first part number on the alphabetical list with the
same price as the one I selected.

I have the AutoCorrect set to "No"

This is the entry for "Row Source" on the Part Number Combo Box:
SELECT [PART_2].PRICE, [PART_2].ID FROM [PART_2] ORDER BY [ID];

Everything I try to monkey with either has no effect or makes it worse and I
have spent way too many hours on this already. Would greatly appreciate help!
Click to expand...
Click to expand...
Click to expand...
 
Finally got back to this project today and got everything to enter without
errors, and it works great. Thank you verrrrry much!!
-Denise

Ofer said:
How did you assign the price to the next field?

On the after update event of the combo, write
Me.PriceFieldName = Me.ComboName

--
\\// Live Long and Prosper \\//


Denise said:
Changing Bound Column to 2 and the "Control Source" for the Part Number field
to Part Number instead of Price will yield the correct part number, but now
it gives no price in the next field.

Ofer said:
It can happen if the combo is bounded to the price field (the first column),
so you actually selecting a price instead of an item, so the combo jump to
the first price record that has that price.
If that the case bound the combo to the second column.
Set the BoundColumn Property to 2

--
\\// Live Long and Prosper \\//


:

I have a form to create service orders for our repair dept. There are 6 combo
box's to enter part numbers and prices used in the repair of each product.
Part numbers, descriptions, prices, etc come from a separate "PART_2" table.
As part numbers are unique, that field is set as primary key. The lookup
field is set to enter part number and price. This works great for most parts,
but not for those parts that have the same price as some other parts.

When I enter a part number or select one from the list, the lookup field
changes my entry to the first part number on the alphabetical list with the
same price as the one I selected.

I have the AutoCorrect set to "No"

This is the entry for "Row Source" on the Part Number Combo Box:
SELECT [PART_2].PRICE, [PART_2].ID FROM [PART_2] ORDER BY [ID];

Everything I try to monkey with either has no effect or makes it worse and I
have spent way too many hours on this already. Would greatly appreciate help!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top