Results 1 to 2 of 2
Thread: Append Query / Form (97)
2005-02-07, 21:09 #1
- 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?
2005-02-07, 21:23 #2
- Join Date
- Mar 2002
- Thanked 30 Times in 30 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
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: