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  
 
 
 
Courses - Microsoft Access 302
Description: Advanced Access
Running Time: 67 minutes
Pre-Requisites: Access 301 very strongly recommended
Previous Lesson: Access 301
Next Lesson: Access 303
Main Topics: Product Table, Select From List, Combo Box Column, DLOOKUP, Error Handling
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 7/28/2014 to get a FREE upgrade to our Access 2010 version!
Click here for details

 
Microsoft Access 302
Advanced Access Development

Product Table & Form, Select Product From Combo Box, Column Property, GoToRecord, DLOOKUP, Error Handling, More. 67 Minutes.
 

AC302 Major Topics

  • Product Table, Form, Combo Box
  • Select Products From List
  • Combo Box Column Property
  • DoCmd.GoToRecord
  • DLOOKUP Function
  • Basic Error Handling

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

First we'll make a Product table and form so we can pick from a list of products when making an order. We'll learn how to deal with products that are non-taxable, or perhaps taxed at a different rate than normal (for example, clothes in NY state have a different tax rate than most products).

 

Next we'll make a combo box to list all of our products so that we can select a product when making an order. We'll put this in the footer section of our Order Details subform.

 

We will then create a command button that we can click on to get the data out of the combo box and put it up into the order details. You will learn how to use the Column property of a combo box.

 

You will learn how to move the focus to a new record in VB code, and how to set a field value. You will learn how to get data from multiple columns in the combo box.

 

As great as combo box columns are, you can't use them for everything. Next you'll learn about a powerful function called DLOOKUP that you can use to retrieve ANY values from ANY tables or queries in your database.

 

When we're finished with this lesson, we'll have added much more functionality to our order-entry system by allowing the user to select the product from a list of products instead of just typing it into the order.

 

Access 302 Outline
 

1. Product Information
Backup Access 301 Database
Create a Product Table
Non Taxable Products
Sales Tax Override
Put ProductID in Order Details table

2. Product Combo Box
Create a Product Combo
Remember the value for later use
Put Combo in OrderDetails Footer
Command Button to Add Product
Get Data from Combo Box Columns
Combo.Column() property

3. Get Data From Combo Box
ProductCombo.Column()
Move to a new record in VB code
DoCmd.GoToRecord
Accessing data from multiple columns

4. DLOOKUP
What is DLOOKUP
When should you use DLOOKUP
DLOOKUP to get values from tables
Me.Refresh
DLOOKUP Order Tax Rate from CustomerT
DIM a variable in VB
Basic Error Handling
On Error Resume Next
 

 


Try a FREE Demo Lesson

 
 
 

Student Interaction: Microsoft Access 302

Richard on 1/1/2007:  Continue Order Entry, Build Product Table & Form, Combo Box to Select Products, DLOOKUP, More VBA Functions
 Bob on 10/3/2008: Richard.I have learned so much with your series..thanks. However, I'm confused; I add a new customer; then go to orders - I see my new customer name. Then, after adding my products I hit the x to close the window and return to the customer form. I then return to the order form and my order is gone...happens no matter which lesson in 301-305 etc. If I want to really save my order it appears I need to click on the customer combo box and reselect my customer name. Note this is only for the FIRST order for that customer. Why does this happen and how can I avoid clicking on the customercombo? Sorry If I am missing something simple...this happens in my own application I am building too.
Richard Rost on 10/3/2008: Bob, I just noticed something interesting, but kind of unrelated. While I was trying to figure out what's happening for YOU, I noticed that there needs to be a Me.Refresh in the "ADD ORDER" button on the CustomerF form. I just added a customer and hit ADD ORDER and did NOT see the customer in the dropdown because the customer record was still dirty (pencil - being edited). Make sure to add a Me.Refresh into that button. I'll add an addendum to the latest class about that.

As far as what's happening to YOU, I can't seem to recreate that problem. Are you adding the order from the ADD ORDER button on the CustomerF form? Or are you closing the CustomerF form and then going straight to the OrderF form?

In any case, it's the CustomerCombo box that creates the relationship between customers and orders. If you don't fill that in first, it won't save the order properly under that customer.

If you use the ADD ORDER button, it should automatically fill in the CustomerCombo for you. Aside from that little bug I mentioned before, everything appears to be working fine.

I anyone else experiencing this problem?

Bob Veenstra on 10/10/2008: Richard: I got back at this, and after many tries and reloading of the lesson databases - I have been able to reproduce the error. As best as I could remember, here is the sequence. I think it has to do with the problem you addressed creating the order before its ready...thanks for helping: I can send my screen caps if you would like: used the 304 file.

1. Added me.refresh to add button on customerf form per your instructions.
2. opened customer list – clicked on id 7 Barker;
3. Open selected Customer selected sales rep Jane, and lead source Newspaper;
4. Show customer orders – none present closed window with x
5. On customerf clicked add new order
6. Order id 17 GamerSkum combo is already filled in as is order date;
7. Stopped by 'select a sales rep first'; OK - did that;
8. Add product – added t-shirt 17 closed with ‘x’
9. Back on customerf – clicked show customer orders worked fine;
10. Second Customer Alan Watson
11. Selected Joe for sales rep; screen26;
12. add new order
13. order form has sales rep joe; and tech rep joe...already filled in
14. Add product t-shirt - no pencil and order_id = 19 screen 27
15. closed with an x
16. returned to customers and ‘show customer orders’ – no orders screen 28

Seems to get the sales rep info from the first person’s order; then the second persons order carried over the data…too confusing for me….I am building a system that must be accurate...could you give a little more iinsight to avoid this problem.

Richard Rost on 10/25/2008: Bob, i went through your directions step-by-step and I couldn't reproduce the problem. Did you make sure that you put a Me.Refresh in BOTH the "show orders" and "add new order" buttons? Try deleting all of the orders and order details in the system and start fresh. See if that helps. It worked perfectly for me and I tried it 3 times with the AC304.ZIP database.
Bin Chen on 3/26/2009: Hi Richard,
I am having a hard time getting the Dlookup to work on my form per Access 302. I am trying to get the address field (a combobox) on my form to automatically populate by entering the Name. Here is my statment Address = DLookup("Address", "[PatientData Table]", "Name=" & NameCombo). I put this in the Address_AfterUpdate. But it doesn't work. Please help - Bin

Richard Rost on 4/1/2009: Bin, first of all, you wouldn't put this in the Address_AfterUpdate. You can't look up the field you're updating. You'd put this in the Name_AfterUpdate. Second, I strongly recommend AGAINST naming any field "Name." It's a reserved word in Access. Finally, make sure you put quotes around any string in your parameters:

"[Name]='" & NameCombo & "'"

Hope this helps. If not, follow the lessons again and do exactly what I do.

Ruth Spohr on 11/13/2009: 6:50 on the video shows error in Handbook (page 18), s is missing from Notes = DLookup etc. Whilst mentioning Handbook 302 on the footer it says Windows 120 instead of Access 305. Enjoying these lessons heaps.
Susan Tatlock on 5/6/2010: Hi Richard,
I have learnt so much from 302 but still have one little problem. My data base lists all the courses available at a University along with the entry rrquirements. Each of the departments have a set of entry requirements and they apply to all courses pertaining to their department, so when a new course is set up DLook up makes my life so much easier. However one department has different levels of language requirements for various courses. Basically I want to run my DLookup to populate most of the fields but when entering a new course for this 'rogue' department I want to be able to choose the language condition rather than have it populated automatically. Is that possible?

Reply from Richard Rost:

Susan, I'm sure it's possible. I don't know how you have your database setup, but you could just code it inside of an If/Then block of VB code.

If DepartmentID = 105 Then
'do crazy department stuff here
Else
'do rest of departments here
End If



 Ayman Hama on 5/21/2010: Can we make a tax table and add it to the order form

Reply from Richard Rost:

I'm sure we could. I don't know exactly what you're looking for, though. How would this tax table work on the order form?

Susan Tatlock on 5/21/2010: Hi Richard,
Thanks for your advice, but I am not proficient enough to fill in the gaps on the VB. Here is another Dlook up that works and if I explain what I am looking for:
Private Sub DptContacts_AfterUpdate()


EmailAddress = DLookup("emailaddress", "Contacts2", "ID=" & DptContacts)
ExtNo = DLookup("extno", "Contacts2", "ID=" & DptContacts)

If a particular email address will not use the same extension number, how can I make it ask me for a different ext no?

Thanks Sue

Reply from Richard Rost:

Susan, I don't understand your question. Can you please elaborate?

Susan Tatlock on 5/31/2010: Hi Richard,

sorry I was not clear with my last post. I work at a University and keep details of all our courses on a data base. We also include the name, email address and ext no of the peson in each department who is our contact. This is the DLookup I use at the moment:

Private Sub DptContacts_AfterUpdate()


EmailAddress = DLookup("emailaddress", "Contacts2", "ID=" & DptContacts)
ExtNo = DLookup("extno", "Contacts2", "ID=" & DptContacts)

What this does is when I update contact details for a department, I just update the department contact and then that persons email address and ext no are updated also. What I want to do is for example, if we have a department contact who uses two ext nos, one for 3 particular courses and a different one for all the others, then I dont want that persons ext number added automatically, I want to be able to choose.Do you think the easiest way would be to just add a second record to the table containing the department contacts but just change the name slightly so I can recognise which ext this will add. Sorry if I am getting all complicated.

Thanks for your patience so far.

Susan

david on 6/8/2010: Dear Richord

I Follow Your excellent course and i try to make with my own the dlookup function, but i do not succeed, and every item in the ProductT that is taxable but dont have a taxoverride value it's dont show a tax value, even that the form have a tax default value.
i copy there my vb code:
Private Sub AddProduct_Click()

On Error Resume Next

Dim IsTaxable As Boolean
Dim SpecialTaxRate


DoCmd.GoToRecord , , acNewRec
Description = ListProduct.Column(1)
ItemPrice = ListProduct.Column(2)
ProductId = ListProduct.Column(0)
Notes = DLookup("note", "productT", "productid=" & "listproduct")

IsTaxable = DLookup("taxable", "productT", "ProductID=" & "listproduct")
SpecialTaxRate = DLookup("TaxOVerRide", "ProductT", "ProductID=" & "listproduct")
If IsTaxable = False Then TaxRate = 0
If Not IsNull(SpecialTaxRate) Then TaxRate = SpecialTaxRate





End Sub



Reply from Richard Rost:

Fix these three lines:

Notes = DLookup("note", "productT", "productid=" & "listproduct")
IsTaxable = DLookup("taxable", "productT", "ProductID=" & "listproduct")
SpecialTaxRate = DLookup("TaxOVerRide", "ProductT", "ProductID=" & "listproduct")

to this:

Notes = DLookup("note", "productT", "productid=" & listproduct)
IsTaxable = DLookup("taxable", "productT", "ProductID=" & listproduct)
SpecialTaxRate = DLookup("TaxOVerRide", "ProductT", "ProductID=" & listproduct)

Remember, you're sending the VALUE of the ListProduct box to the DLOOKUP function... not the WORD "listproduct". So it should NOT be in quotes.

Nick Fuller on 12/29/2010: How come when you don't have to declare what kind of variable the SalesTaxOverride was going to be set to? For example the IsTaxable variable was declared as a boolean. I was tinkering around with the SalesTaxOverride as a double and integer and it broke the code. Thanks!!
DARRELL WOLLAN on 1/25/2011: Richard,

I may be getting ahead of myself, but once an invoice is made, how do you print out just one of them? Darrell

Reply from Richard Rost:

Coming up in Access 304 - printing a single invoice. :)

Ali on 3/19/2011: Richard,
I am having problems creating a login page using dlookup. The fields i am using are username and password, which is from the company table and the text boxes are called User_name and Pass_word. Please would you be able to instruct me on how to build this.

Many Thanks,

Ali

Reply from Richard Rost:

I'm happy to assist you. Let me see your DLOOKUP statements. Copy and paste them here EXACTLY as they appear in your code.

Ali on 3/19/2011: Private Sub LoginButton_Click()
'Check to see if data is entered into the UserName box

If IsNull(Me.Username) Or Me.Username = "" Then
MsgBox "You Much Enter a User Name", vbInformation, "Direct Sports"
Me.Username.SetFocus
Exit Sub
End If

If IsNull(Me.Password) Or Me.Password = "" Then
MsgBox "You Much Enter a Password", vbInformation, "Direct Sports"
Me.Password.SetFocus
Exit Sub
End If

Username = DLookup("Username", "Company", "Company_Id= " & Username)

Password = DLookup("Password", "Company", "Company_Id= " & Customer_Id)


End Sub

thats the piece of code exactly how ive got it i am unsure if it is correct at all

Reply from Richard Rost:

I don't understand why you're looking up username. Shouldn't you be saying something like:

CorrectPassword = DLOOKUP("Password","Company","Username='" & Me.Username & "'")

Then check to see if the passwords match...

IF CorrectPassword = Me.Password then OK_TO_LOGON

And remember, whenever you're using DLOOKUP with string values, you have to enclose it inside of an extra set of quotes.

Ali on 3/20/2011: Richard,

I just want to say thank you for pointing me in the right direction!

Many Thanks,

Ali

Aaitaman Tamang on 5/12/2011: Hey There!

Lesson 3 is not complete lesson. Why?

Reply from Richard Rost:

It should be. It's quite old and nobody else has complained. Perhaps the lesson didn't download correctly to your computer. Try it again.

Debbie on 10/18/2011: I'm trying to DLookup data from a query. (Put the "Info" in the pCPTo field from the EmployeeGlanceQ where the "ContactID" is equal to the value in the PrimaryCPTofficerCB) I keep getting:

Runtime error ‘2001’: You cancelled the previous operation

The VB looks like this:

Private Sub PrimaryCPTofficerCB_AfterUpdate()

pCPTo = DLookup("Info", "EmployeeGlanceQ", "ContactID=" & PrimaryCPTofficerCB)
Me.Refresh


End Sub

What am I doing wrong???

Reply from Richard Rost:

Does EmployeeGlanceQ have any parameters? If so, that will cause the error.

Debbie on 10/19/2011: "Info" is a concantenated field in the EmployeeGlanceQ. I wanted the employee's full name, email, and primary phone number to show up in one line. Should I store all of these items separately? Would that resolve the problem?

Reply from Richard Rost:

Don't STORE them separately in the table. Just make a calculated field for them in your query:

Glance: FullName & " " & Email & " " & Phone

Cheryl Hokanson on 11/28/2011: I want to have my product code fill in on my record of my form when the ProductID which I've made into ProductCombo is entered. I can't seem to figure out how to adapt your examples to my situation. Could you please help me. (I'm in 302 lesson 3 at 01:11)

Reply from Richard Rost:

Sounds like you need an AfterUpdate event. That's covered in the next class (Access 303).

Cheryl Hokanson on 12/2/2011: Why won't this work?
Private Sub PODetailNomen_Click()

PODetailNamen = ProductCombo.Column (2)

End Sub

 Alyson on 4/5/2012: Hi Richard. I am trying to create an Employee Search form where my users can search for an employee by employeeID or LastName & FirstName. I have tried this code: DoCmd.OpenForm "EmployeeF", , , "EmployeeID=" & EmployeeID
and it is not working. What am I doing wrong? It will not let me type in an employee # in the EmployeeID field, and if I type a LastName/FirstName, it creates a new file in the employeeT...

Reply from Richard Rost:

If your search form BOUND to your employee table? Make sure there's nothing listed in the form's RECORDSOURCE property. This needs to be an UNBOUND form. The fact that you can't type an ID tells me it's picking up the AutoNumber (which you can't edit) from the underlying table... and if you're getting a new record when you type in a name, that means the data is being saved to the table.

Alyson on 4/6/2012: Thank you. I got it to search and open form ny employeeID. How would I get it to search from LastName,FirstName? When I try to type in VBA with "LastName=" &LastName it pops up an error...

Reply from Richard Rost:

Remember that STRING values have to be enclosed in QUOTES:

"SELECT * FROM CustomerT WHERE LastName=""" & LastName & """"

Access translates "" inside a string to an actual double-quote, so you get:

SELECT * FROM CustomerT WHERE LastName="Smith"

 Abdel on 6/14/2012: Richard,
I'm having a problem because my db is a little different than here. You see, I have another table called PricesT which is made up of Product ID, Price and Starting date. I use this table to update prices for each product.

My orderDetail Query gets its price from this table. Somehow, I cannot add to the query perhaps because of the calculation and different tables.

Help please!
Abdel

Reply from Richard Rost:

You want to keep a history of prices. Smart. You probably have a query that's not updateable because of your joins, however. I'd need to see your database to tell you how to fix it. Try removing tables from the query one at a time and that will show you which one is causing the problem.

Abdel on 6/14/2012: Richard,
I have identified the table that is causing the query to be not updatable.. But what now?
I can send you a copy of the db which is like 2mb of you want. Send me your email if you would like.

Thanks,
Abdel

Reply from Richard Rost:

You can submit it to me on my TechHelp page. I'm extremely backed up right now though... my line for free tech support is into next month. In a nutshell you probably need to simplify your query. You might need to use some DLOOKUPs or OUTER JOINs to just DISPLAY the information on the client that you want to see.

SandraBischler on 10/15/2012: Hi again!
I am working in 2007. I try to do the VBA code for the button, but it will not work. Not even the Msgbox on click. What am I doing wrong? Is the Access 2007 diffrent?

Reply from Richard Rost:

Nope. Not at all. The code I use in 2003 is exactly the same as the code you'd use in Access 2007 or 2010. I can't tell you what you're doing wrong unless you give me more information. Are you following the example EXACTLY as shown? Are you using my database or your own database? Are you getting an error message?

SandraBischler on 10/19/2012: I figured it out once again (I am really improving here, very proud of that). The database was not in the trusted folder, so the button was blocked. But now it works fine. Thank you.
De on 2/17/2013: I've been trying to move ahead of the lesson a little bit so that I can think through the problem before I have the solution presented to me. My dlook up didn't have a cow because of the null value. Is that because I'm using Access 2010?
 

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