Visual Basic for Apps source code required

**ben** · 17th April 2009 14:35

What's up Boys and Girls,

How's it going all? Thought i'd be a bit chirpy today.

I have a piece of VBA code which basically records any record deletions from a table in Microsoft Access. **The code sets an option button to false in the primary table and then records the users name and details in a seperate table.

I am going to include a reset button, just incase somebody accidently deletes record and then the record can be recovered, for this I will allow only a computer administrator or supervisor to do it. So I need a password prompt before the code executes. I have some examples somewhere which I used way back when, while I was doing this sort of stuff but it isn't to hand currently.

Would anybody be able to lend a hand?

Thanks in Advance

Ben

**(Before you start, I use MS Access to model and then later use MySQL to build live applications, it just makes things a bit quicker and prevents a lot of bugs.)

**process** · 17th April 2009 22:49

Been over five years since Ive touched VB but I remember setting a user/pass prompt & assigning keys to ****
a simple google search will bring this up for you

.....

Private Sub Form_Load()
Dim strPass as String
strPass = InputBox("Enter Password")
If strPass <> "Password" Then
MsgBox "Access denied"
End
End If
End Sub

........

Private sub OK_click()
If PSWbox.text = "your password goes here" then
load.form2
Else MsgBox "tell user that the password is wrong"
End If
End Sub

add something like this to whatever naming convention uve used
textbox.passwordchar="*"

**ben** · 18th April 2009 00:45

Yeah, I found it already, i'm using currently

Private Sub Toggle190_Click()
Const NUM_TRIES As Long = 3
Dim bLoggedIn As Boolean
Dim nTries As Long, nTriesAllowed As Long
nTriesAllowed = NUM_TRIES
nTries = 0
Do While nTries < nTriesAllowed And bLoggedIn = False
nTries = nTries + 1
DoCmd.OpenForm "frm_Password", , , , , acDialog
If Forms("frm_Password").m_bCancel = True Then
' user canceled the form Open
bLoggedIn = False
MsgBox "¤Ó¢Í¶Ù¡Â¡àÅÔ¡"
nTries = nTriesAllowed
Else
If Forms("frm_Password").GetPassword() = "password" Then
MsgBox "ÃÒÂ¡ÒÃ¶Ù¡Åº·Ôé§"
bLoggedIn = True
Else
bLoggedIn = False
End If
End If
DoCmd.Close acForm, "frmPassword", acSaveNo
Loop
End Sub

And on the form's code

Public m_bCancel As Boolean
Private Sub cmdCancel_Click()
m_bCancel = True ' set the flag
Me.Visible = False
End Sub
Private Sub cmdcontinue_Click()
Me.Visible = False
End Sub
Public Function GetPassword() As String
GetPassword = Me!txtpassword & vbNullString ' txtPassword is the name of your textbox control
End Function

Public m_bCancel As Boolean
Private Sub cmdCancel_Click()
m_bCancel = True ' set the flag
Me.Visible = False
End Sub
Private Sub cmdcontinue_Click()
Me.Visible = False
End Sub
Public Function GetPassword() As String
GetPassword = Me!txtpassword & vbNullString ' txtPassword is the name of your textbox control
End Function

But it's not fully debugged yet, I have one problem with it, I think it's this part:

Private Sub cmdCancel_Click()
m_bCancel = True ' set the flag
Me.Visible = False
End Sub

Basically, what happens is, if a user keys in the wrong password and then re-enters the new password it reverts back to the procedure for false passwords. So the cancel procedure is being flagged as True. Have to take another look at it tomorrow, need to make a work around that allows it to start again also thanks for your help by the way.

Like to include the author of this work and say thank you again so compliments to John Mishefske, Microsoft MVP 2007 - 2009 from http://www.utteraccess.com

Ben

**process** · 18th April 2009 01:43

The form appears fine....

I would add some display code to show when & where the while loop is entering & exiting

same with if statements..... seems this is where the prob might be

If Forms("frm_Password").m_bCancel = True Then
bLoggedIn = False
MsgBox "¤Ó¢Í¶Ù¡Â¡àÅÔ¡"
nTries = nTriesAllowed

////////////////// might be worth trying an elseif statement here

ElseIf Forms("frm_Password").GetPassword() = "password" Then
MsgBox "ÃÒÂ¡ÒÃ¶Ù¡Åº·Ôé§"
bLoggedIn = True

Else
bLoggedIn = False
End If
End If

**thai33** · 18th April 2009 02:14

I have never really worked with Visual Basic but need to learn. Taking a crash course on c+ and pjp at the moment

**ben** · 18th April 2009 10:23

Originally Posted by thai33

I have never really worked with Visual Basic but need to learn. Taking a crash course on c+ and pjp at the moment

Become a member of UtterAccess Discussion Forums - Microsoft Access Help Center most people there are MVP certified and are really helpful. Good luck to you.

Originally Posted by process

The form appears fine....

I would add some display code to show when & where the while loop is entering & exiting

same with if statements..... seems this is where the prob might be

Thanks Process

[quote=process;1180553]The form appears fine....

Hey Process,

I changed the Else then if to ElseIf and deleted the End If, now it loops correctly with incorrect passwords taken and number of tries but I get the same problem with the cancel button. If I click cancel it flags the m_bCancel as True and you have to reset visual basic for it to restart the request for password procedure, any ideas?

**process** · 19th April 2009 00:51

the code structure needs to be looked at.... the main loop will be broken if cancel is hit because it meets the loop break rules/limits of ntriesalloed & login false.....

so once cancel is hit logged in is set to false & ntries is set to the allowed amount, which will break the do while loop...the code that follows the end of the loop is end sub...

im pretty sure this is where the prob is - once cancel is hit your setting the variables to the grounds that you said should break the loop

either change the variables assignments
or
change the code structure to accommodate the conditions of cancel being hit..to not break the loop prematurely... could maybe put the cancel code outside of the loop

there's a small chance I maybe wrong but I i'm pretty sure this is where your prob lies...

Do While nTries < nTriesAllowed And bLoggedIn = False ///////////////////////// loop code on the grounds u gave

nTries = nTries + 1
DoCmd.OpenForm "frm_Password", , , , , acDialog

/////////////////////////////////////////////////////////////////////
If Forms("frm_Password").m_bCancel = True Then
bLoggedIn = False
MsgBox "¤Ó¢Í¶Ù¡Â¡àÅÔ¡"
nTries = nTriesAllowed
///////////////////////////////////////////////////////////////////// breaks condition rule & exits loop...goes to end sub

ElseIf Forms("frm_Password").GetPassword() = "password" Then
MsgBox "ÃÒÂ¡ÒÃ¶Ù¡Åº·Ôé§"
bLoggedIn = True

Else
bLoggedIn = False
End If

DoCmd.Close acForm, "frmPassword", acSaveNo

Loop//////////////////////////////////////////////////////////////////////////////////////////

End Sub

**ben** · 19th April 2009 12:09

Feck, i think i'm going to focus on normalisation and SQL queries today. Not in the right frame of mind to change this. It works, just not how I want it to work and it needs improvement. I could add something that tells the user how many tries they have remaining to make it idiot proof.

**Topper** · 19th April 2009 16:02

Not trying to sound mean, but you know how to normalize a query, but don't know how to make a password dialog form and can't figure out how to count how many times a user fucked up on the password?

As I recall, there's a section on doing this within the "Help" that comes with VB. Push F1 to access the help, type in "password dialog" and you'll be off to the races.

**process** · 19th April 2009 17:32

just take out the ntries= ntriesallowed

If Forms("frm_Password").m_bCancel = True Then
bLoggedIn = False
MsgBox "¤Ó¢Í¶Ù¡Â¡àÅÔ¡"

it won't register a cancel hit as a password try so the loop will continue

**ben** · 19th April 2009 17:41

Originally Posted by Topper

Not trying to sound mean, but you know how to normalize a query, but don't know how to make a password dialog form and can't figure out how to count how many times a user fucked up on the password?

As I recall, there's a section on doing this within the "Help" that comes with VB. Push F1 to access the help, type in "password dialog" and you'll be off to the races.

You don't sound mean mate, just a little facetious. I've been out of this for sometime, it's not like it's my job and I was referring to database normalisation, relationships within the database and what not.

The code allows 3 password attempts before it makes the password dialog box invisible to the user. There's no problem there, the problem is that when you hit the cancel button it sets a flag which means you have to close the database or restart the debugger in order to be able to provide a correct password on a seperate occassion. I am taking the design of this code from a user perspective. It's not ideal that the password box remains open all the time, this is why this code doesn't work as expected.

Will have to refine that code later, bit busy getting the rest of this stuff finished.

Anyway, thanks for your input, I checked help and there was no information there but will get back to it later.

Originally Posted by process

just take out the ntries= ntriesallowed

If Forms("frm_Password").m_bCancel = True Then
bLoggedIn = False
MsgBox "¤Ó¢Í¶Ù¡Â¡àÅÔ¡"

it won't register a cancel hit as a password try so the loop will continue

Will give it a crack Process, thanks again for your help, appreciated.