Compare Records & Move to new table in VBA

  • Thread starter Thread starter Tom
  • Start date Start date
T

Tom

I want to make a loop whereby i can compare the first record with every other
record until i find a 'True' comparison, if i find this, move the records
into a new table, otherwise, move onto the second record and repeat.

I figured i would do this with a for next loop, i have done them in excel,
but im not certain of how to compare cells in access.

Thanks

Tom
 
You can do this in queries, without VBA.

Create a query, using 2 copies of the table.
If it's called tblClient, Access will alias the 2nd one as tblClient_1.

Drag the matching fields from tblClient onto tblClient_1.
Add criteria that the primary key is different.
For example, under ClientID of tblClient, enter:
<> tblClient_1.ClientID

That will give you the matching records.
Now turn it into a Append query (Append on Query menu), to add these records
to the new table.
 
Thanks for your response, i know how to do it in queries, but i think i need
to use VBA because i am firstly using user selected fields, (from a form) and
secondly, im not checking for duplicates, im basically going through a list
of transactions to find any returns (i.e. two transactions with same product,
but the sum of their value = 0) and moving them (copy & delete) amongst other
things.

So i need to know how to compare in VB, because the criteria in a query is
not complex enough to allow me to do everything i need to.

Thanks again

Tom
 
Okay: you can do it by opening 3 recordsets:
- first source table;
- second source table;
- the target table.

Loop through the first source table.
FindFirst for the matching conditions on the second one.
AddNew (with Update) on the target table if it matches.

For an example of looping through a recordset, see:
http://allenbrowne.com/func-DAO.html#DAORecordsetExample

(I'm not yet convinced that it would not be a better job to dynamically
generate the SQL statement from the fields the user chose, and Execute it to
populate the target table. But perhaps it is more complex than the overview
you gave.)
 
Thanks for your reply, it has helped greatly.

Another quesiton, similar topic;

If i write an sql query, can the field be a variable pulled from somewhere
else?

i.e. i have a form which lets the user select the field, can i pull that
value, and put it in the sql query?

Thanks

Tom
 
This example assumes you have a text box named WotField, and it contains the
name of the field from Table1, and you want to insert that value into the
field of the same name in Table2:

Dim strSql As String

strSql = "INSERT INTO Table2 ( " & Me.WotField & _
" ) SELECT " & Me.WotField & " FROM Table1;"
Debug.Print strSql
dbEngine(0)(0).Execute strSql, dbFailOnError
 
This is what i have, can you help (i hope you see what im trying to do now):

Sub checkBlanks(field, alphaOrNumeric)

If field <> "" And alphaOrNumeric = "Numeric" Then
'Purpose: How to open a recordset and loop through the records.
'Note: Requires a table named MyTable, with a field named
MyField.
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim cell1 As String
Dim cell2 As String

Set rs1 = DBEngine(0)(0).OpenRecordset("SELECT " & Me.field & "FROM
[01 - Input Data];")
Set rs2 = DBEngine(0)(0).OpenRecordset("SELECT " & Me.field & "FROM
[01 - Input Data];")
Set rs3 = DBEngine(0)(0).OpenRecordset("Removed Transactions")

Do While Not rs1.EOF
cell1 = rs1!field
rs2.BOF
rs2.MoveNext
Do While Not rs2.EOF
cell2 = rs2!field
If cell2 = cell1 Then
'move to rs3 and delete records from rs2 and rs1????
rs1.BOF
rs2.BOF
Else
rs2.MoveNext
Loop
rs1.MoveNext
Loop

rs.Close

End Sub

Sub prepareData()
Dim field As String
Dim alphaOrNumeric As String

field = [Forms]![Form_Data Cleanser].[MatchColumn1].Value
alphaOrNumeric = [Forms]![Form_Data Cleanser].[MatchColumn1AlphaBox].Value
checkBlanks(field, alphaOrNumeric)

End Sub
 
I'm not sure that this whole process makes sense to me, so hopefully this is
of some use.

We will assume that the source table has:
a) a primary key field named ID. (Otherwise the seek on rs2 will find the
*same* record as the one in rs1.)

b) a yes/no field named IsDead, which we will set to Yes to indicate the
record is to be deleted. (This lets you track what's happening, and verify
it all works before any deletion takes place.)

Function CheckBlanks(strField as String, strAlphaOrNumeric As String) As
Long
Dim db As DAO.Database
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim rs3 As DAO.Recordset
Dim strSql As String
Dim strWhere As String
Dim strDelim As String

If strAlphaOrNumeric <> "Numeric" Then
strDelim = """"
End If

Set db = dbEngine(0)(0)
strSql = "SELECT " & strField & ", IsDead FROM [01 - Input Data] ORDER
BY ID;"
Set rs1 = db.OpenRecordset(strSql)
Set rs2 = db.OpenRecordset(strSql)
Set rs3 = db.OpenRecordset("Removed Transactions", dbOpenDynaset)

Do While Not rs1.EOF
If Not IsNull(rs1(strField)) Then
strWhere = "(" & strField = " & strDelim & rs1(strField) &
strDelim & ") AND (ID <> " & rs1!ID & ")"
'Debug.Print strWhere
rs2.FindFirst strWhere
If rs2.NoMatch Then
rs3.AddNew
rs3(strField) = rs1(strField)
rs3.Update

rs2.Edit
rs2!IsDead = True
rs2.Update
End If
rs1.MoveNext
Loop

rs3.close
rs2.close
rs1.close
set rs3 = nothing
set rs2 = nothing
set rs1 = nothing

strSql = "DELETE FROM [01 - Input Data] WHERE IsDead = True;"
db.Execute strSql, dbFailOnError

'Return the number of record deleted.
CheckBlanks = db.RecordsAffected
set db = nothing
End Function

You will need to do your own debugging from there, Tom. (Again, the approach
seems like a lot of unnecessary work to me.)
 
Back
Top