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

1/1/2008 3:11:00 AM
Microsoft Access 311
By Richard Rost   Richard Rost on Facebook Richard Rost on Twitter Richard Rost on Google Plus Richard Rost on LinkedIn Email Richard Rost

Access 311 covers adding COST to our product table and order details table so we can calculate job cost and net profit; dynamically generating SQL statements to control the records on a form; advanced sorting and grouping on reports; more with the Format() function; major overhaul to the OrderListF form.

Permanent Link
Course Link: Microsoft Access 311
Keywords: access
Post Reply

Dlookup Comment from Robert W @ 10/31/2015
Thanks Alex!
That was it, the ItemID fields were text so I needed those extra quotes. Thanks a million!

Reply from Alex Hedley:

No problem, glad it's working.
Show Just This Thread        Post Reply
Dlookup Comment from Robert Whishaw @ 10/23/2015
When we initially made the AddProductButton on click event using VBA  to perform dlookup's for notes, istaxable etc... it never worked for me so I just added those fields to the combo box & it worked great.
Now that we're adding more data to be looked up I'd really like to get to the bottom of why the VBA dlookup's aren't working.
Here's a list of all the combinations I've tried:

UnitCost = DLookup("UnitCost","ItemXVendorT","ItemID=" & ItemCombo)

UnitCost = DLookup("UnitCost","ItemXVendorT","ItemID=" & ItemCombo.Column(0))

UnitCost = DLookup("UnitCost","ItemXVendorT","ItemID" = ItemCombo)

UnitCost = DLookup("UnitCost","ItemXVendorT","ItemID=" & [Forms]![OrderF]![OrderDetailF].Form![ItemCombo])

UnitCost = DLookup("UnitCost","ItemXVendorT","ItemID" = [Forms]![OrderF]![OrderDetailF].Form![ItemCombo])

The field formats I'm looking up are all currency or number, no text fields involved.
I'm looking up UnitCost in the ItemXVendorT since I have a many to many relationship with products, vendors & their prices.
I've scanned forums for many hours without success.
Are there any common issues that I may be overlooking?
Any help would be much appreciated :)

Reply from Alex Hedley:

If you Debug.Print your ItemCombo what value are you getting?
Is your ItemID a Number Field
If it is text you would need "ItemID= ' " & ItemCombo & " ' " (i.e. wrapped in quotes)
Does your ItemXVendorT have values that match your ItemID
With it being a Junction Table are you likely to only have 1 ItemID
Show Just This Thread        Post Reply
FilterCombo issue Comment from Clay FULGHAM @ 4/2/2015
I ran into a little issue here, the FilterCombo initially wasn't selecting the value although the dropdown worked, nothing would be selected when clicked.  After a little digging I figured out that we'd set the OrderList FORM PROPERTIES: Allow Edits to "No" some time ago in the 2010/2013 Expert Series.  Changed it to "Yes", now working perfect.  Carry on.

Reply from Alex Hedley:

Thanks for sharing.
Show Just This Thread        Post Reply
Price as a percentage of cost Comment from Joni Moore @ 8/21/2013
If this comment has already been brought up, I apologize...
Wouldn't it be better to have your price be a certain percentage of your cost?  Then when your costs go up, your prices go up by a certain percentage automatically and you're not having to update twice the number of fields?

Reply from Richard Rost:

That's certainly an option for some situations. If you want to set up your database that way, then that's fine. What if you have a particular product that is a good seller and you advertise it for $14.99, then your costs go up a little bit. You don't want to kill your promotion over a few cents. Always making your prices a function of cost can be troublesome.
Show Just This Thread        Post Reply
Sort Buttons Not Working Comment from Eleanor Mason @ 7/3/2013
Time Stamp 16:49 Changed the AZ/ZA buttons to image. removed the GoToControl.
But my AZ/ZA buttons don't work now, I am using Access 2010.
Show Just This Thread        Post Reply
Comment from Benjamin Chua @ 10/28/2011
Class Access 311 Lesson 2 Time 11:52

In question of the Amount not showing as a Currency.

What I did, was I put a Round before the DSUM in the Query.
Show Just This Thread        Post Reply
Comment from David Leech @ 9/28/2009
One of my favourite classes so far! I love that you decided to use the az za button for this. In my opinion the command buttons in access are pretty ugly,I anticipate using images alot more. much more fun :)
Really liked all these lessons. really cool stuff
Show Just This Thread        Post Reply
Comment from Richard Rost @ 7/31/2009
Jane, there are extra parameters in there (additional options you can specify) that aren't important at this time. Don't worry about them. I'll cover them in future lessons.
Show Just This Thread        Post Reply
Comment from Jane Hu @ 7/31/2009
I see a VB code in the course:
Private Sub CompanyName_DblClick(...)
DoCmd OpenForm "CustomerF",,,"CustomerID=' & Cust....
Could you explain the use of ",,," in the code.
Also I can't see the whole VB code. Could you also explain please.
Thanks very much!
Show Just This Thread        Post Reply
Comment from Richard Rost @ 4/14/2009
Harry, just don't specify a recordsource when you build the form. It will open blank. Then change the recordsource yourself in the OnOpen event to a single record with an SQL statement.
Show Just This Thread        Post Reply
Comment from Richard Rost @ 4/14/2009
Harry, the colon is a habit I picked up because (a) it's old school, and (b) it allows you to create a SINGLE-LINE case statement like this:

case "A": msgbox "do stuff"
case "B": msgbox "do other stuff"
case "C": msgbox "do yet more stuff"

And so on. You can't do this without the colon, so it just kinda stuck with me and I use it all the time.
Show Just This Thread        Post Reply
Comment from Richard Rost @ 4/14/2009
Harry, both work just fine. I prefer the latter as well for continuous forms. For a single form, doing it on the form is OK, but the query is more efficient.
Show Just This Thread        Post Reply
Comment from  Harry @ 4/11/2009
How can one open a form using dynamic sql (the dynamic part I can figure, the open part gives me fits) and avoid docmd.openform which seems to grab all records and then filter them?

As an aside, apart from increased network traffic in a multi user environment, the filters can be accidently messed with by the user causing no end of confusion. Any comments?
Show Just This Thread        Post Reply
Comment from  Harry @ 4/11/2009
Whatis the purpose of a colon after each case statement? I've never seen that done nor used it myself. I can see it works, but what does it do?
Show Just This Thread        Post Reply
Comment from  Harry @ 4/11/2009
Is there an advantage to joining a first and last name using the record source of a text box on a form vs creating a custom field in the underlying query?  I tend to prefer the latter as it seems a little quicker in rendering (esp for continuous forms) but I'm not sure.
Show Just This Thread        Post Reply
Comment from Richard Rost @ 1/12/2008
I believe I covered this in Access 202. I showed you the button that switches between DESIGN mode and DATASHEET mode also can show you the SQL of a query. You're right though - I should have mentioned it again when we started covering dynamic SQL for forms.
Show Just This Thread        Post Reply
Comment from Alan Hill @ 1/11/2008
Some SQL's get pretty complicated. I found creating a query and switching to SQL mode, copying and pasting can be helpful. You did not cover that alternative. I thought the image AZ and ZA buttons was a neat trick. That was new to me.
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 3+1:
  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:

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
8/15/2018Access Dev 9 is ONLINE

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