Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 160
  • Last Modified:

Updating combo lists from Data Environment

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
bowen18
Asked:
bowen18
  • 2
  • 2
1 Solution
 
supunrCommented:
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
 
supunrCommented:
actaully replace:
strHoliday = rscomTime.Fields("Name").Value
with
strHoliday = rscomTime.Fields("Holiday").Value
0
 
bowen18Author Commented:
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
 
bowen18Author Commented:
Thanks again!
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now