Solved Form for adding a new order by customer (1 Viewer)

[debsamguru]

Yes! That's how you do it!

DoCmd.OpenForm "NewOrderF", , , , acFormAdd
Forms!NewOrderF!CustomerCombo = Me.CustomerID

I didn't realise that the code that comes after the OpenForm command relates to the newly opened form. In general programming, the command to do something would then do that something and be taken away from that processing to the next form's code. But here it doesn't.

 

[pbaldy]

You can stop processing there if you want, using the acDialog argument of OpenForm. Code will stop at that line and the newly opened form's code will run. Once that form is closed or hidden, processing will resume at the line after OpenForm. In this case you don't want that, since you want to set a value on the newly opened form. An option if you did want the dialog method would be to pass the customer in OpenArgs and deal with it in the newly opened form.

 

[MajP]

In general programming, the command to do something would then do that something and be taken away from that processing to the next form's code. But here it doesn't.

Not exactly. When you command the other form to open it will run through any code on the other form that is in the initial events
Open

Load

Resize

Activate

Current
However, after that it returns to the calling code.
As @pbaldy points out you can keep if from returning until the called form is closed, by opening the new form using the ACDIALOG argument of the Docmd.Openform method.

However, you are correct in many software development environments simply by opening the form as a Modal form will stop code execution.

 

[debsamguru]

Thank you all for your help! As I said in my previous post, please remind me NEVER to offer to help family and friends just because 'you know computing - you can do this!!"

 

[pbaldy]

No problem, and I think we've all fallen into that trap.

 

[debsamguru]

Typically, when I went back to it this afternoon (having not made any changes from it from last night), it had stopped working again. I'll take another look on Sunday when I'm not as frazzled.

 

[pbaldy]

No problem, post back if you get stuck. Maybe attach the db here if it's feasible.

 

[Pat Hartman]

DoCmd.OpenForm "NewOrderF", , , , acFormAdd
Forms!NewOrderF!CustomerCombo = Me.CustomerID

No, this is not how you do it.

You pass the CustomerID in the OpenArgs. Then in the BeforeInsert event of NewOrderF, you populate the FK

Me.CustomerID = Me.OpenArgs

Using this method prevents YOUR CODE from dirtying the NewOrderF current record. When YOU dirty the record with code, that's when you end up creating "empty" records unless you actually bother with form validation to prevent that from happening.

It is poor practice for you to dirty a form. The BeforeInsert event runs Only for new records and runs as soon as the user dirties the record. The additional benefit of doing it the right way is that if the popup form is designed to add multiple records, they ALL get the correct foreign key value. Doing it the wrong way as above, only populates the FK for the first record. If a second record is added, the FK is not populated which will probably cause an error when you try to save the record.

 

[debsamguru]

OK. OpenArgs is a whole new ball game for me - I'll look it up on Sunday. Thanks

 

[Pat Hartman]

You're welcome

 

[debsamguru]

OK, so I'm playing with OpenArgs now.

Here is my code when opening from OrdersF and passing the CustomerID

Private Sub NewOrder_Click()
Dim CustomerInputID As String

CustomerInputID = Me.CustomerID
DoCmd.OpenForm "NewOrderF", , , , acFormAdd, , CustomerInputID

End Sub

The corresponding code in NewOrderF is

Private Sub Form_Load()
Dim CustomerInputID As String

CustomerInputID = Me.OpenArgs
MsgBox "CustomerInputID = " & CustomerInputID
If Len(CustomerInputID) > 0 Then
Me.CustomerID.Value = CustomerInputID
End If
End Sub

That works fine - yay!

But ... when I open the form in plain Add mode from the MainMenuF

Private Sub NewOrderBtn_Click()

DoCmd.OpenForm "NewOrderF", , , , acFormAdd, , ""

End Sub

I get an error message on the line

CustomerInputID = Me.OpenArgs

It's obviously because there's nothing in the OpenArg but how do I get round this because there's nothing to pass.

 

[Gasman]

Test if anything is in OpenArgs?

You should be doing this as a matter of practice for anything that *might* be empty ?

 

[debsamguru]

I do the test in the NewOrderF but the point is that I WANT the OpenArg to be empty - it's to enter a new order for which I haven't chosen the ID yet. I only want the OpenArg to have a value if I have a CustomerID to pass to it.

 

[jdraw]

If only Customers can have Order(s), what sort of logic is involved in assigning an Order before you have a Customer?
Just curious of your business process(es).

 

[Gasman]

I do the test in the NewOrderF but the point is that I WANT the OpenArg to be empty - it's to enter a new order for which I haven't chosen the ID yet. I only want the OpenArg to have a value if I have a CustomerID to pass to it.

You are not doing a test though?, merely assigning customerid from openargs, whether or not anything is in it?

 

[debsamguru]

So, when I go to the form from the Main Menu, I have a combo-box which allows the Customer to be selected. When I go to the form from the OrdersF form, I have already selected the customer and I want the current Customer details to be displayed as the default in the combo-box.

 

[pbaldy]

You've declared the variable as String which can't accept Null, hence the error. Either change it to Variant or test OpenArgs for Null first.

 

[debsamguru]

Thank you. Using Variant worked exactly as I desired. Thanks for all your help.

 

[pbaldy]

No problem.

 

[Pat Hartman]

You've put the code in the WRONG event. This is what I said:

You pass the CustomerID in the OpenArgs. Then in the BeforeInsert event of NewOrderF, you populate the FK

Putting the code in the Load event limits you to only ever creating a Single record each time you open the form. So, you would need to add additional code and property settings to ensure that you can NEVER scroll to a new record once you have created one.

Why not use the correct event so you have more flexibility?