What went wrong-Please help!

  • Thread starter Thread starter TA
  • Start date Start date
T

TA

Dear all experts,
I do not know what wrong in this piece of code but the
value is null. Please help. Thank you in advange!

Set Rs3 = db.OpenRecordset("dbo_mainerror2",dbOpenDynaset,
dbSeeChanges)
Set rs_cum = db.OpenRecordset _
("SELECT * FROM dbo_cum WHERE batch = '" & Me!Combo0
& "'", dbOpenDynaset, dbSeeChanges)
rs_cum.MoveFirst
tempID = (rs_cum.Fields![ident])
Do While Not rs_cum.EOF
Rs3.AddNew
rs_cum.MoveNext
If rs_cumhiv.Fields![ident] = (tempID) Then
Rs3!ident = tempID
Rs3!batch = rs_cum.Fields![batch].Value
' Move mouse over I see <No current record> !?
Rs3!code = "100"
rs_cum.MovePrevious
End If
Loop
 
Hi,

You're missing a ";" at the end of your SQL statement.

Set rs_cum = db.OpenRecordset _
("SELECT * FROM dbo_cum WHERE batch = '" & Me!Combo0
& "';", dbOpenDynaset, dbSeeChanges)

Regards,
Jen
 
TA said:
I do not know what wrong in this piece of code but the
value is null. Please help. Thank you in advange!

Set Rs3 = db.OpenRecordset("dbo_mainerror2",dbOpenDynaset,
dbSeeChanges)
Set rs_cum = db.OpenRecordset _
("SELECT * FROM dbo_cum WHERE batch = '" & Me!Combo0
& "'", dbOpenDynaset, dbSeeChanges)
rs_cum.MoveFirst
tempID = (rs_cum.Fields![ident])
Do While Not rs_cum.EOF
Rs3.AddNew
rs_cum.MoveNext
If rs_cumhiv.Fields![ident] = (tempID) Then
Rs3!ident = tempID
Rs3!batch = rs_cum.Fields![batch].Value
' Move mouse over I see <No current record> !?
Rs3!code = "100"
rs_cum.MovePrevious
End If
Loop
Click to expand...

You don't explain where the rs_cumhiv recordset is set, but
that may be ok. The only other thing I can see is that you
never checked to make sure that rs_cum actually returned any
records, is it possible that the Where clause might not find
a record? Before doing the rs_cum.MoveFirst, you should
check for rs_cum.EOF or that rs_cum.RecordCount > 0
 
Hi Marsh
I tried used to check:
If rs_cum.recordcount>0 then
....
end if
but it is not work either.

Hi Jen,
I tried to used ";" but it still <No current record>

I still working on it and if your guy have any ideal
please let me know. Thankyou.
TA
-----Original Message-----
TA said:
I do not know what wrong in this piece of code but the
value is null. Please help. Thank you in advange!

Set Rs3 = db.OpenRecordset ("dbo_mainerror2",dbOpenDynaset,
dbSeeChanges)
Set rs_cum = db.OpenRecordset _
("SELECT * FROM dbo_cum WHERE batch = '" & Me!Combo0
& "'", dbOpenDynaset, dbSeeChanges)
rs_cum.MoveFirst
tempID = (rs_cum.Fields![ident])
Do While Not rs_cum.EOF
Rs3.AddNew
rs_cum.MoveNext
If rs_cum.Fields![ident] = (tempID) Then
Rs3!ident = tempID
Rs3!batch = rs_cum.Fields![batch].Value
' Move mouse over I see <No current record> !?
Rs3!code = "100"
rs_cum.MovePrevious
End If
Loop
Click to expand...

You don't explain where the rs_cumhiv recordset is set, but
that may be ok. The only other thing I can see is that you
never checked to make sure that rs_cum actually returned any
records, is it possible that the Where clause might not find
a record? Before doing the rs_cum.MoveFirst, you should
check for rs_cum.EOF or that rs_cum.RecordCount > 0
Click to expand...
 
TA said:
Hi Marsh
I tried used to check:
If rs_cum.recordcount>0 then
...
end if
but it is not work either.
Click to expand...

"it is not work either" is not descriptive enough for me to
decipher what went wrong.

Have you tried setting a break point at the top of the
procedure and single stepping through it to see exactly
where the error happens?
 
Thank you Dinh,
I want to compare field IDENT at begin record set, so I
assigned tempID=rs_cum.fields![ident],
then I add a new record to rs3 by use rs3.Addnew,
then move pointer of rs_cum to next in order to compare
field ident of first record and field ident of the second
record if they are equal then insert into rs3.

I answer to you in line code below:

-- Original code --
rs_cum.MoveFirst
tempID = (rs_cum.Fields![ident])
Do While Not rs_cum.EOF
Rs3.AddNew
rs_cum.MoveNext
If rs_cum.Fields![ident] = (tempID) Then
Rs3!ident = tempID
Rs3!batch = rs_cum.Fields![batch].Value
' Move mouse over I see <No current record> !?
Rs3!code = "100"
rs_cum.MovePrevious
End If
Loop

-----Original Message-----
WHICH value is Null?
Click to expand...
When I move mouse over it said (rs_cum.Fields![ident])=null
Sorry. You don't give enough info. and I can only guess:

1. Is [Batch] a Text (char) Field or a Numeric Field?
You SQL String is written for a Text Field and won't work
if [Batch] is a Numeric Field.
Click to expand...
Batch is a character field
2. You need the Rs3.Update to go with Rs3.AddNew at the
end of the value assignment process. I can't see it in
your code. Check Access VB Help on AddNew for a sample
code.
Click to expand...
Can I put a rs3.update before end while loop OR inside the
IF statement?
3. I am not sure what you are trying to do starting from
rs_cum.MoveFirst but the rs_cum.MoveNext is usually the
last statement in the While loop. See Access Help as per
point 2.
Click to expand...
I mention a bove
4. What is [rs_cumhiv]? You haven't set it anywhere
AFAICS.
Click to expand...
I do not have rs_cumhiv (typo: Sorry) it is rs_cum
HTH
Van T. Dinh
MVP (Access)
Click to expand...

Best Regards,
TA
 
Hi Jen,
Thank you for your reply. I am not using the break point
yet. I will try it.
Best regards,
TA
 
.... the answer to Q4???

--
HTH
Van T. Dinh
MVP (Access)



TA said:
Thank you Dinh,
I want to compare field IDENT at begin record set, so I
assigned tempID=rs_cum.fields![ident],
then I add a new record to rs3 by use rs3.Addnew,
then move pointer of rs_cum to next in order to compare
field ident of first record and field ident of the second
record if they are equal then insert into rs3.

I answer to you in line code below:

-- Original code --
rs_cum.MoveFirst
tempID = (rs_cum.Fields![ident])
Do While Not rs_cum.EOF
Rs3.AddNew
rs_cum.MoveNext
If rs_cum.Fields![ident] = (tempID) Then
Rs3!ident = tempID
Rs3!batch = rs_cum.Fields![batch].Value
' Move mouse over I see <No current record> !?
Rs3!code = "100"
rs_cum.MovePrevious
End If
Loop

-----Original Message-----
WHICH value is Null?
Click to expand...
When I move mouse over it said (rs_cum.Fields![ident])=null
Sorry. You don't give enough info. and I can only guess:

1. Is [Batch] a Text (char) Field or a Numeric Field?
You SQL String is written for a Text Field and won't work
if [Batch] is a Numeric Field.
Click to expand...
Batch is a character field
2. You need the Rs3.Update to go with Rs3.AddNew at the
end of the value assignment process. I can't see it in
your code. Check Access VB Help on AddNew for a sample
code.
Click to expand...
Can I put a rs3.update before end while loop OR inside the
IF statement?
3. I am not sure what you are trying to do starting from
rs_cum.MoveFirst but the rs_cum.MoveNext is usually the
last statement in the While loop. See Access Help as per
point 2.
Click to expand...
I mention a bove
4. What is [rs_cumhiv]? You haven't set it anywhere
AFAICS.
Click to expand...
I do not have rs_cumhiv (typo: Sorry) it is rs_cum
HTH
Van T. Dinh
MVP (Access)
Click to expand...

Best Regards,
TA
Click to expand...
 
Back
Top