By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,968 Members | 1,589 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,968 IT Pros & Developers. It's quick & easy.

Update table from subform default value

P: 5
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!
May 8 '07 #1
Share this Question
Share on Google+
10 Replies


JConsulting
Expert 100+
P: 603
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.

Expand|Select|Wrap|Line Numbers
  1.  
  2. currentdb.execute "Update yourtable set [name] = '" & me.namefield & "';"
  3.  
  4.  
something like that?
J
May 8 '07 #2

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

Expand|Select|Wrap|Line Numbers
  1.  
  2. currentdb.execute "Update yourtable set [name] = '" & me.namefield & "';"
  3.  
  4.  
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".

Expand|Select|Wrap|Line Numbers
  1. Dim strSQL As String
  2.  
  3. strSQL = " UPDATE tblPropertyAssignments " & _
  4. "[Name] = '" & [Forms]![frmSurplus]![frmSurplusSubForm].txtName & "'," & _
  5. "[User Name] = '" & [Forms]![frmSurplus]![frmSurplusSubform].txtUserName & "'," & _
  6. "WHERE [SerialNumber] = " & [Forms]![frmSurplus]![frmSurplusSubForm].txtSerialNumber & ";"
  7.  
  8. DoCmd.SetWarnings False
  9. CurrentDb.Execute strSQL, dbFailOnError
  10. DoCmd.SetWarnings True
  11.  
  12. End Sub 
  13.  
May 8 '07 #3

JConsulting
Expert 100+
P: 603
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".

Expand|Select|Wrap|Line Numbers
  1. Dim strSQL As String
  2.  
  3. strSQL = " UPDATE tblPropertyAssignments " & _
  4. "[Name] = '" & [Forms]![frmSurplus]![frmSurplusSubForm].txtName & "'," & _
  5. "[User Name] = '" & [Forms]![frmSurplus]![frmSurplusSubform].txtUserName & "'," & _
  6. "WHERE [SerialNumber] = " & [Forms]![frmSurplus]![frmSurplusSubForm].txtSerialNumber & ";"
  7.  
  8. DoCmd.SetWarnings False
  9. CurrentDb.Execute strSQL, dbFailOnError
  10. DoCmd.SetWarnings True
  11.  
  12. End Sub 
  13.  

give this a shot

Expand|Select|Wrap|Line Numbers
  1. Dim strSQL As String
  2.  
  3. strSQL = " UPDATE tblPropertyAssignments " & _
  4. "[Name] = '" & [Forms]![frmSurplus].Form.[frmSurplusSubForm].txtName & "'," & _
  5. "[User Name] = '" & [Forms]![frmSurplus].Form.[frmSurplusSubForm].txtUserName & "'," & _
  6. "WHERE [SerialNumber] = " & [Forms]![frmSurplus].Form.[frmSurplusSubForm].txtSerialNumber & ";"
  7.  
  8. DoCmd.SetWarnings False '<---You don't need this or the True part using execute
  9. CurrentDb.Execute strSQL, dbFailOnError
  10. DoCmd.SetWarnings True  '<---you don't need this
  11.  
May 8 '07 #4

JConsulting
Expert 100+
P: 603
what event are you running this off of?
May 8 '07 #5

P: 5
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.
May 8 '07 #6

JConsulting
Expert 100+
P: 603
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
May 8 '07 #7

JConsulting
Expert 100+
P: 603
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 & ";"
May 8 '07 #8

P: 5
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.
May 9 '07 #9

P: 5
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.
May 10 '07 #10

JConsulting
Expert 100+
P: 603
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
May 10 '07 #11

Post your reply

Sign in to post your reply or Sign up for a free account.