Find the value

[Syed Haider Ali]

Dear Freinds,

When we append the data in database. How we can find that if th
particular data is already exist. then msgbox "xyz", otherwise appen
the data

Thanks

SYED HAIDER AL

 

[Tom Ogilvy]

Assume the key ID field is in Column A of the worksheet Data

Dim rng as Range
With Worksheets("Data")
set rng = .Range(.Range("A2"), .Range("A2").End(xldown))
End With
res = Application.Match(KeyIDValueofNewRecord, _
rng,0)
if iserror(res) then
' no existing ID with this value, append record
Else
' Duplicate ID
End if

 

[Guest]

I assume you are inserting data into an access,sql, or oracle
database....without knowing which one i'm not sure what syntax to explain
this in...but here it goes.

Wrap the insert command in an IF statement. the criteria should first do a:

dim myCount as Integer
Select Count(*) from myTable Where myFiled = myCriteria

set myCount = to the resultSet from the previous select command.

IF myCount = 0 then Run Insert '0 means no record found meeting your
criteria.
else
msgbox("Already exist")
end if

exit sub

 

[Syed Haider Ali]

Dear Tom Ogilvy,

i have the following codes but when the data is already exit, it again
append whether it is a new record or not.

Private Sub cmdAdd_Click()
Dim ws As Worksheet
Dim irow As Long
Dim rng As Range

Set ws = Worksheets("code")
With ws
Set rng = .Range(.Range("a2"), .Range("a2").End(xlDown))
End With
res = Application.Match(keyIDValueofnewrecord, _
rng, 0)

If IsError(res) Then

irow = ws.Cells(Rows.Count, 1) _
..End(xlUp).Offset(1, 0).Row

If Me.TextBox1.Value = "" Then
Me.TextBox1.SetFocus
MsgBox "Please Enter the Code"
Exit Sub
End If


ws.Cells(irow, 1).Value = Me.TextBox1.Value
ws.Cells(irow, 2).Value = Me.TextBox2.Value
ws.Cells(irow, 3).Value = Me.ComboBox1.Value
ws.Cells(irow, 4).Value = Me.TextBox3.Value



Me.TextBox1.Value = ""
Me.TextBox2.Value = ""
Me.ComboBox1.Value = ""
Me.TextBox3.Value = ""

Else

MsgBox "Data is already entered"

End If
End Sub

Where i am wrong. Please help me

SYED HAIDER ALI

 

[Dave Peterson]

If the record already exists, then just give a message?

This compiled for me--but I didn't test it. I didn't know where
"keyIDValueofnewrecord" comes from.

Option Explicit
Private Sub cmdAdd_Click()

Dim ws As Worksheet
Dim iRow As Long
Dim rng As Range
Dim res As Variant

If Me.TextBox1.Value = "" Then
Me.TextBox1.SetFocus
MsgBox "Please Enter the Code"
Exit Sub
End If

Set ws = Worksheets("code")

With ws
Set rng = .Range(.Range("a2"), .Range("a2").End(xlDown))
End With

res = Application.Match(keyIDValueofnewrecord, rng, 0)

If IsError(res) Then
iRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
ws.Cells(iRow, 1).Value = Me.TextBox1.Value
ws.Cells(iRow, 2).Value = Me.TextBox2.Value
ws.Cells(iRow, 3).Value = Me.ComboBox1.Value
ws.Cells(iRow, 4).Value = Me.TextBox3.Value

Me.TextBox1.Value = ""
Me.TextBox2.Value = ""
Me.ComboBox1.Value = ""
Me.TextBox3.Value = ""

Else

MsgBox "Data is already entered"

End If
End Sub