Help with Login scree developed in visual basic 6.0

[wrichardson530]

Hi all. I'm having difficulty making a log-in screen. i am using an adoc contorl called adoClerk, which contains only two fields and ClerkId and Password. The ClerkId is a autonum and password is text. I Have two text boxes that need to be read from this screen txtClerId and txtPassowrd. I have a button called Login. This button is suppposed to have the code eneterd in that checks and opes the record source, but i am having an error with the password. If i can get some advice on how to make this simpler or help with debugging. Either one is good. Here is my code I have so far.

Private Sub cmdLogIn_Click()

' finds the matching clerk Id
Dim tempRecordSet As Recordset
Set tempRecordSet = adoClerk.Recordset("select * from Clerk where UCase(trim(ClerkId)) = '" & UCase(Trim(txtClerkId)) & "'")

'retrieve the Password field from the Clerk table if the ClerkID matches the txtClerkId Field
If tempRecordSet.RecordCount <> 0 Then
Password = UCase(Trim(tempRecordSet("Password")))
End If

'close the recordset and release the recordset object variable
tempRecordSet.Close
Set tempRecordSet = Nothing
'check the password
If Password <> UCase(Trim(txtPassword)) Then
MsgBox "Incorrect password", vbExclamation
txtPassword.SetFocus
Else
'passwords match, allow the user to procede to the desk top
Unload Me 'unloads the form
FrmMain.Show vbModal
End If

End Sub

[ROBBOHORT]

In relatio to this code:

'passwords match, allow the user to procede to the desk top
Unload Me 'unloads the form
FrmMain.Show vbModal

I hope you are not trying to show the desk top "FrmMain" as vbModal to the form you have just unloaded ?

[choudhuryshouvi]

i am having an error with the password.

what problem are you facing with your password?
what is the error?

plz explain...

[wrichardson530]

I've encountered another problem with this screen. I forgot after making all the ado controls and screens, to link the two text boxes to the ado control with the login screen. After I done that i'm getting a run time error "Run-time Error'3265' Item cannot be found in the collection to the requested name or ordinal" Its corresponding with this code

Set tempRecordSet = AdoClerk.Recordset("select * from Clerk where UCase(trim(ClerkId)) = '" & UCase(Trim(txtClerkId)) & "'")

Am I using the wrong code for use with a ado control???

[choudhuryshouvi]

i'm getting a run time error "Run-time Error'3265' Item cannot be found in the collection to the requested name or ordinal" Its corresponding with this code

Set tempRecordSet = AdoClerk.Recordset("select * from Clerk where UCase(trim(ClerkId)) = '" & UCase(Trim(txtClerkId)) & "'")

your sql query is invalid. in your query the usage of "UCase" and "Trim" functions are invalid. They are vb6 library functions. so they won't be recognized by your ado data control and cannot be converted into sql commands. rectify the statement like this,

select * from Clerk where upper(ClerkId)='" & ucase(trim(txtClerkId.text)) & "'"

one more thing, which database you are using? try the above syntax if you are using sql server or oracle database. but if you are having an ms-access database the above syntax won't work because there is no upper() in access library. access doesn't care whether you type uppercase or lowercase characters. in that case use like this,

select * from Clerk where ClerkId='" & trim(txtClerkId.text) & "'"

Am I using the wrong code for use with a ado control???

well, you can have two answers in this context. if you use ado data control then there is nothing wrong with your code. but it is always recommended that you donot use any data bound control. instead of it use ADODB (Microsoft ActiveX Data Objects Library) objects and methods to communicate with your database. it becomes more easy and secure and gives you lots of advanced functionality. now the decision completely depends on you.

hope this helps...

regards
Shouvik