Relational Combo Boxes not Working

[bob fitz]

Ok...so I'm still getting a couple problems.

I've tried both @MajP and @bob fitz methods and getting the same error message when I try to tab to a new record in the subform. A message box pops up which states You cannot add or change a record because a related record is required in table 'tblproducts'. Also, I noticed when i did select a product the same product shows in the line below.

I then tried @June7 method and I am getting the error message that I tried entering text in a numeric field.

Combo Products needs to be bound to the ProductID field.
The Row Source property needs to be changed to something like:

SELECT tblProducts.ProductID, tblProducts.ProductName FROM tblCategories INNER JOIN tblProducts ON tblCategories.CategoryID = tblProducts.CategoryID WHERE (((tblCategories.CategoryID)=[Forms]![frmMaterialRequisition]![frmMaterialRequisitionDtls].[Form]![cboCategories]));

You will also need to change its ColumnCount property to 2 and its Column Widths property to: 0cm;2.501cm

 

[Tophan]

With everyone's help and through trial and error (several trials and errors) I think I finally got it fixed.
This is what I have for combo cboProduct.

Control Source: ProductID
Row source: as below

SELECT tblProducts.ProductID, tblProducts.ProductName
FROM tblProducts
WHERE (((tblProducts.CategoryID)=[cboCategory]))
ORDER BY tblProducts.ProductName;

Then I also have the combo cboCategories requerying cboProduct on the After Update event.

Again...that you all for your patience and help.

 

[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