Wrox Programmer Forums

Need to download code?

View our list of code downloads.

| FAQ | Members List | Search | Today's Posts | Mark Forums Read
BOOK: Access 2010 VBA Programmer's Reference
This is the forum to discuss the Wrox book Access 2010 Programmer's Reference by Teresa Hennig, Rob Cooper, Geoffrey L. Griffith, Jerry Dennison; ISBN: 978-0-470-59166-6
Welcome to the p2p.wrox.com Forums.

You are currently viewing the BOOK: Access 2010 VBA Programmer's Reference section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old June 19th, 2013, 04:08 PM
Registered User
Points: 29, Level: 1
Points: 29, Level: 1 Points: 29, Level: 1 Points: 29, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2013
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Default Cascading Combo Boxes pg 473-474

I following the code providing in the book (replacing my values) and keep getting errors. I have 2 tables that I want to reference as combo boxes. Table 1 is Denial Category. I want this to drive what is available for selection in Table 2 named Root Cause. here is my code below:


Private Sub cboDenialCat_AfterUpdate()

Me!cboRootCause = Null

If IsNull(cboDenialCat) Then
Me!cboDenialCat.SetFocus

Me!cboRootCause.Enabled = False

Else
Me!cboRootCause.Enabled = True
Me!cboRootCause.RowSource = ReplaceWhereClause(Me!cboRootCause.RowSource, _
"Where Root Cause = " & Me!cboDenialCat)
Me!cboRootCause.Requery
End If

End Sub
Reply With Quote
  #2 (permalink)  
Old June 20th, 2013, 01:41 PM
gjgriffith's Avatar
Wrox Author
Points: 517, Level: 8
Points: 517, Level: 8 Points: 517, Level: 8 Points: 517, Level: 8
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jul 2009
Location: Boulder, CO
Posts: 110
Thanks: 5
Thanked 14 Times in 14 Posts
Default I think your "Where Clause" is incorrect!

Hello Mooredk,

Thank you so much for reading the Access 2010 Programmer's Reference and for posting your question here, we really appreciate your readership! I had a chance to look over your question and the code you posted here and I think I know what is going on.

So, you said: "I following the code providing in the book (replacing my values) and keep getting errors..." and then listed the code that you are using for your database. And after reviewing your code, I can see one possible problem in the following lines:

Code:
Me!cboRootCause.RowSource = ReplaceWhereClause(Me!cboRootCause.RowSource, _
"Where Root Cause = " & Me!cboDenialCat)
Specifically, in this code, notice that it says: "Where Root Cause = ". Is it possible that you don't have a field in your table with the EXACT name of "Root Cause"? In the above code, the "Where" statement needs to reference the EXACT name of the field you want to filter on, as the field name appears in the underlying table attached to the form. Can you check to make sure that your table has a field named "Root Cause" (with the space and everything)? If not, this is the source of your problem!

Otherwise, if your table does have a field named this way, then this is NOT the problem. In that case, can you provide a little more information about what the "errors" are saying?

So, either way, please let me know when you come up with when you have a chance and I'll do my best to help out. And thank you again for your readership, we really appreciate it!

Best Regards,
__________________
Geoffrey L. Griffith
http://www.ImagineThought.com

Wrox Author of:
Microsoft Access 2010 24-Hour Trainer
Access 2010 Programmer's Reference
Access 2007 VBA Programmer's Reference

*** Please click the THANKS button (to the right) if this post helped you! *** ---------------------------------------------------------------------------------------------------------->
Reply With Quote
  #3 (permalink)  
Old June 20th, 2013, 04:06 PM
Registered User
Points: 29, Level: 1
Points: 29, Level: 1 Points: 29, Level: 1 Points: 29, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2013
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi Geoffrey,

Thanks so much for responding!! Believe it or not I have had issues getting responses from other forums. I just joined this one yesterday and it was only by mistake when I read in the book that i could download the code from this site. Anyway to answer your question, yes there is a Root Cause field. Does the field have to be unique? Basically here is what I want:

Denial Category table has 1 field with 4 values: ATH, EXP, COD, MED
Root Cause and desc table has 4 fields: Root Cause (same values as what is in Denial Category table), Reason, Description, & Responsible Party.

I want the Root Cause table to only show the values associated with the denial table. For example, If in the denial combo box ATH is selected I want for only the reasons for ATH to show in the Root Cause table. I would also like for all of the values to be stored so that I can reference them later in reports and queries. THe user also needs to be able to add rows to the combo box if needed. I have been working on a lookup field but not working either and not sure that is what I want.

I know this is long...sorry. I have been working on this for days along w/ automating importing an excel file w/ no luck. I have the import specs set up and thaought i could write a macro and then call the macro by a command button but there is no import action under macros in 2010. This is how I used to do it in previous versions of Access. I got code from your book as well. Used it but it is not working. I know it is probably me. I'm very frustrated if you can't tell:)...

Again, what ever help you can provide, I would appreciate. Thanks!
Reply With Quote
  #4 (permalink)  
Old June 20th, 2013, 04:45 PM
Registered User
Points: 29, Level: 1
Points: 29, Level: 1 Points: 29, Level: 1 Points: 29, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2013
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I am getting this error:

Compile Error:

Sub or function not defined

It is highlighting this Me![Denial Category] in this line of code:

"Where Root Cause = " & Me![Denial Category])

I re-did both combo boxes. I used the names from the table which is the control source name. Is this correct?
Reply With Quote
  #5 (permalink)  
Old June 20th, 2013, 05:52 PM
gjgriffith's Avatar
Wrox Author
Points: 517, Level: 8
Points: 517, Level: 8 Points: 517, Level: 8 Points: 517, Level: 8
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jul 2009
Location: Boulder, CO
Posts: 110
Thanks: 5
Thanked 14 Times in 14 Posts
Default Good info - looks like the problem is the name of the ComboBox control

Hello Mooredk,

Thank you for the quick response to my reply message. You said: "Compile Error: Sub or function not defined. It is highlighting this Me![Denial Category] ..."

Ok so, I think I know what is going on here now. My guess is that it is that either the "Root Cause" field or the "Denial Category" ComboBox control is not referenced correctly, from what you are saying here. In the following code, you have:

Code:
"Where Root Cause = " & Me![Denial Category]
Specifically, the Me![Denial Category] should reference the actual name of the ComboBox control that you want the user to choose the filter value FROM, and it sounds like the Access compiler is not finding the control name of Me![Denial Category]. For this reason, I usually like to make sure my ComboBox controls have names that starts with "cbo"...and in this case, I would probably name this specific ComboBox control to "cboDenialCategory".

The other thing to look for is to make sure that the name of the table field is referenced correctly, especially when they have a space in them, which is usually NOT a recommended practice. When you have a field name with a space in it, it is highly recommended to BRACKET the field name, so that the space is not misinterpreted. Does that make sense?

So for this specific example, if the specified ComboBox was named "cboDenialCategory" and the table field name was "Root Cause", then the code you should use here would be:

Code:
"Where [Root Cause]=" & Me![cboDenialCategory]
Please notice I've bracketed both the control name and the table field name, so that the space is accepted properly. And really, what this code is saying is: Set the "Where" cause in the underlying recordset for the "cboRootCause" to filter on only the values that correspond to the records associated with the specified "cboDenialCategory".

However, one other thing that I have also noticed here...it looks like you are trying to filter the "Root Cause" field on values from the "Denial Category". Is that really what we want to happen here and are those "Denial Category" values valid for the "Root Cause" field? Or do you really want to filter on the "Denial Category" field? Perhaps the code should actually be:

Code:
"Where [Denial Category]=" & Me![cboDenialCategory]
And then in that case, the previous line of code will ALSO need to be fixed, again to fix the names of the ComboBox. So, in that case, the code would be something like:

Code:
Me![cboDenialCategory].RowSource = ReplaceWhereClause(Me![cboDenialCategory].RowSource, _
"Where [Denial Category]=" & Me![cboDenialCategory]
Does this all make sense? Either way, I just wanted to check on that point as well, because I'm not actually looking at your exact Access database, just a small piece of code.

Anyway, I hope this all makes sense and helps solve your problem, but if you have any more questions about this, please just let me know!

Thanks again,
__________________
Geoffrey L. Griffith
http://www.ImagineThought.com

Wrox Author of:
Microsoft Access 2010 24-Hour Trainer
Access 2010 Programmer's Reference
Access 2007 VBA Programmer's Reference

*** Please click the THANKS button (to the right) if this post helped you! *** ---------------------------------------------------------------------------------------------------------->
Reply With Quote
  #6 (permalink)  
Old June 21st, 2013, 01:20 PM
Registered User
Points: 29, Level: 1
Points: 29, Level: 1 Points: 29, Level: 1 Points: 29, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2013
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hey Geoffrey, I wondered if I was referencing the correct field. I had originally name the combo boxes as you suggested. I then deleted them and re-created them. I thought maybe i should reference the name in the table. Also, Denial category is the name of the table and the field name in the table (probably should change that). Root Cause table and the name of the field is identical to what is in Denial CAtegory. Here is a sample of what is in my root cause table:

Root Cause Reason Description Responsible Area
ATH Wrong Svc Wrong Svc Auth CAU
COD Dx Error Missing Dx HIM
MED NCCN Meets NCCN req CAU
EXP Clinical Trial Pt in clinical trial Clinic

There are multiple lines and reason for each root cause so this is just a sample. I will try changing names as you suggested and get back to you as well. Can I store all of these fields attached to a record not just one? THat is another concern b/c we will want to do trending etc on root causes etc on denials.

Thanks again for responding so quickly!! I really appreciate it!
Reply With Quote
  #7 (permalink)  
Old June 21st, 2013, 01:21 PM
Registered User
Points: 29, Level: 1
Points: 29, Level: 1 Points: 29, Level: 1 Points: 29, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2013
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Oh and my name is Debbie. Sorry I have been meaning to sign off with that and forget:)
Reply With Quote
  #8 (permalink)  
Old June 27th, 2013, 01:32 AM
gjgriffith's Avatar
Wrox Author
Points: 517, Level: 8
Points: 517, Level: 8 Points: 517, Level: 8 Points: 517, Level: 8
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jul 2009
Location: Boulder, CO
Posts: 110
Thanks: 5
Thanked 14 Times in 14 Posts
Default Create a Lookup Table

Hi Debbie,

Thanks for your message back and I'm glad to hear that we seem to be making progress on this issue. So hopefully at this point, you've got this working, but if there is anything else I can do to help, please just let me know.

Also, in your last message, you asked: "Can I store all of these fields attached to a record not just one?" and provided a sample table structure with some data.

To try to answer that question: Normally, in this situation, you would create the table and data (you've provided here) as a separate, standalone table, typically called "a lookup table." Then you would create a lookup field from your primary table to this new lookup table. The Access lookup wizard is very helpful in this regard, and can be selected when in Table Design mode under the pull-down for the "Data Type" setting for a given field in a table. The wizard will set up the lookup field for you and even help you display multiple fields from a single record in the lookup table, in the pull down that is created for the lookup field in the primary table (if needed). I know that last sentence might sound a bit confusing (I had to read it several times myself), so does this make sense?

Anyway, I hope my answers have been helpful, but if there is anything else, or if you have any other questions, please just let me know!

Thanks again,
__________________
Geoffrey L. Griffith
http://www.ImagineThought.com

Wrox Author of:
Microsoft Access 2010 24-Hour Trainer
Access 2010 Programmer's Reference
Access 2007 VBA Programmer's Reference

*** Please click the THANKS button (to the right) if this post helped you! *** ---------------------------------------------------------------------------------------------------------->
Reply With Quote
  #9 (permalink)  
Old July 15th, 2013, 02:51 PM
Registered User
Points: 29, Level: 1
Points: 29, Level: 1 Points: 29, Level: 1 Points: 29, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2013
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hey Geoffrey,

I'm sorry I haven't responded to your last reply. Unfortunately it was end of fiscal year and my "other" job took priority. I'm just now really reading your responses. I did try the lookup table before and I couldn't get it to work. I will try again b/c it has been over a month and I cannot remember why I decided to try something else. So I should have the 4 columns in a separate table called (for example) "look up table". I will then load this field on my form. Will it be a combo box? Will I still be able to (if I can figure out the code) choose a category under the denial category drop down box and it will only give me the "root causes" that pertain to this category in the lookup field?

I'm sorry if I'm making this more confusing. It is as if this is the only thing I can be working on or I get lost and have to "refresh" my memory when I return. In this case, it could take a while. Unfortunately, I have been asked to try to get this completed in 2 weeks. I still have to set up security, reports etc..But you don't need to know all of that:)..
Reply With Quote
  #10 (permalink)  
Old July 15th, 2013, 03:11 PM
Registered User
Points: 29, Level: 1
Points: 29, Level: 1 Points: 29, Level: 1 Points: 29, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2013
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Default Lookup table

Hey Geoffrey,

Now I remember. I created a table called Root Cause and Descriptions that contains 4 fields (denial category, reason, description and responsible party). I then created a "lookup table" called tblList of Root Causes. When I open this table in "open" view, I can click the drop down arrow and see all of my fields from table Root Case and Descriptions. Each row has a check box beside it. I cannot check multiple boxes because I get an error. If I check 1 box that is fine and I will see that selection but only the first field from that selection. WHen I added a combo box to my data entry form I referenced the lookup table but when you click the drop down arrow, the field is blank. I would like to also store this data in my table that is updated when the user fills in the missing data.

I'm not sure if I told you already, but I have a table that will be imported daily. I'm appending the unique identifier from this table to a "data entry" table. I then have these 2 tables (the import and the data entry table) connected in a query to create a table that I base my form off of. When the user enters in the missing data it updates this table. Confusing I know...I seem to have most of it working with importing the data and running some queries with a push of the button. I just (I think) need to get the denial category and root causes field to work and store this data so that we can access them in reports.

Clear as mud:)?
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Cascading Combo Boxes in Excel 2007 kmmagee Excel VBA 0 October 26th, 2007 10:20 AM
Cascading Combo Box Maureen227 Access 2 May 30th, 2006 05:42 AM
Cascading combo lists in continuous forms rjd97c Access 1 June 30th, 2004 02:51 PM
Combo Boxes tjs206 VB Databases Basics 2 December 10th, 2003 05:20 PM
combo boxes damnnono_86 Access 2 October 15th, 2003 09:00 PM



All times are GMT -4. The time now is 09:01 AM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.