passing a query with filter through a form 1

[dolodolo]

I am trying to execute an update query on a form so that only the records filtered on the form are updated.

Please help - thanks,
Dolores

 

[Remou]

You should be able to use the recordsetclone for the form.

Notes:

Set rs=Me.RecordsetClone

Do While Not rs.EOF
  rs.Edit
  rs!FieldBlah="Blah"
  rs.Update
  rs.MoveNext
Loop

 

[dolodolo]

Hi,

I'm not sure how to do this. Right now I execute a query and it prompts me for the filter values. I would like have only the record on the header form be updated on the detail form and therefore bypass the need for the filter prompt. Here's my code:

Private Sub UpdateDetail_Click()
On Error GoTo Err_UpdateDetail_Click

Dim stDocName As String

stDocName = "qry_UpdateUserDetail"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_UpdateDetail_Click:
Exit Sub

Err_UpdateDetail_Click:
MsgBox Err.Description
Resume Exit_UpdateDetail_Click

End Sub

Any help is much appreciated!

 

[PHV]

What is the SQL code of qry_UpdateUserDetail ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dolodolo]

Here it is:

UPDATE tbl_UserHeader INNER JOIN tbl_UserDetail ON (tbl_UserHeader.Supervisor = tbl_UserDetail.Supervisor) AND (tbl_UserHeader.ClockID = tbl_UserDetail.ClockID) AND (tbl_UserHeader.LoginName = tbl_UserDetail.LoginName) AND (tbl_UserHeader.DB = tbl_UserDetail.DB) AND (tbl_UserHeader.UserGroup = tbl_UserDetail.UserGroup) SET tbl_UserDetail.[Action] = tbl_UserHeader.Action, tbl_UserDetail.Comment = tbl_UserHeader.Comment, tbl_UserDetail.DateModified = tbl_UserHeader.DateModified, tbl_UserDetail.TimeModified = tbl_UserHeader.TimeModified, tbl_UserDetail.UserModified = tbl_UserHeader.UserModified
WHERE (((tbl_UserDetail.LoginName)=[Login name]) AND ((tbl_UserDetail.DB)=[DB name]));

 

[PHV]

What are [Login name] AND [DB name] ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dolodolo]

They are the two values on the form that comprise the unique record set e.g.

[LoginName] = 'fracca'
[DB] = "LIVE

 

[PHV]

WHERE tbl_UserDetail.LoginName=[Forms]![name of your mainform]![Login name] AND tbl_UserDetail.DB=[Forms]![name of your mainform]![DB name];

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886