Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.
COURSE of the MONTH
14 days, 17 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
MS Access Form Customer Order Form Building Button Open Up New Form To Same Customer ID Record
Posted on 2016-10-03
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!
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!
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
12
5
5- +1
23 Comments
Active today
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
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.
Active 1 day ago
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)
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
Active today
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.
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.
Active 7 days ago
Ok Thanks I will try these out today. When I said
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.
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.
Active today
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).
Active 7 days ago
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.
Active 7 days ago
Ok would
be in my case
and would that go in the frmOrders OnLoad?
and the:
would that go on the btnAddNewOrder on say event OnMouseDown or what and where?
Thanks!
Me.CustomerID = Forms!Orders.CustomerID
be in my case
Me.CustomerID = Forms!frmOrders.CustomerID
and would that go in the frmOrders OnLoad?
and the:
if me.dirty then me.dirty=false
would that go on the btnAddNewOrder on say event OnMouseDown or what and where?
Thanks!
Active today
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.
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.
Active 7 days ago
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
Would I be putting the code in the frmOrders Form
Me.CustomerID = Forms!frmOrders.CustomerID
Active 7 days ago
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??
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??
Active today
I see - in that case then Yes, the line Me.CustomerID = Forms!frmOrders.CustomerID
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 :)
Active 7 days ago
Success Mostly!
I put the code
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.
I put the code
Private Sub Form_Load()
Me.CustomerID = Forms!frmCustomers.CustomerID
End Sub
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.
Active 7 days ago
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.
Active 1 day ago
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
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
Active 7 days ago
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:
exit with saving has macro:
SaveRecord and following Close Form
Exit without has the code:
Private Sub btnUndoRecord_Click()
Me.Undo
DoCmd.Close acForm, "frmOrders", acSaveNo
End Sub
exit with saving has macro:
SaveRecord and following Close Form
Active 1 day ago
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
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)
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
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)
Active 7 days ago
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?
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
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
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
Active today
Private Sub Form_Load()
Me.CustomerID = Forms!frmCustomers.CustomerID
End Sub
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.
Active 1 day ago
>"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)
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)
Active 7 days ago
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!
Featured Post
Increase expert visibility of your issues by participating in Priority Question, our latest feature for Premium and Team Account holders. Adjust the priority of your question to get emergent issues in front of subject-matter experts for help when you need it most.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string.
Specify the first argument, which is the expression to be returned:
Specify the second argument, which …
Suggested Courses
Course of the Month14 days, 17 hours left to enroll
770 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.