Go Back   Access World Forums > Microsoft Access Discussion > Tables

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 11-23-2012, 03:25 PM   #1
effebruno
Newly Registered User
 
Join Date: Nov 2012
Posts: 3
Thanks: 2
Thanked 0 Times in 0 Posts
effebruno is on a distinguished road
lookup field using another multi value lookup field as data source

Hi,
I cannot realize how to do a particular thing in Access 2010 (I don't even know if it is possible), and I hope in someone's help or advice.

I have a table named PRODUCTS:
ID_PRODUCT (primary key, autonumber long integer)
...
...
ALLOWED_OPTIONS (multi value text lookup field: "Option 1";"Option 2";...;"Option 9")

So I can store, for each different product, none, one, or more options to let the customers choose from.



I have a table named ORDERS:
ID_ORDER (primary key, autonumber long integer)
...
...
FK_CUSTOMER (foreign key, linked to the primary key of a CUSTOMERS table; represents the customer that places the order.)
...
...
FK_PRODUCT (foreign key, linked to PRODUCTS.ID_PRODUCT; represents the product that the customer has choosen)
...
CHOOSEN_OPTION (lookup text field; the customer must choose ONE option among those allowed for the product he has ordered)


The problem is that I would like the CHOOSEN_OPTION field to show as a combobox, listing the values stored into PRODUCTS.ALLOWED_OPTIONS, so that when a customer buys a product, he can choose only among the options allowed by that particular product.

How can I manage a multi value field to populate a combobox, in which every item stays on its line?

If I use, as a query to populate the combobox:

select [PRODUCTS].[ALLOWED_OPTIONS]
from PRODUCTS
where [PRODUCTS].[ID_PRODUCT]=[FK_PRODUCT]

I obtain an empty combobox.
If I refer to the last field as [ORDERS].[FK_PRODUCT], Access asks me to type a value for "[ORDERS].[FK_PRODUCT]", treating it as an unknown parameter.

I think that the problem is that when the combobox expands, the record is not committed yet, so FK_PRODUCT is unknown (NULL?). But this happens even if I commit the record typing something in FK_PRODUCT and then I re-enter the record and I expand the CHOOSEN_OPTION combobox, that is still empy although FK_PRODUCT exists, now.
Is there a particular syntax to refer to a field in a record not committed yet (something like "THIS." or "ME.")?


Thank you for your support and help! :)

Regards,

Filippo

effebruno is offline   Reply With Quote
Old 11-24-2012, 04:01 AM   #2
RainLover
VIP From a land downunder
 
RainLover's Avatar
 
Join Date: Jan 2009
Location: Qld Australia
Posts: 5,044
Thanks: 28
Thanked 517 Times in 492 Posts
RainLover will become famous soon enough
Re: lookup field using another multi value lookup field as data source

Formatting in Table Design View is a bad practice. So get rid of all that except for the Data Type.
Create a Table as a lookup. This table would contain a PK (AutoNumber) and another Field for the Type of thing you want to find in your Combo Box. In This case it would be Product.
The Main Table has a Field (LONG) which is the FK to the Products Table.
You store in it the PK which is a Number.
Create a simple Form using the Main Table as the Record Source. Include the field that is the FK to the Products Table.
The Control that is the FK should be converted to a Combo Box. The Row Source should be the Products Table.
So you select the Product's PK and store that in the Control Source.
If you can't work this out then make a very small DB and send it to me. It should have Two Tables and one Form. Don't forget to put some dummy data in it.
Before you post convert to Access 2003. I don't use 2007 or 2010.
__________________
Regards Rain.

Windows 7. Access 2003. East Coast Australia. GMT +10,

RainLover is offline   Reply With Quote
Old 11-24-2012, 01:37 PM   #3
effebruno
Newly Registered User
 
Join Date: Nov 2012
Posts: 3
Thanks: 2
Thanked 0 Times in 0 Posts
effebruno is on a distinguished road
Re: lookup field using another multi value lookup field as data source

Hi RainLover, and thank you for your quick reply!
I appreciated very much your advice but I am trying to figure out Access capabilities with lookup fields and so on.

I know I can (better: I should) use a 3rd table to make "many-to-many" associations between tables, but in my case I am not trying to link customers with products, but customers with purchasing options, depending on the product they have choosen. Example:

product#1 can be purchased using Visa, Paypal and Check
product#2 can only be purchased using Visa
product#3 ....

So I already use a table (ORDERS) to link customers with products (using as FK their own PKs) but I have also tried to use a multi value field in PRODUCTS (ALLOWED_OPTIONS) so that, when a customer place an order, he can choose only among the options flagged for that particular product.

Following your advice, I should create a 4th table:
PAYMENT_OPTIONS:
ID_P_OPTION (PK, autonumber)
DESCRIPTION (Text)

containing:

1 Visa
2 American Express
3 PayPal
4 ...

Then I would need a 5th table:
PRODUCTS_OPTIONS:
PRODUCT_CODE (FK, long integer)
OPTION_CODE (FK, long integer)
(PK on the two field as a pair)

So, if product#7 can be purchased using Visa and PayPal, the former table would contain:
7 1
7 3

The question remains quite the same: in the Form used to let a customer choose which product he wants to purchase, how to populate the combobox with the payment options, basing on the product choosen in that form, but not yet committed? Should I use some VBA event like "onExit" on the products combobox to reload the payment options combobox's items when the users changes the product? Or is there a simpler way? I just don't know what happens behind the scenes when the user types something into a field and goes to the next one without leaving the record yet. Is that value accessible and readable?

At work I use Oracle, and I know its triggers fire on events happening on the entire record (BeforeUpdate, AfterDelete,...). Access seems to me to mix Database and Application stuff, so for example it can trigger a validation routine as soon as the user leaves a field pressing TAB (Validation Rule field property). I am so confused!

If I am forced to rely on some VBA code to load the payment options items as soon as the user changes the order product, I could think it would be better to avoid creating 2 more tables just to represent 4 or 5 choices: I would parse the semicolon separated string ("Visa";"Am.Express";...) and split the values...

I hope someone could hep me to understand Access' logic...

Thank you again,

Filippo

effebruno is offline   Reply With Quote
Old 11-25-2012, 02:31 AM   #4
RainLover
VIP From a land downunder
 
RainLover's Avatar
 
Join Date: Jan 2009
Location: Qld Australia
Posts: 5,044
Thanks: 28
Thanked 517 Times in 492 Posts
RainLover will become famous soon enough
Re: lookup field using another multi value lookup field as data source

Filippo

I don't know what you do or don't know.

What exactly do you want this Combo to select for you.

Do you want it to select both an Item plus a payment method at the same time.

Or do you want just a Item and then have another combo to select the payment method, With this second method the payment method been restricted based upon the first Combo's selection. This is known as Cascading Combo Boxes. You will be able to Google that with lots of results.
__________________
Regards Rain.

Windows 7. Access 2003. East Coast Australia. GMT +10,

RainLover is offline   Reply With Quote
The Following User Says Thank You to RainLover For This Useful Post:
effebruno (11-25-2012)
Old 11-25-2012, 05:13 AM   #5
effebruno
Newly Registered User
 
Join Date: Nov 2012
Posts: 3
Thanks: 2
Thanked 0 Times in 0 Posts
effebruno is on a distinguished road
Re: lookup field using another multi value lookup field as data source

Hi RainLover,

Thank you very much for your reply! Now that I know the precise terminology of what I was looking for (cascading comboboxes) I've googled around and I found the solution. Quite everyone uses an afterUpdate() event on the 1st combo to generate in runtime the SQL code to populate the 2nd combobox...

Thank you again for your patience and for replying to me even on sunday!

Filippo
effebruno is offline   Reply With Quote
Old 11-25-2012, 06:23 PM   #6
RainLover
VIP From a land downunder
 
RainLover's Avatar
 
Join Date: Jan 2009
Location: Qld Australia
Posts: 5,044
Thanks: 28
Thanked 517 Times in 492 Posts
RainLover will become famous soon enough
Re: lookup field using another multi value lookup field as data source

If you go to the link in my signature there is a sample Database that was written by Pat Hartman.

Download that, it may help.

Looks like you now understand why we ask so many questions. We need to find out what you really want as opposed to what you say you want.

Please keep in touch. You appear to be very close now.

__________________
Regards Rain.

Windows 7. Access 2003. East Coast Australia. GMT +10,

RainLover is offline   Reply With Quote
Reply

Tags
lookup multi valued

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Creating a Lookup Field that Displays Data based on Other Field's Selection spora Tables 5 01-11-2013 12:05 AM
Lookup Field based on another Lookup Field's selection mike_esn Forms 3 07-05-2012 12:28 AM
SQL query to return lookup of a lookup field StevenBee Forms 5 01-08-2011 02:27 AM
ComboBox Serving as Lookup field w/o using Lookup Field shocka Forms 2 08-30-2007 04:53 PM
data from lookup field in vba jplavalle Modules & VBA 2 05-28-2007 11:12 PM




All times are GMT -8. The time now is 01:05 PM.


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