Set Criteria for a Query in a Module 1

[CopperWire]

I'm currently using a form to set the criteria in a query and I'm wondering if there is a way to do that with a module. Specifically, I want to change the CD_WR using a form. The SQL is below:

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
strSQL = "SELECT WRInquiry.* FROM WRInquiry Where CD_WR = Forms!RefundableForm!EnterWR;"

I tried to reference the form and field. The code failed at Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset, I get a run-tme error '3061': Too few parameters. Expected 1.

So it appears the form is not being read??. I even tried passing the variable and that didn't work.

Any ideas?

 

[Crowley16]

have you initialized your dbs object?

is the form you're trying to reference open when this is executed?



--------------------
Procrastinate Now!

 

[CopperWire]

Yes, the form is open. I tried passing the WR number from the form to the module and it worked (at least is psses), but still not recognized. See below

Sub GetRefundData(EnterWR)

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim intEnterWR As Variant

intEnterWR = [EnterWR](IntEnterWR is set to 3283139)

strSQL = "SELECT WRInquiry.* FROM WRInquiry Where CD_WR = intEnterWR;"

Dim stPremise, stArea, stCustName, stJobAddress, stJobCity, stJobState, stJobZip, stBillName, stBillAdd, stBillCity, stBillState, stBillZip, stOpUnit, stUtilityType, stProjectID, stActivityID, stStatus, stWRDesc, stTypeWR, stJobType As String
Dim dtCompleteDate As Date
Dim intWRNumber, intTotal As Integer

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset)

Still getting the same error Too few parameters. Expected 1.

I'm not sure what you mean by initialized you dbs object? I belive I did (Dim dbs As DAO.Database).

 

[OhioSteve]

Each time I start on a project, I make a table called single1. It always has exactly one record. I use it to hold global variables. This eliminates problems like yours.

 

[CopperWire]

I don't understand how this will help? Are you saying that this single1 table should be linked in the query that I'm running? How do you populate the table?

Sorry not getting it!

 

[Crowley16]

Dim dbs As DAO.Database only specifies dbs as a database, it doesn't tell it what database it should be.

you'll need to have the line:
set dbs = currentdb
or something along those lines.

note1:
strSQL = "SELECT WRInquiry.* FROM WRInquiry Where CD_WR = intEnterWR;"
won't work because the intEnterWR exists only within the scope of the function and you're trying to use it from a query, what would work is:
strSQL = "SELECT WRInquiry.* FROM WRInquiry Where CD_WR = " & intEnterWR

note2:
Dim stPremise, stArea... as string
this only declares the last variable as string, all others are set as variants, the syntax should be:
dim var1 as string, var2 as string, var3 as string...

--------------------
Procrastinate Now!

 

[CopperWire]

That worked, thank you so much. I also didn't know you had to declare variable separately. Thanks for that.

So, now I open my form type in the WR and the code runs and adds the information to the table. Now how do I get my form to populate based on what I just added to the table? The only way I can figure out how to do it is to close the form and open it again, then the information that was populated is there, but I dont' want to do this.

Is there any other way?

 

[TheAceMan1]

How are ya CopperWire . . .

In your post origination:

[blue]   strSQL = "SELECT WRInquiry.* FROM WRInquiry Where CD_WR = Forms!RefundableForm!EnterWR;"
[purple][b]should be:[/b][/purple]
   strSQL = "SELECT WRInquiry.* FROM WRInquiry Where CD_WR = " & Forms!RefundableForm!EnterWR & ";"[/blue]

See Ya! . . . . . .