Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Oct 2009
    Posts
    9

    Unanswered: Using results in a text box from a search.

    My database has 2 tables. I made a search form that locates a record and displays the information I need from that record. Now, I want to take it a step further and have a command button that I can press that will change certain fields in the record.

    For instance, I search for a Number and it shows the first name, last name, etc. associated with that record. I want to be able to hit a button that will change the last name to a different value. I'm having trouble figuring out how to declare the value from the Number text box and then use SQL to locate the record and change some fields.

    I'm not a database person but I've done a lot of manipulating code as a Sys Admin. I'm trying to create this from scratch so forgive my ignorance if this seems like an easy thing to do or if I'm not providing enough info. Thanks!!

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    It could be something like:
    Code:
        Dim strSQL As String
        
        strSQL = "UPDATE <Table> SET <Table>.<Column> = '<Something>' WHERE <Table>.<Number> = <Value>"
        CurrentDb.Execute strSQL
    Have a nice day!

  3. #3
    Join Date
    Oct 2009
    Posts
    9
    This is the code how I have it now:

    Private Sub cmdUndeny_Click()
    Dim strsql

    strsql = "UPDATE dbo_WORK_LOG SET dbo_WORK_LOG.LOG_TYPE = 'Nonform' WHERE dbo_WORK_LOG.CASE_ID = txtSRNumber"
    CurrentDb.Execute strsql
    MsgBox ("It worked!")
    End Sub

    I get an error when I try to run it:
    Run-time error '3061':
    Too few parameters. Expected 1

    When it opens the debugger it highlights the "CurrentDb.Execute strsql" in yellow. That line should be in the Private Sub with everything else, correct?

    In the code above, the txtSRNumber is the text box in the form that is populated after the search is done. I want the command button, when clicked, to find the txtSRNumber record depending on the value in the text box and change the LOG_TYPE column of that record to a different value. Also, I'm not sure if it matters, but the column that the txtSRNumber is in is not the primary key but it is a unique number.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Like this:
    Code:
    strsql = "UPDATE dbo_WORK_LOG SET dbo_WORK_LOG.LOG_TYPE = 'Nonform' WHERE dbo_WORK_LOG.CASE_ID = " & Me.txtSRNumber.Value
    Have a nice day!

  5. #5
    Join Date
    Oct 2009
    Posts
    9
    That worked perfectly! Thank you for taking the time to help out.

    If I want to update information on 2 tables, do I just add another strsql = line using the same code and just put in the other table name and fields, etc.?

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    That's it: you can use this technique to update (or delete) records in any table, local or attached. With an attached table it must be updatable (i.e. have an IDENTITY column). It also works with a query if this query is updatable too.

    Be aware however that if the recordset of the form from which you call this method is based on the table you update, the change will not be immediately visible in the form. You might want to use Me.Requery to update the form's recordset and make the changes visible in the form.
    Have a nice day!

  7. #7
    Join Date
    Oct 2009
    Posts
    9
    I'm having a little problem with updating all fields. I have 3 fields that need to be updated when I click the button. The code is below:

    Private Sub cmdUndeny_Click()
    Dim strsql

    strsql = "UPDATE dbo_SEC_FORM_TRACKING SET dbo_SEC_FORM_TRACKING.FORM_STATUS = 'authorized' WHERE dbo_SEC_FORM_TRACKING.TRACKING_ID = " & Me.txtSRNumber.Value
    strsql = "UPDATE dbo_SEC_FORM_TRACKING SET dbo_SEC_FORM_TRACKING.FORM_COMPLETE_DT = Null WHERE dbo_SEC_FORM_TRACKING.TRACKING_ID = " & Me.txtSRNumber.Value
    strsql = "UPDATE dbo_SEC_FORM_TRACKING SET dbo_SEC_FORM_TRACKING.FORM_DENY_CODE = Null WHERE dbo_SEC_FORM_TRACKING.TRACKING_ID = " & Me.txtSRNumber.Value
    CurrentDb.Execute strsql
    MsgBox ("It worked!")

    End Sub

    This code will update the 1st line and the 3rd line, but no matter which one I put in the middle of the 3 lines, that field does not updated.

    For instance, the above code will change the FORM_STATUS field to "authorized" and the FORM_DENY_CODE to "null". But the FORM_COMPLETE_DT in the second strsql line doesn't update the record.

    I mixed up the lines and put them in a different order and whichever line is in the middle will not update the pertaining field, but the 1st and 3rd will. Any ideas?

  8. #8
    Join Date
    Aug 2009
    Location
    Up Nort' Wi
    Posts
    140
    You're doing nothing but reassigning the strsql value 3 times before executing the strsql. You need to either execute the line before assigning another value to it, or, create three different variables (strsql1, strsql2, etc etc), assign a value to each and then execute them.

    Sam, hth
    Good, fast, cheap...Pick 2.

  9. #9
    Join Date
    Oct 2009
    Posts
    9
    Ok, I got it going. Wasn't thinking with the right logic with that variable I guess. Thanks for everything. It's doing what I need now. This will help make one process so much easier. :-)

  10. #10
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I want to know how it is you reached the conclusion that the first one was working!! lol
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  11. #11
    Join Date
    Oct 2009
    Posts
    9
    The table has over 70k records so I had a big test bed of data to use. Every time I ran it, it updated the fields referenced in the 1st and 3rd strsql lines. I rearranged those lines about 20 times and checked what fields changed and it was always the 1st and 3rd. I'm assuming by your comment that shouldn't have happened? :-)

  12. #12
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Correct.

    That code could not possibly update the first one. Only the last one.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  13. #13
    Join Date
    Oct 2009
    Posts
    9
    Hmmm, I'll look through the rest of the code to make sure nothing is set to update without me knowing. I don't like knowing it was doing something it shouldn't have. Thanks for the info.

    So, it's amazing what a Manager can do when they find out you are doing something to make your job easier....sigh...

    I have something I need to change in my form now. The search form I created is from here:

    Access: Create a form where you can search any text field in a table in Access 2003/XP/2000/97

    Is there a way to do this search without the Search button opening up a new form? For instance, in the example from the link, if the form is empty and I enter in a value in the Customer ID field, is there a way to click the Search button and have it populate all the fields in the form without it opening up a new Search form? I'd like to skip having a Search form altogether and just have it populate the fields based off of one text box.

  14. #14
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I'm sure you could populate your fields with a bunch of DLookups, using a search textbox that you put an ID into.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  15. #15
    Join Date
    Aug 2009
    Location
    Up Nort' Wi
    Posts
    140
    I've got a few forms where I'll have the user select an object from a combobox. I'll then open the table and populate the fields using the record specified by the combobox selection. I use this typically in an unbound edit form.

    hth

    Sam, I can post a code snippet if I wasn't clear enough.
    Good, fast, cheap...Pick 2.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •