ERROR #3704: Operation is not allowed when the object is closed. Microsoft Access 303: Order List, On Double Click Event, Bill To Ship To, Public v Private Subs
 

4/24/2017: You may see an "operation not allowed" error on the site. We're working on fixing the problem. Nothing seems to be affected, it's just annoying. Carry on. :)   [dismiss]
 
Access 2007-2013
Access 2000-2003
Access Seminars
TechHelp Support
Tips & Tricks
Access Forum
Course Index
Topic Glossary
Insider Circle
 
Home   News   Tips   Glossary   Templates   Forums   Help   Logon   Order   Contact Us  
 
NEW Release: Access Advanced Level 4 and Level 5   dismiss
 
 
Courses - Microsoft Access 303
Description: Advanced Access
Running Time: 77 minutes
Pre-Requisites: Access 302 very strongly recommended
Previous Lesson: Access 302
Next Lesson: Access 304
Main Topics: Order List, On Double Click Event, Bill To Ship To, Public v Private Subs
Versions: This course is valid for Access 2000 through 2003. If you are using Access 2007 or 2010, you will still benefit from this course. You will find that the concepts are the same, but there are cosmetic differences between the versions.

    

Order before 4/27/2017 to get a FREE upgrade to our Access 2010 version!
Click here for details

 
Microsoft Access 303
Advanced Access Development

Order List Form, On Double Click Event, Bill-To, Ship-To Addresses, Public & Private Subroutines, Missing Orders, More. 77 Minutes
 

AC303 Major Topics

  • Order List Form
  • On Double Click Event
  • Bill-To, Ship-To Addresses
  • Public v. Private Subroutines
  • Missing Orders

Access 303 continues our advanced Access database development with VBA programming. We continue work on our sales and order-entry system.

We will begin by creating a form to show all orders, and all orders for a specific customer.

 

Next you will learn how to deal with a problem that comes up if the user tries adding a product when there is no order yet. We'll also see how to force the user to select a sales rep first.

 

We will next learn how to double-click on the customer name to open up that customer's record using the On Double Click event. I like to make these kinds of fields blue. We'll also make a double-click event to open a specific order too.

 

In previous Access classes, I taught you that you should almost never store duplicate data in your database - like storing the customer's address on every order since you can just look that up from the customer table. Well, here's one exception: when you want to track history. For example, when this order was shipped, it might have gone to a different address. In this lesson we'll address that with Bill To and Ship To addresses for each order.

 

You can make the addresses different, but I'll show you how to copy them from one to the other if one is blank (the ship-to address will default to the bill-to unless you change it).

Next we'll use our DLOOKUP function from the last class to lookup the billing and shipping addresses from the customer table and automatically fill them in to the order form when a customer is selected.

 

It's starting to look like a real order-entry system now.

 

Next you'll learn how to create your own subroutines in VB code so that you can reuse code without duplicating it. You'll learn about public and private subs and what the difference is.

 

You'll learn how to call subroutines from inside your code, and how to access public subroutines from a different form. Very cool stuff.

 

Next we'll see how to deal with a problem when your user doesn't select a customer for an order. You need to adjust your query join types to show ALL orders, and matching customer records if they exist.

 

Access 303 Outline
 

1. Order List Form
Create a Form to Show All Orders
Deal with adding a product when no order exists
Force user to select a sales rep
Create OrderListQ with LineTotal

2. On Double Click
Double-click on Customer to open Customer record
On Dbl Click Event
Always Use Event Procedures
Command Button to OpenForm Without Wizard
Double Click Event to Open Specific Order

3. Bill To & Ship To Address
Why Store Duplicate Data
Bill To / Ship To Addresses for Customers / Orders
Automatically copy one to the other if blank
Fix tab order

4. Subroutines
DLOOKUP Both Addresses on Order Form
Add New Order Button
acFormAdd
Create Your Own Subroutine
Private v. Public Subroutines
Calling Public Subs from a Different Form

5. Missing Records
DLOOKUP SalesTaxRate for Orders
Fix Joins in OrderListQ
Show All Orders even if Missing a Customer
 

 


Try a FREE Demo Lesson

 
 
 

Student Interaction: Microsoft Access 303

Richard on 1/1/2007:  Creating an Order List Form, On Dbl Click, ShipTo, BillTo, Creating Subroutines, Public v. Private, Get Sales Tax
J Finley on 6/6/2008: Hi,

Firstly may i just say how helpful these tutorials are! Thanks to these unique learning opportunities, i have made some great progress with access.

I was just wondering if it was possible to automatically populate and update database field with data from the internet? For example, if i want to check the progress of a share price or an order?

Thanks for your help

Jay

Richard Rost on 6/13/2008: Jay, YES it's possible - ANYTHING is possible with Access - but it will involve some programming and use of the Internet Explorer object. I do have databases myself that use this (in fact, the very program that pulls down 599CD orders from my web site into my Access database uses this technique) but it's not a quick or simple solution. I do plan on covering this in a future lesson - I'll get to it quicker if more people are interested (speak up!).
 veenstra470@optonline.net on 10/3/2008: Followup - I can't always get the same error...I an thinking that by testing - I deleted the product items - then going back and adding - may have caused the strange results...there is a lot to this..

Thokozani Ginindza on 1/9/2009: firstly i would like to say how helpfull this tutorials are and i wanna say thank you.

i have a problem writing code for update querries and as much as making the update querries themselves so may you just outline for me.thanks

Richard Rost on 1/20/2009: Thokozani, I cover Update Queries in detail in Access 222:

http://599cd.com/site/courselist/access/access222

Yoshiko on 2/9/2009: 16:10...Command83Button does not work.I can open a new order of a customer shown on CustomerF correctly,but SalesRepID is not shown on a new
order,SalesRepID is left blank.Address is shown correctly.
After I change VB code of GetCustomerAddress into
SalesRepCombo = "SalesRepID","CustomerT","CustomerID="&CustomerCombo
instead of
SalesRepID = "SalesRepID","CustomerT","CustomerID="&CustomerCombo
it does work!
Name of Combobox is SalesRepCombo(301-3.4:00...) and Controlsource is SalesRepID.
Left hand side of "=" should be Name of Combobox?
Or Controlsource of Combobox?

Greg Paradise on 3/11/2009: How important are the spaces in VBA?
Greg Paradise on 3/11/2009: How do you highlight a word so fast? Keyboard shortcut?
Richard Rost on 3/12/2009: Very. You can't use spaces in things like variable names, and if you use spaces in your table or field names you have to remember to enclose everything in square brackets.

Something that should be simple like:

Forms!CustomerF!FirstName

Now becomes a pain if you have spaces in it:

Forms![Customer Form]![First Name]


Richard Rost on 3/12/2009: Generally to highlight a single word I just double-click on it.
 Chris on 3/28/2010: Reference: Access 303, part 3 TS 14:42.

The Country Field has Canada, you change it to USA, then to no text string. Does this action of deleting the text string make the field null or does it make it contain "" - an empty string?


Reply from Richard Rost:

It would be EMPTY. A NULL value says, "there was never data in this field." An EMPTY string says, "there is no data in this string now, but there might have been at one time."

 Chris on 4/5/2010: Access 303.4 TI 16:25, Using Access 2007 - format Access 2000

When I execute the code, I get:

Run-time error '3075':

Syntax error(missing operator) in query expression 'CustomerID='.

When I debug, I see that CustomerID is Null just as the subroutine is called. It appears as though customerID doesn't yet exist because this is a new record, the data may not yet be present.

In Fact, one difference I saw between your working example and mine is that my OrderF opens up and has "(New Record)" in the OrderID field when the error is thrown.

I tried to pass the customerID through the WhereCondition:

Private Sub AddNewOrderBtn_Click()

DoCmd.OpenForm "OrderF", , , "CustomerID=" & CustomerID, acFormAdd

Forms!OrderF.GetCustomerAddress

End Sub

But got the same resulting error - in this case because CustomerID for a new record doesn't exist until it is entered.


The fix (Two):

1. Add a catch for when GetCustomerAddress is not called within its form:

Public Sub GetCustomerAddress(Optional Customer As Integer = 0)

If (Customer <> 0) Then CustomerID = Customer


As well, on the CustomerF code, behind the button:

Private Sub AddNewOrderBtn_Click()

DoCmd.OpenForm "OrderF", , , , acFormAdd

Forms!OrderF.GetCustomerAddress (CustomerID)

2. A better fix is to add CustomerID to the newly opened form:

Private Sub AddNewOrderBtn_Click()

DoCmd.OpenForm "OrderF", , , , acFormAdd
Forms!OrderF!CustomerID = CustomerID
Forms!OrderF.GetCustomerAddress

End Sub


Regards,

Chris

Mubeezi Micah on 6/5/2010: Dear Richard,

I noticed some minor issue at time index 3.14, you mention that an Excel trick does not work in Access. Actually it works. After highlighting the rows, if you place the cursor at the begining of the highlighted rows until it becomes a CROSS, Then right click, you can insert the rows. You could try it. May be one day you may want to edit this part of the video.

Warm greetings from Juba and keep up the great work.

MICAH

Reply from Richard Rost:

Micah, I'm a big enough man to admit when I've just learned something new from one of my students! You're absolutely correct. I never knew that before, and I've TRIED to do this numerous times.

You can highlight multiple rows, then move the mouse over any of the BOUNDARIES between those rows (that's where you get the two-way arrow that Micah refers to as a cross), right-click, and then select Insert Rows. Access will insert as many rows as you have selected.

You can also select multiple rows and then click on Insert > Rows on the menubar.

Thanks for sharing, Micah!

Jim Upton on 6/21/2010: I enjoyed all your lessons to date. I then have to convert it to my data base. I use the ideas. I can do a mixture of levels. But still get struck on basics. Partly because I go big gaps where I am unable to do Access so when I go back I am starting again.
Barry on 9/28/2010: The show customer orders button does not show existing orders anymore. Also if I open the orders form i have no data. There is however data in the order table and order list form. I don't know where I went off course, any help would be greatly appreciated.
 eddy geijselaers on 6/15/2011: Rick,
I thought you would come up with some normalisation in this class.
For example what if a customer has different "shipto" adresses? Like in "amazon.com" I can send any item to up to 4 ship to adressess (like a christmas gift or so).
Wouldn't it be better to normalize and get the data out of a "shipto-table" based on "customerid" (or orderid)?

Reply from Richard Rost:

Yes, you are absolutely correct. The perfect database would allow you to enter multiple addresses for each customer using a secondary table - so you could have as many addresses as you want. I believe I mentioned in the video somewhere that this is just a "good enough" solution for the purposes of class. But I like how you're thinking! :)

 Elaine on 9/25/2011: Time Stamp 7:03 adding the msg box I get a Error when I try to add a product it's a Compile Error Expected End Sub.
The Pribate Sub AddProductButton_Click() is highlighted in yellow with a yellow arrow next to it. The code looks just like yours the (If, Then, Exit Sub, and End If) are all in blue. I have put the code in twice I am not seeing where I have made and error.

 Jase on 10/21/2011: At the end of the Missing Records video you say that in a future lesson you show how to refresh the order list form, could you tell me how you go about this as you dont seem to cover this anywhere.
Cheryl Hokanson on 11/30/2011: All I want to do is have the product code field filled when a form opens with the product field. Right now I have the ProductID field coming from a lookup source of ProductQ. I've looked for this situation in 220-303. Could you please give me some very specific help. I want to give a program to the person who needs it ASAP.
Alex Hedley on 11/30/2011: Cheryl are you picking a product from a list and opening a form with the details about the product?

Have you tred using a DLOOKUP?

Alex

 Alyson on 5/3/2012: I sign on, but when I go and choose my class, it pops up a message telling me to purchase. I've already purchased the entire Access series. I've tried several times and it does the same thing.

Reply from Richard Rost:

Try using the new Theater page. Make sure you're logged in using the new Login feature on the main site menu. If you don't do anything for 20 minutes (click on a video, for example) the site may time you out. I'm working on a complete redesign of the Theater which won't have this problem.

Randall P on 5/3/2012: I've tried using the main log in and also the new theater page. It notices me as logged on once I use the main log in, but when I go to the theater, it prompts me to log in again. I do so, then click to start my class and it prompts me to buy it. Under settings, it never changes from "Guest/Log in" even when it shows me as logged on in the bar with "theater", "account", and "log off". I have tried several times. I never get logged on to have it idle for 20 minutes.

Reply from Richard Rost:

I just tested it using one of my other accounts on a different PC and it worked fine for me. I then tested it using YOUR username and password, and again it worked fine. Is anybody else having this issue? Randall, are you behind a corporate firewall of any kind?

Randall P on 5/3/2012: No, not that I know of. I was able to access the videos yesterday just fine...
 Pat on 7/20/2012: Hi Richard,
Lesson 4 @ 8:08 minutes, When I go to the order form and put in a customer with an existing order I'm getting a run-time error '-2147352567(80020009)' You can't assign a value to this object. When I click on "debug" it highlites the 1st address line in the AfterUpdate() sub. Any help would be greatly appreciated.
Thanks
Pat

Reply from Richard Rost:

I've never heard of this one before. Check your spelling... many times errors like this are caused by misspelled field names. Is the control locked? Is the recordset updateable? What is the exact line that's getting flagged?

Patrick H on 7/20/2012: Hi Richard,
When I copy & pasted the address info from the Customer form to the Order form the control source on the Order form didn't acknowledge/recognize it, so I went into the Order table and brought each item into the Order form separately and it worked fine. I'm using Access 2007 if that makes a difference

Thanks
Pat

 Relja Koprolcec on 8/23/2012: When I enter my code in the VB code window,and try to close it a message pops-up telling."This command will stop the debugger".Between OK Cancel and Help options I choose OK.After that,I'm not able to open any documents that I already opened and that are minimized at the bottom (Ican see their icons).When I try to open any objects from the object manu (Tables,Forms,Reports)a bell starts to yell at me while clicking and everything gets stuck? What's the problem? Regards Relja

Reply from Richard Rost:

Click on the STOP button in the VBA editor to halt execution and then figure out what's causing the error.

Relja Koprolcec on 8/24/2012: Tnx,I'll try it out!
Carl Kowalski on 11/26/2012: Why is that I cannot edit a form when I use the wizard to generate it? It seem the labels and controls are unseperatable. I am using access 2007.

thanks,
Carl

Reply from Richard Rost:

I'm not exactly sure what you mean. The wizard just goes through the steps of creating the form much like you would. You should be able to edit the final product. Give me more details on what's happening.

Alex Hedley on 11/26/2012: I think Carl means the new way that Access groups together controls, stacked or tabular.

Just right click -> layout -> remove (I think)

Al

Reply from Richard Rost:

Ah. That could do it. :)

James Gray on 3/7/2014: Is it possible to include a sring variable in the caption property? I would like to open a form that shows items specific to the current user and would like the caption to read "My Form Name for:" [specificuser]. I have tried to put the varial into that field without success.... Thanks Richard as always for your help. I am looking forward to your continued access classes as well..



Reply from Richard Rost:

You can change the Me.Caption property of the form in the OnOpen event (or whenever you want) for that affect.

Nicholas S on 4/3/2015: Hi, I have looked but may have overlooked, is there a equal version of this class for access 2013, in particular the bit about Bill To, Ship To address?
Kind Regards
Nicholas

Reply from Alex Hedley:

Not yet, there's still some more expert lessons to do before we get to the advanced.
The good thing about the old 300 series is that the VBA used there transfers over to 2007/2010/2013 no problem although the screens may look a little different.

It may have been covered in the Work Order Seminar

Emad on 2/13/2016: Hello, Kindly, How can I make Subform Follow the Main form? once I chose Company name from Dropdown List the subform did not follows?

Reply from Alex Hedley:

I'm not sure what you mean Emad, are you wanting the subform to update to show data relating to the chosen company?

Dennis Owens on 7/18/2016: I am working on Access 303 #4 Subroutines.
After working on your assignments in class and I have an understanding of the lessons I take what I've learned and put it into a project that I am working on. Question, when I designed the Bill To and Ship To Address my state is in a combo box. I can't get the Bill To State to get the data from the customer table. It will bring up the Ship To Address data. I can manually enter the state into the order form. It just won't do it automatically. What am I doing wrong?

Reply from Alex Hedley:

Is State stored as a Number or Text?
Is the Combo that has all the States got an ID and a Name, so multiple columns.
Are you copying the Bill To State value to the Ship To State value?

Dennis O on 7/22/2016: State is stored as a text.
State Combo has an ID and the abbreviation of the state plus the state name. Only the abbreviation shows in the state column.
This is the line that tells state what to do.
StateCombo = DLookup("StateCombo", "MemberT", "MemberID=" & MemberCombo)

I tried the same line using State and StateCombo. No difference, it still won't get the info from the other form. It pulls everything else with no problem except state.

Reply from Alex Hedley:

Does MemberCombo have a value?
Are all the controls named the same, you've mentioned State but it's called StateCombo.

 

You may want to read these articles from the 599CD News:

 

Learn
 
Accessindex
Excelindex
Wordindex
Windowsindex
PowerPointindex
Photoshopindex
Visual Basicindex
ASPindex
Seminars
More...
Customers
 
Account Login
Online Theater
Downloads
Lost Password
Free Upgrades
Insider Circle
Student Databases
Info
 
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Articles
Search Our Site
Waiting List
Production Schedule
Collapse Menus
Help
 
Live Chat
Customer Support
WalkThru Tutorials
Troubleshooting
FAQs
TechHelp
Consulting Services
About Us
 
Background
Testimonials
Jobs
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Order
 
Video Tutorials
Handbooks
MYOLP Memberships
Idiot's Guide to Excel
Volume Discounts
Payment Info
Shipping
Terms of Sale
Gift CDs
Contact
 
Live Chat
General Info
Support Policy
Contact Form
Email Us
Mailing Address
Phone Number
Fax Number
Course Survey
Facebook    Twitter

Google Plus    LinkedIn

Blog RSS Feed    YouTube Channel
Richard Rost Microsoft MVP