how to tell a record in a table is printed

[manoskok]

Please help !
I need help to identify records in a table as printed.

I have a table with all the microbiological results from experiments.
There is a form which I use to enter the results to the table.
There is also a form with 2 combo boxes where I can select the number of experiments (from experimentid....to experimentid)and then by using a bit of code to print the specified results, as :

If IsNull(from) Or IsNull(Me!to) Then
MsgBox "Select a ""From"" and a ""To"" Sample Code", vbExclamation + vbOKOnly, "Ooooops!"
Exit Sub
End If
criteria = "[experimentid] Between " & Me!from & " And " & Me!to
DoCmd.OpenReport "rpt_nero", acPreview, , criteria

So far the db is working fine.

What I need is a function or procedure (or I don't know what!!) which will take effect when the specified records are printed and then it will update the original table where the experimental results are, by adding the value yes/no to a specific field in each record that is printed.
This way I will know by using another form wich records have been printed and which not.

Thanks in advance.

 

[Guest_imported]

Place this towards start of sub
dim db as database, rst as recordset
dim strsql as string

place this after "criteria = "[experimentid] Between " & Me!from & " And " & Me!to"

strsql = "select * from samerecordsourceasreport where "& criteria
set db = currentdb
set rst = db.openrecordset(strsql)
rst.movefirst
do until rst.eof
rst!yesnofieldname = -1 'mark as printed
rst.update
rst.movenext
loop
rst.close
set rst = nothing
you will need to change the names to match your fields
this assumes that you are considering the above records as printed.(even though they are just being previewed)
You will also want to add some error trapping and such to the above

 

[manoskok]

Thanks alot for your help but Now I get a type mismatch error
I include the complete sub for advice :

Private Sub Command5_Click()
Dim db As database, rst As Recordset
Dim strsql As String
Dim criteria As String

On Error GoTo Err_Command5_Click
If IsNull(from) Or IsNull(Me!to) Then
MsgBox "Select a ""From"" and a ""To"" Sample Code", vbExclamation + vbOKOnly, "Ooooops!"
Exit Sub
End If

criteria = "[experimentid] Between " & Me!from & " And " & Me!to

strsql = "select * from tbl_nero where " & criteria
Set db = CurrentDb
Set rst = db.openrecordset(strsql)
rst.MoveFirst
Do Until rst.EOF
rst!printed = -1 'mark as printed
rst.Update
rst.MoveNext
Loop
rst.Close
Set rst = Nothing

DoCmd.OpenReport "rpt_nero", acPreview, , criteria

Exit_Command5_Click:
Exit Sub
Err_Command5_Click:
MsgBox Err.Description
Resume Exit_Command5_Click

End Sub

Notes :
This sub is on frm_nero with record source tbl_nero
tbl_nero : the table I want to update
printed : the field name of the above table to change to yes
rpt_nero : report with recordsource from qry_nero
error : type mismatch

 

[braindead2]

Sorry!The place I can see where I missed is needed to have

Do Until rst.EOF
rst.edit 'add this line to code
rst!printed = -1
rst.Update

but it can't explain the type mismatch error
try that and see.
If it still errors you may want to change the -1 to a yes or a true but it should accept a -1 if it is a yes no field
Good luck!

 

[manoskok]

Thanks again for your time.
It seems I need a lot of work with VB
I still have problem

I added the line : rst.edit
but now I get this message :
compile error, method or data member not found

What am I doing wrong ? What do I need to have in Tools/References menu ?
I still get the type mismatch.
Thanks again

 

[braindead2]

Which line is being highlighted when the error occurs?
Also which version of Access are you using? IF 2000 make sure Microsoft DAO library is checked.

 

[manoskok]

I use the 2000 and the line that is highlighted is
rst.edit (the word edit is highlighted)
Microsoft DAO 3.6 object library is checked and Ms ActiveX Data Objects 2.1 Library).
Thanks again

 

[braindead2]

Sorry for the delay I've been rather busy today

I ran a quick test on the above code and it works perfectly
except when I changed my experimentid from a number field to a text field then I got a datatype mismatch error. Is it possible that your experimentid is not a number field?
This is how the code reads on my working version
Private Sub Command5_Click()
Dim db As database, rst As Recordset
Dim strsql As String
Dim criteria As String

On Error GoTo Err_Command5_Click
If IsNull(from) Or IsNull(Me!to) Then
MsgBox "Select a ""From"" and a ""To"" Sample Code", vbExclamation + vbOKOnly, "Ooooops!"
Exit Sub
End If

criteria = "[experimentid] Between " & Me!from & " And " & Me!to

strsql = "select * from tbl_nero where " & criteria
Set db = CurrentDb
Set rst = db.openrecordset(strsql)
rst.MoveFirst
Do Until rst.EOF
rst.edit
rst!printed = -1 'mark as printed
rst.Update
rst.MoveNext
Loop
rst.Close
Set rst = Nothing

'DoCmd.OpenReport "rpt_nero", acPreview, , criteria
'I blocked out above so I would not have to create a report
Exit_Command5_Click:
Exit Sub
Err_Command5_Click:
MsgBox Err.Description
Resume Exit_Command5_Click

End Sub

 

[manoskok]

FINALLY IT WORKS
A BIG THANKS TO YOU.
Your help has been very important.
(I also unchecked the ADO feature in tools/references).