I have a form with textboxes that a user
can enter values into.
After the user enters values into one or more
of these textboxes, they can click a button to
run a stored proc that returns a query result set
used as the underlying recordset for a subForm
....filtered to their search params.
HTH ..
This is the Form side code:
Dim oCmd As Command, param As Parameter
Dim cn As New ADODB.Connection, sqlString As String
sqlString = "DynamicSearch_DCC"
Set oCmd = New ADODB.Command
Set cn = CurrentProject.Connection
Set oCmd.ActiveConnection = cn
oCmd.CommandText = sqlString
oCmd.CommandType = adCmdStoredProc
oCmd.CommandTimeout = 15
Set param = New ADODB.Parameter
param.Type = adChar
param.Size = 50
param.Direction = adParamInput
param.Value = Me.CurUser
param.Name = "u"
oCmd.Parameters.Append param
Set param = New ADODB.Parameter
param.Type = adChar
param.Size = 50
param.Direction = adParamInput
param.Value = Me.Customer
param.Name = "cust"
oCmd.Parameters.Append param
Set param = New ADODB.Parameter
param.Type = adChar
param.Size = 50
param.Direction = adParamInput
param.Value = Me.JobSite
param.Name = "jobsite"
oCmd.Parameters.Append param
Set param = New ADODB.Parameter
param.Type = adDate
'param.Size = 40
param.Direction = adParamInput
param.Value = Me.Early
param.Name = "early"
oCmd.Parameters.Append param
Set param = New ADODB.Parameter
param.Type = adDate
'param.Size = 40
param.Direction = adParamInput
param.Value = Me.Latest
param.Name = "late"
oCmd.Parameters.Append param
Set param = New ADODB.Parameter
param.Type = adChar
param.Size = 30
param.Direction = adParamInput
param.Value = Me.OrderTakenBy
param.Name = "TakenBy"
oCmd.Parameters.Append param
Set param = New ADODB.Parameter
param.Type = adChar
param.Size = 5
param.Direction = adParamInput
param.Value = Me.CreditBy
param.Name = "CreditBy"
oCmd.Parameters.Append param
Set param = New ADODB.Parameter
param.Type = adChar
param.Size = 20
param.Direction = adParamInput
param.Value = Me.Salesman
param.Name = "slm"
oCmd.Parameters.Append param
Set param = New ADODB.Parameter
param.Type = adInteger
param.Size = 8
param.Direction = adParamInput
param.Value = Me.OrderID
param.Name = "oid"
oCmd.Parameters.Append param
Set param = New ADODB.Parameter
param.Type = adInteger
param.Size = 8
param.Direction = adParamInput
param.Value = Me.JobNumber
param.Name = "jid"
oCmd.Parameters.Append param
Set param = New ADODB.Parameter
param.Type = adChar
param.Size = 20
param.Direction = adParamInput
param.Value = Me.Contract
param.Name = "contract"
oCmd.Parameters.Append param
Set param = New ADODB.Parameter
param.Type = adInteger
param.Size = 8
param.Direction = adParamInput
param.Value = Me.OffRentID
param.Name = "offid"
oCmd.Parameters.Append param
Set param = New ADODB.Parameter
param.Type = adChar
param.Size = 8
param.Direction = adParamInput
param.Value = Me.UNIT
param.Name = "unit"
oCmd.Parameters.Append param
Set param = New ADODB.Parameter
param.Type = adChar
param.Size = 3
param.Direction = adParamInput
param.Value = Me.mt
param.Name = "mt"
oCmd.Parameters.Append param
Set param = New ADODB.Parameter
param.Type = adChar
param.Size = 3
param.Direction = adParamInput
param.Value = Me.dur
param.Name = "dur"
oCmd.Parameters.Append param
oCmd.Execute , , adExecuteNoRecords
cn.Close
Set cn = Nothing
ALTER Procedure [dbo].[DynamicSearch_DCC]
@u char(50),
@Cust char(50),
@jobSite char(50),
@early datetime,
@late datetime,
@TakenBy char(30),
@CreditBy char(5),
@slm char(20),
@oid int,
@jid int,
@contract char(20),
@offid int,
@unit char(8),
@mt char(3),
@dur char(1)
As
set nocount on
delete from DynamicLast50kSearch
where CurUser = @u
set nocount on
declare @sqlText nvarchar(4000)
declare @ParamList nvarchar(4000)
select @sqlText =
'
Insert Into DynamicLast50kSearch
(
markedCompleteBy,
or_eq_id,
CurUser,
mt,
OrderID,
jobnumber,
OffRentID,
Contract,
unit,
preference,
JobCompleted,
Customer,
JobSite,
Salesman,
OrderTakenBy,
CreditBy,
early,
latest,
dur
)
Select
"---",
ore.or_eq_id,
@xu,
mt.mt,
orq.OrderID,
j.jobnumber,
ore.OffRentID,
ua.Contract,
ore.unit,
ore.preference,
j.JobCompleted,
orq.Customer,
rtrim(st.Street) + '' '' + rtrim(st.Address2) + '' '' + rtrim(st.City)
jobSite,
s.Salesman,
orq.OrderTakenBy,
j.stage,
convert(char(5),ore.moveEarliest,10) early,
convert(char(5),ore.moveLatest,10) latest,
ore.dur
from
OrderRequest orq
inner join OrderRequest_Equipment ore on
orq.orderid = ore.orderid
left join Salesmen s on
orq.SalesmanID = s.[emp #]
inner join jobs j on
ore.or_eq_id = j.or_eq_id
left join tbl_Site st on
orq.SiteID = st.Siteid
left join UnitAssignments ua on
ua.or_eq_id = ore.or_eq_id
left join mt_types mt on
ore.movetype = mt.mt_id
where
1 = 1 '
--CUSTOMER
if @cust Is not null
begin
Select @sqlText = @sqlText +
' and orq.Customer like rtrim(@xcust) + ''%'''
-- SELECT @sql = @sql + ' AND c.CompanyName LIKE @xcustname + ''%'''
end
--JOBSITE
if @jobSite is not null
begin
Select @sqlText = @sqlText +
' and st.street like rtrim(@xjobSite) + ''%'''
end
--EARLIEST
if @early is not null
begin
-- select @early = cast(@early as datetime)
--print @early
Select @sqlText = @sqlText +
' and ore.moveEarliest >= @xearly '
end
--LATEST
if @late is not null
begin
-- select @late = cast(@late as datetime)
--print @early
Select @sqlText = @sqlText +
' and MoveEarliest <= movelatest and ore.movelatest <= + @xlate'
end
--TakenBy
if @TakenBy is not null
begin
Select @sqlText = @sqlText +
' and orq.OrderTakenBy like rtrim(@xTakenBy) + ''%'''
end
--CreditBy
if @CreditBy is not null
begin
Select @sqlText = @sqlText +
' and ua.Credit like rtrim(@xCreditBy) + ''%'''
end
--Salesman
if @slm is not null
begin
Select @sqlText = @sqlText +
' and s.salesman like rtrim(@xslm) + ''%'''
end
--OrderID
if @oid is not null
begin
select @oid = cast(@oid as int)
Select @sqlText = @sqlText +
' and orq.OrderID = @xoid'
end
--JobNumber
if @jid is not null
begin
select @jid = cast(@jid as int)
Select @sqlText = @sqlText +
' and j.jobnumber = @xjid'
end
--Contract
if @contract is not null
begin
Select @sqlText = @sqlText +
' and ua.contract = @xcontract'
end
--OffRentID
if @offid is not null
begin
select @offid = cast(@offid as int)
Select @sqlText = @sqlText +
' and ore.offRentID = @xoffid'
end
--Unit
if @unit is not null
begin
Select @sqlText = @sqlText +
' and ore.unit = @xunit'
end
--Move Type
if @mt is not null
begin
Select @sqlText = @sqlText +
' and mt.mt = @xmt'
end
--Duration
if @dur is not null
begin
Select @sqlText = @sqlText +
' and ore.dur = @xdur'
end
Select @ParamList = '@xu char(50),
@xCust char(50),
@xjobSite char(50),
@xearly datetime,
@xlate datetime,
@xTakenBy char(30),
@xCreditBy char(5),
@xslm char(20),
@xoid int,
@xjid int,
@xcontract char(20),
@xoffid int,
@xunit char(8),
@xmt char(3),
@xdur char(1)'
EXEC sp_executesql @sqlText, @ParamList,
/*the following vars feed the ParamList*/
@u,
@Cust,
@jobSite,
@early,
@late,
@TakenBy,
@CreditBy,
@slm,
@oid,
@jid,
@contract,
@offid,
@unit,
@mt,
@dur
