Ad

How To Save A Textbox Query Result To A Database After Editing It? - MS Access

- 1 answer

Table of Documents:

Company    Document       Status        Notes:
A            1            Submission    Submitted on 12-12-12
A            1            Review        Reviewed on 12-12-13
A            1            Adopted       Adopted on 12-12-14
A            2            Submission    Submitted on 01-01-01
A            2            Review        Reviewed on 01-01-02
A            2            Adopted       Adopted on 01-01-03

I have two combo boxes on a form; first combo box allows me to select the document and is linked up to the first query: e.g. Selecting '1' would return:

Company    Document       Status        Notes:
A            1            Submission    Submitted on 12-12-12
A            1            Review        Reviewed on 12-12-13
A            1            Adopted       Adopted on 12-12-14

The second combo box then allows me to select its' status and is linked up to the second query; the second query is used to filter the results from the first query: e.g. Selecting 'Adopted' would return:

Company    Document       Status        Notes:
A            1            Adopted       Adopted on 12-12-14

Lastly, I have a text box that displays the 'Notes' field based on your selection from both of the combo boxes. E.g. Selecting '1', and then 'Adopted' would display this in the text box:

'Adopted on 12-12-14'

I can edit the text (Note) that appears in the text box, but I want to assign a function to a button, that when clicked; will replace the 'Notes' field in the database with the text that is currently in the text box.

I've been searching for a while and can't seem to get anything working; I know the code will go in the click_event properties of the button; but I'm not sure what code to use exactly. In it's most standard form, I want this to happen when the button is clicked:

UPDATE Document(Table) SET Notes(Field)=textboxname.value

Any ideas?

Ad

Answer

The code below will allow you to enter text, then will execute the sql command to update notes in a record.

  1. You will need to place this code inside your button click.
  2. You need to change the names of the comboBoxes in the sql to use the names you have.
  3. I assume there is not a unique key to use instead of 'document' and 'status'?
  4. I certainly hope the document numbers never repeat across companies, else you will update multiple rows!

    Private Sub cmdAddNote_Click()
    Dim strNote As String
    Dim strSQL  As String
    Dim dbs     As DAO.Database
    
    strNote = InputBox("Enter notes below, then click OK:", "Add Notes")
    If strNote = "" Then
        ' Cancel
    Else
    
        strSQL = "UPDATE [Documents] SET [Documents].Notes = '" & strNote & "' " & _
                 "WHERE ((([Documents].[Document])= '" & cbo1 & "') AND (([Documents].[Status])='" & cbo2 & "'));"
        Set dbs = CurrentDb
        dbs.Execute strSQL
        dbs.Close
        Set dbs = Nothing
    End If
    End Sub
    
Ad
source: stackoverflow.com
Ad