Solved

Please, amend this code

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

691 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