Search a Table via a form

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi I am hoping that this is quite simple.

I have a main table called "Issues", which is linked to another table called
"Issue updates" - so a single record (issue) in the main table might have 10
related records in the sub table.

Using the wizard I have created a form and sub form to show all this
information

Next however I would like a form where I can enter search critera, from a
drop down box (for instance an issue name) or maybe all issues entered
between one day and another. Once I hit a macro button I would like the
relevent record to be displayed in the same form ( ie the form I have already
created using a wizard would be a subform of the Search Form.

I have reasonable vb excel skills so I understand a little coding but I need
to know how to start this / structure etc

Any help much appreciated
Thanks
 
The simplest answer I know to this is to use Filter By Form.
Click the Filter By Form button on the toolbar, and every textbox on your
main form becomes available to receive filter criteria.
After you've entered your criteria, click Apply Filter on the toolbar.
You can return to the full data set by clicking Remove Filter, but remember
that your filter will be stored and available for re-use until you clear the
grid.
 
There's not a lot of point having the form as a subform. If you just save
your existing form under a new name you can then simply add controls to it,
e.g. in its header for searching. To search by Issue add a combo box,
cboFindIssue say, with a RowSource along these lines:

SELECT Issue
FROM Issues
ORDER BY Issue;

Put a command button next to it, labelled something like 'Go' and in the
button'sClick event procedure put some code which navigates to the selected
Issue record by synchronizing the form's Bookmark with the Bookmark of the
record found in a Clone of the form's recordset, e.g.

Dim rst As Object

Set rst = Me.Recordset.Clone

rst.FindFirst "Issue = """ & cboFindIssue & """"
Me.Bookmark = rst.Bookmark

where Issue is a text field in the form's underlying recordset.

To find records within a date range a different approach is needed as in
this case you want to filter the form rather than navigate to a particular
record, so add two text boxes in which to enter the dates (or you could use
two combo boxes drawing their lists from a Calendar table, i.e. a table with
a column of all dates over a range of a number of years (tip: produce a
column of dates in Excel by serially filling down and import it into Access).
Whichever way you do it the code for a 'Go' button associated with these
controls would be the same. If the controls were text boxes txtStartDate and
txtEndDate then the code for its Click event would go like this:

Dim strCriteria As String

' make sure both dates are entered
If IsNull(Me.txtStarDate) Or IsNull(Me.txtEndDate) Then
MsgBox "Please enter both dates.", vbExclamation, "Invalid Operation"
Else
strCriteria = "IssueDate >= #" & _
Format(txtStartDate,"mm/dd/yyyy") & "#" & _
" AND IssueDate < " & _
Format(txtEndDate,"mm/dd/yyyy") & "# + 1"
Me.Filter = strCriteria
Me.FilterOn = True
End If

where IssueDate is a date/time field in the form's underlying recordset.

To clear the filter you can either use the built in toolbar button, or add a
'Show All' button to the form with the following in its Click event procedure:

Me.FilterOn = False
 
Back
Top