Update table from subform default value

[karlspharm]

I'm using Access to track property assigned to personnel. The problem that I've encountered has to do with marking the property as "Surplus" when it is no longer used.

I have a surplus form (frmSurplus) that has an imbedded subform (frmSurplusSubForm). frmSurplus identifies the group that is disposing of the property, and frmSurplusSubForm is a data sheet view of multiple pieces of property being disposed of.

I would like a control button to pass back new information (date surplussed, condition of property, etc.) to the original property assignment table AND update the person assigned to "SURPLUSSED".

frmSurplus is bound to tblSurplussed, and contains only the information pertaining to the act of disposing of the property. frmSurplusSubForm is bound to the table of property assignments (tblPropertyAssignments) which has all of the specifics about each piece of property, including the assignee.

I know how to update tblPropertyAssignments with data added via frmSurplus/frmSurplusSubForm, but want to also update the name of the person assigned to the property to "SURPLUSSED" without the user having to select "SURPLUSSED" from a list. Any and all suggestions are appreciated!

[JConsulting]

I'm using Access to track property assigned to personnel. The problem that I've encountered has to do with marking the property as "Surplus" when it is no longer used.

I have a surplus form (frmSurplus) that has an imbedded subform (frmSurplusSubForm). frmSurplus identifies the group that is disposing of the property, and frmSurplusSubForm is a data sheet view of multiple pieces of property being disposed of.

I would like a control button to pass back new information (date surplussed, condition of property, etc.) to the original property assignment table AND update the person assigned to "SURPLUSSED".

frmSurplus is bound to tblSurplussed, and contains only the information pertaining to the act of disposing of the property. frmSurplusSubForm is bound to the table of property assignments (tblPropertyAssignments) which has all of the specifics about each piece of property, including the assignee.

I know how to update tblPropertyAssignments with data added via frmSurplus/frmSurplusSubForm, but want to also update the name of the person assigned to the property to "SURPLUSSED" without the user having to select "SURPLUSSED" from a list. Any and all suggestions are appreciated!

if there is an event you feel is appropriate, you can run an insert statement to chang the name.

 
currentdb.execute "Update yourtable set [name] = '" & me.namefield & "';"
 
 

something like that?
J

[karlspharm]

if there is an event you feel is appropriate, you can run an insert statement to chang the name.

 
currentdb.execute "Update yourtable set [name] = '" & me.namefield & "';"
 
 

something like that?
J

Thanks for the quick reply!
I've been hammering on it, but no luck yet. The above code would appear to update the Name field for ALL entries. I would like to update only those records added to the Surplus form (each unique record identified by the property serial number).

This is some code that I've tried that results in "Runtime error 438 - Object doesn't support this property or method".

Dim strSQL As String
 
strSQL = " UPDATE tblPropertyAssignments " & _
"[Name] = '" & [Forms]![frmSurplus]![frmSurplusSubForm].txtName & "'," & _
"[User Name] = '" & [Forms]![frmSurplus]![frmSurplusSubform].txtUserName & "'," & _
"WHERE [SerialNumber] = " & [Forms]![frmSurplus]![frmSurplusSubForm].txtSerialNumber & ";"
 
DoCmd.SetWarnings False
CurrentDb.Execute strSQL, dbFailOnError
DoCmd.SetWarnings True
 
End Sub 
 

[JConsulting]

Thanks for the quick reply!
I've been hammering on it, but no luck yet. The above code would appear to update the Name field for ALL entries. I would like to update only those records added to the Surplus form (each unique record identified by the property serial number).

This is some code that I've tried that results in "Runtime error 438 - Object doesn't support this property or method".

Dim strSQL As String
 
strSQL = " UPDATE tblPropertyAssignments " & _
"[Name] = '" & [Forms]![frmSurplus]![frmSurplusSubForm].txtName & "'," & _
"[User Name] = '" & [Forms]![frmSurplus]![frmSurplusSubform].txtUserName & "'," & _
"WHERE [SerialNumber] = " & [Forms]![frmSurplus]![frmSurplusSubForm].txtSerialNumber & ";"
 
DoCmd.SetWarnings False
CurrentDb.Execute strSQL, dbFailOnError
DoCmd.SetWarnings True
 
End Sub 
 

give this a shot

Dim strSQL As String
 
strSQL = " UPDATE tblPropertyAssignments " & _
"[Name] = '" & [Forms]![frmSurplus].Form.[frmSurplusSubForm].txtName & "'," & _
"[User Name] = '" & [Forms]![frmSurplus].Form.[frmSurplusSubForm].txtUserName & "'," & _
"WHERE [SerialNumber] = " & [Forms]![frmSurplus].Form.[frmSurplusSubForm].txtSerialNumber & ";"
 
DoCmd.SetWarnings False '<---You don't need this or the True part using execute
CurrentDb.Execute strSQL, dbFailOnError
DoCmd.SetWarnings True  '<---you don't need this
 

[JConsulting]

what event are you running this off of?

[karlspharm]

what event are you running this off of?

This is from an On Click procedure.

I tried the code, but I get the same error.

[JConsulting]

This is from an On Click procedure.

I tried the code, but I get the same error.

I believe you would want to run this when the record on the subform has been updated right?

So wouldn't that put this code on the subform's After_Update event?

If you do that, you can change the code to reference your fields directly.

ex. me.myfield instead of the form!mainform.subform.form...etc...
J

[JConsulting]

I believe you would want to run this when the record on the subform has been updated right?

So wouldn't that put this code on the subform's After_Update event?

If you do that, you can change the code to reference your fields directly.

ex. me.myfield instead of the form!mainform.subform.form...etc...
J

also, remove the dbFailOnError from your execute statement.
J

you are also missing your SET statement in this

strSQL = " UPDATE tblPropertyAssignments set " & _
"[Name] = '" & [Forms]![frmSurplus].Form.[frmSurplusSubForm].txtName & "'," & _
"[User Name] = '" & [Forms]![frmSurplus].Form.[frmSurplusSubForm].txtUserName & "'," & _
"WHERE [SerialNumber] = " & [Forms]![frmSurplus].Form.[frmSurplusSubForm].txtSerialNumber & ";"

[karlspharm]

also, remove the dbFailOnError from your execute statement.
J

you are also missing your SET statement in this

strSQL = " UPDATE tblPropertyAssignments set " & _
"[Name] = '" & [Forms]![frmSurplus].Form.[frmSurplusSubForm].txtName & "'," & _
"[User Name] = '" & [Forms]![frmSurplus].Form.[frmSurplusSubForm].txtUserName & "'," & _
"WHERE [SerialNumber] = " & [Forms]![frmSurplus].Form.[frmSurplusSubForm].txtSerialNumber & ";"

Thank you again for your input. I'll try the changes and post the results.

[karlspharm]

Thank you again for your input. I'll try the changes and post the results.

No luck yet in getting the code to work. I've put together an update query that will take care of passing the new value back to the existing record, but have another question. is it posible to simultaneously update multiple records listed on a subform? The subform that I have displays each record in a datasheet view.

[JConsulting]

No luck yet in getting the code to work. I've put together an update query that will take care of passing the new value back to the existing record, but have another question. is it posible to simultaneously update multiple records listed on a subform? The subform that I have displays each record in a datasheet view.

To update multiple records, you just have to create the criteria

example

PK...FK...Name....Color.....City............Day
1.....2......bob.......green....hometown.....tuesd ay
2.....2......sam......white.....Dallas..........mo nday

if you have an update statement like

update Day from yourtable set Day = 'Wednesday' where FK=2

Any record with a FK = 2 will have it's day set to Wednesday.

J