Results 1 to 2 of 2
  1. #1
    New Lounger
    Join Date
    Dec 2004
    Thanked 0 Times in 0 Posts

    Append Query / Form (97)

    I want to create a form that has a field which will append data to all blank field's in a certain column. For Example.. I have ID's as a primary key, and I have some dept names that are blank. I don't want to just make a new append query every time I want to fill them all in. I want to make a form that will append all blank dept fields with whatever I chose to type in the field. I am sort of thinking it will be an unbound field with a command button. Am I on the right track? And if so what is the event procedure?
    Thanks guys!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 28 Times in 28 Posts

    Re: Append Query / Form (97)

    You would use an unbound form (i.e. its Record Source is blank) with an unbound text field (its Control Source is blank), and an OK button.
    You can make your append query dynamic, i.e. use the value from the form:
    - Create a query based on your table.
    - Add the Department field to the query grid.
    - Enter Is Null in the Criteria line, to select only records whose Department field is blank.
    - Select Query | Update Query to change the query to an update query.
    - Enter [Forms]![frmNewValue]![txtDepartment] in the Update to line, where frmNewValue is the name of the form, and txtDepartment is the name of the text box.
    - Save this query as (say) qryUpdateDepartment.
    - Make the code for the On Click event of the command button look like this (cmdOK is the name of the button):

    Private Sub cmdOK_Click()
    ' Test whether user entered a department
    If IsNull(Me.txtDepartment) Then
    MsgBox "Please enter a department name.", vbExclamation
    Exit Sub
    End If

    DoCmd.OpenQuery "qryUpdateDepartment"
    End Sub

    When you click the button, you will be warned that you are about to execute an action query, then that you are about to update n records. This is useful for testing - you can see that the code does something. In the final version, you'll probably want to suppress these warnings. You can do it like this:

    DoCmd.SetWarnings False
    DoCmd.OpenQuery "qryUpdateDepartment"
    DoCmd.SetWarnings True


    CurrentDb.Execute "qryUpdateDepartment"

Posting Permissions

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