Very long post - help!

Hi,

I'm in high school and I've been given a database based computing project to do over the summer. Before school finished, our teacher was too busy tinkering with the new server to find the time to teach us. Hence, none of the class know enough to do this project. Please could you help me with this... The following code is something which I'm trying to get to work to delete records from an Access database. I attempted to design this module based on my add record module, which did work, which I have pasted at the bottom of this message. The remove record module is still incomplete, at the moment, I am simply trying to get it to find the record before removing it, so I would be very grateful for any help anyone could give for either or both functions of the module.

Remove module (sorry for the partial simple annotations. I'm not insulting anyone's intelligence, it's just that I'm supposed to fully annotate all the code, so that anyone would know what it means):

Private Sub EXITButton_Click()
RemoveUser.Hide
AdminMenu.Show
End Sub

Private Sub Form_Load()
Dim UserIDTextboxFilled As Boolean 'Declares the variable "UserIDTextboxFilled" as a boolean variable
End Sub

Private Sub RemoveUserButton_Click()
Dim dbsPasswords As Database 'Declares the "dbsPasswords" variable as a database
Dim rsPasswords As Recordset 'Declares the variable "rsPasswords" as a set of records
Dim found As Boolean
Dim last As String
Dim current As String
Set dbsPasswords = OpenDatabase("C:Projectusers.mdb") 'Sets the variable "dbsPasswords" to the named database file, and opens it
Set rsPasswords = dbsPasswords.OpenRecordset("userspws") 'Sets the variable "rsPasswords" to the named set of records, and opens it
If UserIDTextbox.Text = "" Then 'In the event that the User ID textbox is blank, follow these instructions
MsgBox ("Please enter user ID") 'Displays a message box on the screen containing the message between the two " quote marks
Else: UserIDTextboxFilled = True 'Otherwise, the variable "UserIDTextboxFilled" is set to "true"
End If 'This is the end of the instructions relating to the previous If statement
found = False
With rsPasswords
.MoveLast
last = !UserID
.MoveFirst
current = !UserID
End With
Do
If UserIDTextbox.Text <> current Then
rsPasswords.MoveNext
current = !UserID
Else: found = True
Loop Until found = True Or current = last
If found = True Then
MsgBox (vbYesNo & "Are you sure you want to remove the user " & current & " ?")
End If
rsPasswords.MoveFirst
rsPasswords.Close
UserIDTextbox.Text = ""
End Sub

VB complains about the last=!UserID bit, even if I don't have the with rsPasswords statement.

Add module (this actually works):


Private Sub Form_Load() 'Declares the subroutine to be executed on the loading of this form
Dim UserIDTextboxFilled As Boolean 'Declares the variable "UserIDTextboxFilled" as a boolean variable
Dim PasswordTextboxFilled As Boolean 'Declares the variable "PasswordTextboxFilled" as a boolean variable
Dim PasswordRetypeMatch As Boolean 'Declares the variable "PasswordRetypeMatch" as a boolean variable
UserIDTextboxFilled = False 'Sets the variable "UserIDTextbox" to "false"
PasswordTextboxFilled = False 'Sets the variable "PasswordTextboxFilled" to "false"
PasswordRetypeMatch = False 'Sets the variable "PasswordRetypeMatch" to "false"
End Sub 'Declares the end of the subroutine

Private Sub OKButton_Click() 'Declares the subroutine to be executed in the event of the OK button being clicked on
Dim dbsPasswords As Database 'Declares the "dbsPasswords" variable as a database
Set dbsPasswords = OpenDatabase("C:Projectusers.mdb") 'Sets the variable "dbsPasswords" to the named database file, and opens it
If UserIDTextbox.Text = "" Then 'In the event that the User ID textbox is blank, follow these instructions
MsgBox ("Please enter user ID") 'Displays a message box on the screen containing the message between the two " quote marks
Else: UserIDTextboxFilled = True 'Otherwise, the variable "UserIDTextboxFilled" is set to "true"
End If 'This is the end of the instructions relating to the previous If statement
If PasswordTextbox.Text = "" Then 'In the event that the Password textbox is blank, follow these instructions
MsgBox ("Please enter password") 'Displays a message box on the screen containing the message between the two " quote marks
Else: PasswordTextboxFilled = True 'Otherwise, the variable "PasswordTextboxFilled" is set to "true"
End If 'This is the end of the instructions relating to the previous If statement
If RetypePasswordTextbox.Text <> PasswordTextbox.Text Then 'In the event that the Password textbox and the Re-type-Password textboxes don't match, follow these instructions
MsgBox ("Password and Re-type Password do not match. Please re-enter both") 'Displays a message box on the screen containing the message between the two " quote marks
Else: PasswordRetypeMatch = True 'Otherwise, the variable "PasswordRetypeMatch" is set to "true"
End If 'This is the end of the instructions relating to the previous If statement
If (UserIDTextboxFilled = True) And (PasswordTextboxFilled = True) And (PasswordRetypeMatch = True) Then 'In the event that all the named variables are set to "true", follow these instructions
Dim rsPasswords As Recordset 'Declares the variable "rsPasswords" as a set of records
Set rsPasswords = dbsPasswords.OpenRecordset("userspws") 'Sets the variable "rsPasswords" to the named set of records, and opens it
With rsPasswords 'Using the variable "rsPasswords"...
.MoveLast 'Move to the last record
.AddNew 'Add a new record
!Password = PasswordTextbox.Text 'The attribute "Password" in the new record is set to the same as the Password textbox
!UserID = UserIDTextbox.Text 'The attribute "UserID" in the new record is set to the same as the User ID textbox
.Update 'Updates the set of records with the new record in place
.MoveFirst 'Moves to the first record
.Close 'Closes the set of records
End With 'This is the end of the instructions relating to the previous With statement
UserIDTextbox.Text = "" 'Sets the User ID textbox to blank
PasswordTextbox.Text = "" 'Sets the Password textbox to blank
RetypePasswordTextbox.Text = "" 'Sets the Re-type-Password textbox to blank
MsgBox ("User Added") 'Displays a message box on the screen containing the message between the two " quote marks
End If 'This is the end of the instructions relating to the previous If statement
End Sub 'Declares the end of the subroutine

Private Sub EXITButton_Click() 'Declares the subroutine to be executed in the event of the EXIT button being clicked on
AddUser.Hide 'Hides the Add User menu from the user
AdminMenu.Show 'Shows the Admin Menu to the user
End Sub 'Declares the end of the subroutine

Thanks for any help you can give,
Chris Williams.
(age: 17)
(IQ: 2)

Hello Chris,
Well to start off I see a couple of problems:
1) When you test for information being entered into a text box you should exit the subroutine if the box is empty and you do not want to continue processing if the text box is empty. This will eliminate the code for setting all the tests that you have. You might want to try something like:

Private Sub OKButton_Click() 'Declares the subroutine to be executed in the event of the OK
button being clicked on
'here is all your other processing stuff
'
If UserIDTextbox.Text = "" Then
optval = vbExclamation + vbOKOnly '<font color=blue>this is the variable to describe the type of message box and the buttons</font color=blue>
msgtxt = "You must Input User ID" '<font color=blue>this is the text that is displayed in your msgbox</font color=blue>
retvalue = MsgBox(msgtxt, optval, "No ID entered")
UserIDTextbox.SetFocus '<font color=blue> this will set the focus to the text box so the user can input the ID</font color=blue>
Exit Sub '<font color=blue>this will exit the subroutine and allow the user to re-enter the ID</font color=blue>
End If
If PasswordTextbox.Text = "" Then 'In the event that the Password textbox is blank, follow these instructions
optval = vbExclamation + vbOKOnly '<font color=blue>this is the variable to describe the type of message box and the buttons </font color=blue>
msgtxt = "You must Input Password" '<font color=blue>this is the text that is displayed in your msgbox</font color=blue>
retvalue = MsgBox(msgtxt, optval, "No Password entered")
PasswordTextbox.SetFocus '<font color=blue> this will set the focus to the text box so the user can input the Password </font color=blue>
Exit Sub '<font color=blue>this will exit the subroutine and allow the user to re-enter the Password </font color=blue>
End If
If RetypePasswordTextbox.Text <> PasswordTextbox.Text Then 'In the event that the
Password textbox and the Re-type-Password textboxes don't match, follow these
instructions
optval = vbExclamation + vbOKOnly 'this is the variable to describe the type of message box and the buttons
msgtxt = "Your Passwords do not match" 'this is the text that is displayed in your msgbox
retvalue = MsgBox(msgtxt, optval, "No Match")
PasswordTextbox.SetFocus '<font color=blue> this will set the focus to the text box so the user can input the password </font color=blue>
PasswordTextbox.SelStart = 0
PasswordTextbox.SelLength = Len(txtOnHand.Text) '<font color=blue> the last two lineswill highlight the text so all the user has to do is to retype the password. You might want to put some code for a KeyUp event so that if the enter button is pressed it will move to the RetypePassword box and highlight the text for rentry of the retype password. </font color=blue>
Exit Sub 'this will exit the subroutine and allow the user to re-enter the Password
End If
'
' <font color=blue>You can use this type of routine for all of your tests. There is no reason to stay in the subroutine if your conditions are not met
now for your logic in the delete section, I have the code here look at the comments I inserted.</font color=blue>

found = False
With rsPasswords
.MoveLast
last = !UserID
.MoveFirst
current = !UserID
End With <font color=blue>NOTE that here you ENDED the With Variable </font color=blue>
Do
If UserIDTextbox.Text <> current Then
rsPasswords.MoveNext
current = !UserID <font color=blue> Here you try to reference the With Variable! </font color=blue>
Else: found = True
Loop Until found = True Or current = last
If found = True Then
MsgBox (vbYesNo & "Are you sure you want to remove the user " & current & " ?")
<font color=blue>You don't have a test to see if they want it deleted or not!! And no Delete routine</font color=blue>
End If
rsPasswords.MoveFirst <font color=blue>Why MoveFirst ? </font color=blue>
rsPasswords.Close
UserIDTextbox.Text = ""

<font color=blue>This is a simpler way to do the same thing </font color=blue>

' you don't need the found test!
rsPasswords.MoveFirst

Do until rsPasswords.EOF = True <font color=blue>This will step through all of your records</font color=blue>
If rsPasswords.Fields("UserID") = UserIDTextbox.Text Then
RetValue=MsgBox (vbYesNo & "Are you sure you want to remove the user " & UserIDTextbox & " ?")
If RetValue = vbYes then
rsPasswords.Delete
rsPasswords.Update
Exit Sub
End If
rsPasswords.MoveNext
Loop
<font color=blue>Here you could put a message box stating that no records are found </font color=blue>
rsPasswords.Close
UserIDTextbox.Text = ""
End Sub
<font color=blue>I didn't test this but it should work, I might have made a minor error but this is YOUR homework so if I did then figure it out!! </font color=blue> /images/icons/wink.gif

Thanks for the help :-) I know I made a lot of mistakes, but my teacher didn't teach me much, and this is my first attempt at VB programming. Anyway, you've helped me a lot. Thanks once again.