Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Need to Query a table for like records write to another table

Status
Not open for further replies.

d1trupinoy

Technical User
Dec 11, 2001
14
US
Hi I am a newbie to ACCESS and VBA. I need to write a VBA script module to search an existing table for like records based on a field (e.g. find all records in animal table that are dogs). I then need to write all the found record field conents into one field of another table (e.g. when you find all the dogs write all the colors of dogs found to one filed called "all possible colors" in table "dogs_2).

Can anyone help?
 
you can do this via SQL, DAO or ADO which do you prefer

dao
private sub getcolors(stranimal as string)
'call by getcolors("dog")
dim rstget as recordset,rstadd as recordset
dim db as database
dim strcolor as string

set db = currentdb
set rstget = db.openrecordset("animaltable")
rstget.findfirst "[strtype] = "stranimal"
do until rst.eof
strcolor = strcolor & rstget![color] & ", "
rstget.findnext "[strtype] = "stranimal"
loop
rstget.close
set rstadd = db.openrecordset("newtablename")
rstadd.addnew
rstadd!type = "dog"
rstadd![colors] = strcolor
rstadd.close
rstadd.update
set rstadd = nothing
set rstget = nothing
set db = nothing
end sub

sql
private sub getcolors(stranimal as string)
'call by getcolors("dog")
dim rstget as recordset,sqladd as string
dim db as database
dim strcolor as string
dim strsql as "Select color from Animals where type = '" & stranimal & "'"

set db = currentdb
set rstget = db.openrecordset(strsql)
rst.movefirst
do until rst.eof
strcolor = strcolor & rstget![color] & ", "
rstget.movenext
loop
rstget.close
strsqladd = "insert into newtablename [type], colors value(stranimal,strcolor)
domcd.runsql strsql
set rstget = nothing
set db = nothing
end sub

you get the IDEA

good luck
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top