?
Solved

Updating combo lists from Data Environment

Posted on 2003-03-03
4
Medium Priority
?
154 Views
Last Modified: 2010-05-01
I am using two unbound combo boxes to act as lists for two different fields in my Access database. First I populate the cboName on form load.  I would like to be able to update cboHoliday when cboName changes.  Here is the code I'm using but the cboHoliday is not updating when cboName changes.  

Private Sub Form_Load()
   
    'Fill the list box with names
    With deTime
        .rscomTime.MoveFirst
        Do Until .rscomTime.EOF
            If .rscomTime![Name] <> "" Then
                cboName.AddItem .rscomTime![Name]
            End If
            .rscomTime.MoveNext
        Loop
        .rscomTime.MoveFirst
    End With
   
End Sub

Private Sub cboName_Click()
   
    Dim strHoliday As String
    '****The list for holiday's is not updating based on a name change
   
    'Find the entry in the database
    deTime.rscomTime![Name] = cboName.Text
    strHoliday = deTime.rscomTime![Holiday]
   
    'Update holiday list when name changes
    If strHoliday = "None" Then
        cboHoliday.Clear
        cboHoliday.AddItem "None"
        cboHoliday.AddItem "Martin Luther King's Birthday"
        cboHoliday.AddItem "Presidents' Day"
        cboHoliday.AddItem "Columbus Day"
        cboHoliday.AddItem "Veterans' Day"
        cboHoliday.Text = "None"
    Else
        cboHoliday.Clear
        cboHoliday.Text = strHoliday
        cboHoliday.Locked = True
    End If
   
   
End Sub

Any and all help is very much appreciated.

Thank you,
Jeff
0
Comment
Question by:bowen18
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 11

Expert Comment

by:supunr
ID: 8061832
You could use the find command in the recordset.

instead of the following two line,

deTime.rscomTime![Name] = cboName.Text
strHoliday = deTime.rscomTime![Holiday]

use the code below.

if (rscomTime.RecordCount > 0) then
   rscomTime.MoveFirst ' Search start from the begining
   deTime.rscomTime.Find "Name=""" & cboName.Text & """"
   if (not rscomTime.EOF) ' name found
     strHoliday = rscomTime.Fields("Name").Value
   else
     strHoliday = "" 'name not found
   end if
else
  strHoliday = "" 'no records, so no holiday
end if

I assume that you are using ADO to access recordsets.  Hope this is useful. Good Luck!
0
 
LVL 11

Accepted Solution

by:
supunr earned 300 total points
ID: 8061837
actaully replace:
strHoliday = rscomTime.Fields("Name").Value
with
strHoliday = rscomTime.Fields("Holiday").Value
0
 
LVL 2

Author Comment

by:bowen18
ID: 8064533
This worked!  I had to change a couple of things though.  I had to add my Data Environment to each recordset, deTime.rscomTime.  Also I had to change the deTime.rscomTime.Find "Name=""" & cboName.Text & """" to
deTime.rscomTime.Find "Name='" & cboName.Text & "'"

Those are very minor things and I appreciate the code.  You saved me many hours of trying to figure this out :-)

Thanks,
Jeff
0
 
LVL 2

Author Comment

by:bowen18
ID: 8064534
Thanks again!
0

Featured Post

Enroll in August's Course of the Month

August's CompTIA IT Fundamentals course includes 19 hours of basic computer principle modules and prepares you for the certification exam. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question