Combo Box Requery Not Working

[kmjones]

Hi,

Brand new to this and I have no background in programming. I'm working on a fairly straightforward database for a not-for-profit organization tracking member information and I'm using Access 2010. I have cascading combo boxes set up to track company Sector (aka Combo88) and Subsector (aka Combo81). (Subsector depends on selected Sector). Everything works fine for the first record entry and the Sector/Subsector choices match but when you move to enter the next record, the Subsector list will not reset.

Per everything I've read, in the AfterUpdate event for Sector/Combo 88, I have:

Private Sub Combo88_AfterUpdate()
On Error Resume Next
Me.Combo81.Value = Null
Me.Combo81.Requery
End Sub

The Subsector box is cleared but when you click to bring up the list, the previous selection still appears, so apparently the requery part isn't working. I've read a zillion postings but can't figure out where the problem might be.

Any suggestions appreciated!

 

[boblarson]

1. Don't set the subselector to null.
2. Subselector (I assume is Combo81 - which you should rename to be more useful like cboSubselector) needs to be bound to a field or you need to add in the Form's On Current event the requery as well for the combo.

 

[kmjones]

Thanks, tried removing the Null statement and tried the "on Current" statement, unfortunately its still not working.

If "bound" means points to a control source, (remember, I'm no programmer) Combo81 (the dependent Subsector combo box) is bound to a Subsector field in another table (tblData) which has all of the basic contact data for each company. The master Sector field (Combo88) is also bound to a Sector field in tblData. I didn't bother renaming the combo boxes since they are the only ones on the form - I tried recreating them a few times which is why the dependent one has a lower number than the master one.

Both combo boxes are pulling data from a table (tblAll) which has two fields, sector and subsector. The RowSource statement in the master Sector/Combo88 pulls the distinct list of sectors from tblAll. The RowSource statement in the dependent Combo81/Subsector box pulls the associated subsectors from tblAll:

SELECT [tblAll].[Subsector] FROM tblAll WHERE tblAll.Sector=Forms![Company Information]!Combo88.Value;

It works fine the first time, it just doesn't reset for the next record. Is there a way to add a requery statement into the RowSource code? Would a subform be an option to try?

 

[boblarson]

It works fine the first time, it just doesn't reset for the next record. Is there a way to add a requery statement into the RowSource code? Would a subform be an option to try?

What needs to happen is that you requery the combo in both the after update event of the first combo but you also need to do it in the On Current EVENT (not statement, event - some technical jargon for you to learn to help you later on as you go).

If you still can't get it, perhaps you can run compact and repair on the database, zip the file up and then post it here so we can take a look to see what you have. There may be other issues which are fighting you.

If you do upload a copy, make sure it isn't real data but only test data in it. And you need to do the compact and repair first and then zip the file (you can do that by right-clicking on the Access file and selecting SEND TO > COMPRESSED FOLDER from the shortcut menu that pops up).

 

[kmjones]

Thanks so much! I really appreciate your time to check this out. I have the requery statement in both the combo box "After Update" and the form "On Current" events. Still not working.

I'm attaching a zipped copy of the database (Master Database). Per your suggestion, I did update the combo box names (now Combo88 = cboSector and Combo81=cboSubsector).

I'm sure it is something simple I'm just missing...

 

[boblarson]

Okay, here you go.

I think you'll notice some changes in:
1. Tables (added some, tblAll can be removed, some fields were added, some fields were deleted).
2. Your subform's recordsource was modified slightly, and your subform's master/child links were changed.
3. I removed the On Error Resume Next. You should almost never use that unless absolutely necessary. You should use a real error handler because some important errors can be obscured if you just use resume next.

4. I also made a few modifications to some of the row sources of the combos's and hopefully you will be able to discover anything I have forgotten to mention here.

 

[kmjones]

Thanks Bob! I get what you did and how the tables are linked (makes complete sense!) and thank you very much for the pointers. Unfortunately, when I saved a copy of the revised database back onto my computer, it's still not working. The Subsector selection still isn't changing when I move between records. Maybe it's me?

Thank you for all of your help. I completely understand if I'm a lost cause!

 

[boblarson]

Thanks Bob! I get what you did and how the tables are linked (makes complete sense!) and thank you very much for the pointers. Unfortunately, when I saved a copy of the revised database back onto my computer, it's still not working. The Subsector selection still isn't changing when I move between records. Maybe it's me?

Thank you for all of your help. I completely understand if I'm a lost cause!

If the code isn't working, then it is likely you don't have the database in a Trusted Location. To check to see if any of the events fire, put a breakpoint (here's how if you don't know-http://www.btabdevelopment.com/ts/bps) in the form's ON CURRENT event to see if it actually stops at the breakpoint. If not, then the code is likely not enabled due to not being in a trusted location.

 

[kmjones]

Bob, you are a GENIUS!! That was it, exactly!! I knew it had to be something simple like that. Everything is working smoothly! THANK YOU THANK YOU THANK YOU!! You have just made life a lot easier for this little not-for-profit!

 

[jai kushwaha]

hello friends, i am working on a database and i have a combo box. in my combo box there are 5(values like 1,2,3,4,5). Now what i want that if i select value"1" from the combo box the text box named "txtbx" will enable to edit and if i choose other values it will disable for edit.
Private Sub Cbo_name_AfterUpdate()
If [Cbo_complaintStatus] = "1" Then
txtbx.Enabled = True

Else
txtbx.Enabled = False

End If


End Sub


but its not working whenever i choose any value from the combo box it disable the "txtbx".


i want help friend

 

[Minty]

If the combo box value is a number you need to remove the "" around the 1, unless it's text. You should also explicitly refer to the control.

If Me.Cbo_complaintStatus = 1 Then ...

 

[jai kushwaha]

well thank you my friend for answering but it is still not working when i did it Me.XYX = "xyz" then
ddddd.enable = true
else
ddddd.enable = false
end if
end sub
but it still not working


when i am selecting XYZ from combo box the txtbox "ddddd" disabled for all option available in combo box even for XYZ also.

 

[Minty]

what is the record source for your combo box, can you post the SQL and what is the bound column?

 

[jai kushwaha]

thanks my dear friend but the thing is that i using another computer from anywhere else so now i cant post you the bound column but in my column i have species name like "canine", "volwine", and "other" and i want when i choose "Other" the text box will only "enable" and also "visisble" but it is not working.

 

[jai kushwaha]

the record source of the combo box is a table

 

[Minty]

Does the table have a record ID. Could you show us the contents of the table?
It would be very helpful if you uploaded your exact code or a stripped down version of your db. We are all guessing at the moment.

 

[jai kushwaha]

sorry friend because i am not using my own pc now so i can't do it now but tomorrow i will upload my database here that you can see the containts easily../..


Thanks

can you tell me what are you thinking about my database what is the problem??
i mean what are the possiblities to be wrong with it???

 

[jai kushwaha]

i will send you a screenshot of my combo box

 

[jai kushwaha]

here is the screen shot of combo box and also programming

 

[jai kushwaha]

hello friend it is not easy to talk on this forum because i dont know that how to add here things like a copy of database or multiple pic etc. if you can provide me your email id it will be easier.


thanks friend.....