Querydef / recordset problems 3

[boab]

Can anyone help (please!) What I am trying to do is search a table of user ids and passwords based on a form with two text boxes one contains the user id and the other contains the password the query searches the table for the user ID and returns the user password in the table then I want to compare the value in the second "password" texbox with the returned value to verify it
the code below I keeps giving an error box stting that more parameters were expected


Private Sub Login_Click()
On Error GoTo Err_Login_Click

'declare variables
Dim stUser, stPass As String
Dim DatBas As Database
Dim Records As Recordset
Dim OpQuery As QueryDef
Dim PasVal, stDocName, stLinkCriteria As String

'define the users details from the form
stUser = Operator_Id.value
stPass = Operator_Password

'define database / queries
Set DatBas = CurrentDb()
Set OpQuery = DatBas.CreateQueryDef("&quot
OpQuery.SQL = "SELECT passwrd FROM ps WHERE Id =" & stUser
OpQuery.ReturnsRecords = True

Set Records = OpQuery.OpenRecordset()

'pass results of query to variable

PasVal = Records!passwrd

If PasVal = stPass Then

DoCmd.close

stDocName = "FrontScreen"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Else

MsgBox ("The Operator Id or password you have entered is invalid please try again&quot

End If

 

[MakeItSo]

Hi boab.

You have misunderstood the QueryDef and Recordset feature.
Here is an Example for your purpose:

Dim DatBas As Database
Dim Records As Recordset
Set DatBas = CurrentDb
Set Records = DatBas.OpenRecordSet("SELECT passwrd FROM ps WHERE Id =" & stUser, dbOpenDynaset)

PasVal = Records!passwrd

If PasVal = stPass Then
Records.Close
DoCmd.close

stDocName = "FrontScreen"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Else

MsgBox ("The Operator Id or password you have entered is invalid please try again&quot

End If

Hope this helps.

Andreas Galambos
EDP / Technical Support Specialist
(andreas.galambos@bowneglobal.de)
HP:

http://www.andygalambos.de

 

[DeanWilliams]

Hi,

A couple of things, not sure if they will solve your problem, but you never know:

1) I don't think you need to use a querydef, you can just open the recordset directly from your sql statement:

Dim strSQL As String

strSQL = "SELECT passwrd FROM ps WHERE Id =" & stUser
Set Records = DatBas.OpenRecordset(strSQL, dbOpenSnapshot)

You should specify the type of Recordset as well, for effiency (Snapshot is a read-only recordset as you are not making changes to the data).

Also don't forget to close the recordset and release the variable by setting it to nothing. This frees up the resources that were being used:

Records.Close
Set Records = Nothing

2) The ReturnRecords property is used for PassThrough queries and this is not the case with your example.

3) Last thing - not really relevant but passing comment. When you declare multiple variables on one line, e.g:

Dim stUser, stPass As String

You need to specify the type for all variables, as this syntax will declare stPass as a string and stUser as a Variant. Should be:

Dim stUser As String, stPass As String

Using that recordset method may solve your problem. Reply if you still have problems.

HTH

Dean

 

[boab]

That's great guys as an aside what would I do to use the results from a queryDef() to populate a form to view or a report to print?

 

[MakeItSo]

I think it's

strSQL = "SELECT... "
CurrentDB.CreateQueryDef "myQuery", strSQL

This creates a query called "myQuery" which performs the SQL statement you state in strSQL.

But: YOu can only do this once, since then "myQuery" already exists and cannot be created any more.

Then you say
Set OpQuery= DBEngine.QueryDefs("myQuery&quot
OpQuery.SQL="SELECT something new"

this way you can alter existing queries.

By binding the from/report to this query, you can alter their recordsource this way.
It's not best practice, but works. ;-)

 

[DeanWilliams]

Just to add to that. You can create temporary QueryDefs by specifying a zero-length string for the name as you originally did.

CurrentDB.CreateQueryDef("", strSQL)

This will not save the query permanently as is normally the case.

Dean.

 

[boab]

Guys I've tried the code early in thread as below

Dim stUser As String, stPass As String
Dim DatBas As Database
Dim Records As Recordset
Dim PasVal As String, stDocName As String, stLinkCriteria As String

'define the users details from the form
stUser = Operator_Id.value
stPass = Operator_Password.value

'define database / queries
Set DatBas = CurrentDb
Set Records = DatBas.OpenRecordset("SELECT passwrd FROM ps WHERE Id =" & stUser, dbOpenDynaset)

'pass results of query to variable

PasVal = Records!passwrd

If PasVal = stPass Then
Records.close

DoCmd.close

stDocName = "FrontScreen"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Else

MsgBox ("The Operator Id or password you have entered is invalid please try again&quot

End If

I'm still getting a dialogue box saying Too few parameters Expected 1

any Ideas?

 

[MakeItSo]

Seems that Operator_Id.value is NULL.
Then stUser = Operator_Id.value
will assign NULL to stUser and thus your criteria is missing in the SQL.

Have a try with
stUser=Me!Operator_Id
stPass=Me!Operator_Password
instead.

Set a breakpoint in that line and check during runtime, if you have the correct value there.

 

[RoyVidar]

Hi!

To add to MakeItSo's suggestion, you've declared stUser as string, if it is a string, try adding text qualifiers:

[tt]"SELECT passwrd FROM ps WHERE Id ='" & stUser & "'"[/tt]

- note the apostropes (') - text qualifiers.

Roy-Vidar

 

[boab]

a poin ton your sugeestion make it so would I be able just to use Me! or would I have to go through Me!Controls!

 

[MakeItSo]

Me! plus the name of the control suffices. Actually even the Control name itself could suffice, if it is not a reserved word (e.g. "Date" or "Name&quot.

 

[boab]

The code below is what I am using I'm trying to to perform this search based on the value of 3 Combo Boxes Month, year and Search type these conditionally set the sql statement which the query uses MakeItSo the set GenQuery = DBEngine.QueryDefs("myQuery&quot isn't working it is returning error messages about obejcts nto being defined btw I'm trying to do all of this on Access 97 any ideas?


'declare variables
Dim DatBas As Database
Dim Records As Recordset
Dim stDocName As String, stLinkCriteria As String
Dim strSQL As String
Dim Month As Integer
Dim Year As Integer
Dim GenQuery As QueryDef

Month = Me!Month.value
Year = Me!Year.value


'define the users details from the form
Set DatBas = CurrentDb()


If searchType.value = "Search by Requested" Then
strSQL = "SELECT * FROM requests WHERE year(Received Date)= " & Year & " AND month(Received Date)= " & Month
ElseIf searchType.value = "Search by Delivered" Then
strSQL = "SELECT * FROM requests WHERE year(Delivered Date)= " & Year & " AND month(Delivered Date)= " & Month
Else
strSQL = "SELECT * FROM requests WHERE year(Received Date)= year (Delivered Date)AND year(Received Date)=" & Year & " AND Month(Received Date)= Month(Delivered Date)AND Month(Received Date)=" & Month
End If

Set GenQuery = DBEngine.QueryDefs("myQuery&quot
GenQuery.SQL = strSQL

 

[MakeItSo]

The error pops up, because the Query "myQuery" does not yet exist.

Replace
Set GenQuery = DBEngine.QueryDefs("myQuery&quot
GenQuery.SQL = strSQL

with
CurrentDb.CreateQueryDef "myQuery", strSQL

Besides: Avoid using Year and Month as Variables, since Year and Month are predefined functions! (return current year/month)

 

[boab]

Two things guys

1. I'm using the Year() and Month() functions to search the dates in the table by year and month and I'm getting an error box telling me Access can't find the variable Month

2. Can I Bind a form to a query in VBA and then open the form?

 

[MakeItSo]

1. - See the comment in my last reply.

2. - You can - but I prefer setting a filter if I want to limit records.

 

[boab]

so how would I search the the table based on month and year by using a string? like strDte = MyMonth"/"MyYear? and using a LIKE Sql statement?

what I am trying to do is perform the search and display the results via a form

 

[MakeItSo]

I see. Use the hash character # to treat a string as date in SQL:
e.g. "WHERE Month([Datefield]) = " & myMonth
or
"WHERE [Datefield] LIKE #" myMonth & "/" & "/*/" & myYear & "#"
if date is in mmddyy(yy) format.

For other formats just re-arrange.

e.g. Datestring= "02/12/2004":
"WHERE [Datefield] = #" & Datestring & "#"

or for myYear="2004":
"WHERE [Datefield] LIKE #*/*/" & myYear & "#"

Hope this helps clarifying.

 

[boab]

so how would I use the information from the query in a form / report as I can't see the query in the query screen in access