Hi John,
Yes my code looks just like this. My access table is getting populated with
38 rows all the same record. An example is the value for my field imageid
is unique to each record, the table has 38 rows all with imageid being the
same. as well as all my other fields which should be unique. It seems as if
my code is only looping through the first record in the recordset. As a side
note the record set is displaying the data correclty on my form, ie...no
duplicate records and all records are visible. Since the data is being sent
to the form correctly is there a way to save the data from the form into an
access table? I attached my full code below.
*******************************************
Private Sub Form_Open(Cancel As Integer)
'variables
Dim db As Database
Dim rsQry As DAO.Recordset
Dim qryDef As DAO.QueryDef
Dim sQuery As String
Dim riTbl As DAO.Recordset
'Table column values
Dim idLocale As Integer
Dim idRecon As Integer
Dim imageid As Integer
Dim Activity As String
Dim ImageCategory As Variant
Dim Path As String
Dim WhenTaken As Date
Dim ImageCaption As String
'SQL query.
sQuery = "select idlocale, idrecon, imageid, activity, imagecategory, path ,
whentaken, imagecaption from getReconImages(" &
Forms![LocalesDataEntry]![ReconnaissanceDataEntrySF]![idRecon] & ");"
Debug.Print sQuery
Set qryDef = CurrentDb.CreateQueryDef("")
'setting values
qryDef.Connect =
"ODBC;DSN=@@@;DATABASE=@@@;SERVER=@@@;PORT=5432;SSLMODE=prefer;"
qryDef.SQL = sQuery
Debug.Print sQuery
Set rsQry = qryDef.OpenRecordset
Set Me.Recordset = rsQry
'Set variables to recordset values
idLocale = rsQry![idLocale]
idRecon = rsQry![idRecon]
imageid = rsQry![imageid]
Activity = rsQry![Activity]
ImageCategory = rsQry![ImageCategory]
Path = rsQry![Path]
WhenTaken = rsQry![WhenTaken]
ImageCaption = rsQry![ImageCaption]
'Set variables to record set for access table
With rsQry
Set riTbl = CurrentDb.OpenRecordset("ttReconImagesForm_tbl")
Do Until .EOF
riTbl.AddNew
riTbl![idLocale] = idLocale
riTbl![idRecon] = idRecon
riTbl![imageid] = imageid
riTbl![Activity] = Activity
riTbl![ImageCategory] = ImageCategory
riTbl![Path] = Path
riTbl![WhenTaken] = WhenTaken
riTbl![ImageCaption] = ImageCaption
riTbl.Update
rsQry.MoveNext
Loop
End With
*************************
Thanks,
Dom Torrez
John Spencer said:
Your code should look something like this.
With rsQry
Set riTbl = CurrentDb.OpenRecordset("ttReconImagesForm_tbl")
Do Until .EOF
riTbl.AddNew
riTbl![idLocale] = idLocale
riTbl![idRecon] = idRecon
riTbl![imageid] = imageid
riTbl![Activity] = Activity
riTbl![ImageCategory] = ImageCategory
riTbl![Path] = Path
riTbl![WhenTaken] = WhenTaken
riTbl![ImageCaption] = ImageCaption
riTbl.Update
rsQry.MoveNext
Loop
End With
Does it?
Dom Torrez said:
Hi Martin,
I followed your suggestion and still get the same issue. My code cycles
through the loop, it just does so while inserting the same recordset row
into
each new row in the table. My table ends up having many rows with the
same
records. I was just reading about the .getrows method to copy and or get
multiple rows from a recordset. Is this the route I should be taking? If
so
how would I use this?
thanks
:
It looks like you need to move the rst.Query.MoveNext inside your loop.
As
it stands, you are looping thru building your table, but not advancing to
the
next record in the query.
Hope this helps.
:
hello
I'm trying to use a recordset to insert values into my access table. I
can
get the insert working somewhat correctly, problem is that the inserted
records are all the same. I believe my problem is in my loop. it
seems like
I need pointers to cycle down through my recordset rows, which isn't
happening. Can some one tell me where i'm going wrong? Like I said my
insert into the access table from the recordset is working partially, I
just
get multiple rows of one record rather than multiple rows of different
records.
***************
With rsQry
Do Until .EOF
Set riTbl = CurrentDb.OpenRecordset("ttReconImagesForm_tbl")
riTbl.AddNew
riTbl![idLocale] = idLocale
riTbl![idRecon] = idRecon
riTbl![imageid] = imageid
riTbl![Activity] = Activity
riTbl![ImageCategory] = ImageCategory
riTbl![Path] = Path
riTbl![WhenTaken] = WhenTaken
riTbl![ImageCaption] = ImageCaption
riTbl.Update
riTbl.MoveNext
Loop
rsQry.MoveNext
End With
*************
I'd appreciate any help offered. Thanks
Dom Torrez
