Filter and search button not working on form in Access 2007

  • Thread starter Thread starter Smitee2006
  • Start date Start date
S

Smitee2006

I have a form view where users can see a listing of all clients which is fed
by a query called ClientBrf. On this same form is a button that users can
push to be prompted for a client name or a portion of a client's name in
order to search the listing and have it filter down the results. Hitting the
button and leaving the prompt blank will return the full client listing
again. This button no longer works now that I am working in Access 2007.
Anybody have any ideas why this might be? I've included the button's
underlying code for information.

Private Sub FilterClient_Click()
On Error GoTo Err_FilterClient_Click

DoCmd.DoMenuItem acFormBar, acRecordsMenu, 2, , acMenuVer70

Exit_FilterClient_Click:
Exit Sub

Err_FilterClient_Click:
MsgBox Err.Description
Resume Exit_FilterClient_Click

End Sub

Thanks in advance for assistance.
 
Smitee,

The DoMenuItem method has really only been supported since Access 2000 for
backward compatibility, and now with Access 2007 and Access 2010 it is no
longer applicable, as the Ribbon works quite differently than the old menus.

Therefore you might need to re-design this part of your application.

My suggestion would be to put an unbound combobox or textbox on the form,
maybe in the form header, where the user can enter the criteria for the
client they want. And then either a command button, or the After Update
event of the textbox/combobox, use either of these approaches:

Me.Filter = "ClientID=" & Me.NameOfCombobox
Me.FilterOn = True

or...

Me.RecordSource = "SELECT * FROM YourQuery WHERE ClientID=" &
Me.NameOfCombobox

or...

Me.RecordSource = "SELECT * FROM YourQuery WHERE ClientName Like '*" &
Me.NameOfTextbox & "*'"
 
I have a simple database, personnel actions daily log with a split form. It has input fields on the top of the form, the lower half is the datasheet. The data fields are name, ssn, rank, actin type, unit, command, mos, etc, these are input manually. We do however have three other tables, with that data already, and I would like to enter the Soldier's ssn, then, hit the button, and it pulls the rest of the fields from the tables, thus eliminating the need to input the data manually. I am stuck on how to do this, please, can anyone help? Thank you.
 
Back
Top