VB Recordset Problem

[dom24]

Hi,

All i'm trying to do is run an SQL statement, store it in a record set then right different values depending on whether any records are found or not.
The code I have at the moment is:

If Me.txtManagerNo <> "" Then
strSQL = "UPDATE dbo_Passwords SET password = 'password' WHERE (((dbo_Passwords.ManagerNo)=[forms]![frmpassword]![txtManagerNo]));"
Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)
If rst.EOF Then
MsgBox "Sorry, this is not a Manager Number. Please try again."
End If

Else ........

I'm getting an error saying "Invalid Operation" on the line Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)

I'm quite new to VB so sorry if tehre is a really obvious mistake in there!
Thanks.

 

[petermeachem]

Yep, you need to do a SELECT to see if the manager number is ok. And don't forget to close the recordset. An UPDATE sql is run (or at least I do) by db.execute strsql

http://www.accuflight.com

http://www.lettersdirect.co.uk

http://www.ourhouseinportugal.co.uk/

 

[ZmrAbdulla]

Hi,
Your criteria
(((dbo_Passwords.ManagerNo)=[forms]![frmpassword]![txtManagerNo]));"
looks like an Access Database Form reference.

In vb I think something similar
(((dbo_Passwords.ManagerNo)=& frmPassword.txtManager.text";"))

HTH




Zameer Abdulla

http://www.geocities.com/zameerabd/

 

[dom24]

I still can't get this working.

I'm passing values from an Access form through a SQL query and I want to open the query in a recordset so I can go through it. I want the user to enter a ManagerNo into the form , then my query will take this value and check if it is in the ManagerNo field of the Passwords table.
If it isn't, I want a messaeg box to say "Sorry this si not a manager number" but if it is I want it to update the password field with the word "password".

 

[petermeachem]

Single step the code and check whether the string looks right.
You still need to use a select though.

http://www.accuflight.com

http://www.lettersdirect.co.uk

http://www.ourhouseinportugal.co.uk/

 

[HarleyQuinn]

Hi,

As PeterMeacham said if you run a SELECT query to see if the Manager no. exists and then if it does run the UPDATE statement. If the select query returns no values then display the message.

Pseudocode would be something like this:

SELECT STATEMENT BASED ON USER INPUT

IF SELECT RETURNS NO ROWS THEN

MSGBOX "NOT A MANAGER NUMBER"

ELSE

UPDATE MANAGER TABLE

END IF

Obviously I have omitted the closing of the recordset and connection etc. but the pseudocode is only to give the gist of how to do this.

Hope this helps.


Harleyquinn

 

[zemp]

If the criteria for the where clause is coming from a variable of some kind then you need to conacatenate that into your SQL properly. I would also qualify the table and field names with square brackets just to ensure that they are not misinterprited. Something like,

strSQL = "UPDATE [dbo_Passwords] SET [password] = 'password' WHERE ((([dbo_Passwords].[ManagerNo])='  & frmpassword.txtManagerNo.text & "'));"

zemp

 

[bjd4jc]

>>I'm passing values from an Access form through a SQL query

How does this question pertain to Visual Basic Version 5 or 6?? It seems like this doesn't use VB at all... strictly VBA.

 

[dom24]

I've change it to this:

If Me.txtManagerNo <> "" Then
strSQL = "SELECT * FROM dbo_Passwords WHERE dbo_passwords.ManagerNo = [forms]![frmPassword]![txtStaffNo]"
Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)

With rst
If .EOF Then
MsgBox "Sorry, this is not a Manager Number. Please try again."
Else
strSQL = "UPDATE [dbo_Passwords] SET [password] = 'password' WHERE ((([dbo_Passwords].[ManagerNo])=' & frmpassword.txtManagerNo.text & " '));"
db.Execute (strSQL)
End If
End With

Else
strSQL = "UPDATE dbo_Passwords INNER JOIN dbo_Staff ON dbo_Passwords.ManagerNo = dbo_Staff.ManagerNo SET dbo_Passwords.password = 'password' " & _
" WHERE (((dbo_Staff.LName)=[forms]![frmpassword]![cboLName]) AND ((dbo_Staff.FName)=[forms]![frmpassword]![cboFName]));"
db.Execute (strSQL)

End If

But now i'm getting an error "TOo few parameters" on the line Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)

 

[dom24]

Didn't realise there was a difference bjd4jc.
Sorry, maybe i'm in the wrong forum then.

 

[petermeachem]

Plonk a breakpoint on Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)
and tell us what the value of strSQL is.

http://www.accuflight.com

http://www.lettersdirect.co.uk

http://www.ourhouseinportugal.co.uk/

 

[dom24]

Sorted it.

If Me.txtManagerNo <> "" Then
strSQL = "SELECT * FROM dbo_Passwords WHERE dbo_passwords.ManagerNo = '" & Me.txtManagerNo & "' "
Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)

With rst
If .EOF Then
MsgBox "Sorry, this is not a Manager Number. Please try again."
Else
strSQL = "UPDATE dbo_Passwords SET password = '" & Me.txtManagerNo & "' WHERE dbo_Passwords.ManagerNo = '" & Me.txtManagerNo & "';"
db.Execute (strSQL)

'MsgBox (strSQL)
End If
End With

ElseIf Me.cboLName <> "" And Me.cboFName <> "" Then
strSQL = "UPDATE dbo_Passwords INNER JOIN dbo_Staff ON dbo_Passwords.ManagerNo = dbo_Staff.ManagerNo SET dbo_Passwords.password = 'password' " & _
" WHERE dbo_Staff.LName = '" & Me.cboLName & "' AND dbo_Staff.FName = '" & Me.cboFName & "';"
db.Execute (strSQL)

MsgBox (strSQL)

Else
MsgBox "Please enter a staff number or select a surname AND first name."
End If

Thanks for your help everyone.