Combo Box Record Source (1 Viewer)

Jamaluddin Jamal · Apr 29, 2017

Hello everyone.

I am working on the form having Tab Control for different "Categories". The tab, say Category 1, contains 2 Combo Boxes cboType and cboID. by selecting Pay Type, cboID only shows all those record (or Emp ID) whose Pay Type is selected from cboType. I used the following VBA on OnChange event of cboType but its not working, however, its working if I but all controls directly on form instead of Tab Control:

Code:

Me.cboID.RowSourceType = "Table/Query"
Me.cboID.RowSource = "SELECT EmpID FROM tblEmp WHERE tblEmp.PayType = me.cboType"

Can anyone please help how to figure out this. File is attached for reference.

Thanks in Advance.

isladogs · Apr 29, 2017

You need to consider the datatype of the source in Me.cboType
Also add the trailing ; as shown in RED

Try this

If Me.cboType is a number type:

Code:

Me.cboID.RowSource = "SELECT EmpID FROM tblEmp WHERE tblEmp.PayType = [COLOR="Red"]" & me.cboType;[/COLOR]

or if its a text string:

Code:

Me.cboID.RowSource = "SELECT EmpID FROM tblEmp WHERE tblEmp.PayType =[COLOR="red"] '" & me.cboType & "';"[/COLOR]

Unless some other code elsewhere changes the row source to e.g. value list, I'm not sure of the need for this line:

Code:

Me.cboID.RowSourceType = "Table/Query"

You can just set this in the combo box properties

EDIT:
I've just looked at your form & its text datatype so use the 2nd option above.
Also the code would be better in the cboType_AfterUpdate event

You should also use Option Explicit in all objects to prevent errors later

The code would be:

Code:

Option Compare Database
Option Explicit

Private Sub cboType_AfterUpdate()

    Me.cboID.RowSourceType = "Table/Query"
    Me.cboID.RowSource = "SELECT EmpID FROM tblEmp WHERE tblEmp.PayType = '" & Me.cbotype & "';"

End Sub

Combo Box Record Source (1 Viewer)

Jamaluddin Jamal

Registered User.

Attachments

isladogs

MVP / VIP

Similar threads

Users who are viewing this thread