filter form using NewData

[Mr_Adams]

I use the folloing code to add a new record to a table if no record exist. Then the form used for entry into the table should open on that record so the user can complete all required fileds. But right now the form opens up with record number 1

Is there a way to use Filter with along with the "DoCmd.OpenForm "Department Maintenance"" and the "NewData" (from code below) so that the new record opens?
If not could someone show me how I can accomplish this?

Here is the code

Private Sub DEPARTMENT_NotInList(NewData As String, Response As Integer)
        ' Add a new category by typing a name in Department combobox.
    Dim intNewDepartment As Integer, strTitle As String, intMsgDialog As Integer
    Dim strMsg As String, rst As Recordset, Db As Database, newRst As Recordset
    Dim DepartmentID As Long
        ' Display message box asking if user wants to add a new Department category.
    strTitle = "New Department"
    strMsg = "'" & NewData & "' is not in the Department list.  "
    strMsg = strMsg & "Would you like to add it?"
    intMsgDialog = vbYesNo + vbQuestion + vbDefaultButton1
    intNewDepartment = MsgBox(strMsg, intMsgDialog, strTitle)
        If intNewDepartment = vbNo Then
            Response = acDataErrDisplay
                Else
Set Db = CurrentDb()
    Set rst = Db.OpenRecordset("Departments")
    rst.AddNew
    rst![Department Name] = NewData
    rst.Update
    Response = acDataErrAdded
    rst.Close
                    'Open the Department Maintenance form
                DoCmd.OpenForm "Department Maintenance", acNormal, , , acFormEdit, acDialog
                End If
End Sub

 

[Mr_Adams]

I tried

 'Open the Department Maintenance form
                DoCmd.OpenForm "Department Maintenance", acNormal, , , acFormEdit, acDialog
                Me.Filter = "[Department Name] =" & NewData
                End If

"Department Name" being the field name in the table that the form is base on

But still no luck

Please Help

 

[mdmilner]

You have to turn the filter on first after setting it.

This is a snip out of a form I built that uses the contents of a combo box to filter the records displayed by a subform. The visible option is just because mine stays invisible until the user selects something valid.

Me.sfmSpecDetail.Form.Filter = "[SpecID]=" & Me.cboSelectDetail.Value
Me.sfmSpecDetail.Form.FilterOn = True
Me.sfmSpecDetail.Form.Visible = True

Mike

 

[Mr_Adams]

I have tried

Me.sfmSpecDetail.Form.Filter = "[Department Name]=" & NewData 
Me.sfmSpecDetail.Form.FilterOn = True

error on sfmSpecDetail

Me.Form.Filter = "[Department Name]=" & NewData
Me.Form.FilterOn = True

Me.Filter = "[Department Name]=" & NewData
Me.FilterOn = True

and the form still opens with the the first record
I can't for the life of me figure out why.

 

[mdmilner]

*Grin* you forgot to change my form_names.
Sorry, thought about that after I pasted it.

Take a peek at this:

http://www.mrexcel.com/board2/viewtopic.php?t=87674&highlight=

Top section of the code actually has the snippet in it that I quoted above.
As background...

This is a form with a subform.
Form uses a combobox that uses a SQL query as it's recordsource (SELECT DISTINCT) - after selecting the value (afterupdate) it unhides the subform that was loaded initially) and turns on the filter function. The parameter limits it to the value selected and it sorts the recordsource for the subform in one shot. It then makes it visible.

One problem I recall encountering years ago is that forms don't always refresh on the screen unless you explictly tell them to do so with an action that requires it. I believe making it Visible is one - so does using the Requery or Refresh methods.

You could try one of the three...making it .Visible False, setting the filter, then .Visible True. Could also Requery the form.

Mike

 

[Mr_Adams]

I still learning so I'm sorry if I am misunderstandiong what you said.
I'm familiar with SQL or how to requery in this instance, so I tried .visible and the form still opens with the first record.

Private Sub DEPARTMENT_NotInList(NewData As String, Response As Integer)
        ' Add a new category by typing a name in Department combobox.
    Dim intNewDepartment As Integer, strTitle As String, intMsgDialog As Integer
    Dim strMsg As String, rst As Recordset, Db As Database, newRst As Recordset
    Dim DepartmentID As Long
        ' Display message box asking if user wants to add a new Department category.
    strTitle = "New Department"
    strMsg = "'" & NewData & "' is not in the Department list.  "
    strMsg = strMsg & "Would you like to add it?"
    intMsgDialog = vbYesNo + vbQuestion + vbDefaultButton1
    intNewDepartment = MsgBox(strMsg, intMsgDialog, strTitle)
        If intNewDepartment = vbNo Then
            Response = acDataErrDisplay
                Else
Set Db = CurrentDb()
    Set rst = Db.OpenRecordset("Departments")
    rst.AddNew
    rst![Department Name] = NewData
    rst.Update
    Response = acDataErrAdded
    rst.Close
                    'Open the Department Maintenance form
                DoCmd.OpenForm "Department Maintenance", acNormal, , , acFormEdit, acDialog
                Me.Form.Filter = "[Department Name]=" & NewData
                Me.Form.Visible = False
                Me.Form.FilterOn = True
                Me.Form.Visible = True
                End If
End Sub

 

[Mr_Adams]

OK I can't seem to get it to work so I just open the form in ADD mode instead of EDIT and removed the portion of the code that added the name to the table.
I figured I would have the user add the new vendor then just Requery on GOT FOCUS when they close the sub form.
But I can't even get that to work.
I tried Requery on the combobox gotfoucs and I tried requery on the form gotfocus. Niether will work. I even tried saverecord before requery (as shown below) and save the record when the sub form close (also seen below)
Is there anyone out there that can help me figure this out please.

Thank you

Private Sub Form_GotFocus()
DoCmd.RunCommand acCmdSaveRecord
DoEvents
DEPARTMENT.Requery
DoEvents
End Sub

Private Sub Form_Close()
DoCmd.RunCommand acCmdSaveRecord
End Sub

Private Sub DEPARTMENT_NotInList(NewData As String, Response As Integer)
        ' Add a new category by typing a name in Department combobox.
    Dim intNewDepartment As Integer, strTitle As String, intMsgDialog As Integer
    Dim strMsg As String, rst As Recordset, Db As Database, newRst As Recordset
    Dim DepartmentID As Long
        ' Display message box asking if user wants to add a new Department category.
    strTitle = "New Department"
    strMsg = "'" & NewData & "' is not in the Department list.  "
    strMsg = strMsg & "Would you like to add it?"
    intMsgDialog = vbYesNo + vbQuestion + vbDefaultButton1
    intNewDepartment = MsgBox(strMsg, intMsgDialog, strTitle)
        If intNewDepartment = vbNo Then
            Response = acDataErrDisplay
                Else
                        'Open the Department Maintenance form
                DoCmd.OpenForm "Department Maintenance", acNormal, , , acFormAdd, acDialog
        End If
End Sub