Solved

Visual basic 6 .Seek NoMatch usign DAO Recordset

Posted on 2008-10-30
28
1,060 Views
Last Modified: 2011-10-19
I have the following situation:
I'm reading a string "229050708073528073621000041597" and storing it as primary Key field1
but now I need to break the string into four fields
                           field1        ,field2   ,field3   ,field4
Original String "229050708,073528,073621,000041597"
                         field1:  229050708
                         field2:  073528
                         field3:  073621
                         field4:  000041597
Before I was storing the whole string and searching through it, to know if it already exists.
Using  DAO.Recordset
            RecordsetA.index = "field1"
            RecordsetA.seek =, string
            if (RecordsetA.NoMatch)  then
               RecordsetA.AddNew
               RecordsetA.field1 = string
               RecordsetA.Update
            End if
               
It was working fine, but my question is since I can have several strings starting the same on field1 sample

                     field1        ,field2   ,field3   ,field4                                           field1        ,field2   ,field3   ,field4  
String1      "229050708,073528,073621,000041597"           String2      "229050708,051558,052032,000041597"
       field1:  229050708                                                                field1:  229050708                              
                          field2:  073528                                                                      field2:  051558
                                       field3:  073621                                                                      field3:  052032
                                                    field4:  000041597                                                               field4:  000041597

How can I still store and search by field1 and make sure it doesn't exist already
code snipet expected
Or any other suggestions are greatly appreciated


0
Comment
Question by:ranhell
  • 12
  • 11
  • 5
28 Comments
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 22847006
A index can comprise more than one field.

If this is Access, select the four fields in the table design and click on the primary key button.
0
 
LVL 11

Expert Comment

by:Antagony1960
ID: 22848779
 In case you can't easily add the index Graham suggests (which would be best), you could change the recordset's selection query to concatenate the four fields into one and then use FindFirst rather than Seek.  

'Define the query  
sSQL = "SELECT field1 & ',' & field2 & ',' & field3 & ',' & field4 AS ID FROM Table"  
Set RecordsetA = DB.OpenRecordset(sSQL)  

'Look for existing record
With RecordsetA  
    .MoveFirst  
    .FindFirst "ID = " & string  
    If .NoMatch Then  
        sArray = Split(string, ",")  
        .AddNew  
        !field1 = sArray(0)  
        !field2 = sArray(1)  
        !field3 = sArray(2)  
        !field4 = sArray(3)  
        .Update  
    End If  
End With  <!--[if gte mso 9]>      <![endif]--><!--[if gte mso 9]>   Normal  0          false  false  false    EN-GB  X-NONE  X-NONE                                                                          <![endif]--><!--[if gte mso 9]>                                                                                                                                                                                                                                                                                    <![endif]--><!--[if gte mso 10]><![endif]-->
0
 
LVL 11

Expert Comment

by:Antagony1960
ID: 22848798
Woa... I don't know what happened there ^

Here's the post again, hopefully without all the appended garbage:

In case you can't easily add the index Graham suggests (which would be best), you could change the recordset's selection query to concatenate the four fields into one and then use FindFirst rather than Seek.  

'Define the query  
sSQL = "SELECT field1 & ',' & field2 & ',' & field3 & ',' & field4 AS ID FROM Table"  
Set RecordsetA = DB.OpenRecordset(sSQL)  

'Look for existing record
With RecordsetA  
   .MoveFirst  
   .FindFirst "ID = " & string  
   If .NoMatch Then  
       sArray = Split(string, ",")  
       .AddNew  
       !field1 = sArray(0)  
       !field2 = sArray(1)  
       !field3 = sArray(2)  
       !field4 = sArray(3)  
       .Update  
   End If  
End With
0
 
LVL 10

Author Comment

by:ranhell
ID: 22851983
GrahamSkan

Yes I'm using Access, could you explain a little further what do I need to do, regarding to the DB table design.
Coz Access only gives me chance to select 1 field as primary key.
I can index the four fields but only 1 primary key
0
 
LVL 10

Author Comment

by:ranhell
ID: 22852056
Sorry my mistake, Access does give a chance to select several fields as primary keys.
Now by making these 4 fields as primary keys does that means that they are already concatenated, what I mean is
All 4 fields can repeat the same string but all togheter they will not, so how would I handle the table desing regarding to Indexing, should i make them all
Indexed : YES (DUPLICATES OK) or what would be the best approach

thanks in advance for UR help

0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 22852204
If the four fields are all a member of a single key , and if that key is specified as unique (i.e. no duplicates), then to break the no-duplicates rule, all four would have to be equal in two separate records. It would be legal for individual fields to be the same, provided at least one of them is different in the two records, so yes, it would act as if you were using the old long string in that regard.

If you wanted to also use any or all of the fields as an index, you could specify "YES (DUPLICATES OK)" for each particular field at the same time as having the single combined field as the unique key.
0
 
LVL 10

Author Comment

by:ranhell
ID: 22852513
OK Now how would I use:
Using  DAO.Recordset look for the 4 fields , is it posible ..?  I mean.
            RecordsetA.index = "field1,field2,field3,field4"
            RecordsetA.seek =, string
            if (RecordsetA.NoMatch)  then
               RecordsetA.AddNew
               RecordsetA.field1 = left( string,9)
               RecordsetA.field2 = Left(Right(strLine, 21), 6)
               RecordsetA.field3 = Left(Right(strLine, 15), 6)  
               RecordsetA.field4 = left( string,9)
               RecordsetA.Update
            End if
0
 
LVL 10

Author Comment

by:ranhell
ID: 22852548
I forgot to mention that if the string is the same twice one is handle as duplicate, so I need to look for all fields to know if it has been entered before.
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 22852566
Not quite. When you create an index, you give it a name. The default name is the name of the first field in the index, so you probably haven't noticed, but you can edit it to anything that you want.
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 22852603
My last comment was to your penultimate one. I think you last question is covered.
Without reminding myself of DAO syntax, the code should be something like this

            RecordsetA.index = "MyBigIndex"
            RecordsetA.seek =, string
            if (RecordsetA.NoMatch)  then
               RecordsetA.AddNew
               RecordsetA.field1 = left( string,9)
               RecordsetA.field2 = Left(Right(strLine, 21), 6)
               RecordsetA.field3 = Left(Right(strLine, 15), 6)  
               RecordsetA.field4 = left( string,9)
               RecordsetA.Update
            End if

0
 
LVL 11

Expert Comment

by:Antagony1960
ID: 22852610
I think what ranhell is asking is how to search for duplicates. Which, assuming the four fields are now the primary key, would be:

            RecordsetA.Index = "PrimarKey"
            RecordsetA.Seek "=", Number1, Number2, Number3, Number4

BTW, I'm not touting for points--Graham has earned them--just trying to help out.
0
 
LVL 10

Author Comment

by:ranhell
ID: 22852666
Didn't quite understand regarding to the "MyBigIndex"
How would I refer the 4 fields into "MyBigIndex" sort of speak
I mean How in sintax code or in the Database do
MyBigIndex = "field1,field2,field3,field4"
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 22853464
Actually, you have to list the parts with the seek method:

RecordSetA.Seek "=", Left(strLine, 9), Left(Right(strLine, 21), 6),...
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 22853505
Incidentlly, you might find the Mid() or Mid$() function easier to use than the Left(Right(... construct. I know that it is easier for me to understand.
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 10

Author Comment

by:ranhell
ID: 22854191
Ok I get the part of the RecordSetA.Seek "=", Left(strLine, 9), Left(Right(strLine, 21), 6),...
Excuse me, one more time but I still don't get the part of using the

RecordserA.Index = "MyBigIndex" 'How do I do this......???

RecordSetA.Seek "=", Left(strLine, 9), Left(Right(strLine, 21), 6),...etc...I get it

Thanks a million for your patience

0
 
LVL 10

Author Comment

by:ranhell
ID: 22854693
sorry my mistake one more time.
I figure it out how to assigned the 4 fields into a Indexed Primary on the Database
But now, I'm testing duplicates and the code below

RecordserA.Index = "MyBigIndex"
if (RecordsetA.NoMatch)  then ==> is not finding the record eventhough a previous record with the same information has been entered.
What am I doing wrong here.....??
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 22854700
Just set the Index to be used to the name of the composite index.
I used "MyBigIndex" as the name
Antagony1960 suggested "PrimaryKey"
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 22854707
Sorry. Cross-posted again.
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 22854737
That is now hard to guess.

If this is an Access database, can I suggest that you post a db with enough of the table to show the problem? Also, we would need the exact code that you are using.
0
 
LVL 10

Author Comment

by:ranhell
ID: 22854820
Code Used:
    RsMASTER.Index = "CBMASTER"
    RsMASTER.Seek "=", strLine
     If (RsMASTER.NoMatch) Then
    RsTEMPIN.AddNew
        RsTEMPIN!ID = Format(Me.TXID, "0000000000")
        RsTEMPIN!CBFECHA = Left(strLine, 9)
        RsTEMPIN!CBHI = Left(Right(strLine, 21), 6)
        RsTEMPIN!CBHF = Left(Right(strLine, 15), 6)
        RsTEMPIN!CBSE = Right(strLine, 9)
        RsTEMPIN!IDPROVE = idprovee
        RsTEMPIN!FECHAIN = Format(Now, "DD/MMM/YYYY")
        RsTEMPIN!tipoid = tipoid
        RsTEMPIN!polvorin = polvorin
    RsTEMPIN.Update
DB-set-up.GIF
0
 
LVL 10

Author Comment

by:ranhell
ID: 22854830
Sorry mistaped the code
here it is again

RsMASTER.Index = "CBMASTER"
    RsMASTER.Seek "=", strLine
     If (RsMASTER.NoMatch) Then
    RsMASTER.AddNew
        RsMASTER!ID = Format(Me.TXID, "0000000000")
        RsMASTER!CBFECHA = Left(strLine, 9)
        RsMASTER!CBHI = Left(Right(strLine, 21), 6)
        RsMASTER!CBHF = Left(Right(strLine, 15), 6)
        RsMASTER!CBSE = Right(strLine, 9)
        RsMASTER!IDPROVE = idprovee
        RsMASTER!FECHAIN = Format(Now, "DD/MMM/YYYY")
        RsMASTER!tipoid = tipoid
        RsMASTER!polvorin = polvorin
    RsMASTER.Update
0
 
LVL 76

Accepted Solution

by:
GrahamSkan earned 500 total points
ID: 22854891
Hmm. I was expecting the table itself (in a database), rather than a picture of the design. but that might have been for you difficult to arrange.
However, you should list the data in each key as a separate parameter in the .Seek line. I only showed you the start because it would be a bit long.

RecordSetA.Seek "=", Left(strLine, 9), Left(Right(strLine, 21), 6),...


Perhaps we should introduce some new variables
Dim str1 As String

Dim str2 As String

Dim str3 As String

Dim str4 As String
 

str1 = Left(strLine, 9)

str2 = Left(Right(strLine, 21), 6)

str3 = Left(Right(strLine, 15), 6)

str4 = Right(strLine, 9)
 

RsMASTER.Index = "CBMASTER"

    RsMASTER.Seek "=", str1, str2, str3, str4

     If (RsMASTER.NoMatch) Then

    RsMASTER.AddNew

        RsMASTER!ID = Format(Me.TXID, "0000000000")

        RsMASTER!CBFECHA = str1

        RsMASTER!CBHI = str2

        RsMASTER!CBHF = str3

        RsMASTER!CBSE = str4

        RsMASTER!IDPROVE = idprovee

        RsMASTER!FECHAIN = Format(Now, "DD/MMM/YYYY")

        RsMASTER!tipoid = tipoid

        RsMASTER!polvorin = polvorin

    RsMASTER.Update

Open in new window

0
 
LVL 10

Author Comment

by:ranhell
ID: 22854998
I've tested your code exactly as you posted it and having the same issue with the duplicates, I'm entering the same value twice and is not finding a Match
What I mean is
the code "RsMASTER.Seek "=", str1, str2, str3, str4
     If (RsMASTER.NoMatch) Then
     --->> is not working
Any other ideas
attached is the DB
DB.mdb
0
 
LVL 10

Author Comment

by:ranhell
ID: 22855023
I'm gonna accept your comments as a solution due to the excellent feedback and patience I receive from you, the whole point of this was not having repetitive fields in the db, but at this point I guess I would have to do it different.
I'm still interested in the solution to this so, if you get it to work let me know.
So you know I did learn valueble things through this issue and thanks to you.

0
 
LVL 10

Author Closing Comment

by:ranhell
ID: 31511920
thanks for your help and support
0
 
LVL 11

Expert Comment

by:Antagony1960
ID: 22855113
@renhell:
Although it appears ok in the picture you posted above, I downloaded the DB and you've got the grouped index fields in a different order to the actual table fields. So str2 and str3 are matching to the wrong fields in the seek.
Index CBFECHA, CBHF, CBHI, CBHE
Table CBFECHA, CBHI, CBHF, CBHE

 To prove it, swap str2 & str3 in the Seek line and it will probably work.

0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 22856123
Morning,
Sorry that I wasn't around to see the end of this question.

I will take it that Antagony has found the final problem. I know that you will want to thank him . If you think it is appropriate, you can have the question reopened so that you can re-assign the points.

0
 
LVL 11

Expert Comment

by:Antagony1960
ID: 22856167
No need to re-assign the points. Just glad to help.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
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…
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…
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…

863 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

26 Experts available now in Live!

Get 1:1 Help Now