Add item to Combo Box dilemma

G

Guest

Among others, my database includes three tables: tblBrand, tblBrandDetails,
and tblWholesaler with a many-to-many relationship between tblBrand and
tblWholesaler.

The tblBrandDetails table consists of two fields: BrandID and WholesalerID
and the WholesalerID field is a lookup field with its row source set to
lookup the WholesalerID and WholesalerName fields in tblWholesaler.

I have a Brand form (single form view) with a subform (datasheet view) in my
database. The subform (fsubSelectWholesalers) is based on a query that has a
one-to-many relationship between tblWholesaler and tblBrandDetails. There is
a combo box on the subform that allows you to select one or more wholesalers
related to the brand.

I have a button on the Brand form that opens another form (frmAddWholesaler)
so that if the wholesaler is not in the combo box you can open
frmAddWholesaler and add it.

But after adding a new wholesaler and closing frmAddWholesaler, it doesn’t
show up in the combo box on the subform unless you close the Brand form (and
the embedded fsubSelectWholesalers) and reopen it. I’ve tried adding a save
record button on frmAddWholesaler, on the subform and on the Brand form, but
it still doesn’t work. Is there a simple way (a new user may hopefully
understand) to make this work?
 
J

Joan Wild

Katherine said:
But after adding a new wholesaler and closing frmAddWholesaler, it
doesn't show up in the combo box on the subform unless you close the
Brand form (and the embedded fsubSelectWholesalers) and reopen it.
Click to expand...

Requery the combobox when you close frmAddwholesaler.
 
R

RuralGuy

Among others, my database includes three tables: tblBrand, tblBrandDetails,
and tblWholesaler with a many-to-many relationship between tblBrand and
tblWholesaler.

The tblBrandDetails table consists of two fields: BrandID and WholesalerID
and the WholesalerID field is a lookup field with its row source set to
lookup the WholesalerID and WholesalerName fields in tblWholesaler.

I have a Brand form (single form view) with a subform (datasheet view) in my
database. The subform (fsubSelectWholesalers) is based on a query that has a
one-to-many relationship between tblWholesaler and tblBrandDetails. There is
a combo box on the subform that allows you to select one or more wholesalers
related to the brand.

I have a button on the Brand form that opens another form (frmAddWholesaler)
so that if the wholesaler is not in the combo box you can open
frmAddWholesaler and add it.

But after adding a new wholesaler and closing frmAddWholesaler, it doesn’t
show up in the combo box on the subform unless you close the Brand form (and
the embedded fsubSelectWholesalers) and reopen it. I’ve tried adding a save
record button on frmAddWholesaler, on the subform and on the Brand form, but
it still doesn’t work. Is there a simple way (a new user may hopefully
understand) to make this work?
Click to expand...

You need to add:
Response = acDataErrAdded

in your NotInList event so the ComboBox will Requery.

_______________________________________________
hth - RuralGuy (RG for short)
Please post to the NewsGroup so all may benefit.
 
G

Guest

This is what I have now in the NotInList event (of the combo box):

Private Sub WholesalerID_NotInList(NewData As String, Response As Integer)
Response = acDataErrAdded
End Sub

It still doesn't work. Did I leave something out? (Sorry, I'm still
working on the beginner level)

I'm not sure if it matters, but the subform (fsubSelectWholesalers) is based
on a query that is based on the tables tblBrandDetails and tblWholesaler, and
frmAddWholesaler is based on a query that is based on just one table -
tblWholesaler.
 
R

RuralGuy

I made an assumption (which was wrong)! Joan got it right, as usual.
:) Post back if you need more instruction. The way you have done
this, you don't need anything in the NotInList event of your ComboBox,
sorry.

This is what I have now in the NotInList event (of the combo box):

Private Sub WholesalerID_NotInList(NewData As String, Response As Integer)
Response = acDataErrAdded
End Sub

It still doesn't work. Did I leave something out? (Sorry, I'm still
working on the beginner level)

I'm not sure if it matters, but the subform (fsubSelectWholesalers) is based
on a query that is based on the tables tblBrandDetails and tblWholesaler, and
frmAddWholesaler is based on a query that is based on just one table -
tblWholesaler.
Click to expand...

_______________________________________________
hth - RuralGuy (RG for short)
Please post to the NewsGroup so all may benefit.
 
G

Guest

Thanks. I don't understand Joan's response: "Requery the combox when you
close frmAddWholesaler" How do I do that? Help Joan! (or RuralGuy).
 
G

Guest

I've got it now. I added the following to the On Got Focus event of the
ComboBox:
Me!cboWholesaler.Requery

Thank you both!!!
 
G

Guest

R

RuralGuy

You can now remove the requery from the GotFocus event. It is not
necessary and just slows down your code. You're welcome from both of
us. Glad you were able to get things working.

I've got it now. I added the following to the On Got Focus event of the
ComboBox:
Me!cboWholesaler.Requery

Thank you both!!!
Click to expand...

_______________________________________________
hth - RuralGuy (RG for short)
Please post to the NewsGroup so all may benefit.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Tables and Relationships Design Question 2
Make a field based on combining records? 3
Tables and Relationships (Re Posted) 4
Link forms using two primary keys 6
Limit combo box to show only values not already selected 2
Auto fill fields based on combo box selection 5
Form/Subform combo box dependent combo box 7
Changing a combo box from unbound to bound! 5

Top