MS Access Form Customer Order Form Building Button Open Up New Form To Same Customer ID Record

Ok I am working on my Customer Order Forms.

Tables Involved are Customers, Orders, and OrderDetails.

Forms are called: frmCustomers, frmOrders, and sbfrmOrderdetails.

I have it set up like this.

1: Customer Profile Form with a button that says Add New Order.

2: Click the Add New Order Button.

3: The Order Form Pops Up with the OrderDetails Subform attached to the Orders Form.

Very basic simple but I can't figure out how to get the CustomerID to match up on the Orders Form when I click the Add New Orders button.

On the Add New Orders button I have set up and tried some code and Macros like:

OnMouseDown: SaveRecord

With

OnClick: OpenForm frmOrders  Where Condition setting being "[CustomerID]=" & Me![CustomerID]

I have tried different things nothing. Also I would like a button or radio button in my Orders Form to autofill the shipping address information tot he same as billing information.

Remember this is to link the Orders Form with the customer on existing customers AND NEW customers also.

CustomerID Links Customers to Orders and Order ID Links Orders to Order Details.

THANK YOU!


db71.jpgdb70.jpgdb72.jpg
LVL 1
Dustin StanleyEntrepreneurAsked:
Who is Participating?
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Assuming your Orders form CustomerID Combo is where you're storing that value, you can set it directly using several techniques. One of those would be to refer back to the Customer form when the Orders form opens - so in the Open event:

Me.CustomerID = Forms!Orders.CustomerID
Remember this is to link the Orders Form with the customer on existing customers AND NEW customers also.
I'm not sure what you mean by that. I assume you are adding a Customer BEFORE you are adding an Order for that Customer. Otherwise, you'll be in for a mess at some point.
0
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
I think that would have to be in the LOAD event, Scott ...

anyway, I think a better place would be on the AfterInsert event just in case the user changes their mind about creating a record

Alternatively, I think you can also put what Scott mentioned in the DefaultValue property for the CustomerID control (Visible=No), prefaced with =

be sure before you open a form, you save the record you are on if it is dirty (has unsaved changes)
   if me.dirty then me.dirty=false

Open in new window

it is best to test IF a record needs to be saved and only save it if needed.
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Sorry - Crystal is right, the Load event would work better for this.

I don't really see the need for the AfterInsert event, but of course that is a personal preference. I was of the assumption that when the Details form is opened, the "default" understanding is that the user is going to be adding new records. If you're going to have users both viewing and editing data in the Details form, then the AfterInsert event, which first just after you begin editing a new record, would be the better choice.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Dustin StanleyEntrepreneurAuthor Commented:
Ok Thanks I will try these out today.  When I said

 
Remember this is to link the Orders Form with the customer on existing customers AND NEW customers also.


I was just notifying whomever because of what I understand for some events like BeforeInsert they only work for a New Record and not for a Existing Record. I wanted whomever's solution to work for both a new and existing records.
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I see - but I'm not sure why you would want to modify the Parent/Child link for an EXISTING record? Unless you have mistakenly assigned an Order to the wrong Customer, that value should never be changed (and if you need to change that, you'd have to have a different workflow for that, I'd think).
0
 
Dustin StanleyEntrepreneurAuthor Commented:
Ok I see how that could be confusing. The OrderDetails and Orders will ALWAYS be new records but the Customer may sometimes be a new or existing record. I like details but I am not good at giving details Sorry.
0
 
Dustin StanleyEntrepreneurAuthor Commented:
Ok would

Me.CustomerID = Forms!Orders.CustomerID

Open in new window


be in my case

Me.CustomerID = Forms!frmOrders.CustomerID

Open in new window


and would that go in the frmOrders OnLoad?


and the:

if me.dirty then me.dirty=false

Open in new window



would that go on the btnAddNewOrder on say event OnMouseDown or what and where?

Thanks!
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
We don't know your application, so we can't tell you for sure exactly what your syntax should be. If your Orders form is named "frmOrders", and if the control on that form that holds your Customer ID value is named "CustomerID", then the syntax you show should work.

would that go on the btnAddNewOrder on say event OnMouseDown or what and where?

Assuming btnAddNewOrder is the button that launches your second form, then include this code in the btnAddNewOrder.Click event. No need to use the MouseUp or MouseDown for things like this, as they can behave oddly at times.
0
 
Dustin StanleyEntrepreneurAuthor Commented:
Ok I said OnMouseDown because I was using the OpenForm with the OnClick Event.

Would I be putting the code in the frmOrders Form

Me.CustomerID = Forms!frmOrders.CustomerID
0
 
Dustin StanleyEntrepreneurAuthor Commented:
Also if everything is correct. When I click the Add New Orders Button on the Customers Form.

The Orders Form will pop up and have the Customer ID automatically matching the Customer ID from the Customers Form I was previously on. Without me having to use the dropdown combobox. Correct??
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I see - in that case then Yes, the line Me.CustomerID = Forms!frmOrders.CustomerID would go in the Details form Load event.
The Orders Form will pop up and have the Customer ID automatically matching the Customer ID from the Customers Form I was previously on. Without me having to use the dropdown combobox. Correct??
That would be the hope :)
0
 
Dustin StanleyEntrepreneurAuthor Commented:
Success Mostly!

I put the code
Private Sub Form_Load()
Me.CustomerID = Forms!frmCustomers.CustomerID
End Sub

Open in new window


 inside the form called frmOrders which has the subform called sbfrmOrderDetails in it.  I used the "OnLoad" Event. It refered back to my form called "frmCustomers" which they are linked on the field "CustomerID".

Now whenever I go back and press the Add New Order button for another customer the form frmOrders pops up and it keeps everything the same from the last order but just changes the CustomerID field to whomever I came from before. So it changes the customer to an old Order.
0
 
Dustin StanleyEntrepreneurAuthor Commented:
Ok I switched the data mode to add and it seems to work good except if I back out and don't fill anything out it still adds a record. How can I avoid that if it is blank.
0
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
try the BEFOREinsert event (told you wrong before) ... this only happens if a new record is beginning to get created

put the code to check dirty and save in the same procedure you use to open the form -- you should save the record before you:
1. open another form
2. open a report
3. switch to another record
0
 
Dustin StanleyEntrepreneurAuthor Commented:
BEFOREinsert didn't work correctly. I changed the OpenForm data type to new and that helped make new ones. and the only work around I could think of was to add a Exit without saving and a exit with saving button to the Orders Form.

Exit without has the code:
Private Sub btnUndoRecord_Click()
Me.Undo
DoCmd.Close acForm, "frmOrders", acSaveNo
End Sub

Open in new window



exit with saving has macro:
SaveRecord and following Close Form
0
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
hmmm, wonder why BeforeInsert didn't work -- oh well, you got it.  However, it would be good to put a Form BeforeUpdate event in there to check to ensure required data is filled before saving the record.

to validate a record and prevent it from being saved, put code in the form BeforeUpdate event.  Here is a generic example  you can modify  
  Private Sub Form_BeforeUpdate(Cancel As Integer)
'161003 strive4peace
   
   '-------------- make sure required data is filled out
   With Me.ControlName 'substitute your control Name (ideally, the same as the field name in the Control Source)
      If IsNull(.Value) Then
        'if it is not filled out, then move the focus to that control
        .SetFocus
        
        'give the user a message
        MsgBox "You must enter WHATEVER", , "Missing Data" 'substitute Whatever
        
        'IF this is a combobox, drop the list for them - uncomment next statement
        ' .Dropdown
        
        'don't save the record yet
        Cancel = True
        
        'if you want to undo the record, uncomment next statement
        'Me.Undo

        'quit checking and give them a chance to fill it out
        Exit Sub
      End If
   End With
   
   'if you have a field in the table to keep track of when a record was modified, uncomment next statement
   ' Me.dtmEdit = Now()
   
End Sub 

Open in new window

I call the field to keep track of when a record was added dtmAdd and the field to keep track of when it was edited dtmEdt. Both have a DefaultValue =Now()

even though macros seem easier to create  (if you are really using macros and not just calling the VBA code a macro), you should convert the macros to VBA so it is easier to see what is going on.  While in Form Design (or Report Design, or the design of a macro):
1. DESIGN ribbon tab
2. Convert [Form's] Macros to Visual Basic (located on the far right for Form/Report or in the left group in macro design)
0
 
Dustin StanleyEntrepreneurAuthor Commented:
Ok Was able to use the code for validation in on my Save and Exit Button.

on this part how would I add multiple fields or would I have to insert the code in several times for each field?

'-------------- make sure required data is filled out
   With Me.ShipAdd1 '
      If IsNull(.Value) Then
        'if it is not filled out, then move the focus to that control
        .SetFocus

Open in new window


Would it be like this

Me.ShipAdd1 And Me.ShipCity And Me.ShipState And Me.ShipZip

or

Me.ShipAdd1 or Me.ShipCity or Me.ShipState or Me.ShipZip
0
 
PatHartmanCommented:
Private Sub Form_Load()
Me.CustomerID = Forms!frmCustomers.CustomerID
End Sub

Open in new window


This code belongs in the BeforeInsert event as Crystal told you.  If you leave it where it is, it will only ever get executed ONCE.  That means, you can NEVER successfully add more than ONE record in the popup so you better change its properties to not allow it to move off the current record.

The BeforeInsert event runs ONCE but for EACH NEW RECORD. It is the first event  to fire when a NEW record is  being inserted.  The Dirty event fires second but it fires for both inserts and updates.  The Load event runs once and only once each time the form is opened.  The Load event has nothing to do with individual records.

One of the most important things about learning how to develop with Access is understanding what triggers a particular event so that you understand in which event to place code to implement your business rules.
0
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
>"on this part how would I add multiple fields or would I have to insert the code in several times for each field?

insert that block of code for each control you want to check.

Also, as Pat mentioned, BeforeInsert is the right place because you may enter more than one record.  If you aren't getting it to work ... is it that you are not seeing the value right away? It may be going into the table ... check after you test. Refresh Records if the table was already open

Also, as you are writing code, before you test it, choose: Debug, Compile [your database name] from the menu, then Save (diskette icon on the toolbar)
0
 
Dustin StanleyEntrepreneurAuthor Commented:
If you aren't getting it to work ... is it that you are not seeing the value right away? It may be going into the table ... check after you test. Refresh Records if the table was already open


That is it Exactly. It doesn't show a value but AS SOON as I dirty the record it populates the correct Customer ID instantly!

Good Job Crystal!
0
 
Dustin StanleyEntrepreneurAuthor Commented:
Thank You For The Increased Knowledge!
0
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
you're welcome, Dustin ~ happy to help.  Please be sure when you close a question to give points to EVERYONE who helped ...
0
 
Dustin StanleyEntrepreneurAuthor Commented:
Will Do!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.