populating table after search on another

[tanzania]

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?? Or would it be better to create a form for tblAllInfo. Any help would be appreciated! Tania smile

 

[pbaldy]

The direct answer to your question is to use 2 different recordsets; 1 on the source table and another on the destination table. However, it would be more efficient to execute an append query:

INSERT INTO tblCurrentEvent (Field1, Field2, ...)
SELECT Field1, Field2, ...
FROM tblAllInfo
WHERE FieldName = 1

By the way, date and time should not be used as field names, as they are reserved words (built in functions in this case):

http://support.microsoft.com/default.aspx?scid=kb;EN-US;209187

 

[tanzania]

Of course, thats much easier! Except im getting an error on the query at INTO - compile error: expected end of statement. What could be wrong with the code? Yes I noticed the date and time thing before and changed it, and have nearly come up with a solution using assignment to arrays-though that is defineately a long way round

cheers

 

[pbaldy]

I would have to see the SQL to figure out what's wrong with it. Can you post it here?

 

[tanzania]

Dim strSql As String

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

im getting a data type mismatch. Any ideas? both fields are text...

 

[pbaldy]

If it's text try

"WHERE Event_No = '1' "

 

[tanzania]

yep thats correct-should have spotted it earlier..thanx!

 

[pbaldy]

No problem. That should be a lot cleaner and faster than recordset loops.

 

[Johnco_UK]

******** ALREADY SOLVED, DID AN APPEND QUERY IN ACCESS, LOOKED AT THE SQL ********


Hey Paul,

great info provided for the post, but as a newb, I was wondering how to do the following...

I want to populate a table with records from a query on another table, however, slightly different...

I have a table full of Questions relating to a number of different Machines, on the form, the user will
select a machine from a combo box, then they (probably) press a button "Show Questions", this will fire
off the code below, which will go to ALL Questions for All Machines, and INSERT INTO my new table, only
the Machines which is relevant to the Combobox selection fromt he Form, the reason for populating a new
table is, I need to score each Machine on a given day etc, and give comments.

I want to hold (in my new table)

TheID-------------Question----------Score------------Comment
1-----------------question1---------1 to 5------------comments here
1-----------------question2---------1 to 5------------comments here
1-----------------question3---------1 to 5------------comments here
2-----------------question1---------1 to 5------------comments here
2-----------------question2---------1 to 5------------comments here


Here is what I tried...


Dim strSql
Dim TheMachine As String
Dim TheID As String

TheMachine = Forms![AuditMaster]![Shifts]![audit]!Machine
TheID = Forms![AuditMaster]![Shifts]![audit]!RefID


strSql = "INSERT INTO AuditQuestions (ID, Question) TheID " & _
"SELECT Question FROM [AllMachineQuestions] " & _
"WHERE (Machine=" & TheMachine & ")"

DoCmd.RunSQL strSql


I didn't think was going to work, the logic of what it is trying to do isn't right, I am inserting 2 values
into my new table, and I am trying to give it a static value AND the select is gathering the second value,
any help would be appreciated.

 

[pbaldy]

Glad you got it solved, and thanks for posting the update so people didn't waste time trying to help when you'd already sorted it out.