|
|||||||||
|
|||||||||
|
|||||||||
| |
|||
 |
|
|
|
|
|||||||||
|
|||||||||
|
|||||||||
| |
|||
|
|
|
|
| ||||||||||||||||||||||||||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Display Modes |
Dev Articles Community Forums Sponsor:
|
|
|
|
#1
July 27th, 2009, 05:49 AM
|
|||
|
|||
|
Execute Stored Proc through AccessVBA
I have this code below which will run a Stored Procedure and works great, until the incorrect Username or Password is input.
Then I get an error "Operation is not allowed when the object is closed" This debugs on line - Quote:
What should I do when there is a wrong Username or Password input incorrectly? Code:
Private Sub lblLogIn_Click()
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim cmd As ADODB.Command
'************************************************* ************************************
If IsNull(Me.txtUsername) Or Me.txtUsername = "" Then
MsgBox "You must enter a Username.", vbCritical, cApplicationName & " - Required Data"
Me.txtUsername.SetFocus
Exit Sub
End If
'************************************************* ************************************
If IsNull(Me.txtPassword) Or Me.txtPassword = "" Then
MsgBox "You must enter a Password.", vbCritical, cApplicationName & " - Required Data"
Me.txtPassword.SetFocus
Exit Sub
End If
'************************************************* ************************************
Set cnn = New ADODB.Connection
Set cmd = New ADODB.Command
cnn.Open cCnnStr
cnn.CursorLocation = adUseClient
cmd.ActiveConnection = cnn
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "sp_CIP_LogIn"
cmd.Parameters.Append cmd.CreateParameter("@pUserLogIn", adVarChar, adParamInput, 100, txtUsername.Value)
cmd.Parameters.Append cmd.CreateParameter("@pPassword", adVarChar, adParamInput, 25, txtPassword.Value)
Set rs = cmd.Execute
'************************************************* ************************************
If rs.BOF And rs.EOF Then
MsgBox "Nothing"
Else
MsgBox Me.txtUsername & " " & Me.txtPassword
End If
rs.Close
Set rs = Nothing
cnn.Close
Set cnn = Nothing
Set cmd = Nothing
DoCmd.Close acForm, "frmLogIn", acSaveNo
DoCmd.OpenForm "frmSplash"
End Sub
|
|
#2
July 27th, 2009, 04:19 PM
|
||||
|
||||
|
Why not put in an error message telling the user that the input was incorrect, resubmit and reprocess.
Alan
__________________
Alan Sidman __________ If I helped you, I'd like to know. Click on the icon next to the thread number and tell me.
|
|
#3
July 28th, 2009, 02:07 AM
|
|||
|
|||
|
Quote:
I was thinking that but where would I put this, as am unsure where to put it?
|
|
#4
July 28th, 2009, 05:49 AM
|
||||
|
||||
|
Just before your If IsNull statements, put an error handler that tells Access what to do in the event of an error. Put your error vba that the error handler points to at the end of the function.
Here is an example http://allenbrowne.com/ser-23b.html HTH Alan
|
|
#5
July 28th, 2009, 08:11 AM
|
|||
|
|||
|
Ok, this below is what I have done...
When I try compile it I get an error "Label Not Defined" this is on the line On Error GoTo Err_Retry highlighting Err_Retry Where have I gone wrong? Code:
Private Sub lblLogIn_Click()
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim cmd As ADODB.Command
'************************************************* ************************************
On Error GoTo Err_Retry
If IsNull(Me.txtUsername) Or Me.txtUsername = "" Then
MsgBox "You must enter a Username.", vbCritical, cApplicationName & " - Required Data"
Me.txtUsername.SetFocus
Exit Sub
End If
'************************************************* ************************************
If IsNull(Me.txtPassword) Or Me.txtPassword = "" Then
MsgBox "You must enter a Password.", vbCritical, cApplicationName & " - Required Data"
Me.txtPassword.SetFocus
Exit Sub
End If
'************************************************* ************************************
Set cnn = New ADODB.Connection
Set cmd = New ADODB.Command
cnn.Open cCnnStr
cnn.CursorLocation = adUseClient
cmd.ActiveConnection = cnn
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "sp_CIP_LogIn"
cmd.Parameters.Append cmd.CreateParameter("@pUserLogIn", adVarChar, adParamInput, 100, txtUsername.Value)
cmd.Parameters.Append cmd.CreateParameter("@pPassword", adVarChar, adParamInput, 25, txtPassword.Value)
Set rs = cmd.Execute
'************************************************* ************************************
If rs.BOF And rs.EOF Then
MsgBox "Nothing"
Else
MsgBox Me.txtUsername & " - " & Me.txtPassword
End If
rs.Close
Set rs = Nothing
cnn.Close
Set cnn = Nothing
Set cmd = Nothing
DoCmd.Close acForm, "frmLogIn", acSaveNo
DoCmd.OpenForm "frmSplash"
Exit_Retry:
Exit Sub
End Sub
Err_Retry:
MsgBox "Password Invalid. Please Try Again", vbExclamation, cApplicationName & " - Invalid Entry!"
Me.txtDummy.SetFocus
Exit Sub
intLogInAttempts = intLogInAttempts + 1
If intLogInAttempts > 3 Then
MsgBox "You do not have access to this database. Please contact the System Administrator.", vbCritical, cApplicationName & " - Restricted Access!"
Application.Quit
Exit Sub
End If
End Sub
|
|
| Viewing: Dev Articles Community Forums > Databases > Microsoft Access Development > Execute Stored Proc through AccessVBA |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
|
 |
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
