Solved

Visual basic 6 .Seek NoMatch usign DAO Recordset

Posted on 2008-10-30
28
1,055 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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
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…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…

757 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