Order Form referencing two fields in table (1 Viewer)

VegMan

New member
Local time
Today, 01:40
Joined
Nov 19, 2018
Messages
6
Hello,

I have am setting up a new database to track orders.

I have a table containing the product name and also a SKU. When placing an order I want the user to be able to select either a product or a SKU from the list and the other field to be automatically populated with the corresponding product or SKU. At the minute I select a SKU and the product field stays empty.

How can I do this ?


Thanks
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 18:40
Joined
Oct 29, 2018
Messages
21,358
Hi. Can you show us your table structure? Thanks.
 

Mark_

Longboard on the internet
Local time
Yesterday, 18:40
Joined
Sep 12, 2017
Messages
2,111
For myself, I'd have this done with two unbound comboboxes; one for product name and the other for SKU. BOTH would function the same by looking up the ProductID (Unique, auto numbered record identifier in your product table) and filling that in. You would then default both product name and SKU when viewing the record, but not actually save them when entering in a sale.
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 21:40
Joined
Feb 19, 2002
Messages
42,981
Since this is data being saved, you can have two combos bound to a single field. The control will be bound to the ProductID which is the unique identifier of a product (unless SKU is your unique identifier). Then one combo uses a query that selects:
Select ProductID, ProductName, SKU From yourtable Order by ProductName
and the other is:
Select ProductID, SKU, ProductName From yourtable Order by SKU

Make sure the column count is 3 and the bound column is 1 and the column widths are:
0, 1, 1

so ProductID is hidden but both lists show both the name and the sku.

When you pick a value using one combo, the other shows the same result.
NO CODE is required.

When you have a form where you want to search by two values, THOSE combos would be unbound but I think we are talking here about a data entry form.
 
Last edited:

Users who are viewing this thread

Top Bottom