Code does not return all records

  • Thread starter Thread starter DavidC
  • Start date Start date
D

DavidC

Hi,
I have a code, below, which returns records stored in a
vertical database to a horizontal. IE Each refid could
have a number of records each with a different value
associated with the custref . Some may not have the same
combination of custref. The code seems to work generally
well but I have noticed that it does not write all the
records to the database. In fact using a crosstab query
there is a difference of 720 records out of 3886 records
between the two. The problem is that I cannot use the
crosstab query as the custref may or may not appear in the
database, so the query cannot include every possible
custref in it as if the custref does not appear in the
database then it goes into error. I have only reproduced
two out of the 13 possible instances of custref. The
database is a linked database hence the reason that some
databases that it links to may or may not have the same
combination of custref.

I cannot figure out why some records are returned byu the
code correctly yet there are the 700 that are not
returned. When I use the debug feature, the values for
the missing records are recorded in the variables and it
runs through the .addnew to .update and the varioabels
contain the right values, but the database does not have
them written in.

Any thoughts would be appreciated.

CODE
Set db = DBEngine.Workspaces(0)(0) 'CurrentDb()
Set rsqt = db.OpenRecordset("qrytext_fields",
dbOpenDynaset)

Do Until rsqt.EOF = True

unid1 = rsqt!refid

If IsNull(rsqt!txt_value) Then
custdesc = ""
Else
custdesc = rsqt!txt_value
End If
custref = rsqt!txt_id

Select Case custref

Case 188

jdescrip = custdesc

Case 1889

custwo = custdesc
End Select

rsqt.MoveNext

unid2 = rsqt!refid

If unid1 = unid2 Then

Else
Set rsc = db.OpenRecordset("tblcustom_fields",
dbOpenDynaset)

With rsc

.AddNew
!UID = unid1
!Work_Order = custwo
!Job_Description = jdescrip
.Update

End With

custwo = ""
jdescrip = ""
End If
Loop

Thanks in advance of any help
regards

DavidC
 
I will not claim to follow all that the code is doing, but when unid1=unid2,
you completely skip over the writing of the second record to the new table.
Is that your intent?

Your code also opens a new recordset for rsc each time through the code, but
you're never closing it first? May work ok, but it's not how I would code
such activity. I'd either leave that rsc recordset open throughout or else
I'd specifically close it after doing the .Update. Much less overhead to
keep it open and then close it at the end of the procedure.

I also note that your code will error out when it does the .MoveNext and
that moves the recordset rsqt to EOF and then you try to read a field's
value for setting unid2. I don't think this is a source of what you're
seeing, but I'm surprised that your code doesn't error each time as it moves
through the last record or the rsqt recordset.
 
Thanks for your comments, I will amend as you have
recommended.

I have reproduced a small portion of records to show why I
need to check unid1 and unid2. As the number of records
for each refid will vary between each one as I step
through the table I need to check to see if the next
record is a new instance of refid or the same one. While
ref_id is the same I need to write the txt_value to a
variable and hold it there untill I have reached the end
of that ref_id and then write all the data to one new
record in the table. It is changing the vertical storage
of data to a horizontal form where the fields are the
txt_ID and then there is one record only for each refid
but a number of txt_id fields.

The xtx_id is not always the same in each refid and the
txt_value will change for each instance of txt_ID.

The crosstab query does the job for one specific
combination of xtx_id, but as the database may or may not
have an instance of txt_id then it is difficult to have
the append query run for all possible combinations of
txt_id.

So for example 4014 may not exist in the next database
that this one is linked to, but the downstream queries and
reports must be able to return 4014 if and when it does
exist.

PROJ_ID REFID TXT_ID TXT_VALUE
1 1 4014 Isolation
1 1 20899 Operations
1 1 20904 Chemist
1 2 4016 Overview Milestone
1 2 3743 XXXXXXXX
1 2 3746 AAA
1 2 3747 60R Blr Press Parts
1 2 3750 XXXXXXXX
1 2 4000 Planner
1 2 4001 00
1 2 4014 Isolation
1 2 4015 01
1 3 4010 ?
1 3 4001 160R
1 3 4000 Operations
1 3 3750 CQUY1A
1 3 3746 QUY
1 3 4014 Isolation
1 3 3747 Planner
1 4 4001 10R
1 4 4014 Isolation
1 4 4010 Work List
1 4 4000 Boiler C&I
1 4 3750 CQUY1A
1 4 3746 QUY
1 4 3747 10R Boiler C&I

As for the error. Yes it does error out on the last record
as it tries to .movenext. That is whay I put in the
error. The problem is that I cannot do the .movenext at
the beginning of the routine as the routine must look at
the first record in the set.

I am baffled by the failure to update all the records and
have checked to make sure there are no criteria limitng
the records, but there isn't.

regards

DavidC
 
I'm not sure that you're understanding my reply. Your code is not
remembering the values from all records when the unid1 and unid2 values are
the same. It only remembers the values of the last record (the one just
before unid1 no longer equals unid2). Thus, the code is only writing out ONE
record for each unique value of refid field. I don't think that is what you
want? But this is how your code will "omit" many records, and this may be
the source of your difficulty.
 
Thanks, I certainly do not want only the last record being
remembered. I guess I still do not understand why though
3166 records work but 720 don't. It does appear though
that it must be the way my code is written so I will take
your kind advice and thoroughly review the code.

Thanks again for your time and help.
Regards

DavidC
 
Back
Top