filter on new record

[banderson]

Hello I have an "add new" button on a form. I would like the button to go to a new record AND filter the form on that new record. The form also has an unbound combo box used to look for and filter to existing records. My code for the "add new" button OnClick Event so far (below) removes any existing filter, and goes to a new record, but I would like to have the form filter so only the new record is shown.

Private Sub btnAddBldg_Click()
On Error GoTo Err_btnAddBldg_Click
 
If Me.FilterOn = True Then
    Me.FilterOn = False
    End If
 
    DoCmd.GoToRecord , , acNewRec
 
Exit_btnAddBldg_Click:
    Exit Sub
 
Err_btnAddBldg_Click:
    MsgBox Err.Description
    Resume Exit_btnAddBldg_Click
 
End Sub
 

I tried adding Me.FilterOn = True after the DoCmd, but this did not work - the form returned to the previous filter.
Any help would be greatly appreciated!
Bridget

[banderson]

Hello I have an "add new" button on a form. I would like the button to go to a new record AND filter the form on that new record. The form also has an unbound combo box used to look for and filter to existing records. My code for the "add new" button OnClick Event so far (below) removes any existing filter, and goes to a new record, but I would like to have the form filter so only the new record is shown.

Private Sub btnAddBldg_Click()
On Error GoTo Err_btnAddBldg_Click
 
If Me.FilterOn = True Then
    Me.FilterOn = False
    End If
 
    DoCmd.GoToRecord , , acNewRec
 
Exit_btnAddBldg_Click:
    Exit Sub
 
Err_btnAddBldg_Click:
    MsgBox Err.Description
    Resume Exit_btnAddBldg_Click
 
End Sub
 

I tried adding Me.FilterOn = True after the DoCmd, but this did not work - the form returned to the previous filter.
Any help would be greatly appreciated!
Bridget

One additional thing...
I tried using "DoCmd.ApplyFilter" as below, but got "syntax error (missing operator) in query expression '[BldgID] = '."
Bridget

Private Sub btnAddBldg_Click()
On Error GoTo Err_btnAddBldg_Click
  Dim strSQL As String
 
If Me.FilterOn = True Then
    Me.FilterOn = False
    End If
 
    DoCmd.GoToRecord , , acNewRec
 
  strSQL = "[BldgID] = " & Me![txtBldgID]
 
  DoCmd.ApplyFilter wherecondition:=strSQL
 
Exit_btnAddBldg_Click:
    Exit Sub
 
Err_btnAddBldg_Click:
    MsgBox Err.Description
    Resume Exit_btnAddBldg_Click
 
End Sub
 

[FishVal]

Hi, Bridget.

Form has a special property for that - DataEntry. When set to True in design view or via VBA code it allows user to enter new record only, the rest records are invisible.

[banderson]

Hi, Bridget.

Form has a special property for that - DataEntry. When set to True in design view or via VBA code it allows user to enter new record only, the rest records are invisible.

Hi FishVal,
Wow -- such a quick response! thank you. I set Data Entry to "yes" in my form and now when my form opens, it opens to a new record, which is great. However, I would also like to set Data Entry to true / yes when clicking the "Add New" button on the form when its already open. Do you have any pointers on code to make this happen?

Thanks again!
Bridget

[banderson]

Hi FishVal,
Wow -- such a quick response! thank you. I set Data Entry to "yes" in my form and now when my form opens, it opens to a new record, which is great. However, I would also like to set Data Entry to true / yes when clicking the "Add New" button on the form when its already open. Do you have any pointers on code to make this happen?

Thanks again!
Bridget

OK, I replied before playing enough with it. After the DoCmd I added a Me.DataEntry line of code as below:

DoCmd.GoToRecord , , acNewRec
Me.DataEntry = true
 

and it seems to work well!

Thanks again,
Bridget

[FishVal]

You are welcome, Bridget.

Best regards,
Fish