cbo AFTER UPDATE EVENT NOT WORKING EVERYTIME (1 Viewer)

[chrisjames25]

HI. Been a while but as snow has ground company to a halt i have some time to crack on with access.

I have a form with 2 comboboxes. THe second is populated depending on the choice of the first combobox.

What i wanted and what is kind of working is the following. I wanted that if i chose say Coke in cbo1, cbo2 would populate with all the varieties of coke sold. ie diet coke normal coke etc. However if i chose a brand with only 1 variety. Lets say Doctor Pepper, it would automatically lock the second cbo and populate it with the only variety available.

THis all works fine based on code below. NOTE - only put the code in that does the populating second cbo if only 1 exsits as it appears where error is

 Dim msg, style, title, response, MyString, Field, source, criteria
 
     
        Field = StrTier1 & "_ID"
        source = "Tbl_" & StrTier2
        criteria = Field & "= " & Me.Cbo_Tier1
        
         If DCount(Field, source, criteria) = 1 Then
        Me.Cbo_Tier2.Requery
        Me.Cbo_Tier2 = Me.Cbo_Tier2.ItemData(0)
        
       Me.Cbo_Tier2.Locked = True
       'Me.Cbo_Tier2.Enabled = False
            
        With Me.Txt_NewTier3
            .Value = ""
            .Enabled = True
        End With
        
        With Me.Txt_ConfirmTier3
            .Value = ""
            .Enabled = False
        End With
        
         Me.SrchText.Value = vbNullString
        
         Me.Child585.Requery

Heres issue. If i choose Dr Pepper in cbo1 the above code works and cbo 2 is automatically generated and locked. Brilliant for what i want.

THe problem is if i choose coke in cbo 1 then tab through all the form back to the top and change my cbo1 selection to doctor pepper the automatically chooseing of cbo2 and locking of it doesnt occur even if the vba code cycles past the code.

Any ideas? Is after update the incorrect event to use?

 

[RuralGuy]

I'm going to start by suggesting your use this link to review your code and the use of reserved words: http://www.allenbrowne.com/AppIssueBadWord.html#F

 

[Ranman256]

why do you need so much code?
cbo2 uses a query that looks at cbo1
select city from table where [state]=forms!myForm!cbo1State

if cbo1 is states, then user picks a state,
all the afterupdate code you need for cbo2 (cities) is this:

sub cbo1States_Afterupdate()
  cbo2Cities.requery
sub

 

[chrisjames25]

Hi, Thanks for feedback so far. Decided best to post my work to illustrate my problem. See file attached. Follow these steps to see issue:

1. Open Frm_AddTier3
2. In top combobox enter Fuchsia
3. You will then see second combobox is automatically populated and locked and the form moves to first textbox.
4. Click reset.
5. type in clematis in first combobox. Press tab
6. as more than one tier2 option for clematis the seoncd combobox is not auto populated so you tab index to that next.
7. Now shift tab back to first combobox and type in fuchsia again and press tab.
8. You end up in the second combobox rather than it being locked and you jumping to the textbox. Cannot figure out why this is occurring as it runs through the code ok.

ANy thoughts much appreciated.

 

[jdraw]

Nothing posted??

 

[chrisjames25]

Got an upload file size issue. Working on it

 

[chrisjames25]

Here it is

 

[JHB]

Is this the way you want it to act?

 

[arnelgp]

also this one.