By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,551 Members | 2,710 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,551 IT Pros & Developers. It's quick & easy.

Combo Box questions

P: 78
I have two lists of suppliers (internal and external) for an NCR (Non-Conformance Report) database (Supplier Rating System) on a form. Currently we use a combo box to drop down the Internal Suppliers. However, I need to be able to add a check box or other control that chooses which list to provide. Combining the two is not an option....The list of external vendors is a pass-through query to our ERP system. Also, I use Joins in many many many of my queries to differentiate between internal and external suppliers, so combining them is not an option. How would be the best way to handle this?
Jun 12 '07 #1
Share this Question
Share on Google+
14 Replies


NeoPa
Expert Mod 15k+
P: 31,494
Have a CheckBox on the form for Internal/External.
In the AfterUpdate event procedure, set up the ComboBox RecordSource et all properties.
Jun 13 '07 #2

P: 78
Have a CheckBox on the form for Internal/External.
In the AfterUpdate event procedure, set up the ComboBox RecordSource et all properties.

Do you have an example? I have never done this before.
Jun 13 '07 #3

NeoPa
Expert Mod 15k+
P: 31,494
If you set up the basic form with the required controls on it and post the names of all of them we'll see what we can do to make it update.
I will also need the two different versions of the RecordSource strings you need.
Jun 13 '07 #4

P: 78
how do i send this to you?
Jun 13 '07 #5

NeoPa
Expert Mod 15k+
P: 31,494
All the things I've asked for are strings, so can be posted in here.
Jun 13 '07 #6

P: 78
All the things I've asked for are strings, so can be posted in here.
?? Still confused about what you need then. Please be patient cause I am no way near being an expert in this. You say these things are strings however I am not sure what you mean. So talk to me as if I dont know anything. =)
Jun 13 '07 #7

NeoPa
Expert Mod 15k+
P: 31,494
If you set up the basic form with the required controls on it and post the names of all of them we'll see what we can do to make it update.
I will also need the two different versions of the RecordSource strings you need.
An example of what I asked for would be something like :
Expand|Select|Wrap|Line Numbers
  1. Form Name = frmNCR
  2. Control (CheckBox) = chkInternal
  3. Control (ComboBox) = cboSuppliers
  4. RecordSource (when in Internal mode) = qryInternalSuppliers
  5. RecordSource (when in External mode) = qryExternalSuppliers
Obviously I don't know what you've got though so this is simply an example. I need to know what you've got in reality. Please be as accurate as you can and don't miss anything out as returning to ask for things again will just slow the whole process (We'll do it if we must though - I'm not planning on leaving you in the lurch any time soon).
Jun 13 '07 #8

P: 78
Okay, Now I know what you mean and I think I know what you need.

Okay currently I don't have the checkbox but this is what I will be adding:

Expand|Select|Wrap|Line Numbers
  1. Form = frmMasterEntry
  2. Control  (Checkbox) = chkExternal
  3. Control (Combo Box)= cbosuppname (currently bound; control source = SuppName row source= tblSupplier)
  4. Recordsource (when Intenal)= tblsupplier;  SuppName (text)
  5. Recordsource (when External) = qryMacolaVendA;  VendName (text)
Jun 13 '07 #9

NeoPa
Expert Mod 15k+
P: 31,494
That seems to be the business.
I'll see what I can come up with for you today if possible.
Jun 13 '07 #10

NeoPa
Expert Mod 15k+
P: 31,494
Try the following as a starting point :
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private Const conInternal As String = "SELECT [SuppName] FROM [tblSupplier]"
  5. Private Const conExternal As String = "SELECT [VendName] FROM [qryMacolaVendA]"
  6.  
  7. Private Sub chkExternal_AfterUpdate()
  8.   Me.cboSuppName.RowSource = IIf(Me.chkExternal, conExternal, conInternal)
  9.   Call Me.cboSuppName.Requery
  10. End Sub
Jun 13 '07 #11

P: 78
Try the following as a starting point :
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private Const conInternal As String = "SELECT [SuppName] FROM [tblSupplier]"
  5. Private Const conExternal As String = "SELECT [VendName] FROM [qryMacolaVendA]"
  6.  
  7. Private Sub chkExternal_AfterUpdate()
  8.   Me.cboSuppName.RowSource = IIf(Me.chkExternal, conExternal, conInternal)
  9.   Call Me.cboSuppName.Requery
  10. End Sub
Thank you!! Though for some reason it works one-dirction only. If for some reason the data entry person clicks on it accidentally then unclicks the combo list is blank, it doesn't return to tblSupplier list.
Jun 14 '07 #12

NeoPa
Expert Mod 15k+
P: 31,494
Have you tried tracing the code to see what goes wrong?
Put a breakpoint on line 8 and see what happens. See Debugging in VBA for help on tracing.
Jun 14 '07 #13

P: 78
Never done any debugging....Wouldn't have a clue where to start.
Jun 15 '07 #14

NeoPa
Expert Mod 15k+
P: 31,494
That's why I posted the link - let me know if you have any dificulties after reading the article.
Jun 15 '07 #15

Post your reply

Sign in to post your reply or Sign up for a free account.