#1
  1. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Aug 2012
    Posts
    28
    Rep Power
    0

    Button on Form needs VB to fill in something in another Table


    I've got a form the has a button that when clicked should send an email (this part works fine) and then on exit, matches a key field and finds it in a table and then makes a yes/no field "yes". I need help figuring out the VB code to make the "yes" part happen, please and thank you.

    Ps. this field I want checked in the parent table is here on my form but since the form runs on a query (table/table/query), I can't update anything even when I change the properties to Dynaset (Inconsistent Updates)

    Form Name: frmHeaderInfo-Edit
    Table Name: tbl1HeaderInfo
    Table Field Name shared with Form = PRNumTemp
    Table Field Name needing changed to "Yes" = SubmitYesNo
  2. #2
  3. Contributing User
    ASP Adventurer (500 - 999 posts)

    Join Date
    Jun 2011
    Location
    South Jersey
    Posts
    544
    Rep Power
    58
    One simple method is to create an update query based on the table that holds the data you want to change. You need two fields. The key will be the first. use criteria that references the key on your form. 2nd field will be your checkbox. update to yes. just add the command to run the query to your vb code and you'll update your yes/no field. Turn the warnings off so that the user is not prompted to up update it everytime they push the button. Very simple vb code

    Code:
    Docmd.setwarnings false
    docmd.openquery "UpdateSubmitYesNo"
    Docmd.Setwarnings true
    Last edited by meratigoerr; November 28th, 2012 at 05:37 PM.
  4. #3
  5. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Aug 2012
    Posts
    28
    Rep Power
    0
    Thank you for your time. I created the query as you suggested, but I named it "14qryUpdateSubmitYesNo". I inserted your code (with the correct query name) and it didn't do what I had hoped. I want the record I'm looking at on the form to updated on the query (this DB is for several users, so I was hoping it could be updated behind the scenes), so I need the code to find that specific record and update the "SubmitYesNo" field to "Yes/True"?
  6. #4
  7. Contributing User
    ASP Adventurer (500 - 999 posts)

    Join Date
    Jun 2011
    Location
    South Jersey
    Posts
    544
    Rep Power
    58
    it didn't do what I had hoped.
    Do you get an error? Ifit is doing something, what is it doing? Can you post the sql for the query along with the code you're using to call it?
  8. #5
  9. Moderator
    ASP Super Genius (4500 - 4999 posts)

    Join Date
    Apr 2009
    Location
    The Great Land
    Posts
    4,618
    Rep Power
    649
    Alternative to saved Access query, VBA code executes SQL action (SetWarnings not needed):

    CurrentDb.Execute "UPDATE tablename Set fieldname=True WHERE ID=" & Me.ID

    Otherwise, post the sql statement of your saved query object and code (macro or VBA?).
    Last edited by June7; November 29th, 2012 at 02:49 PM.
    I like to know if my suggestion helped you. One way to let me know is by clicking star at bottom left corner of post.
    Debug!Debug!Debug!http://www.cpearson.com/excel/debug.htm
  10. #6
  11. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Aug 2012
    Posts
    28
    Rep Power
    0
    Your code ran without error, it just took me to the new query you asked me to create, nothing else. I wanted the SubmitYesNo checkbox of the record on the form to be checked as Yes/True in the query, and I was hoping for the code to do this without the user's involvement/behind the scenes. If this is a little confusing, I would say read my initial post one more time and it should make sense.
  12. #7
  13. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Aug 2012
    Posts
    28
    Rep Power
    0
    June 7,

    Here is the code from what I interpreted from your response, which I'm sure needs tweaking because I'm getting "Expected: end of statement" and the word "Set" is highlighted.

    CurrentDb.Execute "tbl1HeaderInfo" Set me.SubmitYesNo= True Where "PRNumTemp = '" & Me.PRNumTemp & "'"
  14. #8
  15. Moderator
    ASP Super Genius (4500 - 4999 posts)

    Join Date
    Apr 2009
    Location
    The Great Land
    Posts
    4,618
    Rep Power
    649
    Use my example syntax and replace the generic table and field references in my example with yours. Do not change the quote marks positions.

    CurrentDb.Execute "UPDATE tbl1HeaderInfo SET SubmitYesNo=True WHERE PRNumTemp = '" & Me.PRNumTemp & "'"
  16. #9
  17. Contributing User
    ASP Adventurer (500 - 999 posts)

    Join Date
    Jun 2011
    Location
    South Jersey
    Posts
    544
    Rep Power
    58
    Originally Posted by kableland
    Your code ran without error, it just took me to the new query you asked me to create, nothing else. I wanted the SubmitYesNo checkbox of the record on the form to be checked as Yes/True in the query, and I was hoping for the code to do this without the user's involvement/behind the scenes. If this is a little confusing, I would say read my initial post one more time and it should make sense.
    The query should be an update query, not a select query. An update query would not show you any results, just updated the fields as intended, without input from the user.

    Code should look like;
    Code:
    UPDATE tbl1HeaderInfo SET tbl1HeaderInfo.SubmitYesNo = True
    WHERE (((tbl1HeaderInfo.PRNumTemp)=[Forms]![frmHeaderInfo-Edit]![PRNumTemp]));
    note the 'UPDATE' at the begining and not 'Select'

    this is pretty much what June7 suggested as well, except this is a saved query, while his was in VBA.

    my other two cents:
    Avoid using '-' in form, table, or field names. one problem specifically is when you copy and past it in access it wants to change '[frmHeaderInfo-Edit]' to '[frmHeaderInfo]-[Edit]'
    Last edited by meratigoerr; November 29th, 2012 at 06:33 PM.

Similar Threads

  1. Access 2010 automaticaly fill in table value from another table
    By jbataille in forum Microsoft Access Help
    Replies: 6
    Last Post: June 14th, 2011, 11:57 AM
  2. Populate table entries when clicked on form button
    By geekster in forum Microsoft Access Help
    Replies: 3
    Last Post: June 4th, 2009, 02:08 AM
  3. Adjust Button size to fill cell in table
    By Incogneato in forum HTML, JavaScript And CSS Help
    Replies: 1
    Last Post: July 18th, 2007, 05:47 AM
  4. Editing Table Data from within a Form using a button
    By mattrsa in forum Microsoft Access Help
    Replies: 3
    Last Post: June 13th, 2007, 05:05 AM
  5. How to use a combo box to fill form fields from a table
    By Jeffro in forum Microsoft Access Help
    Replies: 1
    Last Post: May 24th, 2004, 09:41 AM

IMN logo majestic logo threadwatch logo seochat tools logo