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

  • Thread starter Thread starter Desilu via AccessMonster.com
  • Start date Start date
D

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.
 
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)
Click to expand...

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
 
Hunter57 said:
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)
Click to expand...

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
Click to expand...
[quoted text clipped - 10 lines]
Thanks.
Click to expand...
Click to expand...

Thank you. I'll give this a try!
 
Back
Top