Access crashes because of this code

[Afrosheen via AccessMonster.com]

I can't understand it. For some reason this code gives me the Access Screen
of Death and I can't figure out why. When ever I do a backup and the program
restarts Access crashes. This is the code I've nailed it down to this. I
thought it was correct. Access crashes even if I don't press the update key.

Private Sub cmdUpdate_Click()
Dim myName As DAO.Recordset
10 On Error GoTo cmdUpdate_Click_Error

20 Set myName = Me.Recordset.Clone
30 myName.FindFirst "[user] = '" & Me!txtUserName & "'"
40 If myName.NoMatch Then
50 Me.txtUserName = ""
60 Me.txtUserName.SetFocus
70 DoCmd.OpenForm "frmWrong"
80 numCount = 1
90 End If

100 Me.Bookmark = myName.Bookmark
110 Me.[Password] = Text25
120 Call MsgBox("Password change has been confirmed." _
& vbCrLf & "It will be Activated the next time you Log In." _
& vbCrLf & "Until then use your current password." _
, vbInformation, Application.Name)

130 Me.Text25 = ""
140 Me.txtUserName = Null
150 Me.txtPassword = Null
160 Me.txtUserName.SetFocus
170 cmdpword.Enabled = False
180 Me.FormFooter.Visible = False
190 DoCmd.RunCommand acCmdSizeToFitForm
200 'End If

myName.Close
Set myName = Nothing

210 On Error GoTo 0
220 Exit Sub

cmdUpdate_Click_Error:

230 MsgBox "Error " & Err.Number & " (" & Err.Description & ") in
procedure cmdUpdate_Click of VBA Document Form_frmLogin"
End Sub

What it is supposed to do is to update a persons password and it does do this.
I want to do is search for the person that matches the [user] that = the
txtUserName.

Thanks for looking at this.

 

[Stuart McCall]

I can't understand it. For some reason this code gives me the Access Screen
of Death and I can't figure out why. When ever I do a backup and the
program
restarts Access crashes. This is the code I've nailed it down to this. I
thought it was correct. Access crashes even if I don't press the update
key.

Private Sub cmdUpdate_Click()
Dim myName As DAO.Recordset
10 On Error GoTo cmdUpdate_Click_Error

20 Set myName = Me.Recordset.Clone
30 myName.FindFirst "[user] = '" & Me!txtUserName & "'"
40 If myName.NoMatch Then
50 Me.txtUserName = ""
60 Me.txtUserName.SetFocus
70 DoCmd.OpenForm "frmWrong"
80 numCount = 1
90 End If

100 Me.Bookmark = myName.Bookmark
110 Me.[Password] = Text25
120 Call MsgBox("Password change has been confirmed." _
& vbCrLf & "It will be Activated the next time you Log In." _
& vbCrLf & "Until then use your current password." _
, vbInformation, Application.Name)

130 Me.Text25 = ""
140 Me.txtUserName = Null
150 Me.txtPassword = Null
160 Me.txtUserName.SetFocus
170 cmdpword.Enabled = False
180 Me.FormFooter.Visible = False
190 DoCmd.RunCommand acCmdSizeToFitForm
200 'End If

myName.Close
Set myName = Nothing

210 On Error GoTo 0
220 Exit Sub

cmdUpdate_Click_Error:

230 MsgBox "Error " & Err.Number & " (" & Err.Description & ") in
procedure cmdUpdate_Click of VBA Document Form_frmLogin"
End Sub

What it is supposed to do is to update a persons password and it does do
this.
I want to do is search for the person that matches the [user] that = the
txtUserName.

Thanks for looking at this.

I thinkl the problem lies with this code:

DoCmd.RunCommand acCmdSizeToFitForm

The acCmdSizeToFitForm is designed to work with a form open in design view,
not form view.
You can achieve the same objective by reducing the .Height property of the
form by the height of it's footer:

Me.Height = Me.Height - Me.Section(acFooter).Height

That's air code. If it doesn't work, that's the gist of it at least.

 

[Afrosheen via AccessMonster.com]

Thanks for the reply. I tried your "Air" code and it didn't work. It told me
Method or Data member not found.

I can't understand it. For some reason this code gives me the Access Screen
of Death and I can't figure out why. When ever I do a backup and the

[quoted text clipped - 50 lines]

Thanks for looking at this.

I thinkl the problem lies with this code:

DoCmd.RunCommand acCmdSizeToFitForm

The acCmdSizeToFitForm is designed to work with a form open in design view,
not form view.
You can achieve the same objective by reducing the .Height property of the
form by the height of it's footer:

Me.Height = Me.Height - Me.Section(acFooter).Height

That's air code. If it doesn't work, that's the gist of it at least.

 

[David C. Holley]

Along with the other response. There are some style-related comments inline.

I can't understand it. For some reason this code gives me the Access
Screen
of Death and I can't figure out why. When ever I do a backup and the
program
restarts Access crashes. This is the code I've nailed it down to this. I
thought it was correct. Access crashes even if I don't press the update
key.

Private Sub cmdUpdate_Click()
Dim myName As DAO.Recordset
10 On Error GoTo cmdUpdate_Click_Error

20 Set myName = Me.Recordset.Clone
30 myName.FindFirst "[user] = '" & Me!txtUserName & "'"
40 If myName.NoMatch Then
50 Me.txtUserName = ""
60 Me.txtUserName.SetFocus
70 DoCmd.OpenForm "frmWrong"

Why are you using a form to advise the user that the password is invalid? It
is more customary to either display a MsgBox or to place a label on the form
and change its .Visible Property to True to make it appear.

Why won't a DLookup() on the underlying table with the user Id's work?

100 Me.Bookmark = myName.Bookmark
110 Me.[Password] = Text25

Your controls should be explicity named.

Why bother with a record set to up the value? Why not simply use an Update
query? The form where the user enters their Password DOES NOT have to be
bound to a recordset. Most implementations will go with an Unbound form and
then use DLookup() to validate the entry and an UPDATE query to change it.

If you're code throws an error, the record set will NOT be closed. Always
close what you open and destroy what you create.

End Sub

What it is supposed to do is to update a persons password and it does do
this.
I want to do is search for the person that matches the [user] that = the
txtUserName.

Thanks for looking at this.

I thinkl the problem lies with this code:

DoCmd.RunCommand acCmdSizeToFitForm

The acCmdSizeToFitForm is designed to work with a form open in design
view, not form view.
You can achieve the same objective by reducing the .Height property of the
form by the height of it's footer:

Me.Height = Me.Height - Me.Section(acFooter).Height

That's air code. If it doesn't work, that's the gist of it at least.

 

[Stuart McCall]

Thanks for the reply. I tried your "Air" code and it didn't work. It told
me
Method or Data member not found.

But did removing the DoCmd line stop Access from crashing? If not there's no
point pursuing this. Something else must be at fault. Although I can't see
anything else that would or could crash out like that.

 

[Stuart McCall]

Try removing the DoCmd line and implementing Mr. Holley's suggestions.

 

[David C. Holley]

Have you read my inline responses a few posts up? I think that you've got
some major design issues going on, which if corrected will solve your
problem.

Thanks for the reply. I tried your "Air" code and it didn't work. It told
me
Method or Data member not found.

I can't understand it. For some reason this code gives me the Access
Screen
of Death and I can't figure out why. When ever I do a backup and the

[quoted text clipped - 50 lines]

Thanks for looking at this.

I thinkl the problem lies with this code:

DoCmd.RunCommand acCmdSizeToFitForm

The acCmdSizeToFitForm is designed to work with a form open in design
view,
not form view.
You can achieve the same objective by reducing the .Height property of the
form by the height of it's footer:

Me.Height = Me.Height - Me.Section(acFooter).Height

That's air code. If it doesn't work, that's the gist of it at least.

 

[Afrosheen via AccessMonster.com]

Thanks for the reply. I've changed some of the item you suggested and it made
more sense. Thank you. The problem now comes in the Update Query. It kept on
telling me that there were not enought parameters..The upper part to the Else
works great. This is the revised code

20 If myName = Nz(DLookup("[user]", "tblPass1", "[User] = '" & txtUserName
& "'")) Then
30 Me.txtUserName = ""
40 Me.txtUserName.SetFocus
50 Call MsgBox("Your UserId or Password can not be confirmed." _
& vbCrLf & "Please try again." _
, vbCritical, Application.Name)
60 Else
70 myName = "Update [tblpass1] " & _
"Set [password] = " & Text25 & " " & _
"Where [user] =" & txtUserName & ";"


When I hovered over the myName statement it looked ok.
I'm getting error: 3061 (too few parameters.. expected 2). This error is from
line 80


80 CurrentDb.Execute myName, dbFailOnError
90 Call MsgBox("Password change has been confirmed." _
& vbCrLf & "It will be Activated the next time you Log In." _
& vbCrLf & "Until then use your current password." _
, vbInformation, Application.Name)

'Me.Text25 = ""
'Me.txtUserName = Null
'Me.txtPassword = Null
'Me.txtUserName.SetFocus
'cmdpword.Enabled = False
'Me.FormFooter.Visible = False
'DoCmd.RunCommand acCmdSizeToFitForm
100 End If

 

[Paolo]

Hi Afrosheen,
to solve the issue you just need to enclose between quotes the text argument
so your update must be

myName = "Update [tblpass1] " & _
"Set [password] = """ & Text25 & """ " & _
"Where [user] =""" & txtUserName & """;"

Assuming that text25 and txtUserName are text variables.

HTH Paolo

Thanks for the reply. I've changed some of the item you suggested and it made
more sense. Thank you. The problem now comes in the Update Query. It kept on
telling me that there were not enought parameters..The upper part to the Else
works great. This is the revised code

20 If myName = Nz(DLookup("[user]", "tblPass1", "[User] = '" & txtUserName
& "'")) Then
30 Me.txtUserName = ""
40 Me.txtUserName.SetFocus
50 Call MsgBox("Your UserId or Password can not be confirmed." _
& vbCrLf & "Please try again." _
, vbCritical, Application.Name)
60 Else
70 myName = "Update [tblpass1] " & _
"Set [password] = " & Text25 & " " & _
"Where [user] =" & txtUserName & ";"


When I hovered over the myName statement it looked ok.
I'm getting error: 3061 (too few parameters.. expected 2). This error is from
line 80


80 CurrentDb.Execute myName, dbFailOnError
90 Call MsgBox("Password change has been confirmed." _
& vbCrLf & "It will be Activated the next time you Log In." _
& vbCrLf & "Until then use your current password." _
, vbInformation, Application.Name)

'Me.Text25 = ""
'Me.txtUserName = Null
'Me.txtPassword = Null
'Me.txtUserName.SetFocus
'cmdpword.Enabled = False
'Me.FormFooter.Visible = False
'DoCmd.RunCommand acCmdSizeToFitForm
100 End If

Along with the other response. There are some style-related comments inline.

--



.

 

[Afrosheen via AccessMonster.com]

Thanks Paolo, You de man.

These things are so confusing.
Now I think I have two problems solved. I'm still checking on my other
problem.

Hi Afrosheen,
to solve the issue you just need to enclose between quotes the text argument
so your update must be

myName = "Update [tblpass1] " & _
"Set [password] = """ & Text25 & """ " & _
"Where [user] =""" & txtUserName & """;"

Assuming that text25 and txtUserName are text variables.

HTH Paolo

Thanks for the reply. I've changed some of the item you suggested and it made
more sense. Thank you. The problem now comes in the Update Query. It kept on

[quoted text clipped - 33 lines]

 

[Afrosheen via AccessMonster.com]

Now that I have made the changes the program still crashes. Any suggestions
or some where I could look?

Thanks Paolo, You de man.

These things are so confusing.
Now I think I have two problems solved. I'm still checking on my other
problem.

Hi Afrosheen,
to solve the issue you just need to enclose between quotes the text argument

[quoted text clipped - 13 lines]

 

[Paolo]

Now that I have made the changes the program still crashes. Any suggestions
or some where I could look?

Thanks Paolo, You de man.

These things are so confusing.
Now I think I have two problems solved. I'm still checking on my other
problem.

Hi Afrosheen,
to solve the issue you just need to enclose between quotes the text argument

[quoted text clipped - 13 lines]

Along with the other response. There are some style-related comments inline.