|
|
Our Hot Pick: Rising Antivirus 2006 - Certified by TUV & Checkmark! Get 10% discount by entering this coupon code: ONDISCOUNT10
I've been fumbling around with this for entirely too long, and I need some
assistance.
What I'm trying to do is to set up a form where the user can select from a
particular set of fields (which obviously exist in one of my tables).
After selecting the fields, I'd like to execute an SQL query via VBA. I only
want the query to display the fields that have been selected.
Does anyone know how to do this?? I'm in definate need of one of you gurus.
Thanks
(After selecting the fields I'd eventually like them to be able to set up
some sorting options and maybe specific criteria as to which records to
display.) - not too concerned with this though - I just really need help with
the above problem
--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-adp-sqlserver/200606/1
|
|
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
[Quoted Text] >>>This is the Stored Proc
ALTER Procedure [dbo].[DynamicSearch_DCC]
[ at ]u char(50),
[ at ]Cust char(50),
[ at ]jobSite char(50),
[ at ]early datetime,
[ at ]late datetime,
[ at ]TakenBy char(30),
[ at ]CreditBy char(5),
[ at ]slm char(20),
[ at ]oid int,
[ at ]jid int,
[ at ]contract char(20),
[ at ]offid int,
[ at ]unit char(8),
[ at ]mt char(3),
[ at ]dur char(1)
As
set nocount on
delete from DynamicLast50kSearch
where CurUser = [ at ]u
set nocount on
declare [ at ]sqlText nvarchar(4000)
declare [ at ]ParamList nvarchar(4000)
select [ at ]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,
[ at ]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 [ at ]cust Is not null
begin
Select [ at ]sqlText = [ at ]sqlText +
' and orq.Customer like rtrim([ at ]xcust) + ''%'''
-- SELECT [ at ]sql = [ at ]sql + ' AND c.CompanyName LIKE [ at ]xcustname + ''%'''
end
--JOBSITE
if [ at ]jobSite is not null
begin
Select [ at ]sqlText = [ at ]sqlText +
' and st.street like rtrim([ at ]xjobSite) + ''%'''
end
--EARLIEST
if [ at ]early is not null
begin
-- select [ at ]early = cast([ at ]early as datetime)
--print [ at ]early
Select [ at ]sqlText = [ at ]sqlText +
' and ore.moveEarliest >= [ at ]xearly '
end
--LATEST
if [ at ]late is not null
begin
-- select [ at ]late = cast([ at ]late as datetime)
--print [ at ]early
Select [ at ]sqlText = [ at ]sqlText +
' and MoveEarliest <= movelatest and ore.movelatest <= + [ at ]xlate'
end
--TakenBy
if [ at ]TakenBy is not null
begin
Select [ at ]sqlText = [ at ]sqlText +
' and orq.OrderTakenBy like rtrim([ at ]xTakenBy) + ''%'''
end
--CreditBy
if [ at ]CreditBy is not null
begin
Select [ at ]sqlText = [ at ]sqlText +
' and ua.Credit like rtrim([ at ]xCreditBy) + ''%'''
end
--Salesman
if [ at ]slm is not null
begin
Select [ at ]sqlText = [ at ]sqlText +
' and s.salesman like rtrim([ at ]xslm) + ''%'''
end
--OrderID
if [ at ]oid is not null
begin
select [ at ]oid = cast([ at ]oid as int)
Select [ at ]sqlText = [ at ]sqlText +
' and orq.OrderID = [ at ]xoid'
end
--JobNumber
if [ at ]jid is not null
begin
select [ at ]jid = cast([ at ]jid as int)
Select [ at ]sqlText = [ at ]sqlText +
' and j.jobnumber = [ at ]xjid'
end
--Contract
if [ at ]contract is not null
begin
Select [ at ]sqlText = [ at ]sqlText +
' and ua.contract = [ at ]xcontract'
end
--OffRentID
if [ at ]offid is not null
begin
select [ at ]offid = cast([ at ]offid as int)
Select [ at ]sqlText = [ at ]sqlText +
' and ore.offRentID = [ at ]xoffid'
end
--Unit
if [ at ]unit is not null
begin
Select [ at ]sqlText = [ at ]sqlText +
' and ore.unit = [ at ]xunit'
end
--Move Type
if [ at ]mt is not null
begin
Select [ at ]sqlText = [ at ]sqlText +
' and mt.mt = [ at ]xmt'
end
--Duration
if [ at ]dur is not null
begin
Select [ at ]sqlText = [ at ]sqlText +
' and ore.dur = [ at ]xdur'
end
Select [ at ]ParamList = '[ at ]xu char(50),
[ at ]xCust char(50),
[ at ]xjobSite char(50),
[ at ]xearly datetime,
[ at ]xlate datetime,
[ at ]xTakenBy char(30),
[ at ]xCreditBy char(5),
[ at ]xslm char(20),
[ at ]xoid int,
[ at ]xjid int,
[ at ]xcontract char(20),
[ at ]xoffid int,
[ at ]xunit char(8),
[ at ]xmt char(3),
[ at ]xdur char(1)'
EXEC sp_executesql [ at ]sqlText, [ at ]ParamList,
/*the following vars feed the ParamList*/
[ at ]u,
[ at ]Cust,
[ at ]jobSite,
[ at ]early,
[ at ]late,
[ at ]TakenBy,
[ at ]CreditBy,
[ at ]slm,
[ at ]oid,
[ at ]jid,
[ at ]contract,
[ at ]offid,
[ at ]unit,
[ at ]mt,
[ at ]dur
"dan2bhm via AccessMonster.com" <u19174[ at ]uwe> wrote in message
news:6178921401e20[ at ]uwe...
> I've been fumbling around with this for entirely too long, and I need some
> assistance.
>
> What I'm trying to do is to set up a form where the user can select from a
> particular set of fields (which obviously exist in one of my tables).
>
> After selecting the fields, I'd like to execute an SQL query via VBA. I
> only
> want the query to display the fields that have been selected.
>
> Does anyone know how to do this?? I'm in definate need of one of you
> gurus.
>
> Thanks
>
> (After selecting the fields I'd eventually like them to be able to set up
> some sorting options and maybe specific criteria as to which records to
> display.) - not too concerned with this though - I just really need help
> with
> the above problem
>
> --
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-adp-sqlserver/200606/1
|
|
dan2bhm via AccessMonster.com wrote:
[Quoted Text] > I've been fumbling around with this for entirely too long, and I need some > assistance. > > What I'm trying to do is to set up a form where the user can select from a > particular set of fields (which obviously exist in one of my tables). > > After selecting the fields, I'd like to execute an SQL query via VBA. I only > want the query to display the fields that have been selected. > > Does anyone know how to do this?? I'm in definate need of one of you gurus. > > Thanks > > (After selecting the fields I'd eventually like them to be able to set up > some sorting options and maybe specific criteria as to which records to > display.) - not too concerned with this though - I just really need help with > the above problem > > -- > Message posted via AccessMonster.com > http://www.accessmonster.com/Uwe/Forums.aspx/access-adp-sqlserver/200606/1
Have you tried something along the lines of this?
mystring = "Select aField"
if not IsNull(aField):
set mystring = mystring +", anotherField"
if not IsNull(yetAnotherField):
set mystring = mystring + ", yetAnotherField"
.....
set mystring = mystring + " From aplace "
set yourForm.RecordSource= mystring
|
|
|