FIlter and copy records

[jams]

Hi!
I am new to VBa in Access and it is a bit different from the Excel.

I need to find how to loop through the recrds in a table and filter the values for each record regarding one value in order to then copy it to an empty table with the same fields.

THe main question I think is to:
1.Loop through the records
1.1. Filter the values
1.2. Copy the most recent records comparing the [date] field

I would apreciate anyones hlp.
Thx

 

[Dennisk]

the easiest way is to use an append query with a where clause.
or if like me you enjoy writing code then again apply a where clause to your recordset.

ie

dim rst as DAO.recordset

Set rst=CurrentDB.Openrecordset("SELECT * FROM tblYourTable WHERE [Value] = insert your value here")

do until rst.eof
' your processing here
rst.MoveNext
Loop

 

[Ripley]

This is fine, but the way i would do it is by simply creating a query. The QBE (Query By Example) is much simpler to start with, and involves less coding.

Create a query with all the fields you require.

On the form, create a text box (or other text control) and name it (for arguments sake) txtText1.

In the query, set the criteria as:

[Forms]![YourForm]!txtText1.

Now on the form, you place a command button next to txtText1, with the following code:

Private Sub cmdButton_Click()
'Check to see if the text box is empty, if it is display error message.
If IsNull(txtText1) Then
MsgBox "There is no data entered!"
Exit Sub
Else
Me.Form.RecordSource = "Your_Query_Name_Here"
End If
End Sub

 

[jams]

Ok. But I need VBA to check the last version of the record

the easiest way is to use an append query with a where clause.
or if like me you enjoy writing code then again apply a where clause to your recordset.

ie

dim rst as DAO.recordset

Set rst=CurrentDB.Openrecordset("SELECT * FROM tblYourTable WHERE [Value] = insert your value here")

do until rst.eof
' your processing here
rst.MoveNext
Loop

OK... Thanks for the quick reply but I need VBA to get the latest record of the database in order to then copy it to another table.

How will I specify that under the Where condition!!!!

Will VBA filter the [name]field and then picks out the record with the latest value in the [date]field... How will it compare the value of the field [date] for every record and takes the last?

 

[modest]

There is really no order of records in a table. Records are loaded from memory in random order, unless specified using the Order By clause, which is what you need if doing it by date.

SELECT TOP 1 * FROM [Table Name] ORDER BY [Date Field Name] desc;