Solved

Please, amend this code

Posted on 2002-03-15
4
305 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
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 50 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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
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.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

760 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now