Access 2007-2016
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  

News      User Comments     History     Notify Me

6/2/2014 4:59:24 PM
Microsoft Access Expert 22
By Richard Rost   Richard Rost on Facebook Richard Rost on Twitter Richard Rost on Google Plus Richard Rost on LinkedIn Email Richard Rost

Microsoft Access Expert Level 22 is 2 hours, 3 minutes long. In this class we will learn how to fix non-relational spreadsheet data that we have to import into our database, creating multiple related tables. We will also learn how to import vendor price updates for our products, and we will fix our order-entry system so that product price changes no longer affect previously placed orders. Topics include:

- Fixing Non-Relational Data
- Split a Spreadsheet into Multiple Tables
- Update Vendor Price Sheet Changes
- Create Macro for One-Click Operation
- Fix Our Order-Entry System
- Product Changes No Longer Affect Orders
- Build New Button Macro From Scratch
- IF, GotoControl, GotoRecord Commands
- Manually Add Products Not In Database

Click here for more information on Access Expert Level 22, including a course outline, sample videos, and more. This course was recorded using Access 2013, but is also valid for Access 2007 and 2010 users. This class follows Expert Level 21. The next class in the series is Expert Level 23.

Permanent Link
Course Link: Microsoft Access Expert 22
Keywords: access expert 22 fix non relational data vendor price sheet updates order entry system
Page Tag: whatsnew
Post Reply

OrderDetailF Comment from Ryan W @ 3/14/2016
Hi Alex. Thanks for your reply. I actually submitted a comment/question twice by accident. On the first one I accidentally pressed enter instead of backspace, submitting it before my question was complete. My second comment/question was more detailed. Is it possible to possible to have a ProductCombo in the  detail section that is not directly tied to (or live with) the ProductT such that price changed in the ProductT do not effect all the orders?  And if the answer is yes, when/where is this taught? In advanced/VBA lessons?

Reply from Alex Hedley:

I think its 302
How to get a value from a Combo box and put it into another Field.
You would need to create another Field in the Table that is static, then have an AfterUpdate on the Product and put that value from the Product chosen into your Purchased Fields. You could do this with some Forms!FORMNAME!FIELDNAME = x etc
Show Just This Thread        Post Reply
Comment from Ryan Weemhoff @ 3/11/2016
Hi. Thanks for the lesson. Regarding the OrderDetailF: Is it possible to have a ProductCombo in the line item of the Detail section instead of in the Form Footer (with an "Add Product" button) and still have the same result, i.e., the product price is no longer directly tied to the ProductT. If yes, where/when do you teach this? Advance lesson? Thanks.
Show Just This Thread        Post Reply
OrderDetailF Comment from Ryan Weemhoff @ 3/11/2016
Hi. Thanks for this lesson. Regarding the OrderDetailF: Is it possible to still have a ProductComboBox in the line item of the Detail section instead of the Form Footer with an  button

Reply from Alex Hedley:

Yes you can have combos in the detail section
Show Just This Thread        Post Reply
VendorID Comment from Brian Jensen @ 1/15/2016
Tip for everyone-
At 24:13 there is an easy way to get around this small issue to have a vender name sorted by its ID.  
If you were to add the VenderT to your query and link it to your import query with the IDs being related you can then
add in the Vender Name instead of the VenderID and then filter by the criteria of "XYZ computer Wholesale" in the venderName field.
This prevents any future changes to venderID messing up other queries down the line.  

Reply from Alex Hedley:

It's unlikely that the VendorID changes if it's a Primary Key but filtering on Name might be acceptable but what if the name changes.
Show Just This Thread        Post Reply
Comment from Spiros Poulis @ 9/21/2015
I am interested too, in seeing a lesson on Appending new products from a Vendor to ProductT via import. I see above the same  inquiry of other students and I would like to inform me if there has already been created this  lesson or it is going to be created in the future.
Show Just This Thread        Post Reply
Comment from Wayne Markel @ 7/17/2015
I used a different approach.  I made sure the Excel sheet was sorted by order date before importing.  Let Access assign primary key as ID on import.  Used MAX(ID) instead of MAX(Order Date) to create the Customer list with latest data, then joined to Sheet 1 to get the most current address and phone.  Does anyone see a downside to this?
Show Just This Thread        Post Reply
Address DLookUp Address Sheet1 Customer Comment from Jeffrey Ervin @ 4/26/2015
I now have the Dlookup working in "Update Vendor Pricing", I done this by adding the "ID" Field From "VendorXProductT" to Query "ImportProductPricingXYZWholeSaleQ" Looking back on the video It seems I did miss the part where he added the ID field in, so I will go back on the the previuos lesson and its more then likely an error on my part, spent a lot of time on this part of the course, the upside is, I do now know the Dlookup Function inside out and a few off the problems that can come with it, this one needs to be obsolutly spot on.

Reply from Alex Hedley:

Yeah it's a pain but as you say great way to learn!
Show Just This Thread        Post Reply
Address DLookUp Address Sheet1 Customer Comment from Jeffrey Ervin @ 4/25/2015
Hi Dlookup is not working to well, I have followed this part of the course to exactly as is in the video, the dates are just dates the format is d/mm/yyyy changed it to dd/mm/yyyy then back. I moved onto to lesson two, same problem, is possible the database has become corrupted?

Reply from Alex Hedley:

I've had major problems with DLOOKUP and the date format, I think it's due to Access expecting it in mm/dd format.
I format the date before passing it to the function and it seems to work well.
Show Just This Thread        Post Reply
Address DLookUp Address Sheet1 Customer Comment from Jeffrey Ervin @ 4/24/2015
Address: DLookUp("Address","Sheet1","Customer=""" & [Customer] & """ AND [Order Date]=#" & [MaxOD] & "#")

I got this to work but only when messing around with order dates from the table sheet1, for eg. I had to make the date for Customer "XYZ Inc" at "1001 Starship' more recent then the one at "900 Box St". I followed the course to the letter or so I think. Twice.

Reply from Alex Hedley:

Does DLookUp("Address","Sheet1","Customer="'" & [Customer] & "'" work ok?

Are your Dates just Dates or do they include Times as well as this will change how you need to search for a date.
DLOOKUP can also be picky as to the format of the date, are you mm/dd or dd/mm

I usually use single quotes (') and not double quotes (") when writing this as double double quotes can get annoying.
Show Just This Thread        Post Reply
dlookup function Comment from Jeffrey Ervin @ 4/23/2015
Having Problems with the dlookup function, just cannot get it to work. At 13:00 Fixing non relational.

Reply from Alex Hedley:

Can you share your code please
Show Just This Thread        Post Reply
Need Learn MS Access Comment from Pramod @ 4/1/2015
Need Learn MS Access.

Reply from Alex Hedley:

You can order any course using the order form.
Show Just This Thread        Post Reply
Condo Manager Comment from Betti Baldan @ 3/21/2015
This is not a class question. My son has a property management business and they use a program called Condo Manager. I see that it's an Access database. I'm just curious to know if you're familiar with it, or even may have worked on it.
Betti Baldan

Reply from Alex Hedley:

I've not come across it before but never had the need.
Anyone else?
Show Just This Thread        Post Reply
Delete All Existing Orders Comment from Richard R @ 11/9/2014
It just made things easier to start over. The old table ONLY had the ProductID without the supporting details (price, etc.) That information was linked to the Product table. Now, that information gets copied into the record. You'd have to go back through ALL of the old records and update them with the current information if we chose to keep them all. I've tried to show the evolution of database design, and sometimes when you learn a new way of doing things, you have to radically redesign your database.
Show Just This Thread        Post Reply
Delete All Existing Orders Comment from Sara Ahlers @ 10/28/2014
I was wondering why it was necessary to delete all the existing orders (05:20).  What would happen if you left them alone?  Would the changes made to the database 'break' the existing orders?  Thanks!
Show Just This Thread        Post Reply
appending new products Comment from Sara Ahlers @ 9/11/2014
I also would be interested in seeing a lesson on appending new products to your product list.  I would also like to see a warning regarding a negative profit situation (where your cost is higher than what your sales price is).  

Great series!
Show Just This Thread        Post Reply
GoToControl Macro Comment from Lynda Chase @ 9/9/2014
I have again had a 'blonde' moment, I put the ProductCombo box in the OrderF form footer, rather than the subforms form footer.  I'm such a time waster, many apologies.


Reply from Richard Rost:

I'm glad you're able to figure these all out yourself. That's the best way to learn.
Show Just This Thread        Post Reply
GoToControl Macro Comment from Lynda Chase @ 9/9/2014
Hi Richard,

When I add the GoToControl Macro - to point to controlName ProductID, I get an error message saying: There is no field named 'ProductID' in the current record.  I've double checked spelling, and clicked on the ProductID box in the subform to verify its' spelled correctly.  Can you advise where else I might look.

Show Just This Thread        Post Reply
Action Catalog Comment from Wayne Ayotte @ 7/4/2014
At time Index 21:28 in Expert 22 Lesson 3 you say you don't use the "Action Catalog" much. I have never used it because I don't know why I would want too. Just wondering why/how would I use it or what it's for.

Reply from Richard Rost:

It's just a list of all of the menu commands broken down into "type" categories. I honestly don't use macros enough to need this (I mostly program in VBA) and the macro commands I do need are easy enough to remember. I'll be spending a lot more time covering macros soon, so perhaps we'll start to use it more. Microsoft is trying to shift more towards macros again because they work better with Web Apps. They have a history of "now we're going to use macros... oh, forget macros, now it's all about VBA... oh, now we're back to macros..." LOL
Show Just This Thread        Post Reply
Access Developer Release Date Comment from Landry L @ 6/27/2014
Hi Rick,
Do you have a time frame for the release of the 2013 version of Access300 series?
Thanks, Jim

Reply from Richard Rost:

My goal is to try and release one new Access class a week. I'm behind schedule right now. Just had to spend the past week moving video files to a new server (still not done with that yet). I expect to have the next Expert class out by the end of the month (so, Monday). I've probably got about 4 or 5 more Expert classes, then I'm going to guess about 10 to 15 Advanced classes (macros and events), and THEN I'll start the Developer classes.
Show Just This Thread        Post Reply
Appending NEW Products Comment from Roger Jeffrey @ 6/9/2014
Expert 22
Great class.

Glad you added Update  Products button with a macro on Main Menu.

Yes, I am interested in seeing a lesson on Appending new products from a Vendor to ProductT via import.

Reply from Richard Rost:

Got it. Thanks.
Show Just This Thread        Post Reply
Unique product IDs Comment from B @ 6/8/2014
Great class Rick.  Here's a question (using the class' updating vendor pricing as an example)-what do you do if the vendor's product codes aren't unique enough for you?  For example, you have two vendors, and they both have a product 003, and you don't want to manually tweak the spreadsheet to include your autonumber for the product id etc.  To solve this, I've been using a concatenation for the product id (for example, their product code concatenated with the company id).  Is there a better way?

Reply from Richard Rost:

This is why I recommend having a separate import for each vendor's price updates. They're likely going to have different fields or field names. Unless they're EXACTLY the same and using something universal like a UPC code, then this is probably the best solution for you. Perhaps eventually everyone will get onboard with the same XML-based data set (at least for your business) but until that day it's best to have separate imports, IMHO.

Show Just This Thread        Post Reply

Add Your Comments or Subscribe
If you just want to subscribe to get email updates when the News is updated, then enter your name and email address and check the Notify Me box below. If you would optionally like to add your comments below to be posted, they are welcome.
  Your Name:  Required
  Your Email:  NOT Public


Sorry about this step. It's just to keep the spam bots away:
  Verify: What is 1+9:
  Notify me when the News is updated.
  Remember Me for my next comments
Please do not use this form for Customer Service inquiries! If you have questions about your account, shipping info, courses you've ordered, need passwords, etc. please use the Customer Service Center instead.

I value your comments. They will be displayed on this page (above). Your name will be displayed, but your email address will not be.
As always, I promise to never give away your personal information to anyone else, ever.

NOTE: If you don't leave your name and email address, DON'T expect a reply. I can't promise a personal reply to everyone who posts here. I TRY my best, but I cannot guarantee it. If you don't leave your real name and email address, I won't even bother. I usually just hit DELETE. -Richard



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

6/20/2019TIP: Validation Rules in Access
11/20/2018Microsoft Access Developer 12
8/25/2018NEW: Access Dev 9, 10, 11
8/25/2018Microsoft Access Developer 11
8/25/2018Microsoft Access Developer 10
8/25/2018Microsoft Access Developer 9
8/23/2018Access Dev 11 is ONLINE
8/17/2018Access Dev 10 is ONLINE
8/15/2018Access Tip: Search Form
8/15/2018Access Tip: Locked v. Enabled

Visual Basicindex
Account Login
Online Theater
Lost Password
Free Upgrades
Insider Circle
Student Databases
Change Email
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search Our Site
Waiting List
Production Schedule
Collapse Menus
Live Chat
Customer Support
WalkThru Tutorials
Consulting Services
About Us
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Video Tutorials
MYOLP Memberships
Idiot's Guide to Excel
Volume Discounts
Payment Info
Terms of Sale
Gift CDs
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