Combo Box questions

[alphaomega3]

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?

[NeoPa]

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

[alphaomega3]

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.

[NeoPa]

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.

[alphaomega3]

how do i send this to you?

[NeoPa]

All the things I've asked for are strings, so can be posted in here.

[alphaomega3]

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. =)

[NeoPa]

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 :

Form Name = frmNCR
Control (CheckBox) = chkInternal
Control (ComboBox) = cboSuppliers
RecordSource (when in Internal mode) = qryInternalSuppliers
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).

[alphaomega3]

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:

Form = frmMasterEntry
Control  (Checkbox) = chkExternal
Control (Combo Box)= cbosuppname (currently bound; control source = SuppName row source= tblSupplier)
Recordsource (when Intenal)= tblsupplier;  SuppName (text)
Recordsource (when External) = qryMacolaVendA;  VendName (text)

[NeoPa]

That seems to be the business.
I'll see what I can come up with for you today if possible.

[NeoPa]

Try the following as a starting point :

Option Compare Database
Option Explicit
 
Private Const conInternal As String = "SELECT [SuppName] FROM [tblSupplier]"
Private Const conExternal As String = "SELECT [VendName] FROM [qryMacolaVendA]"
 
Private Sub chkExternal_AfterUpdate()
  Me.cboSuppName.RowSource = IIf(Me.chkExternal, conExternal, conInternal)
  Call Me.cboSuppName.Requery
End Sub

[alphaomega3]

Try the following as a starting point :

Option Compare Database
Option Explicit
 
Private Const conInternal As String = "SELECT [SuppName] FROM [tblSupplier]"
Private Const conExternal As String = "SELECT [VendName] FROM [qryMacolaVendA]"
 
Private Sub chkExternal_AfterUpdate()
  Me.cboSuppName.RowSource = IIf(Me.chkExternal, conExternal, conInternal)
  Call Me.cboSuppName.Requery
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.

[NeoPa]

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.

[alphaomega3]

Never done any debugging....Wouldn't have a clue where to start.

[NeoPa]

That's why I posted the link - let me know if you have any dificulties after reading the article.