help with vba query

[Toeying]

Hello guys,
Please I need your help!!. I have pull up some records from my database. Now I want to set a property to yes. for example, I've pulled up records for clients who need to receive a letter. After the letter has been printed correctly, I want to set the status of the letterprinted field to yes. On the form, it show as a checkbox but when I try to set it to yes using the following code:
Me.LetterPrinted= "yes" , an error message saying recordset not updatable.

What can I do to update this field? many thanks for your help!!!

 

[MStef]

Try with "On" or "Off".

 

[Toeying]

tried that doesnt work

 

[dbDamo]

Try

Me.LetterPrinted = -1

 

[Toeying]

check box problem...

Hello all,
I have just hit another problem.
I have a checkbox on my form which I am using to set the back colour of a label on the form to specify some information. when I click the check box on one record the same back colour shows thru all the records whether the check box is ticked or not.
How do I restrict the effect to just the record being viewed thanks

 

[Steve R.]

Re: check box problem...

You can can place VBA code in the form's current event property to reset the values/properties of your controls.

 

[dbDamo]

Re: check box problem...

Add some code to the OnCurrent event of your form

If Me.YourCheckBoxNameHere = -1 Then
YourBackgroundColourChangeCodeHere
Else
CodeToUndoBackgroundColourChangeHere
End If

 

[boblarson]

Re: check box problem...

Is the checkbox bound or unbound? If unbound and on a continuous form you will have a problem with this code. It will do exactly what you've specified. In order to have it not do that, the checkbox would need to be bound to a field in the table.

 

[Toeying]

Re: check box problem...

Brilliant thanks. i place the same code in the check box click event formerly. now i've put it in the form's current event-- works perfectly. thanks a million

 

[Toeying]

Re: check box problem...

Is the checkbox bound or unbound? If unbound and on a continuous form you will have a problem with this code. It will do exactly what you've specified. In order to have it not do that, the checkbox would need to be bound to a field in the table.

The check box is bound. however, it only checks the first check box if there are a series of boxes to be checked. how can i make it check all the boxes for all the records selected by the query??
Many thanks

 

[Toeying]

Ok I reran the query and it works a bit now. however, it will only check the box for the first record shown. how can i make it loop thru and check all the records selected by the query?


thanks

 

[boblarson]

Okay, since you posted this in two categories (DO NOT DO THAT ANYMORE) I merged the two threads.

As for your question, you mean you have a set of records (not all of them) from the table and you want to mark all of those yes but only the ones that are in your form?

 

[Toeying]

yes. I have selected some records from by table to display on a form based on some criteria. then I want to check all the checkboxes for the records on the form. or if easier, update the particular field in the table to yes for all the records which have satisfied the criteria and showed up on the form...

Many thanks

 

[Toeying]

any help on this pleeeeeeeeeaaaaaaaase??

 

[boblarson]

You can use this:

Dim strIn As String
Dim rst As DAO.Recordset
Dim strSQL As String
 
Set rst = Me.RecordsetClone
 
Do Until rst.EOF
     strIn = strIn & rst!YourIDFieldNameHere & ","
     rst.MoveNext
Loop
 
If Right(strIn, 1) = "," Then
   strIn = Left(strIn, Len(strIn)-1)
End If
 
strSQL = "Update YourTableNameHere Set FieldNameHere = True " 
strSQL = strSQL & "WHERE YourIDFieldNameHere In(" & strIn & ")"
 
CurrentDb.Execute strSQL
 
rst.Close
Set rst = Nothing

 

[Toeying]

Thanks BOBLarson.
I have tried to use this code but i am getting an error message: "Too few parameters. Expected 14".
my Query has pulled up 14 records so I guess that where it gets that number from. What should i do?
Thanks

 

[Toeying]

Hi i have modified the code as follows and now works fine...cheers


Dim strIn As String
Dim rst As DAO.Recordset
Dim strSQL As String

Set rst = Me.RecordsetClone

Do Until rst.EOF
strIn = strIn & "'" & rst!ChildHSReferenceNumber & "',"
rst.MoveNext
Loop

If Right(strIn, 1) = "," Then
strIn = Left(strIn, Len(strIn) - 1)
End If

strSQL = "Update HSChild Set HSChildOver6Month = True "
strSQL = strSQL & "WHERE ChildHSReferenceNumber IN(" & strIn & ")"
Call DoCmd.SetWarnings(False)
Call DoCmd.RunSQL(strSQL)
Call DoCmd.SetWarnings(True)
'CurrentDb.Execute strSQL

rst.Close
Set rst = Nothing