category password

[giridher2000]

I have earlier posted this problem, but I have not received requisite response

In my application I wanted to assigned user specific job such EDIT, DELETE and ADD.

Following users:-

1= admin (only one person)
this will ADD, DELETE, EDIT records

2= manager (only one person)
this will only ADD & EDIT records (DELETE button will be disabled)

3= chemist (there are 8 persons)
this will only ADD records (Delete/Edit button will be disabled)

I have written following code to add username and password for three different user

Private Sub ommand1_click()
Set rst= new ADODB.recordset
Rst.open “mypass”,conn,……………………..
With rst
If combo1.text= 1 and .Recordcount < 1 then
.Addnew
!jobttile=combo1.text
!username=text1.text
!password=text2.text
.update
.requery
else
msgbox “ U cannot add new record”,vbinformation
exit sub
end if


If combo1.text= 2 and .Recordcount < 1 then
.Addnew
!jobttile=combo1.text
!username=text1.text
!password=text2.text
.update
.requery
else
msgbox “ U cannot add new record”,vbinformation
exit sub
end if


If combo1.text= 3 and .Recordcount < 8 then
.Addnew
!jobttile=combo1.text
!username=text1.text
!password=text2.text
.update
.requery
else
msgbox “ U cannot add new record”,vbinformation
exit sub
end if

But my problem is when I add one record for 1 =admin it is added but when i want to add second record for 2 = manager it is not added instead giving message “u cannot add new records”.

How to sort out this problem

[sagarpassion]

can u post ur query plz???
mypass =??

[amrita]

Do not use rst.recordcount. Instaed have a counter value and increment after updating the recordest.and check this counter value for further adding records

[sagarpassion]

he is updating after clicking the button and every time he is passing the query so recordcount should show the same. how it will affect for manager or admin??

[giridher2000]

here is my query:-

Rst.open “mypass”,conn,adOpenkeyset,adLockOptimistic

my problem is i want to assigned different username and password based on the job specification

suppose
1) 1=admin there should be one record in my "mypass" table
if record exist it should take new record against admin i.e.1

2) 2=manager there should be one record in my "mypass" table
if record exist it should take new record against admin i.e.2

3) 3=chemist ther should be eight record in my "mypass"table
if there are 8 records exist it should take new record against admin
i.e.3

i am resctricting admin,manager and chemist username and password in my table.

so that after login i will disable my button using category code like 1=admin, 2=manager and 3=chemist like

rst.open "select * from mypass where code =1 and username= '"&txtpassword"' and password ='"&txtpassword&"'", conn, adOpenKeyset, adLockOptimistic
with rst
if .recorcount > 0 then
cmddelete.enabled= true
cmdadd.enabled=true
cmdedit.enabled=true
end if

like this

[amrita]

The RecordCount returns the number of records that Visual Basic currently knows about. To ensure the count is correct, you need to move through all of the records.

rs.MoveLast
iCount = rs.RecordCount

RecordCount will only give a proper count if we have used the adOpenKeyset or the adOpenStatic enums in the Open method of the RecordSet object

[sagarpassion]

I do agree...
hmmmmmmmmm....
I want ur SQL query

[giridher2000]

I think I have not explained my problem properly

I am once again explaining my problem
suppose there are three different user in my case

code =jobtitle (assumption)
1= admin
2=manager
3=chemist

I have design following table structure:-

code username password
1 xxxxx xxxxx
2 xxxxx xxxx
3 xxxx xxxx

Then i have taken two text boxes on form one for username , second for password and one combo box for selecting code i.e. 1= admin, 2=manager and 3=chemist (combo box will show number to select)

Now I want one admin, one manager and eight chemists. Therefore there will be one record for admin, one record for manager and eight records for chemists in my "mypass" table.

If record against code 1 i.e. admin is exist it should not take/add new records because we want one admin, if record against code 2 i.e. manger is exist it should not take/add new records because we want one manager and if there are eight records against code 3 i.e. chemist it should not take new record because we want only eight(8) chemist.

For this I have written following code

Private Sub ommand1_click()
Set rst= new ADODB.recordset
Rst.open “mypass”,conn,adOpenkeyset,adLockOptimistic
With rst
If combo1.text= 1 and .Recordcount < 1 then --- this is for admin
.Addnew
!jobttile=combo1.text
!username=text1.text
!password=text2.text
.update
.requery
else
msgbox “ U cannot add new record”,vbinformation
exit sub
end if


If combo1.text= 2 and .Recordcount < 1 then ---- this is for manager
.Addnew
!jobttile=combo1.text
!username=text1.text
!password=text2.text
.update
.requery
else
msgbox “ U cannot add new record”,vbinformation
exit sub
end if


If combo1.text= 3 and .Recordcount < 8 then ---- this is for chemist
.Addnew
!jobttile=combo1.text
!username=text1.text
!password=text2.text
.update
.requery
else
msgbox “ U cannot add new record”,vbinformation
exit sub
end if
rst.close
End with
End sub


But my problem is when I add record selecting code 1 i.e. admin it is get added. But when I wan to add new record selecting code 2 i.e. manager it is showing message “ u cannot add new record”, and same for code 3

How to solve this problem

[amrita]

Open the 3 recordsets
1.Rst.open “Select * From mypass Where Code =1”,conn,adOpenkeyset,adLockOptimistic

and check if record exists then leave else add.

2.Similarly Check for code = 2

[giridher2000]

how i can check

rst.open "select * from mypass where code=1....

becasue code filed will add after adding/saving record

[amrita]

For the First time rst.EOF() = TRUE. Add the Admin.Next time it will be false so display message

[sagarpassion]

try this wid public connection & adOpenkeyset lock..

Set rs = New ADODB.Recordset
strCategory = combo1.Text
strUser = text1.Text
strPass = text2.Text
Select Case Cat(strCategory)
    Case "1"
        strQuery = "select * from mypass where code = 1"
        If rs.Recordcount < "1" Then
            Call addNew(strCategory, strUser, strPass)
        Else
            MsgBox "not authorised"
        End If
    Case "2"
        strQuery = "select * from mypass where code = 2"
        If rs.Recordcount < "1" Then
            Call addNew(strCategory, strUser, strPass)
        Else
            MsgBox "not authorised"
        End If
    Case "3"
        strQuery = "select * from mypass where code = 3"
        If rs.Recordcount < "8" Then
            Call addNew(strCategory, strUser, strPass)
        Else
            MsgBox "not authorised"
        End If
End Select
If rs.state = 1 Then rs.Close
If con.state = 1 Then cs.Close
End Sub

Sub addNew(category, user, pass)

Dim strUpdateCategory As String
strUpdateCategory = "update mypass set jobttile = '" & category & "' and username = '" & user & "' and password = '" & pass & "'"
rs.open strUpdateCategory, conn, adOpenDynamic, adLockOptimistic
End Sub

---------------------------------------------
Still I am confuse wid your recordcount logic.

[giridher2000]

I have tried and written following code, it is working fine but is this ok.


Private Sub Comman1_Click()
Set rst =new ADODB.Recordset
Rst.open “mypass”,conn, adOpenkeyset, adLockoptimistic
With rst
If combo1.text =1 and .recordcount < 1 then
.addnew
!jobtitle=combo1.text
!username=text1.text
!password=text2.text
.update
.requery
msgbox “Record added”,vbinformation
text1.text=””
text2.text=””
combo1.text=””
text1.setfocus
elseif combo1.text =2 and .recordcount < 2 then
.addnew
!jobtitle=combo1.text
!username=text1.text
!password=text2.text
.update
.requery
msgbox “Record added”,vbinformation
text1.text=””
text2.text=””
combo1.text=””
text1.setfocus
elseif combo1.text =3 and .recordcount < 10 then
.addnew
!jobtitle=combo1.text
!username=text1.text
!password=text2.text
.update
.requery
msgbox “Record added”,vbinformation
text1.text=””
text2.text=””
combo1.text=””
text1.setfocus
else
msgbox “ u cannot new record, record against the jobtitle is already exist”,vbinformation
text1.text=””
text2.text=””
combo1.text=””
text1.setfocus
end if
end with
rst.close
end sub

[amrita]

Have U tried with adding first a manager and then admin.Or first 3 chemist and then admin or manager ?????

[giridher2000]

yes, good thinking it is not working........
when i add first three record against jobtitle =3 and then want to add record against jobtitle=1

please help...

[amrita]

Follow the steps as I had said. Open queries with where conditions ( 3 cases).

[sagarpassion]

confuseeeeee!!!!!!!!!! amrita wat he want to do & wat I am doing for him???

[giridher2000]

What u have said is below:--

Open the 3 recordsets
Rst.open “Select * From mypass Where Code =1,conn,adOpenkeyset,adLockOptimistic

and check if record exists then leave else add.

2.Similarly Check for code = 2

but my problem is mypass table is initially empty, i am assuming that code field for below users
1= admin
2=manager
3=chemist

this codes 1,2,3 will be added in table while adding username and password which are not set initially or while desining table, so it will compareor check what u have said in the above program code

[amrita]

I have also said that (lets say for admin)
"For the First time Check rst.EOF() = TRUE.
Add the Admin.
Next time it will be false so display message"

[giridher2000]

armita

i am totally confused wiil u please post code for me.... that will help me lot

thanks

[amrita]

armita

i am totally confused wiil u please post code for me.... that will help me lot

thanks

Ok confused man
Try this!

If combo1.Text = 1 Then
    rst.open "Select * From mypass Where jobtitle =1 ", conn, adOpenkeyset, adLockoptimistic
    If rst.EOF() = True Then
       With rst
        .addnew
        !jobtitle = combo1.Text
        !username = text1.Text
        !password = text2.Text
        .Update
    Else
       MsgBox "Record added", vbInformation
    End If
End If

[giridher2000]

thanks amrita... i will try today and come back to u tommarrow....

thanks once again....and hope problem will solve this time

[giridher2000]

amrita i tried ur program code... because i could not wait till tommarrow... but it is giving error at following point

If Combo1.Text = 1 Then
    rst.Open "select * from mypass where code =1", conn, adOpenKeyset, adLockOptimistic
    If rst.EOF() = True Then
        With rst
        .AddNew
        !code = Combo1.Text
        !UserName = Text1.Text
        !Password = Text2.Text
        .Update
        .Requery
    [COLOR="Red"]Else[/COLOR]  ----- here saying else without if   
   MsgBox "record added", vbInformation
    End If
End If
End With

[Hack]

Moved the End With below .Requery

[amrita]

If Combo1.Text = 1 Then
    rst.Open "select * from mypass where code =1", conn, adOpenKeyset, adLockOptimistic
    If rst.EOF() = True Then
        With rst
            .AddNew
            !code = Combo1.Text
            !UserName = Text1.Text
            !Password = Text2.Text
            .Update
        End With
    Else
            MsgBox "record added", vbInformation
    End If
End If

[giridher2000]

thanks amrita... i tried ur code but still it is not working what i wanted

now when i added records for code=3 i.e. chemist and again for code= 1 admin or again for code =2 manager it is saying record added.

What i want is
my admin is only one user so there will be one record in table
my manager is one one user so there will be one record in table
my chemist are eight users so there will be eight records in table

so when i added record for code=1 admin i.e. username and password, it should not new record against code same code becasue we want only one admin. Similarly for manager, however for code=3 i.e. chemist it will take eight records ie.e user name and password. hence there will be 10 records in my table not more than that

1 for admin
1 for manager
8 for chemist
-----
10
---

[amrita]

Have u just written the above code ?? only for admin and for other cases ??
Show me ur total code .

[sagarpassion]

b4 adding any record into ur table u can decide is that record addable or not. so that u can write single funcion for add record.

That will help u to not to rewrite the same lines again and again.
i have already given u the example. plz check the same.

try to write ur code in functions.