Form value shows different value than table

[Kev_1988]

Hi

I've create a form "Test Search" with 6 different search bars so then I can search very specific. If you click on SEARCH button then you will direct to another form with overall overview of products.
The problem is under column "Supplier" it only shows numbers 1 till 5 random.
But if you open the table "tblProduct" you'll see under column "Supplier" instead of numbers different supplier names...
How can I change vba code on SEARCH button in the "Test Search" form? on click?
How can I change the code so that it's show the names of the suppliers instead of the numbers??

I'll add my file so you can see it for yourself.
Open form "Test Search" > click SEARCH button > see column Supplier, all numbers
Open table "tblProduct" > see column Supplier, all names

Note: I'm a beginner with Access and VBA please be patient with me

Please help me out..
Thanks a lot

 

[Tieval]

You have set the supplier as a look-up field in the table so it is displayed as the value of the number in the other table.

You would be better just storing the number and relating it to the other table. When you then use your search form you can query the supplier name against the number.

Tip one; never use look-up values.

 

[Kev_1988]

Can you lead me step by step what I have to do?

You have set the supplier as a look-up field in the table so it is displayed as the value of the number in the other table.

You would be better just storing the number and relating it to the other table. When you then use your search form you can query the supplier name against the number.

Tip one; never use look-up values.

 

[Minty]

Create a separate table with a AutoNumber for the SupplierID as the primary key. Then use that as the source for your suppliers and store the SupplierID in your products table as a foreign key.

http://access.mvps.org/access/lookupfields.htm

 

[Kev_1988]

I have already created a separated table with SupplierID as primary key see "tblSupplierType" If you mean that?
And then I gave Supplier in tblProduct row source like: SELECT [tblSuppliertype].[supplierID], [tblsuppliertype].[suppliertype] FROM tblsuppliertype ORDER BY [SupplierType];
So this convert all the numbers under column Supplier to text.
But If I open the "frmProduct_DS" then you only see numbers instead of text.
I tried to do it another way from table > query > form
But in the end if I want to search with a search form "Test Search" and I type for example Supplier name: DOS then it gives me zero results. But if I type number 3 gives me 8 results. (3 = DOS)
Instead of typing numbers I want names how can I do that?

Create a separate table with a AutoNumber for the SupplierID as the primary key. Then use that as the source for your suppliers and store the SupplierID in your products table as a foreign key.

http://access.mvps.org/access/lookupfields.htm

 

[Minty]

Don't set the lookup value in the table, it leads to all sorts of issues exactly as you are experiencing.

The normal route to solve your form issue is to either - bring the supplier name in as part of the forms underlying query, or more commonly make the SupplierID control on the form a combo with the ID as the bound column but hidden, and the name as the next column. That way you display the supplier name but search for the ID from your search combo which is based the same way.

 

[Kev_1988]

Oke that sounds a solution.
Can you tell me step by step how to do it?
So you telling me I have to deleted the row source code? (aka lookup?)
So instead of the numbers i've to fill in like text?
Im just a beginner of Access and VBA so please be patient with me

Don't set the lookup value in the table, it leads to all sorts of issues exactly as you are experiencing.

The normal route to solve your form issue is to either - bring the supplier name in as part of the forms underlying query, or more commonly make the SupplierID control on the form a combo with the ID as the bound column but hidden, and the name as the next column. That way you display the supplier name but search for the ID from your search combo which is based the same way.

 

[Minty]

Have a look a the attached simple DB and the orders form. There is a combo box control for the customer that displays the customer name, even though that is stored as a number in the Orders table.

Look at the column widths and the record source (which is the same as a query) for the combo box. That should get you started.