Form and subform problems on new record

[ailyn]

I have a form for making orders that contains a subform for the details of the order where I can chose several products for the same order. My problem is that whenever I try making a new order in that form they give me an error because Jet db cannot find matching Ids. Of course not, It's a new record for both forms/tables.
How could I solve this problem?

 

[lupins46]

Are you trying to add records to the subform before you have set up the order header?

 

[ailyn]

yes. I have a form from where I load the selected product into the subform and then I set the mainform data.

 

[lupins46]

Then you have to look at your design.
The subform cannot work correctly until it knows what value to put into its linking field. It gets that from the main form recordsource, so the main form entry must come first.

 

[ailyn]

How could I set that? In the products form when I order a products this works:

DoCmd.OpenForm "NordersMaken", , , , acFormAdd
Forms!NordersMaken.NordersDetails!Serial = Me.ProductId
DoCmd.Close acForm, "Products"

But then when I try working in the main form I get an error. I thought the previous code would set the new record on the main form. What can I do?

 

[lupins46]

OK I see what you aretrying to do here.

Try:
Forms!NordersMaken.NordersDetails.Form!Serial = Me.ProductId

 

[ailyn]

If you meant this:

DoCmd.OpenForm stDocName, , , , acFormAdd
Forms!NordersMaken.NordersDetails.Form!Serial = Me.ProductId
DoCmd.Close acForm, "Products"

It does exactly the same than before. I loads the product in the subform ('NordersDetails') and then gives me those Access errors when I try working on the main form.

 

[lupins46]

Well I have to admit I didn't think it would make n=much difference but at least it had to be tried with correct code.

It's as I said before, you have got to create a main form record first. I assume that the linking fields between your main form and subform are both 'order number' or something similar. If so, you need to establish the order number before adding any detail lines.

 

[ailyn]

Sorry, Lupin but I have no idea how to do that. One assumed that acFormAdd may have done that automathically. Do you know the code? or where to find it?

 

[ailyn]

...Could anyone help me with the code to set this please? I've tried several options but because the OrderId is a autonum value I cannot set it. HELP PLEASE!

 

[lupins46]

There is no way through this.
If your orderid is an autonumber you have to create the main form record first. Why can't you do that?

 

[ailyn]

I tried combinations of this:

Dim stDocName As String
Dim stSub As String
stDocName = "NordersMaken"
If Me![Avail] = True Then
MsgBox "This item has already been ordered once", vbExclamation
Else
DoCmd.OpenForm stDocName, , , , acFormEdit
DoCmd.GoToRecord , , acNewRec
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
Forms!NordersMaken.NordersDetails.Form!OrderIdctrl2 = Forms!NordersMaken.Form!OrderIdctrl
Forms!NordersMaken.NordersDetails.Form!Serial = Me.ProductId
DoCmd.Close acForm, "Products"
End If

But they don't seem to work. the product loads from the form products into the subform and the focus goes to the main form. the problem comes when you need to focus on the subform to add more details.

 

[lupins46]

I didn't really mean why can't you get it to work technically - I meant why are you not able to start by entering data into the main form record - I can't think of any app I've done where it was necessary to enter the subform record first so I'm struggling to imagine why you would need to do this.

 

[ailyn]

Ow! Sorry!!
It's and order form that feeds on two tables: tblOrders for the main form -> NordersMaken & tblOrderDetails for the subform ->NordersDetails
the thing is that for new orders I need to filter productId from tbl OrderDetails and blowerId from the same table to get values that have not been sold.
Those Ids that are in tblOrderDetails come at the same time from other two tables: tblProducts (for profuctId) and tblBlowers (for blowerId). Their corresponding forms have a button 'order this item' and that is the code of the products button.
...er.Ihope you're following.
Now back to the problematic form: NordersMaken is joined to NordersDetails by OrderId (that is present as the keyvalue on the tblOrders and as a value in tblOrderDetails).
I've been trying to solve the problems of this form without much success because when I load a ProductId into the subform and open the main form I get problems dependeing on where I place the focus. It always asks for the BlowerId. Should I try to load the BlowerId instead, I get the program to ask for the ProductId. I've made the form stop giving me trouble with the OrderId that connect form and subform by means of this:

Dim stDocName As String
Dim stSub As String
Dim stSql As String
stDocName = "NordersMaken"
If Me![Avail] = True Then
MsgBox "This item has already been ordered once", vbExclamation
Else
DoCmd.OpenForm stDocName, , , , acFormAdd
Forms!NordersMaken.Form!OrderDatectrl = Date
Forms!NordersMaken.NordersDetails.Form!OrderIdctrl2 = Forms!NordersMaken.Form!OrderIdctrl.Value
Forms!NordersMaken.NordersDetails.Form!Serial = Me.ProductId
DoCmd.Close acForm, "Products"
End If

but I still get the conflict in the subform. I'd like to be able to leave one of them free if I don't want to order a product or a blower. But it seems I can't, always get error 3101. I tried playing with the null values but no luck. Any suggestions? (I already made sure in the tblOrderDetails they could be null if I wanted to).

P.S.: About opening NordersMaken directly and then loading the right productId/blowerId, it simply doesn't go. I get a Jet error on the form/subform relationship.