Linkd a field from other table to a form

[megatronixs]

Hi all,

I'm creating a form for orders. In this form I use a combo box to select a product from a table called "Products". In this table there is also a second column with the "PricePerUnit"
How can I insert the PricePerUnit from the table Products into the table Orders when I select a product with the combo box?

Would be great if I could have this one solved.

Greeting and thank you in advance.

 

[bob fitz]

Change the SQL statement for the combo box to include the Price. Make the price column hidden by adjusting the Column Width and Column Count properties. Put some code in the After Update event of the combo box to set the Price control on the form. Something like:
Me.PriceControl = Me.ActiveControl.Column(2)
The 2 refers to the hidden column of the combo box which has the price details. Remember though, that the column count starts with 0. So first column is 0, second is 1, third is 2 and so on.

 

[megatronixs]

Hi Bob Fitz,

The first part I got, but the second part I'm lost.
Me.PriceControl (is this the name of the field on the other table?)
= Me.ActiveControl.Column(2) (is this the field in the table Orders?)

Mabye by explaining how things are, it wil be better for you to know which code to use.

The table with the price and product name is: Products. The table wher the product name and price per unit will be is: tbl_Orders.
The table Products has 2 columns: ProductName and PricePerUnit
the table tbl_Orders has ProductName and PricePerProcut in it that are the interesting ones.
Can you please let me know how to adjust the code better?

Greetings.

 

[bob fitz]

If "PricePerProcut" is the name of the control on your form that is bound to the field in the "Orders" table try:
Me.PricePerProcut = Me.ActiveControl.Column(1)
Note that I have change the column reference from 2 to 1. Since the "Products" only has two columns I assume the first column will be "ProductName" and the second will be "PricePerUnit".
Set the column widths property of the combo to 1.0;0
Set the column count property of the combo to 2
The control on the form called "PricePerProcut" should be automatically updated each time the combo selection is changed.
EDIT:
PS
I am assuming that once the price has been set for the product in this order it should remain the same, even if the price stored in the Products table changes at some time in the future. If that is not the case, you would need to go about this differently.

 

[megatronixs]

Hi Bob Fitz,

I see I messed up things.

I just forgot to say that the table orders is used as a sub form in the form Client,
I created a new form based on the table Orders and use it to enter the orders and from there using the combo box to select the product and having the price directly in the form, so once finishing the adding orders, they would be visible in the table orders and on their side also in the sub form that is in the client form.
So, sorry for my previous messy text.
Some how, can I use the above code in a different way?

Greetings.

 

[megatronixs]

HI,
I just need to change some things before I come with a solution.
I see that I also need to say to the database that when I press the button to insert new order, it should remember the ID from client and then use this as a link to store the information in the orders table that is related with the client table via the ID nr.
As soon as I have a different setup, I will ask again.

Thanks in the mean time ;-)

 

[megatronixs]

Hi all,

I made some changes and get some crazy idea. I include the database so you can have a look to what I mean. I have a combo box with below a button (in the client form). The idea is that I will select a product and when I press the button it will automatically add it to the orders of the client.

Any idea how to get it solved? I have no clue which direction I have to go to figure out this probably simple idea.

Greetings