Help with VBA please

[data808]

I have this code below. Everything works except for this line: If Not IsNull(Answer) And Me.txtUsername = Dirty Then

I really just need to add the dirty command to this line of code but don't know how in VBA can someone help with this? Sometimes I need to change the password of a user and this code searches to make sure that the username is not used more than once and since I am saving the record by editing the password, it finds that the username is already in use so I have to change the user name slightly different just to save the new password then change the username back to what it was then save again. Thanks for the help.

Private Sub cmdSaveRecord_Click()
Dim Answer As Variant
Answer = DLookup("Username", "tblUser", "Username = '" & Me.Username & "'")



If Not Me.Dirty Then
MsgBox "No Changes Were Made.", vbInformation, "Notification"
DoCmd.GoToControl "[txtEmpName]"
Exit Sub
End If

If Not IsNull(Answer) And Me.txtUsername = Dirty Then
MsgBox "Username Already In Use." & vbCrLf & vbCrLf & "Please Enter A Different Username." _ , vbInformation, "Requirements"
Me.txtUsername.SetFocus
DoCmd.CancelEvent
Exit Sub
End If

If Me.Dirty Then
DoCmd.RunCommand acCmdSaveRecord
DoCmd.GoToRecord , , acPrevious
DoCmd.GoToRecord , , acNext
DoCmd.GoToControl "[txtEmpName]"
End If

End Sub

[Bob Fitz]

I'm not certain about this but I don't think that a control (txtUsername) can be "Dirty". Only the form can be "Dirty".

[ItsMe]

Why not use an integer variable and Dcount in the BeforeUpdate event as validation?

Didn't June already mention the .Dirty thing in another thread? I will be the third person to mention that line of code is not advisable.

[data808]

I remember seeing something that the way I was using the Dirty command was not used in VBA. I didn't know that it was not available at all. Not sure how to do a integer variable and Dcount. I had help doing this Dlookup so I'm not sure exactly how it works. Can you assist?

[ItsMe]

The following VBA is good

If Me.Dirty = True then
Me.dirty = False
End if

or

If Forms!FormName.Dirty = True then
Forms!FormName.dirty = False
End if


The following is not OK

Me.Controlname.Dirty

There is not a Dirty property for Controls. There is a Dirty property for forms.


AS for the Dcount, it is a domain function. It returns an integer. The help files mention it returns a Variant but the examples use Integer so...
http://msdn.microsoft.com/en-us/libr...ffice.14).aspx

Dim intCount as integer

intCount = Dcount("Username", "tblUser", "Username = '" & Me.Username & "'")

If intcount <= 0 then

msgbox "No Match"
else
msgbox "Found Match"
End If


EDIT: Thanks John!!!

[John_G]

Hi -

A quick correction -

intCount = ("Username", "tblUser", "Username = '" & Me.Username & "'")

should be

intCount = DCount("Username", "tblUser", "Username = '" & Me.Username & "'")

John

[data808]

Are you replacing the "Answer" in the VBA code above with "intCount" then also changing "Variant" to "Integer" and lastly, "DLookup" to "DCount"? If not, then is this all for a new event? ItsMe suggested a Before Update event. Was all this for that?

Sorry for the ignorance. I have a hard time understanding how this all works.

[ItsMe]

Your code that you posted does not make much sense from a "Workflow" perspective. In other words, what is the code supposed to do? Additionaly, the validation is not going to work because you cannot check for a Dirty property of a control. Besides, you are assigning a variable to the textbox and then asking if it is Dirty???

If you are trying to find a match in a table, something like this should do the trick.

Dim intCount as integer

 intCount = Dcount("Username", "tblUser", "Username = '" & Me.Username & "'") 

 If intcount <= 0 then

 msgbox "No Match"
 else
 msgbox "Found Match"
 End If

[data808]

What happens is that I try to make a change to just the password. Then I try to save it but it runs my code and searches for a match for the username which it will always find even though I haven't touched it. I only touched the password. Because it always searches even though I did not edit the username it will find it because it was already a record in the table to begin with. Here is an example of what is happening:

Username: John
Password: hello

This is what is in the table at the moment. Now I will make a change to the password:

Username: John
Password: bye

Then I try to save it. It does a search when I save it and it will find John is already being used. I will get the prompt to change the username. So I do this:

Username: Johnx
Password: bye

Then I am now able to save it. However, I do not want "Johnx" as the username so I go back and change it to:

Username: John
Password: bye

Then I save it and all is well in the world but its very annoying to have to do this extra step. Is the code you are offering going to fix this problem? But now you can understand why I wanted the dirty function for a control which now I know does not exist from what you folks are telling me. Please help me with this.

Thanks.

[data808]

Ok so what I decided to do is change the index property in my table for the usernames field to yes (no duplicates). Then I have a button on a form to save the record and this is the VBA behind it:

Private Sub SaveRecord_Click()

If Not Me.Dirty Then
MsgBox "No Changes Were Made.", vbInformation, "Notification"
DoCmd.GoToControl "[EmpName]"
Exit Sub
End If

On Error GoTo ErrHandler:

If Me.Dirty Then
DoCmd.RunCommand acCmdSaveRecord
DoCmd.GoToRecord , , acPrevious
DoCmd.GoToRecord , , acNext
DoCmd.GoToControl "[EmpName]"
Exit Sub
End If

ErrHandler:
MsgBox "Username Already In Use. Please choose another username.", vbInformation, "Notification"
Me.EmpName.SetFocus
Exit Sub

End Sub

Then I have this in the before update event:

Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo ErrHandler:

If MsgBox("Changes Have Been Made To This Record." _
& vbCrLf & vbCrLf & "Do You Want To Save These Changes?" _
, vbYesNo, "Save Changes?") = vbYes Then
DoCmd.Save
Exit Sub
Else
DoCmd.RunCommand acCmdUndo
Exit Sub
End If

ErrHandler:
MsgBox "Username already in use. Please use another username.", vbInformation, "Notification"
Me.EmpName.SetFocus
Exit Sub

End Sub

So now basically if I save a duplicate username it will have an error message and I am trying to replace this error message with a MsgBox that tells the user that this username is already being used and to please choose another username. The problem is that sometimes the prompt will come up twice or not at all because there is two ways that this form can be saved. One is by the save button that the user clicks on. The other is by the navigation buttons I created to move to different records. Before it lets to go to the next or previous record, the before update event is triggered and will ask if you want to save the record. If you click yes and its a duplicate record then nothing happens but no prompt comes up to let the user know the record was not saved.

Does anyone know how to fix this?

[data808]

In case anyone cares I figured it out.

Here is what I needed behind the save button on the click event:

Private Sub SaveRecord_Click()
Dim Answer As Variant
Answer = DLookup("Username", "tblUser", "Username = '" & Me.Username & "'")

If Not Me.Dirty Then
MsgBox "No Changes Were Made.", vbInformation, "Notification"
DoCmd.GoToControl "[EmpName]"
Exit Sub
End If

If Not IsNull(Answer) And Me.NewRecord Then
MsgBox "Username Already In Use." & vbCrLf & vbCrLf & "Please Enter A Different Username." _
, vbInformation, "Requirements"
Me.Username.SetFocus
DoCmd.CancelEvent
Exit Sub
End If

If Not IsNull(Answer) And Me.Username.Value <> Me.Username.OldValue Then
MsgBox "Username Already In Use." & vbCrLf & vbCrLf & "Please Enter A Different Username." _
, vbInformation, "Requirements"
Me.Username.SetFocus
DoCmd.CancelEvent
Exit Sub
End If

If Me.Dirty Then
DoCmd.RunCommand acCmdSaveRecord
DoCmd.GoToRecord , , acPrevious
DoCmd.GoToRecord , , acNext
DoCmd.GoToControl "[EmpName]"
End If

End Sub

Then here is what I needed in the before update event:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim Answer As Variant
Answer = DLookup("Username", "tblUser", "Username = '" & Me.Username & "'")

If Not IsNull(Answer) And Me.NewRecord Then
MsgBox "Username Already In Use." & vbCrLf & vbCrLf & "Please Enter A Different Username." _
, vbInformation, "Requirements"
MsgBox "Your Record Was Not Saved.", vbInformation, "Record Not Saved"
DoCmd.RunCommand acCmdUndo
Exit Sub
End If

If Not IsNull(Answer) And Me.Username.Value <> Me.Username.OldValue Then
MsgBox "Username Already In Use." & vbCrLf & vbCrLf & "Please Enter A Different Username." _
, vbInformation, "Requirements"
MsgBox "Your Record Was Not Saved.", vbInformation, "Record Not Saved"
DoCmd.RunCommand acCmdUndo
Exit Sub
End If

If MsgBox("Changes Have Been Made To This Record." _
& vbCrLf & vbCrLf & "Do You Want To Save These Changes?" _
, vbYesNo, "Save Changes?") = vbYes Then
DoCmd.Save
Else
DoCmd.RunCommand acCmdUndo
End If

End Sub