[RESOLVED] Code Problem

[manofsteel00]

Ok I'm having some problems with this code not doing what I'd like for it to do. I'd like for it to loop through all the records in the MAIN table with the outer loop and then loop and compare the MAIN table records to the ALTER table records adding records that are not in the ALTER table. In other words, if the record is in the ALTER table AND the MAIN table it should not appear in the listbox. This code only loops around 10 times. The MAIN table has over 45,000 records in it. Any suggestions anyone? Thanks.

Set connection = New ADODB.connection
    connection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source= C:\Documents and Settings\P6B0438\My Documents\Programs\Visual Basic\DB\cycleCount.mdb"
    connection.Open
    
    Set newConnection = New ADODB.connection
    newConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source= C:\Documents and Settings\P6B0438\My Documents\Programs\Visual Basic\DB\cycleCount.mdb"
    newConnection.Open
    
    Set recordSet = New ADODB.recordSet
    Set newRecordSet = New ADODB.recordSet
      
    Dim inAlterTable As Boolean
    Dim selectStatement As String
    Dim newSelectStatement As String
    
    inAlterTable = False
    selectStatement = "SELECT DISTINCT PICKSL,DFP,PROD,DESC FROM MAIN"
    'newSelectStatement = "SELECT DISTINCT PICKSL,DFP,DESC FROM [ALTER]"
    
    recordSet.Open selectStatement, connection, adOpenKeyset, adLockPessimistic, adCmdText
    'newRecordSet.Open newSelectStatement, newConnection, adOpenKeyset, adLockPessimistic, adCmdText
    newRecordSet.Open "[ALTER]", newConnection, adOpenKeyset, adLockPessimistic, adCmdTable
    
    recordSet.MoveFirst
    newRecordSet.MoveFirst
        
    Do Until recordSet.EOF
        Do Until newRecordSet.EOF
            If (recordSet.Fields("PICKSL") = newRecordSet.Fields("PICKSL") And _
                recordSet.Fields("DFP") = newRecordSet.Fields("DFP") And _
                recordSet.Fields("PROD") = newRecordSet.Fields("PROD") And _
                recordSet.Fields("DESC") = newRecordSet.Fields("DESC")) Then
                inAlterTable = True
            End If
            DoEvents
            newRecordSet.MoveNext
        Loop
        If (inAlterTable = False) Then
            lstFirstScreen.AddItem (recordSet.Fields("PICKSL") & vbTab _
                & recordSet.Fields("DFP") & vbTab _
                & recordSet.Fields("PROD") & vbTab _
                & recordSet.Fields("Desc"))
        End If
        DoEvents
        recordSet.MoveNext
    Loop
    
    recordSet.Close
    connection.Close
    Set recordSet = Nothing
    Set connection = Nothing

[sevenhalo]

Are you able to use an outter join?

[manofsteel00]

Probably, but not really sure how to do that

[eranfox]

...
This code only loops around 10 times. The MAIN table has over 45,000 records in it. Any suggestions anyone? Thanks.

...
 
    recordSet.MoveFirst
    newRecordSet.MoveFirst
        
    Do Until recordSet.EOF
        Do Until newRecordSet.EOF
            If (recordSet.Fields("PICKSL") = newRecordSet.Fields("PICKSL") And _
                recordSet.Fields("DFP") = newRecordSet.Fields("DFP") And _
                recordSet.Fields("PROD") = newRecordSet.Fields("PROD") And _
                recordSet.Fields("DESC") = newRecordSet.Fields("DESC")) Then
                inAlterTable = True
            End If
            DoEvents
            newRecordSet.MoveNext
        Loop
        If (inAlterTable = False) Then
            lstFirstScreen.AddItem (recordSet.Fields("PICKSL") & vbTab _
                & recordSet.Fields("DFP") & vbTab _
                & recordSet.Fields("PROD") & vbTab _
                & recordSet.Fields("Desc"))
        End If
        DoEvents
        recordSet.MoveNext
    Loop
    
    recordSet.Close
    connection.Close
    Set recordSet = Nothing
    Set connection = Nothing

Hello manofsteel00,
I think the problem is that you should place this line :

newRecordSet.MoveFirst

between the two loops:

...
    Do Until recordSet.EOF
        [B][COLOR=Blue]newRecordSet.MoveFirst[/COLOR][/B]
        Do Until newRecordSet.EOF
...

That way every new iteration of recordSet will be compared to newRecordSet from the begining.
Best Regards,
ERAN

[manofsteel00]

I put the newRecordSet.moveFirst between the loops and it appears to loop through the entire contents; however, it never adds anything to the listbox.

[sevenhalo]

It would be something like this (mind you, I don't know which fields are relational)

select DISTINCT MAIN.PICKSL,MAIN.DFP,.MAIN.PROD,MAIN.[DESC], [ALTER].PICKSL
from MAIN, [ALTER]
where MAIN.PICKSL *= [ALTER].PICKSL

It probably would be a little less demanding. You can use a single recordset and connection. Tweak it a little bit and see if you can get it to work right for you.

(In theory) The [ALTER].PICKSL field will only have a value if it has a matching record in MAIN; otherwise it will be null.

[manofsteel00]

I see what you're saying, but what I'm trying to do is output the only the contents of the MAIN table that are not in the ALTER table. Because if they are in the ALTER table then it means someone has already chosen them.

[sevenhalo]

My response wasn't a silver bullet solution

Instead of creating two recordsets are trying to compare them, you can loop through one and check just the last field to see if it's populated. If it is, it's in both the ALTER and the MAIN table. Using just an if/then, you can add it to the listbox accordingly.

[si_the_geek]

In this case using a subquery should work, eg:

selectStatement = "SELECT DISTINCT PICKSL,DFP,PROD,DESC FROM MAIN " _
              & "WHERE PICKSL Not In(SELECT PICKSL FROM [ALTER])"

(based on same join field assumption as sevenhalo's example)

[manofsteel00]

Could I do this?

selectStatement = "SELECT DISTINCT PICKSL,DFP,PROD,DESC FROM MAIN " _
              & "WHERE PICKSL Not In(SELECT PICKSL,DFP,PROD,DESC FROM [ALTER])"

Since multiple items can have the same pick slot, but they cannot have the same pick slot, dfp, product number, and description.

[si_the_geek]

Nope, 'In' only works for one field...

I think you should be able to get away with a 'Null' Left join, something like this (note the brackets may be wrong):

selectStatement = "SELECT DISTINCT M.PICKSL, M.DFP, M.PROD, M.[DESC] " _
                & "FROM Main M LEFT JOIN [Alter] A ON (" _
                & "  M.PICKSL = A.PICKSL AND M.DFP = A.DFP AND M.PROD = A.PROD AND M.[DESC] = A.[DESC] " _
                & "WHERE A.PICKSL Is Null"

This will perform a left join (so all rows of Main are returned, even if no matching Alter data is present), but only allow rows where there is a Null value for PICKSL in Alter (which is how no match is shown for Left Joins).

[manofsteel00]

Gotcha Thanks!