Jumping around through forms

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

Guest

I have a table of about 8,000 records which the user views through a form. I
need to add a search screen that will display the records in a table like
format, sorted by one of a number of fields where the user can ideally select
the record they want to see from the list and have it brought up.

Any suggestions?
 
Bev,

Use a continuous form in tabular view (all the fields layed out horizontally
on the form). In the header of the form, you will have the column labels.
In the click event of each column that you want to be able to sort by, enter
code that looks similiar to the one below, where the "intNumber" is actually
the name of the control source for the data in that column:

Private Sub intNumber_Label_Click()

SortOrder "intNumber"

End Sub

Then, add a declaration statement to your form:

DIM strSortOrder as string

Finally, add the following subroutine.

Private Sub SortOrder(FieldName)

If Len(strSortOrder) = 0 Then
strSortOrder = "ASC"
ElseIf strSortOrder = "ASC" Then
strSortOrder = "DESC"
Else
strSortOrder = "ASC"
End If

Me.OrderByOn = True
Me.OrderBy = FieldName & " " & strSortOrder
End Sub

Finally, add a command button to the footer of your form, that will open the
other form that will have all of the data you want to be able to edit.

HTH
Dale
 
So you want to create a search form in continuous view, where the user can
select a record to view/edit in another form.

Build a form in tabular view that shows enough fields for the user to select
their record.

In the Form Header section, place some unbound boxes above the columns,
where the user can enter criteria to limit the search results. Add a command
button to read these boxes and create the WHERE clause for the form's
RecordSource. From there, opening the other form is just a matter of:
DoCmd.OpenForm "Form2", WhereCondition = "ClientID = " & Me.ClientID

Initially you can force the form to load with no record, by saving the
RecordSource as:
SELECT * FROM tblClient WHERE (False);

Below is the kind of thing that goes into the Click event of your cmdFilter
command button. It demonstrates the different delimiters for Text fields,
Date fields, and Number/Currency fields. It is constructed so it is very
easy to add as many more of these as you wish.

If you also wanted to let the user sort the form differently, that's just a
matter of changing the ORDER BY clause instead of using the constant.

----------code begins-----------
Private Sub cmdFilter_Click()
Dim strWhere As String
Dim lngLen As Long
Const strcStub = "SELECT tblClient.* FROM tblClient WHERE "
Const strcTail = " ORDER BY Surname, FirstName;"

'Filter a text field.
If Not IsNull(Me.txtFilterSurname) Then
strWhere = strWhere & "([Surname] = """ & Me.txtFilterSurname & """)
AND "
End If

'Filter a date field
If Not IsNull(Me.txtFilterDueDate) Then
strWhere = strWhere & ([DueDate] = #" & Format(Me.txtFilterDueDate,
"mm/dd/yyyy") & "#) AND "
End If

'Filter a Number field
If Not IsNull(Me.txtFilterAmount) Then
strWhere = strWhere & "([Amount] = " & Me.txtFilterAmount & ") AND "
End If
'etc for other fields.

lngLen = Len(strWhere) - 5 'Without trailing " AND ".
If lngLen <= 0 Then
MsgBox "No criteria"
Else
strWhere = Left$(strWhere, lngLen)
Me.RecordSource = strcStub & strWhere & strcTail
End If
End Sub
----------code ends-----------
 
Back
Top