Update all Selected Records

[mrubel99]

Hi,
I am working with access 2003 / XP and this is my first post to the forum and I am pretty new to Access.

I have a Main single form with a subform built in. I would like to add a button on the main form that will change the yes/no fields in the records on the subform to yes. Also, if there is a filter applied, I only want to update those records that are filtered. The filters would only be of "right click-filter by selection" variety.

The fields on the subform are as follows:

Manuf_Na
Manuf_Cat
Prod_Desc
Class_Desc
Excl_Ln (Yes/No)

Probably a simple solution, but I couldn't find any info anywhere else. Any thoughts are greatly appreciated.

Mike

[nico5038]

The update can be coded in VBA and use a defined filter like:

IF Me.SubformName.Form.FilterOn = True then
   ' filter has been specified
   currentdb.execute ("UPDATE tblX SET Excl_Ln = True WHERE [MainID]=" & ME.MainID & " and " & Me.SubformName.Form.Filter)
else
   ' NO filter has been specified
   currentdb.execute ("UPDATE tblX SET Excl_Ln = True WHERE [MainID]=" & ME.MainID)
end if
 

Getting the idea ?

Nic;o)

[mrubel99]

Nico - Thank you for your response. I started to work with the code you provided and here is what I came up with - It keeps resulting in a runtime 3075 error, missing operator? Not sure what I am missing here. Thanks again.

If Me.tblUtilizationDataAll_subform.Form.FilterOn = True Then
' filter has been specified
CurrentDb.Execute ("UPDATE tblUtilizationDataAll SET Exclude_Line = -1 WHERE [PROJECT_NAME] = " & Me.Text81 & " And " & Me.tblUtilizationDataAll_subform.Form.Filter)
Else
' NO filter has been specified
CurrentDb.Execute ("UPDATE tblUtilizationDataAll SET Exclude_Line = -1 WHERE [PROJECT_NAME] = " & Me.Text81)
End If

[nico5038]

Nico - Thank you for your response. I started to work with the code you provided and here is what I came up with - It keeps resulting in a runtime 3075 error, missing operator? Not sure what I am missing here. Thanks again.

If Me.tblUtilizationDataAll_subform.Form.FilterOn = True Then
' filter has been specified
CurrentDb.Execute ("UPDATE tblUtilizationDataAll SET Exclude_Line = -1 WHERE [PROJECT_NAME] = " & Me.Text81 & " And " & Me.tblUtilizationDataAll_subform.Form.Filter)
Else
' NO filter has been specified
CurrentDb.Execute ("UPDATE tblUtilizationDataAll SET Exclude_Line = -1 WHERE [PROJECT_NAME] = " & Me.Text81)
End If

I guess your Project_Name is a extfield, thus the value needs to be embedded in single quotes (') like:

[PROJECT_NAME] = '" & Me.Text81 & "' And " &
'and in the "else" branch
[PROJECT_NAME] = '" & Me.Text81 & "'") 
 

Nic;o)

[mrubel99]

Nico - Thank you again for all of your help. The other branch of this code works, however this string is resulting in a runtime 3061 - Too few parameters. Expected 1 when a filter is on in the subform. I can't figure out why?

CurrentDb.Execute ("UPDATE tblUtilizationDataAll SET Exclude_Line = -1 WHERE [PROJECT_NAME] = '" & Me.Text81 & "' And " & Me.tblUtilizationDataAll_subform.Form.Filter)
 

[nico5038]

Best to place a breakpoint (click in left "ruler" so a dot appears) and execute the code till the command. Now type in the immediate window the line:

? "UPDATE tblUtilizationDataAll SET Exclude_Line = -1 WHERE [PROJECT_NAME] = '" & Me.Text81 & "' And " & Me.tblUtilizationDataAll_subform.Form.Filter)

By pressing enter on that line the created code will appear and check that for the fieldnames as the message indicates that a tablefield isn't found in tblUtilizationDataAll.

Nic;o)