Solved

How to Refresh a Combo Box

Posted on 2006-10-20
24
515 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
  • 9
  • 6
  • 5
  • +2
24 Comments
 
LVL 34

Expert Comment

by:jefftwilley
Comment Utility
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
Comment Utility
Hi frank631,

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

me.secondcomboname.reqeury


Pete
0
 

Author Comment

by:frank631
Comment Utility
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
 
LVL 6

Expert Comment

by:twintai
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility

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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:frank631
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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 125 total points
Comment Utility
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
Comment Utility
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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

743 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now