Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 352
  • Last Modified:

Please, amend this code

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
ray14
Asked:
ray14
1 Solution
 
bruintjeCommented:
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
 
bill02Commented:
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
 
RHuebnerCommented:
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
 
ray14Author Commented:
Hi RH,

Sorry for being late. Your answer is wonderful.

Ray
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now