Parameter Query Help!!

[jwhat]

Hello Everyone..

I have a form (mainform) where a user selects criteria and then runs a module that will make certain updates. In the code of the module a query is called (masterlist) that includes the list necessary for the module. Since the query is getting some of the parameters from (mainform) I seem to be getting a "to few parameters" error in the code.

Help! I have tried to write code to get the parameters in the form. However I am still calling the query and I am not making the connection to get the parameters in the query.

Thanks in advance for any help!

Jwhat..

 

[dhookom]

Do you have any code you would like to share? How about the SQL of your query?


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[jwhat]

The code in the query is a page long since it has a ton of logic to pull the list of records based on the input of the user. Within access I run a form that calls this query and it makes the connection to pull the parameters from the form without a problem, but VB will not make this same connection?


Would you suggest I put all of the sql from the query into the VB module?

 

[dhookom]

You might want to check some of the samples at

http://www.rogersaccesslibrary.com/TableOfContents3.asp#P.

Or this linke

http://www.comeandread.com/access/tip002.htm

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[jwhat]

Thanks for the link. I will look into having the sql in the module to call the table directly.

Any idea how I could simply include the sql in the module and assign the parameter to the same query?

Here is a sample of part of the sql that has the parameter in it. if that helps anyone.

(((Maint.Group)=[Forms]![MainForm]![MLCPGroupList]) AND ((Maint.Dedicated)=IIf([Forms]![MainForm]![MLCPGroupType]=2,Yes,No)

 

[dhookom]

I generally would modify the sql of a saved query or build an action query as just a sql string that I can run.

A "spartan" procedure to change the sql of a saved query is:

Sub ChangeSQL(pstrQuery as String, pstrSQL as String)
   Currentdb.QueryDefs(pstrQuery).SQL = pstrSQL
End Sub

This would allow you to place the values of your controls into the query.

If this doesn't work for you then I don't understand your previous question. Did you find the parameter code in any of the links I provided. The code would have been something like

For Each Param in ...

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[jwhat]

I looked at the links and could not find any code specific to setting the parameter within the VB module. They describe linking the form control to a query or to another form. That I can do within Access without VB and it works fine. Looks like I will need to try your code or re-write the query in sql with the parameters set and defined as variables prior to the sql action.

It is strange that VB can set a variable equal to the form control just fine.. but not when it is within a query in the database.

Thanks alot for the help on this..

 

[dhookom]

I did a google search on "'For Each' Parameter QueryDef Access" and found

http://groups-beta.google.com/group...h"+Access+Parameter+QueryDef#9058c67c12f02fab

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[jwhat]

That is a great link and I should have done the web search on this myself..

Excellent post on how/why this issue is happening and possible resolutions.

Thanks again!!

 

[jwhat]

I am still having problems with this.. So I am using the following to get and set the parameters.

qdef.Parameters(0) = Forms![MainForm].[MLCPGroupList]
qdef.Parameters(1) = Forms![MainForm].[MLCPGroupType]
qdef.Parameters(2) = Forms![MainForm].[MLCPSelectList]

I verify that it is pulling the correct value from the form and I don't get any type errors. I have verified that the parameter number is the correct one and that they match.

Everything will run fine, but my recset is empty. It has only one record that is null. Somehow the parameter is not running correctly in the sql call to the db?

 

[dhookom]

Can you share all your code?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[jwhat]

It is ugly so I apologize up front. This is the code in the module that sets the parameters.

Set myDB = CurrentDb
Set qdef = myDB.QueryDefs("MTMLCPMASTER")

'MsgBox (qdef.Parameters(1).NAME)


qdef.Parameters(0) = Forms![MainForm].[MLCPGroupList]
qdef.Parameters(1) = Forms![MainForm].[MLCPGroupType]
qdef.Parameters(2) = Forms![MainForm].[MLCPSelectList]

This is the sql in the query that is called.

SELECT Maint.ID, Maint.Group, Maint.Dedicated, Maint.Type, Maint.ACCOUNT, Maint.[Sent?], Maint.Reviewed, Maint.Notes, Maint.BR, Maint.VIP, Maint.[SENT DATE], Maint.NAME, Maint.CAMP, Maint.[MG SENT], Maint.[CURR AMT], Maint.[NET WORTH], Maint.[EQ %], Maint.SWVXX, Maint.SM, Maint.[Option Min], Maint.[Non Neg], Maint.[Reg Min], Maint.[Reg IME], Maint.[Net Margin Amt], Maint.MARS, Maint.Security, Maint.AutoDial, Maint.Status, Maint.Sell, Maint.OFS, Maint.[Dupe Margin], Maint.[Client Code], Maint.ChgUser, Maint.ChgDate, Maint.ChgTime, Maint.Qindex
FROM Maint INNER JOIN VARAMT ON Maint.Qindex = VARAMT.Qindex
WHERE ((((Maint.Group)<>"Exception" Or (Maint.Group) Is Null) And (Maint.Group)=[Forms]![MainForm]![MLCPGroupList]) AND ((Maint.Dedicated)=IIf([Forms]![MainForm]![MLCPGroupType]=2,Yes,No)) AND ((Maint.Type)="MLCP") AND ((IIf([Maint]![CURR AMT]>[VARAMT]![MAX] Or [Maint]![Option Min]<0 Or [Maint]![Non Neg]>0 Or [Maint]![SWVXX]>0 Or [Maint]![OFS]=Yes,Yes,No))=IIf([Forms]![MainForm]![MLCPSelectList]=1,-1,0))) OR ((((Maint.Group)="EB" Or (Maint.Group)="IM" Or (Maint.Group)="INTL" Or (Maint.Group)="SPC") And (Maint.Group)=[Forms]![MainForm]![MLCPGroupList]) AND ((Maint.Type)="MLCP") AND ((IIf([Maint]![CURR AMT]>[VARAMT]![MAX] Or [Maint]![Option Min]<0 Or [Maint]![Non Neg]>0 Or [Maint]![SWVXX]>0 Or [Maint]![OFS]=Yes,Yes,No))=IIf([Forms]![MainForm]![MLCPSelectList]=1,-1,0))) OR ((((Maint.Group)<>"Exception" Or (Maint.Group) Is Null) And (Maint.Group)=[Forms]![MainForm]![MLCPGroupList]) AND ((Maint.Type)="MLCP") AND ((Maint.[Sent?])=IIf([Forms]![MainForm]![MLCPSelectList]=2,"Valid"))) OR (((Maint.Group)="Exception" And (Maint.Group)=[Forms]![MainForm]![MLCPGroupList]) AND ((Maint.Type)="MLCP") AND ((IIf([Maint]![CURR AMT]>[VARAMT]![MAX] Or [Maint]![Option Min]<0 Or [Maint]![Non Neg]>0 Or [Maint]![SWVXX]>0 Or [Maint]![OFS]=Yes,Yes,No))=IIf([Forms]![MainForm]![MLCPSelectList]=1,-1)));

 

[dhookom]

I would have used

  Dim myprm as Parameter
  Dim qdef as DAO.QueryDef
  Dim myDB as DAO.Database
  Dim itm
  Set myDB = CurrentDb()
  Set qdef = mydb.QueryDefs("MTMLCPMASTER") 
  For itm = 0 To qdef.Parameters.Count - 1 
    Set myprm = qdef.Parameters(itm) 
    myprm.Value = Eval(myprm.Name) 
  Next itm

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[jwhat]

I am not sure if I understand what that code will do.. however if I put that in my code string and run it.. I doesn't fail, but I still get a blank recordset. Yet if I run the query from the DB it will pull the correct recset.

 

[dhookom]

When I asked for to "share all your code", I actually meant your code, not your sql.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[jwhat]

This is it.. "RunProcess" is a sub function that is started since the recset is = 1. However the recset is not correct and should be the 100+ records I get in the query and the fields I use from it are null.


Public myDB As Database
Public recset As Recordset
Public qdef As QueryDef
Public stDocName

Function Automail()

Set myDB = CurrentDb
Set qdef = myDB.QueryDefs("MTMLCPMASTER")

qdef.Parameters(0) = Forms![MainForm].[MLCPGroupList]
qdef.Parameters(1) = Forms![MainForm].[MLCPGroupType]
qdef.Parameters(2) = Forms![MainForm].[MLCPSelectList]

Set recset = qdef.OpenRecordset()


If recset.RecordCount > 0 Then
RunProcess
Else
End If
End Function

 

[dhookom]

Try:

     Set recset = qdef.OpenRecordset()
[b]     recset.MoveLast
     recset.MoveFirst[/b]
     If recset.RecordCount > 0 Then

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[PHV]

Another way is to replace this:
If recset.RecordCount > 0 Then
By this:
If Not (recset.BOF Or recset.EOF) Then

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244