Insert from listbox into subform

[djeeten]

Hi,

I have this main form frmOrders where you have a lisbox from which you can choose products. Once you have choosen a product you need to click on a button (also on main form) to confirm this and it should be displayed directly in the subform. All you need to do then is fill in an amount (of products) on the subform.

The main form is mainly based on the tables tblOrders(OrderId, date) and tblCustomers(customerid, name).

The subform is based on the tables tblOrderlines(productid) and tblProducts(productname).

These are the relationships:

tblCustomers 1-n tblOrders
tblOrders 1-n tblOrderlines
tblProducts 1-n tblOrderlines


Could someone please help me out a bit, as I have been messing up things a little. Should I put code behind the confirm button to insert a product into tblOrderlines, or not?


Thanks in advance,

dj.

 

[djeeten]

I have tried this:

Private Sub cmdPutProductInSubform_Click()
Dim con As Object
Dim stSql As String

Set con = Application.CurrentProject.Connection

stSql = "INSERT INTO tblOrderlines (OrderId, ProductId)" & _
" VALUES (" & Me.OrderId & ", " & cboID & &quot "

con.Execute (stSql)

sfrmOrders.SetFocus

End Sub
--------------------------

But then I get the message that no record can be inserted because a related record is required in tblOrders?

Plz help me,

dj.

 

[djeeten]

Hi,

It seems to work correctly this way:

Private Sub cmdPutProductInSubform_Click()
Dim con As Object
Dim stSql As String

Set con = Application.CurrentProject.Connection

stSql = "INSERT INTO tblOrderlines (OrderId, ProductId)" & _
" VALUES (" & Me.OrderId & ", " & cboID & &quot "

Me.Refresh

con.Execute (stSql)

sfrmOrders.Requery
sfrmOrders.SetFocus

End Sub
------------------------------

As you can see, I put the focus on the subform, as the customer has to fill in an amount of products he wants to order. The focus is put on the textfield "AskedAmount" (as its the only field that can may be updated and is reached by using tab).

My next problem now is that the focus is always on the first textfield "AskedAmount", while the focus should be on the field "AskedAmount" of the product that just has been choosen.

Thanks in advance,

dj.