search for a existing value in a field before add record, if exists,not to add record

Sir,
kindly see the code my query is below the code:

Private Sub cmdsave_Click()
connectDB
rs.Open "Select*from accreg", db, 3, 3
rs.Open "Select*from accreg where AccNo=" & txtAccNo.Text, db, 3, 3
If txtAccNo.Text <> ListView1.ListItems.Add.SubItems(1) = 0 Then
'for you to check in the entire list you have add a for loop
Dim i%
For i = 1 To ListView1.ListItems.Count
If txtAccNo.Text = ListView1.ListItems(i).SubItems(1) Then
MsgBox "AccNo already exists", vbOKOnly
Else
clearall 'all text boxes
End If
Next
Else
rs.AddNew
rs(1) = txtAccNo
rs(2) = txttitle
rs(3) = txtauthor
rs.Update
End If
Set rs = Nothing
db.Close: Set db = Nothing
MsgBox "Successfully added new record", vbInformation, "SAVE"
txtAccNo = Empty
txttitle = Empty
txtauthor = Empty
txtAccNo.SetFocus
LoadData 'To refresh record
End Sub
Private Sub cmdsearch_Click()
ListView1.ListItems.Clear
Dim list As ListItem
Dim x As Integer
connectDB
rs.Open "Select*from accreg where AccNo=" & txtsearch.Text, db, 3, 3
'rs.Open "Select * from accreg where Title like='" & txtsearch.Text, db, 3, 3
Do Until rs.EOF
Set list = ListView1.ListItems.Add(, , rs(0))
For x = 1 To 3 'number of fields in the date base minus one
list.SubItems(x) = rs(x)
Next x
rs.MoveNext
Loop
Set rs = Nothing
db.Close: Set db = Nothing
End Sub

Private Sub Form_Load()
With ListView1.ColumnHeaders
.Add , , "ID", 0
.Add , , "AccNo", 1000
.Add , , "Title", 3000
.Add , , "Author", 2500
End With
LoadData
End Sub

Sub LoadData()
ListView1.ListItems.Clear
Dim list As ListItem
Dim x As Integer
connectDB
rs.Open "Select*from accreg", db, 3, 3
Do Until rs.EOF
Set list = ListView1.ListItems.Add(, , rs(0))
For x = 1 To 3 'number of fields in the date base minus one
list.SubItems(x) = rs(x)
Next x
rs.MoveNext
Loop
Set rs = Nothing
db.Close: Set db = Nothing
End Sub

in the above code the following code is not working


rs.Open "Select*from accreg where AccNo=" & txtAccNo.Text, db, 3, 3
If txtAccNo.Text <> ListView1.ListItems.Add.SubItems(1) = 0 Then
'for you to check in the entire list you have add a for loop
Dim i%
For i = 1 To ListView1.ListItems.Count
If txtAccNo.Text = ListView1.ListItems(i).SubItems(1) Then
MsgBox "AccNo already exists", vbOKOnly
Else
clearall 'all text boxes
End If
Next
Else

instead of verifying for existing value before add record it is directly adding the value to table of the database.

what is going wrong in code

I'm not sure what you are doing there, a lot of code and your exists statement is in a loop that continues even when a match is found. If you are simply trying to see if the value exists and add the record only if it is not in the database then you should just check to see if it exists

Code:

---

rs.Open "Select AccNo from accreg where AccNo=" & txtAccNo.Text, db, 3, 3
If rs.Recordcount > 0 Then
  msgbox "Record already Exists"
Else
  'code to add the record
End If

---

Sir,
I correct my code as suggested. it is working in verifying the existing value. I submit that the code below is working up to else stage while trying to add a value which is not exists (code to add record) is giving an error like below:

Run time error 3705

"operation is not allowed when the object is open"

the line of code "db.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data source = " & path" become hilighted this line of code, i wrote in a module.

Private Sub cmdsave_Click()
connectDB
rs.Open "Select AccNo from accreg where AccNo=" & txtAccNo.Text, db, 3, 3
If rs.RecordCount > 0 Then
MsgBox "Record already Exists"
clearall
Exit Sub
Else
rs.AddNew
rs(1) = txtAccNo
rs(2) = txttitle
rs(3) = txtauthor
rs.Update
Set rs = Nothing
db.Close: Set db = Nothing
MsgBox "Successfully added new record", vbInformation, "SAVE"
txtAccNo = Empty
txttitle = Empty
txtauthor = Empty
LoadData 'To refresh record
txtAccNo.SetFocus
End If
End Sub

please write the code inside the code tags always . it is much readable.

Code:

---


Private Sub cmdsave_Click()
connectDB
rs.Open "Select*from accreg", db, 3, 3
rs.Open "Select*from accreg where AccNo=" & txtAccNo.Text, db, 3, 3
If txtAccNo.Text <> ListView1.ListItems.Add.SubItems(1) = 0 Then
'for you to check in the entire list you have add a for loop
Dim i%
For i = 1 To ListView1.ListItems.Count
If txtAccNo.Text = ListView1.ListItems(i).SubItems(1) Then
MsgBox "AccNo already exists", vbOKOnly
Else
clearall 'all text boxes
End If
Next
Else
rs.AddNew
rs(1) = txtAccNo
rs(2) = txttitle
rs(3) = txtauthor
rs.Update
End If
Set rs = Nothing
db.Close: Set db = Nothing
MsgBox "Successfully added new record", vbInformation, "SAVE"
txtAccNo = Empty
txttitle = Empty
txtauthor = Empty
txtAccNo.SetFocus
LoadData 'To refresh record
End Sub
Private Sub cmdsearch_Click()
ListView1.ListItems.Clear
Dim list As ListItem
Dim x As Integer
connectDB
rs.Open "Select*from accreg where AccNo=" & txtsearch.Text, db, 3, 3
'rs.Open "Select * from accreg where Title like='" & txtsearch.Text, db, 3, 3
Do Until rs.EOF
Set list = ListView1.ListItems.Add(, , rs(0))
For x = 1 To 3 'number of fields in the date base minus one
list.SubItems(x) = rs(x)
Next x
rs.MoveNext
Loop
Set rs = Nothing
db.Close: Set db = Nothing
End Sub

Private Sub Form_Load()
With ListView1.ColumnHeaders
.Add , , "ID", 0
.Add , , "AccNo", 1000
.Add , , "Title", 3000
.Add , , "Author", 2500
End With
LoadData
End Sub

Sub LoadData()
ListView1.ListItems.Clear
Dim list As ListItem
Dim x As Integer
connectDB
rs.Open "Select*from accreg", db, 3, 3
Do Until rs.EOF
Set list = ListView1.ListItems.Add(, , rs(0))
For x = 1 To 3 'number of fields in the date base minus one
list.SubItems(x) = rs(x)
Next x
rs.MoveNext
Loop
Set rs = Nothing
db.Close: Set db = Nothing
End Sub

in the above code the following code is not working


rs.Open "Select*from accreg where AccNo=" & txtAccNo.Text, db, 3, 3
If txtAccNo.Text <> ListView1.ListItems.Add.SubItems(1) = 0 Then
'for you to check in the entire list you have add a for loop
Dim i%
For i = 1 To ListView1.ListItems.Count
If txtAccNo.Text = ListView1.ListItems(i).SubItems(1) Then
MsgBox "AccNo already exists", vbOKOnly
Else
clearall 'all text boxes
End If
Next
Else

---

Actually firoz it is not any more readable the way you posted it since there is still not indenting at all. The purpose of using code tags is that you can paste or type formatted code and it will retian the indenting to make it readable.

No point in re-posting someones code like that if you are not going to put in the indenting to actually make it readable.

but it is much better than that brother .

DataMiser Sir,
I correct the code as you suggested above. it is working upto else statement and finding the value if exists. but problem arising while adding record. I posted above the details of the error.

in the mean time Firoz Sir gave a valuable suggestion to write the code in taps while post a code.

As i have the new to VB forum, I have no knowledge above tags

Please let me know how post a code in tags. truly i will follow.

kindly see me next post abve to gave your valuable suggestion in the matter

Thanks

deekshitulu

Quote:

---

Originally Posted by firoz.raj

but it is much better than that brother .

---

Not really all that did was put the code in a box it is still not formatted and not easy to read.

Quote:

---

Run time error 3705

"operation is not allowed when the object is open"

the line of code "db.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data source = " & path" become hilighted this line of code, i wrote in a module.

---

That means you are trying to open the connection when it is already open.
You are also not closing your RS object when you are done with it.

You should always close objects when you are done.

to use code tags you simply type
[code] Your code would be here [/code]
Which would give you

Code:

---

Your code would be here

---

or if you go to the advaned window you can use the # button on the toolbar

Code:

---

Private Sub cmdsave_Click()
connectDB
'rs.Open "select*from accreg,db,3,3"
rs.Open "Select AccNo from accreg where AccNo=" & txtAccNo.Text, db, 3, 3
If rs.RecordCount > 0 Then
  MsgBox "Record already Exists"
  clearall
Else
  rs.AddNew
rs(1) = txtAccNo
rs(2) = txttitle
rs(3) = txtauthor
rs.Update
Set rs = Nothing
db.Close: Set db = Nothing
MsgBox "Successfully added new record", vbInformation, "SAVE"
txtAccNo = Empty
txttitle = Empty
txtauthor = Empty
LoadData 'To refresh record
txtAccNo.SetFocus
End If
End Sub

---

DataMiser Sir,
in my last post, suggestion you gave is working properly, c

[code}
rs.AddNew
rs(1) = txtAccNo
rs(2) = txttitle
rs(3) = txtauthor
rs.Update
Set rs = Nothing
db.Close: Set db = Nothing
MsgBox "Successfully added new record", vbInformation, "SAVE"
txtAccNo = Empty
txttitle = Empty
txtauthor = Empty
LoadData 'To refresh record
txtAccNo.SetFocus
End If [/code}

is not working properly

kindly help in tracing the crror in the above date add record code

what do you mean by is not working properly?

You'll have to tell us what it is or is not doing before we can tell what this means.

btw you should close your recordset before you set it to nothing.


Edit: The select statement I used does not select all the fields so you can not add a record to that RS containgin the fields you are trying to add. You would have to add those to the select statement as well if you want to do it that way.

DataMiser Sir,

Table Name: accreg, field names are 1)AccNo.,2)Title, 3)Author

three text boxes are: 1)txtAccNo. (this is box has to be validate for existing value, if value existed, three
text boxes have to be cleared and txtAccNo. has to become in setfocus
condition,
2) txttitle
3) txtauthor

Requirement is if value is not existed, the value in the three txtboxes have to be entered into table accreg

the cmdsave button code is:

Code:

---

Private Sub cmdsave_Click()
connectDB
'rs.Open "select*from accreg,db,3,3"
rs.Open "Select AccNo from accreg where AccNo=" & txtAccNo.Text, db, 3, 3
If rs.RecordCount > 0 Then
  MsgBox "Record already Exists"
  clearall
Else
  rs.AddNew
rs(1) = txtAccNo
rs(2) = txttitle
rs(3) = txtauthor
rs.Update
Set rs = Nothing
db.Close: Set db = Nothing
MsgBox "Successfully added new record", vbInformation, "SAVE"
txtAccNo = Empty
txttitle = Empty
txtauthor = Empty
LoadData 'To refresh record
txtAccNo.SetFocus
End If
End Sub

---

the above code while run mode it perfectly tracing the existing value in the field AccNo of the table accreg
and showing the msgbox: MsgBox "Record already Exists"

The Problems raises when ever entered the not existing value in txtAccNo. my requirement is when ever I enter the not existing value in txtAccNo. which is not in the field of AccNo. of the table accreg,

at this stage code has to come out from the if ,then else condition mode and have to go to rs.AddNew mode and the values entered in the three txtboxs have to be entered in to table accreg in their respective fields like Title,Author

as the rs.Addnew is not working not adding data to table

Quote:

---

Originally Posted by deekshitulu

Code:

---

Private Sub cmdsave_Click()
connectDB
'rs.Open "select*from accreg,db,3,3"
rs.Open "Select AccNo from accreg where AccNo=" & txtAccNo.Text, db, 3, 3
If rs.RecordCount > 0 Then
  MsgBox "Record already Exists"
  clearall
Else
  rs.AddNew
rs(1) = txtAccNo
rs(2) = txttitle
rs(3) = txtauthor
rs.Update
Set rs = Nothing
db.Close: Set db = Nothing
MsgBox "Successfully added new record", vbInformation, "SAVE"
txtAccNo = Empty
txttitle = Empty
txtauthor = Empty
LoadData 'To refresh record
txtAccNo.SetFocus
End If
End Sub

---

---

the code error coz u not closing the connection when the data is exist..
close the connection when the data is exist and may be it can solve ur problem..

can you try the following ?

Code:

---


rs.AddNew
rs(1) = txtAccNo
rs(2) = txttitle
rs(3) = txtauthor
rs.Update
rs.close
Set rs = Nothing
db.Close
Set db = Nothing
MsgBox "Successfully added new record", vbInformation, "SAVE"
txtAccNo = Empty
txttitle = Empty
txtauthor = Empty
LoadData 'To refresh record
txtAccNo.SetFocus
End If

---

You need to add the other fields to your select statement

Code:

---

rs.Open "Select AccNo, Title, Author from accreg where AccNo=" & txtAccNo.Text, db, 3, 3
If rs.RecordCount > 0 Then

---

Also as I have said before always close the objects when you are done with them.
the connection, the recordset and any other such objects and make sure that they close regaurdless of the IF condition.

and please make sure rs.cursorlocation =aduseclient .otherwise If rs.RecordCount > 0 Then is useless .it will not returns no of rows .tried to print ?rs.recordcount and definately it will returns -1 .if you will not use cursor location =aduseclient .

Code:

---

rs.cursorlocation=aduseclient
rs.Open "Select AccNo, Title, Author from accreg where AccNo=" & txtAccNo.Text, db, 3, 3
If rs.RecordCount > 0 Then

---

other alternate way of the above code .

Code:

---

rs.Open "Select AccNo, Title, Author from accreg where AccNo=" & txtAccNo.Text, db, 3, 3
If (not rs.eof and rs.bof) then
 'write the code because you got records
End if

---

Thanks to every one who respond to this thread.

I made the following correction to my cmdsave button code:

Code:

---

If rs.RecordCount > 0 Then
  MsgBox "Record already Exists"
  clearall
  rs.Close
  db.Close: Set db = Nothing

---

now it is working as i expecting.

Later I add a textsearch box to find the title stating with a particular letter ie., c or d ,a like that:

while in execution an error occrued: that is

complile error:

Syntax error

the line of code which is prone to error is :

Code:

---

rs.Open "Select*from accreg where Title like'" &txtsearch&"%'", db, 3, 3

---

&"%'" this part become highlight

Kindly see the above line of code and suggest me what syntax error is:

Thanks in advance.

I think Access uses the * rather than the % sign

DataMiser Sir,

I used the * in place of % as u suggested. when in run mode it is not giving any error, but the problem is while in run mode Title field is not indexed with the letter i entered in txtsearch.text box. simply it clear the ListView1 control and not showing any records, it become empty. I post here the full code of txtsearch_change:

Code:

---

Private Sub txtsearch_Change()
ListView1.ListItems.Clear
Dim list As ListItem
Dim x As Integer
connectDB
rs.Open "Select*from accreg where Title like'" & txtsearch.Text & "*'", db, 3, 3
Do Until rs.EOF
Set list = ListView1.ListItems.add(, , rs(0))
For x = 1 To 3 'number of fields in the date base minus one
list.SubItems(x) = rs(x)
Next x
rs.MoveNext
Loop
Set rs = Nothing
db.Close: Set db = Nothing
End Sub

---

Thanks for you prompt response.

in addition to my above post I also attaching a video clip from which every one know what i am expection from code post previously.http://www.youtube.com/results?searc...utorial+Part+4.