How can I make changes on subform by clicking button on main form?

[djeeten]

Hi,

A brief description of what I'm doing: I'm creating this little program that enables people to place an order using a main form frmOrder(containing OrderId, CustomerId,...) and a subform sfrmOrder(where the customer can choose several products he wants to order and give the amount). The main form is based on the tables tblOrders & tblCustomer, while the subform is based on the tables tblProducts & tblOrderlines.

Is there a way to update several fields at the same time on a subform when clicking on a button on the main form? If a customer orders a certain amount of a product the amount in suppply of that product should be reduced after confirming the order.

This is the code so far:
-------------------
Private Sub cmdOrder_Click()

Dim con As Object
Dim stSql As String
Dim rs As Object

Set con = Application.CurrentProject.Connection

stSql = "UPDATE tblProducts INNER JOIN tblOrderlines ON tblProducts.ProductId = tblOrderlines.ProductId SET AmountInSupply = (AmountInSupply - tblOrderlines.AskedAmount) WHERE tblOrderlines.OrderId =" & Me!OrderId

Set rs = CreateObject("ADODB.Recordset&quot
rs.Open stSql, con, 1 ' 1 = adOpenKeyset

--------------------------

This does not work however, as there's an 1-n query, an update is not possible...

I would be delighted if someone could help me!

dj.

 

[Zathras]

An UPDATE can only be performed on a single table.

You should be able to code a loop in a module to accomplish what you need.

 

[djeeten]

Hi,

I changed this piece of my code:
----------------------------------------
Set rs = CreateObject("ADODB.Recordset&quot
rs.Open stSql, con, 1 ' 1 = adOpenKeyset
----------------------------------------

Into this:
----------------------------------------
con.Execute(stSql)
----------------------------------------

and now its does the job...

I have no idea why, but honestly I don't care ;-)

Thanks for your help,

dj.