Here is part of the code i created to change a pin number. I need it to check the DB to see if the newPinNumber is already in use and if it is then prompt for a new pin. However, I'm missing something to make it work correctly. Any suggestions?

pinCheck = Mid(txtNewPin.Text, 1, 1)
    If IsAlpha(pinCheck) Then
        txtNewPin.Text = ""
        frmChangePin.Refresh
        txtNewPin.SetFocus
        myMsgBox "Please Enter a 3 digit Numeric Pin!"

    If Len(txtNewPin.Text) = 3 Then
        newPinNumber = txtNewPin.Text
        Main.Adodc1.RecordSource = " ""Time"".""EmpInfo"" where ""EmpInfo"".""Pin""=" & currPinNumber
        Main.Adodc1.Refresh
        
    
    If Then 'ya i know this statement isn't finished
        
        Set conn = New ADODB.Connection
            conn.ConnectionString = "DRIVER={MicroSoft Access Driver (*.MDB)};DBQ=time.mdb;Password=" & PASSWORD_DB
            conn.Open
    
            statement = "UPDATE ""EmpInfo"" SET ""Pin""=" & newPinNumber & _
                " WHERE ""ID""=" & Main.Adodc1.Recordset(0) & ";"
            conn.Execute statement, , adCmdText
            conn.BeginTrans
            conn.CommitTrans
       
            conn.Close
            Main.Adodc1.Refresh
            myMsgBox "Your PIN has been changed."
        Else
        myMsgBox "Please Enter a different 3 digit Pin!"
        txtNewPin.Text = ""
        frmChangePin.Refresh
        txtNewPin.SetFocus
        
        End If
        
        
        
        
       End If
    End If

This message was edited by DrMarten at 2006-6-13 15:33:16

---

Hi,

Is this related to banking? Why worry if PIN exists?

If the pin entered is the same then ask the user to confirm maybe?

If this is related to banking or some other kind of account system then the DATABASE primary key(s) should be;

a) i) Sort code if it is a BANK account followed by
a)ii) Account number.

b) If it's related to a LOGIN of some sort then the PRIMARY KEY
should be the user-name only. Like your user-name on this site.


I'm doing databases with SQL stuff next year at university ( my 2nd year of a full time course ).
So i can't help further right now.

I've already done some ACCESS stuff.

Hope some of my comments above help.



Regards,

Dr M.

The reason being is this is for a timeclock program that i created. When employee's are set up in the DB their pin is set to their last 3 of their SSN. Some people don't like to use that and have asked for a way to change their pins to log in to the system. The way they log into the system is only by their pin(as per the way the boss wants it). So in order to be able to change the pins i need to check the DB and make sure that the pin they want to use is not already someone else's.


: This message was edited by DrMarten at 2006-6-13 15:33:16

---

:
: Hi,
:
: Is this related to banking? Why worry if PIN exists?
:
: If the pin entered is the same then ask the user to confirm maybe?
:
: If this is related to banking or some other kind of account system then the DATABASE primary key(s) should be;
:
: a) i) Sort code if it is a BANK account followed by
: a)ii) Account number.
:
: b) If it's related to a LOGIN of some sort then the PRIMARY KEY
: should be the user-name only. Like your user-name on this site.
:
:
: I'm doing databases with SQL stuff next year at university ( my 2nd year of a full time course ).
: So i can't help further right now.
:
: I've already done some ACCESS stuff.
:
: Hope some of my comments above help.
:
:
:
: Regards,
:
: Dr M.
:
:
:
:
:
:

This message was edited by DrMarten at 2006-6-13 22:21:27

---

: The reason being is this is for a timeclock program that i created. When employee's are set up in the DB their pin is set to their last 3 of their SSN. Some people don't like to use that and have asked for a way to change their pins to log in to the system. The way they log into the system is only by their pin(as per the way the boss wants it). So in order to be able to change the pins i need to check the DB and make sure that the pin they want to use is not already someone else's.

======================================================================

Hi,


If your program references say the employees NI number too then the program you create can cater for more than 000 to 999
or ( 1000 employees ).

You would only need to add say their initial so two persons named John and Jane for example might have logins of>>

J567 and J789 respectively.

By adding just one alphabetic character (ignoring or converting the case to upper or lower this now caters for>>

A..Z (26) multiplied by
000..999 (1000) or 26,000 employees.

Something to think about maybe?


Regards,

Dr M.

Dim pin(999) As Boolean

'Check pin entered is used or not.
If Pin(number)=True then
MsgBox("That pin is already used,sorry.")
Else

'Show available pin numbers.

Endif

Something along those lines maybe?


Regards,

Dr M.

:

: Dim pin(999) As Boolean
: 
: 'Check pin entered is used or not.
: If Pin(number)=True then
: MsgBox("That pin is already used,sorry.")
: Else
: 
: 'Show available pin numbers.
: 
: Endif
: 

:
: Something along those lines maybe?
:
:
: Regards,
:
: Dr M.
:
:
:

*unk* ... 999 array items? :-S

Basically, what he needs is an SQL statement to search for a specific PIN in the New PIN numbers...
I suck with 'SQL+VB', so perhaps you can help here? :P


Best Regards,
Richard

This message was edited by DrMarten at 2006-6-20 0:41:33

---

: *unk* ... 999 array items? :-S
:
: Basically, what he needs is an SQL statement to search for a specific PIN in the New PIN numbers...
: I suck with 'SQL+VB', so perhaps you can help here? :P
:
:
: Best Regards,
: Richard

======================================================================

Hi,

I'm doing SQL at university next year.

However the same idea can be done with VB with
RANDOM FILE ACCESS.

If the user TazzyLynn who posted the original question gets in touch after this i can demonstrate it with a piece of code.


Regards,

Dr M.