Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find the value
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 -- Syed Haider Al ----------------------------------------------------------------------- Syed Haider Ali's Profile: http://www.excelforum.com/member.php...fo&userid=2199 View this thread: http://www.excelforum.com/showthread.php?threadid=39126 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find the value
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 -- Regards, Tom Ogilvy "Syed Haider Ali" <Syed.Haider.Ali.1sxmqh_1122642405.2385@excelfor um-nospam.com wrote in message news:Syed.Haider.Ali.1sxmqh_1122642405.2385@excelf orum-nospam.com... Dear Freinds, When we append the data in database. How we can find that if the particular data is already exist. then msgbox "xyz", otherwise append the data Thanks SYED HAIDER ALI -- Syed Haider Ali ------------------------------------------------------------------------ Syed Haider Ali's Profile: http://www.excelforum.com/member.php...o&userid=21994 View this thread: http://www.excelforum.com/showthread...hreadid=391263 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find the value
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" wrote: Dear Freinds, When we append the data in database. How we can find that if the particular data is already exist. then msgbox "xyz", otherwise append the data Thanks SYED HAIDER ALI -- Syed Haider Ali ------------------------------------------------------------------------ Syed Haider Ali's Profile: http://www.excelforum.com/member.php...o&userid=21994 View this thread: http://www.excelforum.com/showthread...hreadid=391263 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find the value
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 -- Syed Haider Ali ------------------------------------------------------------------------ Syed Haider Ali's Profile: http://www.excelforum.com/member.php...o&userid=21994 View this thread: http://www.excelforum.com/showthread...hreadid=391263 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find the value
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 Syed Haider Ali wrote: 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 -- Syed Haider Ali ------------------------------------------------------------------------ Syed Haider Ali's Profile: http://www.excelforum.com/member.php...o&userid=21994 View this thread: http://www.excelforum.com/showthread...hreadid=391263 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find First Non blank cell than find column header and return that value | Excel Worksheet Functions | |||
Despite data existing in Excel 2002 spreadsheet Find doesn't find | Excel Discussion (Misc queries) | |||
find and delete duplicate entries in two columns or find and prin. | Excel Programming | |||
find and delete text, find a 10-digit number and put it in a textbox | Excel Programming | |||
backwards find function to find character in a string of text | Excel Programming |