Programmatically Update a Form's Combo Box Default Value and SQL Record Source

[Desilu via AccessMonster.com]

I have one Sales Activity form that I use for data entry and for viewing all
records by Sales Person (EmployeeID). Without having to have multiple
instances of this form, I want to accomplish the below; based on which
EmployeeID (sales person) opens the form in either mode:

1. For the data entry mode – I want the Combo Box’s Default Value to change,
based on my user (EmployeeID);
2. Show All Records mode – I want the SQL record source to filter on that
sales person’s records (EmployeeID)

I’ve got some code for passwords, so I hope I can utilize that part of it; I
just do not know how to do the above two steps.

Thanks.

 

[Hunter57]

Hi Desilu,

Here is some code that will change the RecordSource for the Combo box.

Private Sub Private Sub Form_Load()

' Create a variable to hold the Employee ID
Dim lngEmpID As Long

lngEmpID = EmployeeLogonID(without more information I can't tell you what
to put here for EmployeeLogonID.)

' Build a SQL statement for the combo box
Dim strSQL As String

strSQL = "Select EmployeeID, myOtherfield, From tblEmployees WHERE
EmployeeID = " & lngEmpID

' Set the combo box recordsource
MyCboName.RowSource = strSQL

'Activate the combo box
Me.CboName.Requery

End Sub

If you need to show a value in the combo box other than the EmployeeID set
the first Column width to 0"

--------------------------------

2. Show All Records mode – I want the SQL record source to filter on that
sales person’s records (EmployeeID)

Just add to your Recordsource Query: "Where EmployeeID = " &
Me.mycboEmployeeID

Or:

Private Sub Form_Current()

Dim strOriginalRecSource As String
Dim strEmployeeRecSource As String

' Get the present RecordSource
strOriginalRecSource = Me.RecordSource

' Add a criteria for the EmployeeID
strEmployeeRecSource = strOriginalRecSource & " Where EmployeeID = " &
Me.mycboEmployeeID

' Change to the New RecordSource
Me.RecordSource = strEmployeeRecSource

End Sub


' To Change the Recordsource back
Me.RecordSource = strOriginalRecSource

Best Regards,
Patrick Wood
www.advancingsoftware.com
www.churchmanagesoftware.com

 

[Desilu via AccessMonster.com]

Hi Desilu,

Here is some code that will change the RecordSource for the Combo box.

Private Sub Private Sub Form_Load()

' Create a variable to hold the Employee ID
Dim lngEmpID As Long

lngEmpID = EmployeeLogonID(without more information I can't tell you what
to put here for EmployeeLogonID.)

' Build a SQL statement for the combo box
Dim strSQL As String

strSQL = "Select EmployeeID, myOtherfield, From tblEmployees WHERE
EmployeeID = " & lngEmpID

' Set the combo box recordsource
MyCboName.RowSource = strSQL

'Activate the combo box
Me.CboName.Requery

End Sub

If you need to show a value in the combo box other than the EmployeeID set
the first Column width to 0"

--------------------------------

2. Show All Records mode – I want the SQL record source to filter on that
sales person’s records (EmployeeID)

Just add to your Recordsource Query: "Where EmployeeID = " &
Me.mycboEmployeeID

Or:

Private Sub Form_Current()

Dim strOriginalRecSource As String
Dim strEmployeeRecSource As String

' Get the present RecordSource
strOriginalRecSource = Me.RecordSource

' Add a criteria for the EmployeeID
strEmployeeRecSource = strOriginalRecSource & " Where EmployeeID = " &
Me.mycboEmployeeID

' Change to the New RecordSource
Me.RecordSource = strEmployeeRecSource

End Sub

' To Change the Recordsource back
Me.RecordSource = strOriginalRecSource

Best Regards,
Patrick Wood
www.advancingsoftware.com
www.churchmanagesoftware.com

I have one Sales Activity form that I use for data entry and for viewing all
records by Sales Person (EmployeeID). Without having to have multiple

[quoted text clipped - 10 lines]

Thanks.

Thank you. I'll give this a try!