Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA 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 Display Modes
  #1 (permalink)  
Old June 8th, 2009, 08:33 AM
Authorized User
Points: 71, Level: 1
Points: 71, Level: 1 Points: 71, Level: 1 Points: 71, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2009
Posts: 14
Thanks: 4
Thanked 0 Times in 0 Posts
Default Combo Box Not In List Problem

I have a single table database that I created for my own personal use to store computer related information. I categorize this data by Topic, Primary Sub Topic, and Secondary Sub Topic which appear as combo boxes (cboTopic, cboPrimarySubtopic, and cboSecondarySubTopic) on my form. Each one has its Limit to List property set to Yes. When I update cboTopic an after update event runs a SELECT DISTINCT query with a WHERE clause which restricts the cboPrimarySubtopic list to those subtopics related to the selected topic. Secondary subtopics are restricted similarly.
In an effort to handle new data not in list I searched through a basic VBA book and found the following procedure and substituted my control names:

Code:
Dim mbxResponse As VbMsgBoxResult
Dim strSQL1 As String
Private Sub cboTopic_NotInList(NewData As String, Response As Integer)
   mbxResponse = MsgBox("Add New Topic?: " & NewData & ,vbQuestion + vbYesNo, "Message to Me")
   If mbxResponse = vbYes Then
       strSQL1 = "INSERT INTO tblRodsCRNotebook([Topic]) " & "VALUES('" & NewData & "');"
       Call DoCmd.SetWarnings(False)
       Call DoCmd.RunSQL(strSQL1)
       Call DoCmd.SetWarnings(True)
       Response = acDataErrAdded
   Else
       Response = acDataErrContinue
       cboTopic = Null
   End If
End Sub
The code above works exactly the way I want. It displays a message box asking if I want to add the new topic I just typed in. If I click Yes it adds the new topic to the cboTopic list. If I click No it does not and deletes the text just typed. (The only quirk with this is that it adds a record to the database with the new topic name in the topic field, but I use a Delete Query to automatically eliminate these on form close so it is not a problem for my purposes.)

Figuring I could use this same procecure for cboPrimarySupTopic by substituting control names, I used the following code:

Code:
Dim mbxResponse As VbMsgBoxResult
Dim strSQL2 As String
Private Sub cboPrimarySubTopic_NotInList(NewData As String, Response As Integer)
   mbxResponse = MsgBox("Add New Primary SubTopic?: " & NewData & , vbQuestion + vbYesNo, "Message to Me")
   If mbxResponse = vbYes Then
       strSQL2 = "INSERT INTO tblRodsCRNotebook([PrimarySubTopic]) " & "VALUES('" & NewData & "');"
       Call DoCmd.SetWarnings(False)
       Call DoCmd.RunSQL(strSQL2)
       Call DoCmd.SetWarnings(True)
       Response = acDataErrAdded
   Else
       Response = acDataErrContinue
       cboPrimarySubTopic = Null
   End If
End Sub
This code does NOT work. It will not add the new data to the cboPrimarySubTopic list. I canít figure out why it works for one combo box and not the other. I know this code looks crude to most of you professionals out there but I am just a novice. Any help would be greatly appreciated.
Reply With Quote
  #2 (permalink)  
Old June 8th, 2009, 10:57 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

It seems to me there is more going on here. What is the table structure of your database? Is there any validation? For example, can you enter a subtopic without entering a topic?
__________________
mmcdonal

Look it up at: http://wrox.books24x7.com
Reply With Quote
  #3 (permalink)  
Old June 9th, 2009, 06:58 AM
Authorized User
Points: 71, Level: 1
Points: 71, Level: 1 Points: 71, Level: 1 Points: 71, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2009
Posts: 14
Thanks: 4
Thanked 0 Times in 0 Posts
Default Combo Box Not In List Problem

From what I have been reading my problem stems from having only one table so a redesign is in order. Thanks for replying mmcdonal.
Reply With Quote
Reply


Thread Tools
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
Msg Box with Combo List Coby Access VBA 4 July 28th, 2007 10:29 AM
IsValidData combo list box function JMichael Visual Basic 2005 Basics 4 June 20th, 2007 11:13 AM
Limiting a combo box to default list hamffjs Excel VBA 1 July 17th, 2006 03:19 PM
Combo List Box dragondayz Pro VB.NET 2002/2003 1 May 30th, 2006 06:05 AM
Populate List Box by Combo Box Selection mmcdonal Access 2 June 15th, 2004 12:08 PM



All times are GMT -4. The time now is 02:11 AM.


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