Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.
Course Of The Month
5 days, 19 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 today
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 today
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 today
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 today
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 today
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 today
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 today
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 today
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 today
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 today
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 today
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 today
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 today
>"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 today
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
Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.
One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works
The attack works by targeti…
- The Email Laundry
- Email Servers
- Cybersecurity
- Email Protocols
- Network Security
- Security, *Phishing
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette.
This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses
Course of the Month5 days, 19 hours left to enroll
707 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.