Add Query Functionality to Existing Form

[aball65]

Hello,

I have a MS Access 2003 risk management form which includes 2 subforms. The form supports adding, updating and deleting risk data in the form's associated tables. When the form opens, it returns all the risk rows in these tables based on a "SELECT *" query. I would like to modify the form so that it can support querying risks based on user specified filtering (e.g., allow a user to query all "OPEN" risks by selecting "OPEN" from the form's risk status combo box). Is there a way to add query-by-filter functionality to my existing form or do I need to create a new form?

Thanks.

 

[Thomko]

You can filter the records in e.g the After_Update event of your combobox.

Something like this

if yourCombobox.Value = "OPEN" then
   Me.Filter = "NameOfYourStatusField = 'OPEN' "
   Me.FilterOn = True
end if

HTH
Thomas

 

[aball65]

Are you suggesting creating a separate form with after_update triggers? The existing form returns all risks so selecting OPEN to query all OPEN risks would update the status of the current risk to OPEN as part of executing the query for all OPEN risks, right?

Thanks

 

[Thomko]

I am not sure if I got the point. Anyhow, you have to differentiate "selecting records by status" from "setting a status for a record". Usually these two steps are done on different forms: a list form with that has filter options and a form for a single record for editing. If you want to do the same on one form, you need two controls, one "Status" control, linked to the table and a "Filter By Status" control, where you can use the After_Update event.

Thomas

 

[aball65]

You answered my question. I'll build two forms. Thanks.

 

[Thomko]

You are welcome.

 

[aball65]

Still struggling with this.

I'm trying to build a read-only query form. I want the form to have:

a. the following combo boxes:

1. First Name (James, John, Brian, Charles, David, Edward)
2. Last Name (Doe, Smith, Jones)

b. a Query button
c. a Clear button
d. the standard record navigation buttons

I want the user to be able to input their query criteria into the form (e.g., Firts Name = John, Last Name = *), hit the Query button and view the queried results within the query form. For example, if this query returned two rows, the form would display the first row (e.g., John Doe) and the user could navigate to the next row (e.g., John Smith) using the standard record navigation buttons.

Can someone help me out? As you can probably tell, I'm an Access noobie.

Thanks

 

[Thomko]

I attached a "quick-hack" DB that shows the concept for you to study. You will have to make changes as drop-downs for first and last name are not the best idea (these fields are not independent).
For a Query button: Call setFilter() from a command button
For a Clear button: set cboFirstNameFilter = "" : cboLastNameFilter="" from another command button

Thomas