Solved

Please, amend this code

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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Suggested Solutions

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
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…

816 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

13 Experts available now in Live!

Get 1:1 Help Now