Solved

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

Posted on 2016-10-03
23
42 Views
Last Modified: 2016-10-04
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
0
Comment
Question by:Dustin Stanley
  • 12
  • 5
  • 5
  • +1
23 Comments
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 250 total points
Comment Utility
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
 
LVL 18
Comment Utility
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
 
LVL 84
Comment Utility
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
 

Author Comment

by:Dustin Stanley
Comment Utility
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
 
LVL 84
Comment Utility
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
 

Author Comment

by:Dustin Stanley
Comment Utility
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
 

Author Comment

by:Dustin Stanley
Comment Utility
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
 
LVL 84
Comment Utility
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
 

Author Comment

by:Dustin Stanley
Comment Utility
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
 

Author Comment

by:Dustin Stanley
Comment Utility
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
 
LVL 84
Comment Utility
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

Author Comment

by:Dustin Stanley
Comment Utility
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
 

Author Comment

by:Dustin Stanley
Comment Utility
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
 
LVL 18
Comment Utility
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
 

Author Comment

by:Dustin Stanley
Comment Utility
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
 
LVL 18
Comment Utility
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
 

Author Comment

by:Dustin Stanley
Comment Utility
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
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
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
 
LVL 18

Assisted Solution

by:crystal (strive4peace) - Microsoft MVP, Access
crystal (strive4peace) - Microsoft MVP, Access earned 250 total points
Comment Utility
>"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
 

Author Comment

by:Dustin Stanley
Comment Utility
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
 

Author Closing Comment

by:Dustin Stanley
Comment Utility
Thank You For The Increased Knowledge!
0
 
LVL 18
Comment Utility
you're welcome, Dustin ~ happy to help.  Please be sure when you close a question to give points to EVERYONE who helped ...
0
 

Author Comment

by:Dustin Stanley
Comment Utility
Will Do!
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now