Go Back   Access World Forums > Microsoft Access Discussion > Forms

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 08-15-2019, 05:25 AM   #1
VegMan
Newly Registered User
 
Join Date: Nov 2018
Posts: 6
Thanks: 2
Thanked 0 Times in 0 Posts
VegMan is on a distinguished road
Order Form referencing two fields in table

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

VegMan is offline   Reply With Quote
Old 08-15-2019, 05:33 AM   #2
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 6,336
Thanks: 158
Thanked 1,699 Times in 1,670 Posts
Minty is a jewel in the rough Minty is a jewel in the rough Minty is a jewel in the rough
Re: Order Form referencing two fields in table

This is known as cascading combo's or filling values and is a common topic.

This is a good introduction either http://www.baldyweb.com/Autofill.htm or http://www.baldyweb.com/CascadingCombo.htm

You may need to implement this twice one for the SKU one for the Product in order to give you the functionality you require.
__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is offline   Reply With Quote
Old 08-15-2019, 07:22 AM   #3
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 3,631
Thanks: 38
Thanked 885 Times in 868 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Order Form referencing two fields in table

Hi. Can you show us your table structure? Thanks.

__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 08-15-2019, 09:15 AM   #4
Mark_
Longboard on the internet
 
Join Date: Sep 2017
Location: Not the middle of no where, but I can see the road to it from my house.
Posts: 1,945
Thanks: 19
Thanked 370 Times in 363 Posts
Mark_ will become famous soon enough Mark_ will become famous soon enough
Re: Order Form referencing two fields in table

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.
Mark_ is offline   Reply With Quote
Old 08-15-2019, 09:34 AM   #5
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 27,808
Thanks: 13
Thanked 1,510 Times in 1,436 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: Order Form referencing two fields in table

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.

__________________
Bridge Players Still Know All the Tricks

Last edited by Pat Hartman; 08-15-2019 at 09:47 AM.
Pat Hartman is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
[SOLVED] Public Function With Variables Referencing Table Date Fields, Access 2010 krasniusha Modules & VBA 5 06-02-2014 05:54 AM
Referencing a fields from a form, from within a subform MrSpoon Forms 3 03-09-2012 08:51 AM
Referencing Form Fields karl009 Modules & VBA 3 02-23-2012 10:19 AM
How do I order the fields in my table? Janet Tables 1 06-09-2011 08:02 AM
Referencing form fields in Queries haywardi SQL Server 0 05-26-2011 12:40 AM




All times are GMT -8. The time now is 01:55 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World