asked on

Search Criteria Form in MS Access 2010

Ok,

My application is an Access 2010 64-bit database that I have created to track different processes in our project. As part of the database, I created a Search Criteria form. On this form, I have three radio buttons to allow the users to select what form they want to search on. So it is Option 1 = Cases; Option 2 = Requests; and Option 3 = Data Objects

The second unbound text boxes allows the users to fill in either or all of the criteria for Case #, Request ID #, Production Name and/or Data Load ID.

Case # = AA-00004 (two letters, hyphen and 5 digits)
Request # = 88888 (all numbers)
Production Name = String
Data Load ID = 333 (three digits all numbers)

There are two date fields that I want to search only on the Data Objects Form so I would like to allow the users to enter date ranges for the Date Received and Date Available. The user should only be able allowed to enter the dates if they choose the Data Objects radio button. When the search button has been clicked on then open up the records in the form that they picked.

I am very lost in writing code for this form. Thank you for any assistance that anyone can give me. I am trying to finish this database by Tuesday.

Flyster

You can add two text boxes to your form for start_date and end_date and set the visiblity to False. Then add the following code to the on click event of the Data Object radio button:

 Private Sub DataObject_Click()

  If Me.Option94.Value = True Then
    Me.StartDate.Visible = True
    Me.EndDate.Visible = True
    Me.DataObject.SetFocus
  Else
    Me.StartDate.Visible = False
    Me.EndDate.Visible = False
    Me.DataObject.SetFocus
  End If
  
End Sub

Open in new window

Just change the control names to the name of your controls.

Flyster

Scott McDaniel (EE MVE )

I would suggest you not show/hide your controls. UI testing always proves that users really don't like disappearing controls.

You can certainly enable/disable them based on user choice, but the only time you should really hide a control is if that control is only available to certain users and not others (like a Manager can see payroll information, but an Employee could not).

So use this:

Me.StartDate.Enabled = (Me.OptionGroup.Value = 1)
Me.EndDate.Enabled = (Me.OptionGroup.Value = 1)

Assuming we're talking about an Option Group, of course - you'd have to change the value of 1 to match whatever value is assigned to your "Date Load" radio button.

Are you having troubles with the filtering as well? Or were you just having troubles with the UI stuff?

Filtering is fairly straight forward, depending on what you are after. If the form is already open, you can just use the Filter property like this:

Forms("YourFormName").Filter = "SomeField=SomeValue"
Forms("YourFormName").FilterOn = True

Note that you must use fuly qualified filtering delimiters.

For a Text field: "SomeField='MyValue'"
For a Date field: "SomeField=#01/01/1900#"

Numeric and others use no delmiters.

lauriecking0623

ASKER

@LSMConsulting

My thoughts with the date fields is to disable/enable them like you had suggested since I did not want them to disappear at all. I do not care for that either.

Anyways, I have them in a option group. Just three choices. other fields. I have put in other code but it does not work.

I will post the entire code in a few minutes.

ASKER CERTIFIED SOLUTION

Jeffrey Coachman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

lauriecking0623

ASKER

Not exactly what I needed; however, I can change it to my needs.