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.
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!
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!
Who is Participating?
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
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.
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.
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).
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.
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!
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.
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
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??
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 :)
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.
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.
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
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
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)
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
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.
>"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)
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!
you're welcome, Dustin ~ happy to help. Please be sure when you close a question to give points to EVERYONE who helped ...
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.
Me.CustomerID = Forms!Orders.CustomerID
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.