We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

How to Refresh a Combo Box

frank631
frank631 asked
on
Medium Priority
629 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
Comment
Watch Question

Top Expert 2006

Commented:
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 = ""
CERTIFIED EXPERT

Commented:
Hi frank631,

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

me.secondcomboname.reqeury


Pete

Author

Commented:
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?

Commented:
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.
Top Expert 2006

Commented:
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

Author

Commented:
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?

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




Author

Commented:
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!

Author

Commented:
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
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
Top Expert 2006

Commented:
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

Author

Commented:
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

Author

Commented:
mpmccarthy

No effect... again I don't understand why????
No error messages just like the lines of code weren't even there
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.
Top Expert 2006

Commented:
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?

Author

Commented:
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?

Author

Commented:
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
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

Top Expert 2006

Commented:
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

Author

Commented:
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

Top Expert 2006
Commented:
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

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
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
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.