Populating table after search on another

[tania20]

Hi, Im trying to, upon the click of a forms button, search the records in one table for all records matching a certain number. I then want to update another table with all these records...but am stuck in the coding. This is what i have so far...

Set db = CurrentDb()
Set rs = db.OpenRecordset("tblAllInfo", dbOpenDynaset)

With rs
.MoveFirst
Do Until .EOF
If .Fields(1).Value Is 1 Then

event_num = .Fields(1).Value
Sport = .Fields(2).Value
team = .Fields(3).Value
Date = .Fields(4).Value
Time = .Fields(5).Value

rs.Update

Set rs = db.OpenRecordset("tblCurrentEvent", dbOpenDynaset)
rs.AddNew
rs![Event_No] = event_num
rs![Sport] = sport
rs![Team_Player] = team
rs![Date] = Date
rs![Time] = Time
DoCmd.OpenForm "frmInfo" 'whose record source is tblCurrentEvent

But I really need to be updating the latter table within the loop but dont know how to do that without resetting the loop pointer?? Any help would be appreciated! Tania

 

[PHV]

Why not simply an Append query ?
RoCmd.RunSQL "INSERT INTO tblCurrentEvent (Event_No,Sport,Team_Player,[Date],[Time])" _
& " SELECT event_num,sport,team,[Date],[Time] FROM tblAllInfo WHERE event_num=1"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[tania20]

Hey, yes I just realised not long ago..heres what i came up with

strSql = "INSERT INTO tblCurrentEvent (Event_No, Sport) " & _
"SELECT Event_No, Sport " & _
"FROM [tblAllInfo] " & _
"WHERE Event_No = 1 "
DoCmd.RunSQL strSql

and the one you suggested

' DoCmd.RunSQL "INSERT INTO tblCurrentEvent (Event_No,Sport)" _
'& " SELECT Event_No,Sport FROM [tblAllInfo] WHERE Event_No =1"

But im getting a data mismatch on both...any ideas? Both fields are text.cheers

 

[PHV]

WHERE Event_No=[!]'[/!]1[!]'[/!]

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[tania20]

of course! cheers for that

 

[fneily]

Has anyone mentioned that now you're holding duplicate data in your database which doesn't abide by the rules of Normalization? Why do you need to do that? Maybe your design is wrong.

 

[tania20]

its only a temporary table, all the information gets cleared as soon as the form is closed