Setting Value for Each Row in Subform

[swimmermx]

At the top of one of my forms in the form header, I have a text box "Status" and whenever it is clicked it want it to set the status for all the rows in this form to have the same status.



Unfortunately, it only sets the status for which ever row has the focus. Does anyone know how to fix it to set the value from the form header for all the rows in the details?

Any help would be appreciated!

[TheShabz]

I'll assume that the form is displaying the rows from a query.

in the OnClick for the "Status" button have it run another query in VBA:

dim strSQL as String

strSQL = "UPDATE myTable SET Status = "& Me.txtStatus & "WHERE (SQL of query that pulled the records in your form)"

DoCmd.RunSQL (strSQL)

Me.Requery

keep the () intact in the WHERE clause above. Also, replace txtStatus with the name of the textbox holding your status.

[swimmermx]

Ok thanks for all your help! Here's the query for the record source:

SELECT Format(DSum("PTAmount","tbl_PutsTakes","((tbl_PutsTakes.CRNumber)=[Forms]![frm_CR-Entry]![CRNumber]) AND (tbl_PutsTakes.PTStatus = 'Approved and Atlas has been updated')"),"$0,000.00") AS ApprovedDollarsTotal, Format(DSum("PTAmount","tbl_PutsTakes","[RecordNumber]<=" & [RecAlias] & " AND ((tbl_PutsTakes.CRNumber)=[Forms]![frm_CR-Entry]![CRNumber])"),"$0,000.00") AS PotentialDollarsTotal, Format(DSum("PTHours","tbl_PutsTakes","[RecordNumber]<=" & [RecAlias] & " AND ((tbl_PutsTakes.CRNumber)=[Forms]![frm_CR-Entry]![CRNumber]) AND (tbl_PutsTakes.PTStatus = 'Approved and Atlas has been updated')")) AS ApprovedHoursTotal, Format(DSum("PTHours","tbl_PutsTakes","[RecordNumber]<=" & [RecAlias] & " AND ((tbl_PutsTakes.CRNumber)=[Forms]![frm_CR-Entry]![CRNumber])")) AS PotentialHoursTotal, tbl_PutsTakes.RecordNumber AS RecAlias, tbl_PutsTakes.*, *
FROM tbl_PutsTakes
WHERE (((tbl_PutsTakes.CRNumber)=Forms![frm_CR-Entry]!CRNumber));

Once I add that in the WHERE clause of the UPDATE sql, it gives me the compile error "expected: end of statement" at the first "PTStatus" (in bold). Any thoughts of why? I kept it intact.

[TheShabz]

short answer is to double up all the " because the VBA treats single " as string identifiers. to insert a " into a ", you need to make it a "". look up "concatenating strings" for more info.

however, this isnt the best way to do it. try throwing just the WHERE clause of your original SQL into the WHERE clause of the UPDATE SQL. see if that does the trick.

Your other option would be to open a recordset with the original SQL and work off of that but lets see if we can avoid that for now.

[swimmermx]

What do you mean double up all the string identifiers? I tried just entering in the where clause of the original SQL and it didn't work either. Any ideas?

[TheShabz]

Well, it's kinda hard to describe it in text. I'll use an example. lets say we have a variable called strText.
strText = "Hello World"
provides:
Hello World
strText = "Hello" World"
provides
Hello and a variable called World and the opening of a new string
strText = "Hello"" World"
provides
Hello" World
Basically, to have a " as a symbol instead of a string identifier, you have to double it up to "".

As far as getting the query to work, that's really all I got for you off the top of my head. It's probably something easy that I'm just not coming up with and I wont be near Access for awhile to try things out. I'll let the others, far wiser than myself, help you out here.

I wish I could do more, my apologies.

[swimmermx]

Thanks for all your help!

Is there any way to do it with a For Each Row in loop or Do While loop?

[TheShabz]

Yes, that's what I meant by opening a recordset in VBA and using that. Exact code I cannot provide but essentially here are your steps:
1. open the table as a recordset.
2. DO your criteria for having check or not check WHILE not EOF
3. loop

[maximus]

I am attaching a simple sample database: in this database there are four forms.
I would like you to look into Forms Table1 and Table11. Typical Main and subform setup.

In form Table1 a code is attached to the AfterUpdate Event of Project in the subform which autofills the StatusID of the Subform with the value selected in the Status Id combo box in the main form.

The Code as Follows:

Private Sub Project_AfterUpdate()
If Not IsNull(Forms!Table1!Combo10) Then
Me.StatusID = Forms!Table1!Combo10
End If
End Sub

Note: Autopopulation only occurs when:
a) A value is selected in the Status Combo on the main form.
b) When the project field is updated

The form Table11 uses a different approach: the code is attached to the Status combo of the main form. When you select a Value in the Status combo of the main form status for all entries in the subform linked to the ID of the main form is autofilled.
the code used:

Private Sub Combo10_AfterUpdate()
Dim strSQL As String

strSQL = "Select * From Table2 Where Link_ID=" & Me.ID
Set rs = CurrentDb.OpenRecordset(strSQL)
If rs.RecordCount > 0 Then
rs.MoveFirst
Do While Not rs.EOF
rs.Edit
rs!StatusID = Me.Combo10
rs.Update
rs.MoveNext
Loop
End If
Set rs = Nothing
Me.Table22.Requery
End Sub

please refer to the sample attached. Open form Table11 one entry is already there select a different Status than the one in the sub-form see the effect.

if this solves the problem mark the thread solved.

Note: Form Table11 is the startup form.

[swimmermx]

ok, that works. thanks a lot everyone!

how do you mark it as solved? Sorry I'm new.

[TheShabz]

just above your initial post, "thread tools"

[swimmermx]

Ok so I still have this problem and it is driving me insane, expecially because it shouldn't be this complicated!

So let me give you a little more background maybe somebody can save me.

I have a main form "frm_CR-Entry" with the unique id "CRNumber" that is connected to a one-to-many relationship form "frm_CR_PutsTakes." In the header of "frm_CR_PutsTakes," There is a "Status" and "Status Date" text box. It is a continuous form because you can have many "puts" and "takes" for each CRNumber.
So I want to be able to set the status and status date for each of the rows in the continuous form to equal the value in the header.

I first tried this in the AfterUpdate of Status

Dim strSQL As String

strSQL = "update tbl_PutsTakes set [PTStatus] = '" & Me.Status & "' where CRNumber = " & Me.CRNumber & ""

DoCmd.RunSQL (strSQL)

And got the "can't update records due to lock violation" error.

Then i tried this in the AfterUpdate of Status:

Dim strSQL As String
strSQL = "SELECT * from tbl_PutsTakes WHERE CRNumber = " & Me.CRNumber & ""
Set rs = CurrentDb.OpenRecordset(strSQL)

If rs.RecordCount > 0 Then
rs.MoveFirst
Do While Not rs.EOF
rs.Edit
rs!PTStatus = Me.Status
rs.Update
rs.MoveNext
Loop
End If

rs.Close
Set rs = Nothing

And got "could not update; currently locked by another session on this machine"

Can anyone please tell me how to fix these errors or another way that this could be completed?! it is driving me insaneee!

Also, i read on another forum to fix the "locked" error to go to Tools>Options>Advanced and set default open mode to "exclusive", default record locking to "No locks" and uncheck "open databases using record-level locking." They're currently set like that, so I don't know if it will affect it.

PLEASE HELP!