Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How to Refresh a Combo Box

Posted on 2006-10-20
24
Medium Priority
?
571 Views
Last Modified: 2008-02-01
I have a Combo Boxes on a form that are populated with lists depending on a choice from another Combo Box Selection.
My Question. How do I REFRESH these combo boxes. In other words, If I go back to the 1st Box and select a different item from that list, how can I cause the subsuquential dependent Combo Boxes to show a blank AND NOT its original selection...
I hope I explained that correctly
0
Comment
Question by:frank631
[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
  • 9
  • 6
  • 5
  • +2
24 Comments
 
LVL 34

Expert Comment

by:jefftwilley
ID: 17775541
in any after update event of any combo box, once you've executed whatever it was you wanted to do you can set it's value back to nothing by doing this

me.mycombobox.value = ""
0
 
LVL 77

Expert Comment

by:peter57r
ID: 17775543
Hi frank631,

In the Afterupdate event procedure for the first combo you put...

me.secondcomboname.reqeury


Pete
0
 

Author Comment

by:frank631
ID: 17775611
Hi Jeff
This is the same set of combo boxes you have been helping me with earlier... I already have the following code (Thanks to you!) in the After_Update
Private Sub ProjectName_AfterUpdate()
Dim strSQL1 As String
    Dim strProjectName As String
    strProjectName = Me!ProjectName
    strSQL1 = "SELECT [Service Name] FROM [Project Detail Services Qry] WHERE ProjectName = '" & strProjectName & "';"
    Me.Service.RowSource = strSQL1
End Sub

Can I simply add the code line you suggested or would Pete's suggestion be better?
0
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 
LVL 6

Expert Comment

by:twintai
ID: 17775630
You don't have to clear all the combo box. Only the ones that come after a combo box that is changed. In that case:

Me.NextComboBox.Clear
Me.NextComboBox.Requery

You have to enter the code above in the After_Update event for the combo box that is changed.
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 17775641
That will work

Private Sub ProjectName_AfterUpdate()
Dim strSQL1 As String
    Dim strProjectName As String
    strProjectName = Me!ProjectName
    strSQL1 = "SELECT [Service Name] FROM [Project Detail Services Qry] WHERE ProjectName = '" & strProjectName & "';"
    Me.Service.RowSource = strSQL1
me.ProjectName.value = ""
End Sub
0
 

Author Comment

by:frank631
ID: 17775735
Jeff
No That doesn't work. I think the reason is that I have a "CASCADING" effect which works like this
1. 1st Combo Box - Choose the CompanyName -------> This then populates the next combo Box with all of the POJECTS associated with that client -------->This the populates a 3rd Combo Box with ONLY the SERVICES associated with that project.   But if the user entered the wrong Company and then wants to tab back to that 1st box and change the client name I want the subsuquent Combo Boxes to Re-initialize

Does that make sense?
0
 
LVL 9

Expert Comment

by:mpmccarthy
ID: 17775757

In the afterupdate event of the first combo box put

Me.SecondBoxName.Requery
Me.ThirdBoxName.Requery

In the afterupdate event of the second combo box put

Me.ThirdBoxName.Requery




0
 

Author Comment

by:frank631
ID: 17775856
mpmccarthy
Not sure why but it didn't work
No error messages and the form worked ok but it seems that the REQUERY had no effect at all
It should have done something but no effect... thats strange!
0
 

Author Comment

by:frank631
ID: 17775875
here is the code for the 2 boxes
Private Sub CompanyName_AfterUpdate()
    Dim strSQL As String
    Dim strCompanyName As String
    strCompanyName = Me!CompanyName
    strSQL = "SELECT ProjectName FROM ScdPjtClientQuery WHERE CompanyName = '" & strCompanyName & "';"
    Me.ProjectName.RowSource = strSQL
    Me.ProjectName.Requery
    Me.Service.Requery

End Sub


Private Sub ProjectName_AfterUpdate()
Dim strSQL1 As String
    Dim strProjectName As String
    strProjectName = Me!ProjectName
    strSQL1 = "SELECT [Service Name] FROM [Project Detail Services Qry] WHERE ProjectName = '" & strProjectName & "';"
    Me.Service.RowSource = strSQL1
    Me.Service.Requery

End Sub
0
 
LVL 9

Expert Comment

by:mpmccarthy
ID: 17775903
Private Sub CompanyName_AfterUpdate()
Dim strSQL As String
Dim strCompanyName As String

    strCompanyName = Me.CompanyName
    strSQL = "SELECT ProjectName FROM ScdPjtClientQuery WHERE CompanyName = '" & strCompanyName & "';"
    Me.ProjectName.RowSource = strSQL
    Me.ProjectName.Requery
    Me.Service.RowSource = ""
    Me.Service.Requery

End Sub


Private Sub ProjectName_AfterUpdate()
Dim strSQL1 As String
Dim strProjectName As String

    strProjectName = Me!ProjectName
    strSQL1 = "SELECT [Service Name] FROM [Project Detail Services Qry] WHERE ProjectName = '" & strProjectName & "';"
    Me.Service.RowSource = strSQL1
    Me.Service.Requery

End Sub
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 17775937
Hey Frank,
Requerying should work, but generally as a rule, I have each box cleanup after itself. With your cascading combos though, because each one rely's on a value from the one to it's left, you want to keep your values all the way until you get to the end. Ones that aren't in that chain..clear them all day. But really, if you are using ALL the input from these boxes, and not just setting recordsets with them, then you want those values in there until you actually perform the event for which they are all intended. It's THAT event where you want to clear them all. Just some thoughts.
J
0
 

Author Comment

by:frank631
ID: 17775987
Hey Jeff
Good points..
Let me ask you this... I created this form on its own... What I mean is that all the other USER DATA INPUT Forms were created based on a 1sr Created table.
I have no table for this SCHEDULE Form.. Where is the data that I am entering (The Records) being stored???
And can I creat a table to hold this data based on the FROM??  I know it is backwards
0
 

Author Comment

by:frank631
ID: 17776080
mpmccarthy

No effect... again I don't understand why????
No error messages just like the lines of code weren't even there
0
 
LVL 9

Expert Comment

by:mpmccarthy
ID: 17776126
You'll need to step through your code to see what values are being passed into the comboboxes, if any.  The code may be in already and not be being changed.

check out the names of your combo boxes.  The quickest way is in the other tab in the properties of the combobox.  What's in for name.

Just as a test set a breakpoint in the middle of one of these pieces of code and see if it stops.
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 17776231
The values aren't being stored anywhere. You've created an unbound form. I use them all the time when I want to do data entry. I use an insert statement or an edit/addnew recordset off a button to take all the data from my fiends and stick them into a table. If you're just collecting data as criteria, then whatever button you plan on pushing at the end of it all will have to loop through all those combo boxes and grab their values. What did you intend to do with them anyway?
0
 

Author Comment

by:frank631
ID: 17776608
This is a scheduling/Dispatching Form.
The Dispatcher uses the form to Schedule the Services for each project for each day. I wanted to keep this information so I could
1. Print out a record of all dispatching for a given time frame
2. Calculate daliy/weekly/monthly etc manpower useage...
3. Check Estimated Qty of services against Actual Services rendered to the Client

I think I may backtrack a bit.... Create a Table and Then re-create the form... The CODE work is what I was hung up on and the FORM is not yet completely formatted.

What do you think?
0
 

Author Comment

by:frank631
ID: 17776730
Jeff -

I need to Fill in a Text Box with a NUMERIC Value. The TEXT Box is for Estimated Qty of each type of services. I used the following code in the AFTER_UPDATE for the COMBO BOX (1 of the combo boxes in my "CASCADING EFFECT) just prior to the Estimaded Text Box:
Private Sub Service_AfterUpdate()
    Dim strSQL2 As String
    Dim strService As String
    strService = Me!Service
    strSQL2 = "SELECT [Estimated Qty] FROM [Project Detail Services Qry] WHERE ServiceName = '" & strService & "';"
    Me.[Estimated Qty].ControlSource = strSQL2
End Sub

I know what the problem is - I am asking to fill the TEXT BOX with a Number but I am DIMing the strSQL2 AS a STRING.
I changed it to AS Interger but STILL got the Type Mismatch in the strSQL2 = "SELECT [Estimated Qty] FROM [Project Detail Services Qry] WHERE ServiceName = '" & strService & "';"

How do I fix this
0
 
LVL 9

Expert Comment

by:mpmccarthy
ID: 17776858
That's not your problem.  You're trying to reassign the control source of [Estimated Qty] so it would no longer reference this field at all.  You want to put a value into the field.  Use the following:

 Me.[Estimated Qty] = strSQL2

0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 17777059
is there more than one est qty for any given service? If not, then you want to do this

Private Sub Service_AfterUpdate()
    Dim rs as DAO.Recordset
    Dim strSQL2 As String
    Dim strService As String
    strService = Me!Service
    strSQL2 = "SELECT [Estimated Qty] FROM [Project Detail Services Qry] WHERE ServiceName = '" & strService & "';"
    set rs = CurrentDB.OpenRecordset(strSQL2)
     if rs.EOF then
         msgbox" Hey, There's no data"
         exit sub
    end if
    Me.[Estimated Qty].value = rs.fields("[Estimated Quantity]").value
End Sub
0
 

Author Comment

by:frank631
ID: 17777078
mpmccarthy
No again that didn't work
it prints "SELECT [Estimated Qty] FROM [Project Detail Services Qry] WHERE ServiceName = '" & strService & "';"
in the TEXT box... Why wouldn't it

0
 
LVL 34

Accepted Solution

by:
jefftwilley earned 500 total points
ID: 17777105
Sorry, I left your recordset open....always close your recordsets
J

Private Sub Service_AfterUpdate()
    Dim rs as DAO.Recordset
    Dim strSQL2 As String
    Dim strService As String
    strService = Me!Service
    strSQL2 = "SELECT [Estimated Qty] FROM [Project Detail Services Qry] WHERE ServiceName = '" & strService & "';"
    set rs = CurrentDB.OpenRecordset(strSQL2)
     if rs.EOF then
         msgbox" Hey, There's no data"
         exit sub
    end if
    rs.movefirst
    Me.[Estimated Qty].value = rs.fields("[Estimated Quantity]").value
    rs.close
    set rs = Nothing
End Sub
0
 
LVL 9

Expert Comment

by:mpmccarthy
ID: 17777369
Sorry, was thinking of something else.

Private Sub Service_AfterUpdate()
Dim strSQL2 As String
Dim tmpQty As Integer
Dim strService As String

    strService = Me!Service
    strSQL2 = "SELECT [Estimated Qty] FROM [Project Detail Services Qry] WHERE ServiceName = '" & strService & "';"
    tmpQty = docmd.RunSQL strSQL2
    Me.[Estimated Qty].ControlSource = tmpQty

End Sub
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

730 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