Microsoft Access Development
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
 
User Name:
Password:
Remember me
 



Go Back   Dev Articles Community ForumsDatabasesMicrosoft Access Development

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Thread Tools Search this Thread Display Modes
 
Unread Dev Articles Community Forums Sponsor:
  #1  
Old May 25th, 2016, 09:40 AM
ssgtwally ssgtwally is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: May 2016
Posts: 5 ssgtwally User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 1 h 5 m 15 sec
Reputation Power: 0
Trouble with Passing a variable into a Query

I am having trouble passing a variable that is created in a form to a query.

The variable is "sysName" which is assigned a value based
on a field in the form:

DoCmd.OpenQuery "TP Link Query Status Update"

The form then calls the following Query:

INSERT INTO TestTableStatusLink ( IVV_ID )
SELECT [TP Link_sysName].[IVV_ID] AS Expr1
FROM [TP Link_sysName]
WHERE ((([TP Link_sysName].[TP])=[Forms]![FQT Test Log]![TP ID]));

Can anyone help me figure this out? I have been banging my head against the wall for a couple of days trying to figure this out.

Kirk

Reply With Quote
  #2  
Old May 26th, 2016, 05:34 PM
informer informer is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: May 2016
Posts: 10 informer User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 1 h 33 m 52 sec
Reputation Power: 0
Hi ssgtwally

Firstly, what do you mean with : "The form then calls the following Query" ?
Could you send the code?

Secondly, in WHERE clause, if the type field is text you need to wrap the value with single quote
"... WHERE [TP Link_sysName].[TP]='" & [Forms]![FQT Test Log]![TP ID] & "'"

Finally, how do you proceed to applied this query, with
  1. DoCmd.RunSQL sSQLQry
  2. CurrentDB.Execute sSQLQry
  3. Others

Reply With Quote
  #3  
Old May 31st, 2016, 10:44 AM
ssgtwally ssgtwally is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: May 2016
Posts: 5 ssgtwally User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 1 h 5 m 15 sec
Reputation Power: 0
Paasing variables

I have the following code in my form:

If Forms![FQT Test Log]![Log Status] = "Analysis Complete" Then
If Forms![FQT Test Log]![Phase] Like "FQT*" Then
DoCmd.SetWarnings False
DoCmd.OpenQuery "DELETE ALL FROM TestTableStatusLink"
DoCmd.OpenQuery "TP Link Query7"
DoCmd.OpenQuery "TP Link Query Status Update"
DoCmd.OpenQuery "Append Table for TP to Real Status Table7"
DoCmd.SetWarnings True

I am trying to pass the variable 'sysName' into the above queries.

Right now there are multiple set of the following code because a variable was not used. I am trying to clean up this code to one set of code, not multiple sets.

ssgtwally


Quote:
Originally Posted by informer
Hi ssgtwally

Firstly, what do you mean with : "The form then calls the following Query" ?
Could you send the code?

Secondly, in WHERE clause, if the type field is text you need to wrap the value with single quote
"... WHERE [TP Link_sysName].[TP]='" & [Forms]![FQT Test Log]![TP ID] & "'"

Finally, how do you proceed to applied this query, with
  1. DoCmd.RunSQL sSQLQry
  2. CurrentDB.Execute sSQLQry
  3. Others

Reply With Quote
  #4  
Old May 31st, 2016, 11:17 AM
informer informer is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: May 2016
Posts: 10 informer User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 1 h 33 m 52 sec
Reputation Power: 0
Hi ssgtwally

The OpenQuery syntax is :
Code:
doCmd.OpenQuery(QueryName, View, DataMode) 


So there is no way to pass criteria through doCmd. But the solution is :

Code:
Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef

Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("Your Query Name")

qdf.Parameters("Parameter 1").Value = "Parameter Value"
qdf.Parameters("Parameter 2").Value = "Parameter Value"
qdf.Execute
qdf.Close

Set qdf = Nothing
Set dbs = Nothing


And in your SQL query, here a select query, you have to declare parameters as follows:

Code:
PARAMETERS pParam1 Long, pParam2 Long;
SELECT Members.*
FROM table 
WHERE memberID Between [pParam1 ] And [pParam2 ]

Reply With Quote
  #5  
Old June 1st, 2016, 11:20 AM
ssgtwally ssgtwally is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: May 2016
Posts: 5 ssgtwally User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 1 h 5 m 15 sec
Reputation Power: 0
Not quite there yet

informer,

I appreciate all of the help that you are giving me. I am new to programming VB in access and I appreciate your patience.

I am still having problem getting this to work. This is my query "TP Link Query":

Code:
INSERT INTO TestTableStatusLink ( IVV_ID )
SELECT [TP Link_Q50].[IVV_ID]
FROM [TP Link_Q50]
WHERE [TP Link_Q50].TP=Forms![FQT Test Log]![TP ID];


What I am trying to do place a variable in the place of the "Q50" above. I want to be able to apply this query to multiple different system names. Currently there is a set of code for each system. Each system has the following code.
Code:
    If Forms![FQT Test Log]![Log Status] = "Analysis Complete" Then
            If Forms![FQT Test Log]![Phase] Like "FQT*" Then
                DoCmd.SetWarnings False
                DoCmd.OpenQuery "DELETE ALL FROM TestTableStatusLink"
                DoCmd.OpenQuery "TP Link Query7"
                DoCmd.OpenQuery "TP Link Query Status Update"
                DoCmd.OpenQuery "Append Table for TP to Real Status Table7"
                DoCmd.SetWarnings True
            Else
                DoCmd.SetWarnings False
                DoCmd.OpenQuery "DELETE ALL FROM TestTableStatusLink"
                DoCmd.OpenQuery "TP Link Query7"
                DoCmd.OpenQuery "TP Status Link Update Increments"
                DoCmd.OpenQuery "Append Table for TP to Real Status Table7"
                DoCmd.SetWarnings True
            End If


I really appreciate any other help that you can provide.

ssgtwally

Reply With Quote
  #6  
Old June 1st, 2016, 11:43 AM
informer informer is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: May 2016
Posts: 10 informer User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 1 h 33 m 52 sec
Reputation Power: 0
Hi ssgtwally,

With pleasure !

You need to change your query as follows:

Code:
PARAMETERS pParam1 Long;
INSERT INTO TestTableStatusLink ( IVV_ID )
SELECT [TP Link_Q50].[IVV_ID]
FROM [TP Link_Q50]
WHERE [TP Link_Q50].TP=pParam1


Take care ! pParam1 type must support the Forms![FQT Test Log]![TP ID] type

add in your code

Code:
Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef

Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("TP Link Query")

qdf.Parameters("Parameter 1").Value = Forms![FQT Test Log]![TP ID]
qdf.Execute
qdf.Close

Set qdf = Nothing
Set dbs = Nothing

Reply With Quote
  #7  
Old June 1st, 2016, 11:47 AM
informer informer is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: May 2016
Posts: 10 informer User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 1 h 33 m 52 sec
Reputation Power: 0
You call the same queries in If and Else blocks, why do you use If condition block?

If Forms![FQT Test Log]![Phase] Like "FQT*" Then
DoCmd.SetWarnings False
DoCmd.OpenQuery "DELETE ALL FROM TestTableStatusLink"
DoCmd.OpenQuery "TP Link Query7"
DoCmd.OpenQuery "TP Link Query Status Update"
DoCmd.OpenQuery "Append Table for TP to Real Status Table7"
DoCmd.SetWarnings True
Else
DoCmd.SetWarnings False
DoCmd.OpenQuery "DELETE ALL FROM TestTableStatusLink"
DoCmd.OpenQuery "TP Link Query7"
DoCmd.OpenQuery "TP Status Link Update Increments"
DoCmd.OpenQuery "Append Table for TP to Real Status Table7"
DoCmd.SetWarnings True
End If

Reply With Quote
  #8  
Old June 1st, 2016, 01:23 PM
ssgtwally ssgtwally is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: May 2016
Posts: 5 ssgtwally User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 1 h 5 m 15 sec
Reputation Power: 0
One slight difference

informer,

You are mostly correct but there is one query that is different between the If and the Else.

If:
DoCmd.OpenQuery "TP Link Query Status Update"

Else:
DoCmd.OpenQuery "TP Status Link Update Increments"

I am going to try and cut all of that duplication of queries out if I can.

Thanks for your help.

ssgtwally

Reply With Quote
  #9  
Old June 1st, 2016, 02:55 PM
informer informer is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: May 2016
Posts: 10 informer User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 1 h 33 m 52 sec
Reputation Power: 0
Hi ssgtwally

You can bypass the problem consecutive of "TP Link Query" call by creating dynamically a SQL query executed with DoCmd.RunSQL command.

Proceed as follow:

Code:
dim strSQL as string

strSQL = " INSERT INTO TestTableStatusLink ( IVV_ID )
SELECT [TP Link_Q50].[IVV_ID]
FROM [TP Link_Q50]
WHERE [TP Link_Q50].TP = " & Forms![FQT Test Log]![TP ID]

DoCmd.RunSQL strSQL



That's all and and very simple. I advice you this method because in the future, you'll probably need to create SQL query more complex because depending of multiple criteria


theoretical example :
Code:
strAnd  = ""
strSQL = "SELECT * FROM myTable  WHERE 1 = 1"

If test1 = value1 then
    strAnd = "AND field1 = " & value1
    if test2 = value2 then strAnd = " AND field2 = " & value2
else 
     strAnd = "AND field4 = " & value3
end if

strSQL = strSQL & strAnd



WHERE 1 = 1 is always true so the query is not impacted by this criterai and it's a way to bypass test if the strAnd is empty and you begin with WHERE or not and you need to assign AND command

Reply With Quote
Reply

Viewing: Dev Articles Community ForumsDatabasesMicrosoft Access Development > Trouble with Passing a variable into a Query


Developer Shed Advertisers and Affiliates


Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump

Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 


Powered by: vBulletin Version 3.0.5
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.

© 2003-2017 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap