Relational Combo Boxes not Working

[Tophan]

My form stopped working again. It was working perfectly and all I did was format it and now everytime i open it the cboCategory parameter box pops up.

What am I doing wrong??

 

[Gasman]

What do you mean 'format it'?
You do not even say what form?

Anyway I opened all 4 forms and did not get one parameter request?

 

[bob fitz]

My form stopped working again. It was working perfectly and all I did was format it and now everytime i open it the cboCategory parameter box pops up.

What am I doing wrong??

You need to add the following line to the GotFocus event of the Product combo:
Me.ActiveControl.Requery

 

[Tophan]

You need to add the following line to the GotFocus event of the Product combo:
Me.ActiveControl.Requery

HI,

By formatting it I meant just making the form look "pretty" - add colour, form headers, adjust font, etc.

I added the event on the GotFocus but still having issues.

The first picture is what I was seeing when I first opened the form (before adding the GotFocus) event

Now the product combo box is working but when I tab to the next record in the subform, the selection in cboProduct disappears and I can only see it when the cursor is in the field.

See pictures 2 & 3

 

[CJ_London]

did you ever look at the link I provided in post #5?

 

[Tophan]

did you ever look at the link I provided in post #5?

Yes I did but unfortunately I didn't fully understand it.

I'll try again to go through it to see if I can figure out what to do.

In the meantime, I have attached a .gif to show what is happening

 

[CJ_London]

would have been nice if you had the courtesy to at least acknowledge you had looked and didn't understand - might have saved you 20+ posts.

Unfortunately my internet has slowed to a snails pace, so cannot open the gif

 

[Tophan]

I'm sorry for not acknowledging the individual post; I did however, thank everyone for their assistance in post #22.

I really do appreciate everyone's help but I guess my beginner status is an irritant as this is the second time I have been admonished on this website for not understanding.

My apologies. I will figure it out on my own.

 

[June7]

Technically, the combobox is working, just not as nicely as you would like. Reason for that behavior was explained in post 15. Here is more on the topic https://stackoverflow.com/questions...ous-form-the-second-combo-doesnt-show-its-val

This is also why MajP suggested VBA solution in post 9 that sets combobox RowSource property. CJ's sample db probably does similar. In any case, I suggest you use the simpler SQL statement for combobox RowSource.

 

[Gasman]

I'm sorry for not acknowledging the individual post; I did however, thank everyone for their assistance in post #22.

I really do appreciate everyone's help but I guess my beginner status is an irritant as this is the second time I have been admonished on this website for not understanding.

My apologies. I will figure it out on my own.

Don't go off in a huff. It is unlikely you will figure it out for yourself, so you are just cutting off your nose to spite your face.
You are not the first person to not say you do not understand whatever is in a link that is posted, and you will not be the last.

Up to you. we have had other members doing the same thing, then realise their error and then they come back asking for more help.

I do not understand how I could open all 4 forms and not get any prompt?

 

[CJ_London]

with respect - if you don't understand, don't just ignore, ask for clarification

I don't see any db uploaded by you so not sure which 4 forms Gasman is referring to but I can now open your gif and what it is indicating to me is you don't have any code (or if you do, it is ineffective) in the combo lost focus or exit event to restore the full list of options- see the example I posted

 

[Gasman]

with respect - if you don't understand, don't just ignore, ask for clarification

I don't see any db uploaded by you so not sure which 4 forms Gasman is referring to but I can now open your gif and what it is indicating to me is you don't have any code (or if you do, it is ineffective) in the combo lost focus or exit event to restore the full list of options- see the example I posted

I *think* it was in post #23, but now removed.
Here is the version I downloaded.

 

[CJ_London]

hmm well in that version there is no code on cbobroduct and a macro on cbocategory which doesn't appear to do anything

I did the following
1. removed the macro
2. put in the code as indicated in my example

Private Sub cboProduct_Enter()

    cboProduct.RowSource = "SELECT ProductID, ProductName FROM tblProducts WHERE CategoryID=[cboCategory] ORDER BY ProductName"

End Sub


Private Sub cboProduct_Exit(Cancel As Integer)

    cboProduct.RowSource = "SELECT ProductID, ProductName FROM tblProducts”

End Sub

3. changed the rowsource in cboProduct so it works when form first opened to SELECT ProductID, ProductName FROM tblProducts


and works for me

Edit: actually only needs the order by in the enter event - code modified above. Ordering takes time (minimal, but why waste resources?) and only necessary when exposing the drop-down

 

[Tophan]

@CJ_London Thank you for your help even after as @Gasman rightfully said I left in a huff.

Please accept my apologies but I can't help but feel slightly embarrassed when I don't fully understand.

I am self-taught and although I have been using Access for years I still consider myself a beginner when it comes to vba.

Again...thank you