accessing records in a query

[Toeying]

Hello All,
I have to write a query in my application to select some records. When these records have been selected, I need to access each record and update a particular field. I think this is done thru Recordset but I have no idea how to go about it. All I need is how to retrieve the ID for each record in the selection and run an update query to it.
many thanks
Ty

 

[Trevor G]

Ty what are you aiming for, are you looking to update a single field with exactly the same data to a number of records?

Which version of MS Access are you using?

 

[Toeying]

Hi,
I am using access 2003. I want to update a single field from "no" to "yes" in all the records that show up in the select query.

The full picture of what I want to do is this: I have to print off letters periodically for the contacts on my database. I am using a form to get the cut off date required. This is the code for the print command button:

Private Sub cmdPrint_Click()
On Error GoTo Err_cmdPrint_Click

Dim stDocName As String
Dim strFilter As String

If (Me!chkChild) Then
stDocName = "rptChild2ndBatchLetterPrint"
strFilter = "[HSChild2ndBatchDate]=Me.txtDateTo.Value"
DoCmd.OpenReport stDocName, acNormal, , strFilter, acDialog


ElseIf (Me!chkMum) Then
stDocName = "rptMum2ndBatch"
strFilter = "[HSMum2ndBatchDate]<= Me.txtDateTo.Value"
DoCmd.OpenReport stDocName, acNormal, , strFilter, acDialog
End If

Exit_cmdPrint_Click:
Exit Sub

Err_cmdPrint_Click:
MsgBox Err.Description
Resume Exit_cmdPrint_Click

End Sub


This is working perfectly. However, once i have printed, I need to set the print status field to yes. This is because sometimes, some contacts are inputed late but the letters still have to be printed.

2.The other problem I am encoutering with the code above is the strFilter. I will like it to be:
strFilter = "[HSChild2ndBatchDate]=Me.txtDateTo.Value AND [HSChild2ndBatchLetterPrinted]='No'" but access keeps saying the query is either wrong or too complex.

hope this sheds more light. Thanks for any help you can give .

 

[Trevor G]

Sounds ok perhaps you can do something along these lines but I will add a warning afterwards.

From your query place in criteria for the printed field to no, then have a continious form that will show all the records from the query

from your code you can then add something along the lines of

me.printed.value="Yes"

And all the records in the form should then indicate yes. So that means they have been printed (or does it!).

My point here is if you are confirming that they have gone to the printer for printing and then adding a yes, what happens to the records if the printer is broken or stops have way through or goes off line, or what ever.

Perhaps you could add a date field to say when they went to print and if you get an issue you can quickly correct that because of the date field.

 

[Toeying]

to tell you the truth I have no idea what you are talking about . It sounds easy, but I just cant find where to put what! I've gotten the criteria part. I put the no in the query builder.

I dont have a form that shows the records to be printed. rather I have a report. please more help...thanks

 

[Toeying]

I have just created a form and added this line of code:
DoCmd.OpenForm "frmChild2ndBatchPrintout", , , strFilter

seems to work fine so far.
where do I add the me.printed.value="yes" Do i use a command button? thanks

 

[Trevor G]

I have just created a form and added this line of code:
DoCmd.OpenForm "frmChild2ndBatchPrintout", , , strFilter

seems to work fine so far.
where do I add the me.printed.value="yes" Do i use a command button? thanks

I have attached a sample database on the form it will show you records in a continious form which is based on a query, the query is set to show all records that have false and no dates. Only a small table is being used.

On the form there are 2 command buttons one to place a tick in the box against all records and also to record the date it was ticked, then another to take the tick out and remove the date.

If you look behind the command buttons all will be revealed.

I think you could then adapt it.

 

[Toeying]

Thanks a lot Trevor G. That was really helpful.

 

[SOS]

Instead of iterating through a recordset, why not just use a simple Update Query? Much easier that way and it can be set to use the criteria you want.