In a combo box I need to get more than one value from the query

[Schultzy]

Hello!
I will try to explain the problem clearly
I am creating a database for my business (restaurant)
I have a table and a form called ‘suppliers’ which work perfecty well
The second table and form is called ‘products’
The fields are: ProductCode
Name
ProductType
TaxesType
A third table called ‘TaxPercentage’ consists of two fields:
TaxIndex
Percentage
A fourth table and form is called ‘Invoice’
The form ‘Invoice’ has a subform called ‘ProductInput’
And it is in this subform that I have the problem
It consists of the following fields:
Code
InvoiceCode
ProductCode
etc, etc
I use a combo box to get the name of the product
I set ‘Row source’ to a query that by means of ‘Name’ I get
The ‘ProductCode’
I set ‘Control source’ to ‘ProductCode’
But I should also get ‘ProductType’ and ‘TaxesTypes’ to get the percentage
from the ‘TaxPercentage’ table and put them in ‘ProductInput’ fields

I am sorry this seems to be badly expressed
Making a summary of it all I asked these questions:
In a combo box, by means of a query, how can I get the other fields of the table?
(I only get one)

If I have the value of a table field (say the code of a product)
How can I get other field values and with these get other values from
other tables?
Can this be achieve easily or should I do it with Visual basic?
Thanks for your time!

Joey

[willakawill]

Hello!
I will try to explain the problem clearly
I am creating a database for my business (restaurant)
I have a table and a form called ‘suppliers’ which work perfecty well
The second table and form is called ‘products’
The fields are: ProductCode
Name
ProductType
TaxesType
A third table called ‘TaxPercentage’ consists of two fields:
TaxIndex
Percentage
A fourth table and form is called ‘Invoice’
The form ‘Invoice’ has a subform called ‘ProductInput’
And it is in this subform that I have the problem
It consists of the following fields:
Code
InvoiceCode
ProductCode
etc, etc
I use a combo box to get the name of the product
I set ‘Row source’ to a query that by means of ‘Name’ I get
The ‘ProductCode’
I set ‘Control source’ to ‘ProductCode’
But I should also get ‘ProductType’ and ‘TaxesTypes’ to get the percentage
from the ‘TaxPercentage’ table and put them in ‘ProductInput’ fields

I am sorry this seems to be badly expressed
Making a summary of it all I asked these questions:
In a combo box, by means of a query, how can I get the other fields of the table?
(I only get one)

If I have the value of a table field (say the code of a product)
How can I get other field values and with these get other values from
other tables?
Can this be achieve easily or should I do it with Visual basic?
Thanks for your time!

Joey

Hi Joey,
One way of doing this is to concatenate the values from your query into a single string value with a field name alias of your choice and set the control source to that field name, in this case ProdDetails:

SELECT ProductCode & ", " & ProductType & ", " & TaxesType As ProdDetails
FROM Product
 

[Schultzy]

Hi Joey,
One way of doing this is to concatenate the values from your query into a single string value with a field name alias of your choice and set the control source to that field name, in this case ProdDetails:

SELECT ProductCode & ", " & ProductType & ", " & TaxesType As ProdDetails
FROM Product
 

Thanks for helping but I think I have not explained the problem very well

Now the other two fields are showing in the combo control but what I need
is to use the other fields of the record I choose to get access to other
tables.
Supose I have two products:
Wine and Milk
When I choose milk I need the taxesPercentage to go to the other table,
extract the value and place it in ProductInput table

It seems the more I write the more complicated it is
I am sorry

[willakawill]

Thanks for helping but I think I have not explained the problem very well

Now the other two fields are showing in the combo control but what I need
is to use the other fields of the record I choose to get access to other
tables.
Supose I have two products:
Wine and Milk
When I choose milk I need the taxesPercentage to go to the other table,
extract the value and place it in ProductInput table

It seems the more I write the more complicated it is
I am sorry

Try this or some variation

Dim ar As Variant
 
ar = Split(Combo1.Text, " ")
 
'blah blah blah
 
...WHERE Field1 = '" & ar(0) & "'"
 
...WHERE Field2 = " & CInt(ar(1))
 

[Schultzy]

I am sorry but I do not understand!
Maybe I have not explained the problem very well
This is an explanation that could be clearer:


I have two tables:

The first table is called:
Products
it has the following fields
ProdName
ProdId
ProdType
ProdTaxes

The second table is called:
ProductInput
with the following fields
ProductId
Quantity
Price
TaxPercentage
...and others...

I have made a form based on table 'ProductInput'
I use a combo to get the ProdId (Products table) by means of
ProdName (Products table) and I put it in ProductId (ProductInput table)

My question is:
How can I get the corresponding record in the table 'Products' using
ProductId (ProductInput table), to extract the other fields (like ProdType
and ProdTaxes -Product table-) and put them in the fields TaxPercentage,
Price, etc of ProductInput table.

Thanks for your time!

[willakawill]

I am sorry but I do not understand!
Maybe I have not explained the problem very well
This is an explanation that could be clearer:


I have two tables:

The first table is called:
Products
it has the following fields
ProdName
ProdId
ProdType
ProdTaxes

The second table is called:
ProductInput
with the following fields
ProductId
Quantity
Price
TaxPercentage
...and others...

I have made a form based on table 'ProductInput'
I use a combo to get the ProdId (Products table) by means of
ProdName (Products table) and I put it in ProductId (ProductInput table)

My question is:
How can I get the corresponding record in the table 'Products' using
ProductId (ProductInput table), to extract the other fields (like ProdType
and ProdTaxes -Product table-) and put them in the fields TaxPercentage,
Price, etc of ProductInput table.

Thanks for your time!

I assume you are using bound controls and want Access to fetch the fields provided that you set things up correctly.

I don't know how to do that. I do know how to write the code that will do it. It may be that you are at the crossing over point where you decide that you want to start writing the code yourself. This would be a good project to learn on.

You can use the ProductID to query either table.

[Schultzy]

Hi!
I have started to read a book about VBA
I think I need it!
Thanks for your help!