Help needed using findNext

[finberg]

I want to make such procedure:
1.Open Table1
2.Search for the first line responding a criteria
3.Get the Data from Field1
5.Run a procedre1 with this data
6.if procedure 1=true add line to Table2
7.Go to line 1 up to the last line responding criteria

Could you please give me a syntaxis of such procedure please- espesially I doubt how to take data from certain field when I found the line needed.
I made the first part to select the firms on criteria

Public Function Addrecord(Field, Sem, Man As Variant)

Dim db As Database, qd As QueryDef, rs As Recordset
Set db = CurrentDb
Set qd = db.CreateQueryDef("Temp&quot
qd.sql = " SELECT Firm.[Firm number], Firm.Field FROM Firm WHERE (((Firm.Field)=" & Field & &quot);"

End Function

But know I don't know how to take one by one the records from and work with then (to check them with other function, to add those values to other table)

Thanks a lot
Arseniy

 

[SgtJarrow]

Try this:

Public Sub AddNewRecord(criteria goes here)

Dim db As Database
Dim rs1 As Recordset
Dim rs2 As Recordset
Dim strCriteria As String
DIm blnRes As Boolean

Set db = CurrentDb
Set rs1 = db.OpenRecordset("Table1", dbOpenSnapshot)
Set rs2 = db.OpenRecordset("Table2", dbOpenDynaset)

With rs1
strCriteria = "[fieldname] = '" & criteria from above & '" And [fieldname] = '" & criteria from above & "'"
.FindFirst strCriteria
If .NoMatch = False Then
blnRes = Procedure1(.fields("Field1&quot)
If blnRes = True Then
rs2.Addnew
rs2.Fields("Field1&quot = .Fields("Field1&quot
rs2.Fields("Field2&quot = .Fields("Field2&quot
' etc
rs2.Update
.MovePrevious
End If
End If
End With

Set rs2 = Nothing
Set rs1 = Nothing
Set db = Nothing

End Sub

****************************
Only two things are infinite, the universe and human stupidity,
and I'm not sure about the former. (Albert Einstein)

Robert L. Johnson III
MCSA, CNA, MCP, Network+, A+
w: robert.l.johnson.iii@citigroup.com
h: wildmage@tampabay.rr.com

 

[mflancour]

Something like this might work.

Dim Criterea as string, MyDB as database, Mytable as recordset
Set MyDB = DBEngine.Workspaces(0).Databases(0)
Set Mytable = MyDB.OpenRecordset("Your table", DB_OPEN_TABLE) ' Open table.
Mytable.Index = "index from your table" ' Select index.
Mytable.FindFirst "=", "some text"
If Not Mytable.NoMatch Then
Criterea = Mytable![field1]
Do Until Mytable.EOF
Procedure1(Criterea)
Mytable.findnext
Loop
end if

 

[finberg]

Dear Robert
On your advice I made such code:
But it is adding only one filed,bespite of the fact that there are 5 or more.Btw, it add the second one.
How could be made, that it goest through all of them?
Thanks

Public Sub Addrecord(Field, Sem, Man As Variant)
Dim db As Database
Dim rs1 As Recordset
Dim rs2 As Recordset
Set db = CurrentDb
Set rs1 = db.OpenRecordset("Firms", dbOpenSnapshot)
Set rs2 = db.OpenRecordset("Contact result", dbOpenDynaset)
Dim a As Integer
With rs1
strCriteria = "[Field] = " & Field
.FindFirst strCriteria
If .NoMatch = False Then
blnRes = fCheck(.Fields("Firm number&quot, Sem)
If blnRes = False Then
rs2.AddNew
rs2.Fields("Firm&quot = .Fields("Firm Number&quot
rs2.Fields("Project&quot = Sem
rs2.Fields("Manager&quot = Man
a = a + 1
' etc
rs2.Update
.MoveNext
End If
End If
End With
MsgBox (a)
Set rs2 = Nothing
Set rs1 = Nothing
Set db = Nothing

End Sub

 

[SgtJarrow]

I would suggest walking through the code one line ata a time and see what is going on. Are you sure values going into the fields are the correct types....text into text, number in number, etc??? Are you sure all the names are correct and avaialble. Since one field is being added, sounds like most of the code is right...Just some problems in the field names and the types...

****************************
Only two things are infinite, the universe and human stupidity,
and I'm not sure about the former. (Albert Einstein)

Robert L. Johnson III
MCSA, CNA, MCP, Network+, A+
w: robert.l.johnson.iii@citigroup.com
h: wildmage@tampabay.rr.com

 

[finberg]

Thanks you very much
It worked,And it was the one I need.
I reworked it a bit, and it doest work now
Thanks a lot
Arseniy