?
Solved

Please, amend this code

Posted on 2002-03-15
4
Medium Priority
?
338 Views
Last Modified: 2010-05-02
Hi ,

In the following code , I want to check if the number which  I enter in column(0) of DBGrid1 has already entered before in this column or not . column(0) correspond  the field “ID” in my Access Table.


Private Sub DBGrid1_AfterColEdit(ByVal ColIndex As Integer)
Dim SW As Integer
If Me.DBGrid1.Col = 0 Then
SW = Me.DBGrid1.Columns(0).Value
M = Me.Adodc1.Recordset.RecordCount
For i = 1 To M
If SW = Me.Adodc1.Recordset!ID Then
MsgBox "This code is in use ", , "Error"
Exit For
End If
Next
End If
End sub

Whenever I put any NEW or USED number in column(0) , it gives me the message !!!

Please help me by amending  this code to check if the data I enter is in the field “ID” or not.

Thanks



0
Comment
Question by:ray14
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 44

Expert Comment

by:bruintje
ID: 6869795
Hi ray14,
did you check the values in SW and the ID you're comapring with? Guess there's something wrong there if you check it....the recordcount is probably zero looking at that code.....i remember that you've to use a counter first to get the number of records and then you can use that value as M

:O)Bruintje
0
 

Expert Comment

by:bill02
ID: 6869944
first of all you never move through the recordset in your for next loop Me.Adodc1.Recordset.movenext also if your grids datasource is set to adodc1 then when you change rows the id field will get set automatically causing an error
0
 
LVL 1

Accepted Solution

by:
RHuebner earned 200 total points
ID: 6871931
One thing to consider is if you want to prevent duplicate ids by using code then you should really be working the DBGrid1_BeforeColUpdate Event.

The reason you get the message either way is that in the DBGrid1_AfterColEdit event the value you are searching for has already been added.

'------------------------------------------Start Example
Private Sub DBGrid1_BeforeColUpdate(index As Integer, colindex As Integer,oldvalue As Variant,cancel As Integer)

Dim iVal as Integer  'Setup value buffer

  iVal = DBGrid1.Columns(colindex).value 'Get new value
  If IDinUse(ival) Then
    Cancel = True 'Cancel all changes
    MsgBox "This code " & iVal & " is in use ", , "Error"
  End If
End Sub

Private function IDInUse(byval iId as Integer) as Boolean
Dim iCnt as Long 'Count returns
'Use the connection to the database to find out the count of the id.
  iCnt = ADOConnection1.Execute("SELECT COUNT(ID) AS C " & _
                           "FROM ???? " & _
                           "WHERE ID = " & iID & " ")!C
  If iCnt > 0 Then   'Returned a count of 1 or more
    IdInUse = True   'Id is in use
  Else               'Returned a count of 0
    IdInUse = False  'Id is not in use
  End if
End Function
'----------------------------------------------End Example
 I did not have a chance to test this example so you will have to finish the SQL statement and check it out.

Good Luck
RH.
0
 

Author Comment

by:ray14
ID: 6880878
Hi RH,

Sorry for being late. Your answer is wonderful.

Ray
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Suggested Courses
Course of the Month12 days, 23 hours left to enroll

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question