Pulling a Parameter from a Form in a Query

[PowerPoint Jedi]

Hello,
First off I am running ACCESS 2003 on an SQL server.

I am running a query that requries a date input. I would ilke to be able to
input the date to a form and have the query pull it from the text box on the
form.
I have done this before (on a non SQL server) and used the following syntax
[forms].["form name].[name of text box]

I don't understand why it won't let me put that line of code, and being self
taught in access I dont know what the proper syntax is for calling out the
form.


Here is the SQL

ALTER PROCEDURE dbo.[QSPC Repair Failures]
(@Start_date datetime)
AS INSERT INTO dbo.[TSPC All Failures]
(Serial, PartNo, TestID, Test_Type)
SELECT dbo.Tests.Serial, dbo.REPAIRS.PartNo, dbo.REPAIRS.TestID,
dbo.Tests.Test_Type
FROM dbo.Tests INNER JOIN
dbo.TEST_TYPES ON dbo.Tests.Test_Type =
dbo.TEST_TYPES.Test_Type INNER JOIN
dbo.REPAIRS ON dbo.Tests.TestID = dbo.REPAIRS.TestID
INNER JOIN
dbo.Failed_Codes INNER JOIN
dbo.Code_Groups ON dbo.Failed_Codes.Code_Group =
dbo.Code_Groups.Code_Group ON
dbo.REPAIRS.Repaired_Code = dbo.Failed_Codes.Fail_ID
WHERE (dbo.Tests.Start_Time <= @Start_Date) AND (dbo.Tests.Test_Type = 1
OR dbo.Tests.Test_Type = 3) AND (dbo.Tests.Start_Time >= @start_date)
ORDER BY dbo.Tests.Serial, dbo.REPAIRS.PartNo


Any Help would be appreciated Thank you

 

[Jeff Boyce]

I suspect it's because the SQL-Server where I'd guess you are trying to run
the procedure cannot "see" Access. If you don't pass the value (not the
location ... i.e., the form) as a parameter, the SQL-Server procedure can't
do it.

As one alternative, how about if you just use an Access query against linked
SQL-Server tables (or against a SQL-Server view)?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[PowerPoint Jedi]

Thanks Jeff,
The issue becomes this. I am running a Macro that runs 7 similiar
queries all requiring the same date multiple times. The idea is to make a
form that allows you to input the parameters and then execute the macro.

I have looked into it a little more and it seems that the way this works
uses the Access on my computer as the front end and the actual data is on a
SQL server.

I see your alternatives but to be honest you lost me a little. I am not
sure how I could link to the tables from my normal network to the SQL
network. Is that even possible?

I tried your alternative (at least I think I did) and I am unable to link
the tables. I can copy them but not link them, which mean when the data
changes I will be missing data.

Seems when I create a new database to link the tables to it creates an .Mdb
and I am unable to link the the .ADP database on the SQL server. Any tips on
how to work around this. I am not sure why it doesn't see the form as I
created it in the same database I created the stored procedure.


Again if I am confusing you I apologize but I am self taught and by no means
a programing wiz.

Thanks for taking a look at this and any other tip are appreciated.

I suspect it's because the SQL-Server where I'd guess you are trying to run
the procedure cannot "see" Access. If you don't pass the value (not the
location ... i.e., the form) as a parameter, the SQL-Server procedure can't
do it.

As one alternative, how about if you just use an Access query against linked
SQL-Server tables (or against a SQL-Server view)?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hello,
First off I am running ACCESS 2003 on an SQL server.

I am running a query that requries a date input. I would ilke to be able
to
input the date to a form and have the query pull it from the text box on
the
form.
I have done this before (on a non SQL server) and used the following
syntax
[forms].["form name].[name of text box]

I don't understand why it won't let me put that line of code, and being
self
taught in access I dont know what the proper syntax is for calling out the
form.


Here is the SQL

ALTER PROCEDURE dbo.[QSPC Repair Failures]
(@Start_date datetime)
AS INSERT INTO dbo.[TSPC All Failures]
(Serial, PartNo, TestID, Test_Type)
SELECT dbo.Tests.Serial, dbo.REPAIRS.PartNo, dbo.REPAIRS.TestID,
dbo.Tests.Test_Type
FROM dbo.Tests INNER JOIN
dbo.TEST_TYPES ON dbo.Tests.Test_Type =
dbo.TEST_TYPES.Test_Type INNER JOIN
dbo.REPAIRS ON dbo.Tests.TestID = dbo.REPAIRS.TestID
INNER JOIN
dbo.Failed_Codes INNER JOIN
dbo.Code_Groups ON dbo.Failed_Codes.Code_Group =
dbo.Code_Groups.Code_Group ON
dbo.REPAIRS.Repaired_Code = dbo.Failed_Codes.Fail_ID
WHERE (dbo.Tests.Start_Time <= @Start_Date) AND (dbo.Tests.Test_Type =
1
OR dbo.Tests.Test_Type = 3) AND (dbo.Tests.Start_Time >= @start_date)
ORDER BY dbo.Tests.Serial, dbo.REPAIRS.PartNo


Any Help would be appreciated Thank you

 

[Jeff Boyce]

Ah, I hadn't seen the .ADP connection before. No experience in that arena,
but others here may be able to help...

Regards

Jeff Boyce
Microsoft Office/Access MVP

Thanks Jeff,
The issue becomes this. I am running a Macro that runs 7 similiar
queries all requiring the same date multiple times. The idea is to make a
form that allows you to input the parameters and then execute the macro.

I have looked into it a little more and it seems that the way this works
uses the Access on my computer as the front end and the actual data is on
a
SQL server.

I see your alternatives but to be honest you lost me a little. I am not
sure how I could link to the tables from my normal network to the SQL
network. Is that even possible?

I tried your alternative (at least I think I did) and I am unable to link
the tables. I can copy them but not link them, which mean when the data
changes I will be missing data.

Seems when I create a new database to link the tables to it creates an
.Mdb
and I am unable to link the the .ADP database on the SQL server. Any tips
on
how to work around this. I am not sure why it doesn't see the form as I
created it in the same database I created the stored procedure.


Again if I am confusing you I apologize but I am self taught and by no
means
a programing wiz.

Thanks for taking a look at this and any other tip are appreciated.

I suspect it's because the SQL-Server where I'd guess you are trying to
run
the procedure cannot "see" Access. If you don't pass the value (not the
location ... i.e., the form) as a parameter, the SQL-Server procedure
can't
do it.

As one alternative, how about if you just use an Access query against
linked
SQL-Server tables (or against a SQL-Server view)?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hello,
First off I am running ACCESS 2003 on an SQL server.

I am running a query that requries a date input. I would ilke to be
able
to
input the date to a form and have the query pull it from the text box
on
the
form.
I have done this before (on a non SQL server) and used the following
syntax
[forms].["form name].[name of text box]

I don't understand why it won't let me put that line of code, and being
self
taught in access I dont know what the proper syntax is for calling out
the
form.


Here is the SQL

ALTER PROCEDURE dbo.[QSPC Repair Failures]
(@Start_date datetime)
AS INSERT INTO dbo.[TSPC All Failures]
(Serial, PartNo, TestID, Test_Type)
SELECT dbo.Tests.Serial, dbo.REPAIRS.PartNo, dbo.REPAIRS.TestID,
dbo.Tests.Test_Type
FROM dbo.Tests INNER JOIN
dbo.TEST_TYPES ON dbo.Tests.Test_Type =
dbo.TEST_TYPES.Test_Type INNER JOIN
dbo.REPAIRS ON dbo.Tests.TestID =
dbo.REPAIRS.TestID
INNER JOIN
dbo.Failed_Codes INNER JOIN
dbo.Code_Groups ON dbo.Failed_Codes.Code_Group =
dbo.Code_Groups.Code_Group ON
dbo.REPAIRS.Repaired_Code =
dbo.Failed_Codes.Fail_ID
WHERE (dbo.Tests.Start_Time <= @Start_Date) AND
(dbo.Tests.Test_Type =
1
OR dbo.Tests.Test_Type = 3) AND (dbo.Tests.Start_Time >= @start_date)
ORDER BY dbo.Tests.Serial, dbo.REPAIRS.PartNo


Any Help would be appreciated Thank you

 

[Ragnar Midtskogen]

I am not sure how you would do this with a macro, I never use them, but you
can run a VBA sub that creates an ADODB command and use the form reference
to supply the date as a command parameter.

Sample code:
Dim cmd As ADODB.Command

Set cmd = New ADODB.Command
With cmd
.ActiveConnection = CurrentProject.Connection
.CommandText = "EXEC NameOfYourProc ' " & [forms].["form name].[name of
text box] & " ' "
.CommandType = adCommandText
.Execute
End With

This should work with your SQL Server stored proc, I see you have the date
as a parameter

Ragnar

 

[PowerPoint Jedi]

Thanks for the reply. At first glance this looks a little over my head but I
will play around with it. I am trying another path where I design a macro
that imports the data into an .Mdb database and then runs all the queries to
get around the form issue. Only problem is I don't know how to get the macro
to import so back to square one . Thanks again