enabled=true command button in VBA

[SoxPats83]

i have a command button on my form that i do not want to be enabled until 1 of 10 or so passwords are typed into a text box on the same form. i wish that i had more insight on this then the zero i have, but i am pretty lost on this one. is anyone able to assist?

 

[boblarson]

Do you mean until something is typed, or a particular password is input in its entirety and correctly?

 

[SoxPats83]

say the password is cat. then the button remains disabeled until the full word of cat is typed.

 

[boblarson]

Well, assuming your passwords are in a table, you could load them up as an array when you open the form and then you would need to use the text box's ON CHANGE event and you would do something like this:

In the GENERAL DECLARATIONS SECTION of the form

Private varArray(1 To 10) As String

In the form's Load Event assign an array with the values

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim i As Integer

Set db = Currentdb
Set rst = db.OpenRecordset("YourTableNameHere")

i = 1

Do Until rst.EOF
   varArray(i) = rst("PasswordFieldNameHere").Value
   i = i + 1   
   rst.MoveNext
Loop
rst.Close
Set rst = Nothing

And then in your ON CHANGE event of the text box you could use:

Dim i As Integer

For i = 1 To UBound(varArray)
Me.YourButtonNameHere.Enabled = (Me.YourTextBoxNameHere.Text = varArray(i))    
Next

 

[SoxPats83]

Well, assuming your passwords are in a table, you could load them up as an array when you open the form and then you would need to use the text box's ON CHANGE event and you would do something like this:

In the GENERAL DECLARATIONS SECTION of the form

Private varArray(1 To 10) As String

In the form's Load Event assign an array with the values

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim i As Integer
 
Set db = Currentdb
Set rst = db.OpenRecordset("YourTableNameHere")
 
i = 1
 
Do Until rst.EOF
   varArray(i) = rst("PasswordFieldNameHere").Value
   i = i + 1   
   rst.MoveNext
Loop
rst.Close
Set rst = Nothing

And then in your ON CHANGE event of the text box you could use:

Dim i As Integer
 
For i = 1 To UBound(varArray)
Me.YourButtonNameHere.Enabled = (Me.YourTextBoxNameHere.Text = varArray(i))    
Next

that code works great when i type the incorrect password in the text box. but yet the button remains enabled when i do not select the text box at all and on the load of the form itself.

 

[SoxPats83]

i corrected it. i just added

Me.commandbutton.enabled = false

on the form load. thanks!

 

[boblarson]

Set the button's Enabled property to be disabled to start with in design view.

 

[SoxPats83]

do you know of any coding that does the same thing except i preload the passwords into the VBA coding? this works great for 1 of my passwords, but the reast are not triggering the command button to enable.

 

[boblarson]

do you know of any coding that does the same thing except i preload the passwords into the VBA coding? this works great for 1 of my passwords, but the reast are not triggering the command button to enable.

Post the code you are trying to use.

 

[SoxPats83]

Post the code you are trying to use.

Private varArray(1 To 9) As String
Private Sub Form_Load()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim i As Integer
Set db = CurrentDb
Set rst = db.OpenRecordset("Employees")
i = 1
Do Until rst.EOF
   varArray(i) = rst("Password").Value
   i = i + 1
   rst.MoveNext
Loop
rst.Close
Set rst = Nothing
Me.Command.Enabled = False
End Sub
Private Sub txtPassword_Change()
Dim i As Integer
For i = 1 To UBound(varArray)
Me.Command.Enabled = (Me.txtPassword.Text = varArray(i))
Next
End Sub

thanks for your help!

 

[boblarson]

Remember, you will have to close and open the form before attempting it. The private variable is destroyed if you go into design view.

You might put a breakpoint into the on change event to step through and see what is happening as you do each keystroke, especially looking at the last one where you complete a password. My tests all worked.

 

[SoxPats83]

i am trying the breakpoint, but i do not quite understand it. it's odd that only 1 of my passwords work.

 

[boblarson]

i am trying the breakpoint, but i do not quite understand it. it's odd that only 1 of my passwords work.

What may be happening is that it isn't loading them into the array.

See here on how to set a breakpoint.

 

[SoxPats83]

do you have any idea why/how these fields may not be loading into the array?

 

[boblarson]

Well, it would appear that you have used an Access Reserved Word for the field name ("Password") so that might have something to do with it. Other than that, you might put a breakpoint on the code that builds the array and step through it and see as each record changes what the field value shows.

 

[SoxPats83]

when i have the varArray up top set to 1 to 9 or 0 to 9 it will only acknowledge the password in the record #9 on my assigned table

 

[boblarson]

Okay, perhaps we need to change this part:

For i = 1 To UBound(varArray)
Me.Command.Enabled = (Me.txtPassword.Text = varArray(i))
Next

to this:

For i = 1 To UBound(varArray)
If Me.txtPassword.Text = varArray(i) Then
Me.Command.Enabled = True
Exit For
Else
Me.Command.Enabled = False
End If
Next

 

[SoxPats83]

Okay, perhaps we need to change this part:

For i = 1 To UBound(varArray)
Me.Command.Enabled = (Me.txtPassword.Text = varArray(i))
Next

to this:

For i = 1 To UBound(varArray)
If Me.txtPassword.Text = varArray(i) Then
Me.Command.Enabled = True
Exit For
Else
Me.Command.Enabled = False
End If
Next

perfect. that worked! thanks!

 

[gemma-the-husky]

Private Sub txtPassword_Change()
Dim i As Integer
For i = 1 To UBound(varArray)
Me.Command.Enabled = (Me.txtPassword.Text = varArray(i))
Next
End Sub

not sure if its this bit thats the problem?

where are you actually examining the password? is it here?

if so this tests every value in the array, and will end up setting the enabled flag back to false, unless you happen to use the last password.

you may get what you want by adding the bold line below, so that you exit as soon as you get a match

Me.Command.Enabled = (Me.txtPassword.Text = varArray(i))
if Me.Command.Enabled then exit sub

 

[boblarson]

Dave, apparently you missed the post where I added an

Exit For

into the code while restoring the IF and not relying on the equation.

And then the OP posted that the change worked.