list form

[Zanstemic]

I'm new to access but I've seen two ways of listing data. 1 is in a form view
and the other is in a list view.

Is there a way to get a form to look more like a list?

Or is there a way to make a list view easier to navigate?

If these are bad questions, how to you recommend making the navigation of
the database simpler?

Thanks in advance for all the advice.

 

[Allen Browne]

Forms have 3 views in Access:

- Form View shows one record at a time, laid out the way you placed the
forms at design time.

- Datasheet View shows multiple records (one per row), with the columns
looking like the columns of a table. This looks nothing like the design view
layout.

- Continuous View shows multiple records, with each record matching the way
you laid the form out at design time. If you place the text boxes side by
side, and move their labels into the Form Header section, it starts to look
more like Datasheet View, but it has several advantages over Datasheet view,
e.g.
o Can show total in the Form Footer section;
o Can use command buttons;
o No chance of users getting confused by hidden or frozen columns.

For navigating records, the navigation buttons are ideal. You can also use a
combo to select a record to jump to. If that interests you, see:
Using a Combo Box to Find Records
at:
http://allenbrowne.com/ser-03.html

 

[Sprinks]

Zanstemic,

See Help on the DefaultView property. A form can be in 3 main views:

Datasheet Looks much like viewing the table itself
Continuous Form Lists multiple records but customizable
Single Form Shows a single record

There are also the Pivot Table and Pivot Chart views.

So, Continuous Form view shows your data like a list.

To navigate through your data in any of the views, you can use Find
(Ctrl-F), the mouse scroll wheel, a vertical sliding bar. To look up a
specific record, you can provide a method for them. A simple way to look up
a record by a single field is a combo box in the form header.

After the user selects the record in the combo box (which is Bound to the
RecordSource's primary key but displays more meaningful text), an AfterUpdate
event procedure reopens the form to the selected record and sets the focus to
the control of your choosing:

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "YourForm"

stLinkCriteria = "[YourPrimaryKey] =" & "'" & Me![YourComboBox] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Me![SomeControl].SetFocus

The quotes around the value of your combo box above are required for text
fields. If the primary key is numeric, which is usually the case, the line
can be simplified to:

stLinkCriteria = "[YourPrimaryKey] =" & Me![YourComboBox]

For more complex filtering of your data by multiple fields, I use a series
of controls. On the press of a Filter button, code executes that loops
through those controls, building an SQL string that is written to a hidden
textbox. Then the following code executes to filter the records:

Dim stDocName As String
Dim stWhere As String

stDocName = "YourForm"
stWhere = Me![YourHiddenSQLTextbox]

DoCmd.OpenForm stDocName, , , stWhere

To be more precise, the code loops through ALL of the controls, but I set
the Tag property of each of the lookup controls to something unique to only
use them:

Dim ctl as Control
Dim strControlValue as String
Dim strSQL as String

For Each ctl in Me.Controls
If ctl.Tag = "LU" Then
strSQL = strSQL &
End If

If (Nz(ctl.Value) <> 0 And Nz(ctl.Value) <> "") Then

If IsNumeric(ctl.Value) Then
strControlValue = ctl.Value
Else
strControlValue = "'" & ctl.Value & "'"
End If

'See note below
Me!txtYourHiddenTextbox = Me!txtYourHiddenTextbox & _
"[" & LTrim(Right(ctl.name, Len(ctl.name) -
3)) _
& "]=" & strControlValue & " AND "

End If

Next ctl

' Strip the last " AND " off end of filter
Me!txtYourHiddenTextbox = Left(Me!txtYourHiddenTextbox,
Len(Me!txtYourHiddenTextbox) - 5)

Then use the Openform method with the SQL string as the Where clause:

Dim stDocName As String
Dim stWhere As String

stDocName = "YourForm"
stWhere = Me![txtYourHiddenTextbox]

DoCmd.OpenForm stDocName, , , stWhere

The line above,

Me!txtYourHiddenTextbox = Me!txtYourHiddenTextbox & _
"[" & LTrim(Right(ctl.name, Len(ctl.name) -
3)) _
& "]=" & strControlValue & " AND "

makes use of my convention of naming a control with a three-character prefix
followed by the field to which it's bound.

Hope that gives you some ideas.

Sprinks

 

[Zanstemic]

Thanks so much for all the help. This will make for a fantastic improvement.

Zanstemic,

See Help on the DefaultView property. A form can be in 3 main views:

Datasheet Looks much like viewing the table itself
Continuous Form Lists multiple records but customizable
Single Form Shows a single record

There are also the Pivot Table and Pivot Chart views.

So, Continuous Form view shows your data like a list.

To navigate through your data in any of the views, you can use Find
(Ctrl-F), the mouse scroll wheel, a vertical sliding bar. To look up a
specific record, you can provide a method for them. A simple way to look up
a record by a single field is a combo box in the form header.

After the user selects the record in the combo box (which is Bound to the
RecordSource's primary key but displays more meaningful text), an AfterUpdate
event procedure reopens the form to the selected record and sets the focus to
the control of your choosing:

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "YourForm"

stLinkCriteria = "[YourPrimaryKey] =" & "'" & Me![YourComboBox] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Me![SomeControl].SetFocus

The quotes around the value of your combo box above are required for text
fields. If the primary key is numeric, which is usually the case, the line
can be simplified to:

stLinkCriteria = "[YourPrimaryKey] =" & Me![YourComboBox]

For more complex filtering of your data by multiple fields, I use a series
of controls. On the press of a Filter button, code executes that loops
through those controls, building an SQL string that is written to a hidden
textbox. Then the following code executes to filter the records:

Dim stDocName As String
Dim stWhere As String

stDocName = "YourForm"
stWhere = Me![YourHiddenSQLTextbox]

DoCmd.OpenForm stDocName, , , stWhere

To be more precise, the code loops through ALL of the controls, but I set
the Tag property of each of the lookup controls to something unique to only
use them:

Dim ctl as Control
Dim strControlValue as String
Dim strSQL as String

For Each ctl in Me.Controls
If ctl.Tag = "LU" Then
strSQL = strSQL &
End If

If (Nz(ctl.Value) <> 0 And Nz(ctl.Value) <> "") Then

If IsNumeric(ctl.Value) Then
strControlValue = ctl.Value
Else
strControlValue = "'" & ctl.Value & "'"
End If

'See note below
Me!txtYourHiddenTextbox = Me!txtYourHiddenTextbox & _
"[" & LTrim(Right(ctl.name, Len(ctl.name) -
3)) _
& "]=" & strControlValue & " AND "

End If

Next ctl

' Strip the last " AND " off end of filter
Me!txtYourHiddenTextbox = Left(Me!txtYourHiddenTextbox,
Len(Me!txtYourHiddenTextbox) - 5)

Then use the Openform method with the SQL string as the Where clause:

Dim stDocName As String
Dim stWhere As String

stDocName = "YourForm"
stWhere = Me![txtYourHiddenTextbox]

DoCmd.OpenForm stDocName, , , stWhere

The line above,

Me!txtYourHiddenTextbox = Me!txtYourHiddenTextbox & _
"[" & LTrim(Right(ctl.name, Len(ctl.name) -
3)) _
& "]=" & strControlValue & " AND "

makes use of my convention of naming a control with a three-character prefix
followed by the field to which it's bound.

Hope that gives you some ideas.

Sprinks

I'm new to access but I've seen two ways of listing data. 1 is in a form view
and the other is in a list view.

Is there a way to get a form to look more like a list?

Or is there a way to make a list view easier to navigate?

If these are bad questions, how to you recommend making the navigation of
the database simpler?

Thanks in advance for all the advice.

 

[Sprinks]

Zanstemic,

I noticed an error in the code I posted for looking up by multiple fields.
Please omit the following lines:

Dim strSQL as String
....keep this line...If ctl.Tag="LU" Then
strSQL = strSQL &
End If

Sprinks

Thanks so much for all the help. This will make for a fantastic improvement.

Zanstemic,

See Help on the DefaultView property. A form can be in 3 main views:

Datasheet Looks much like viewing the table itself
Continuous Form Lists multiple records but customizable
Single Form Shows a single record

There are also the Pivot Table and Pivot Chart views.

So, Continuous Form view shows your data like a list.

To navigate through your data in any of the views, you can use Find
(Ctrl-F), the mouse scroll wheel, a vertical sliding bar. To look up a
specific record, you can provide a method for them. A simple way to look up
a record by a single field is a combo box in the form header.

After the user selects the record in the combo box (which is Bound to the
RecordSource's primary key but displays more meaningful text), an AfterUpdate
event procedure reopens the form to the selected record and sets the focus to
the control of your choosing:

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "YourForm"

stLinkCriteria = "[YourPrimaryKey] =" & "'" & Me![YourComboBox] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Me![SomeControl].SetFocus

The quotes around the value of your combo box above are required for text
fields. If the primary key is numeric, which is usually the case, the line
can be simplified to:

stLinkCriteria = "[YourPrimaryKey] =" & Me![YourComboBox]

For more complex filtering of your data by multiple fields, I use a series
of controls. On the press of a Filter button, code executes that loops
through those controls, building an SQL string that is written to a hidden
textbox. Then the following code executes to filter the records:

Dim stDocName As String
Dim stWhere As String

stDocName = "YourForm"
stWhere = Me![YourHiddenSQLTextbox]

DoCmd.OpenForm stDocName, , , stWhere

To be more precise, the code loops through ALL of the controls, but I set
the Tag property of each of the lookup controls to something unique to only
use them:

Dim ctl as Control
Dim strControlValue as String
Dim strSQL as String

For Each ctl in Me.Controls
If ctl.Tag = "LU" Then
strSQL = strSQL &
End If

If (Nz(ctl.Value) <> 0 And Nz(ctl.Value) <> "") Then

If IsNumeric(ctl.Value) Then
strControlValue = ctl.Value
Else
strControlValue = "'" & ctl.Value & "'"
End If

'See note below
Me!txtYourHiddenTextbox = Me!txtYourHiddenTextbox & _
"[" & LTrim(Right(ctl.name, Len(ctl.name) -
3)) _
& "]=" & strControlValue & " AND "

End If

Next ctl

' Strip the last " AND " off end of filter
Me!txtYourHiddenTextbox = Left(Me!txtYourHiddenTextbox,
Len(Me!txtYourHiddenTextbox) - 5)

Then use the Openform method with the SQL string as the Where clause:

Dim stDocName As String
Dim stWhere As String

stDocName = "YourForm"
stWhere = Me![txtYourHiddenTextbox]

DoCmd.OpenForm stDocName, , , stWhere

The line above,

Me!txtYourHiddenTextbox = Me!txtYourHiddenTextbox & _
"[" & LTrim(Right(ctl.name, Len(ctl.name) -
3)) _
& "]=" & strControlValue & " AND "

makes use of my convention of naming a control with a three-character prefix
followed by the field to which it's bound.

Hope that gives you some ideas.

Sprinks

I'm new to access but I've seen two ways of listing data. 1 is in a form view
and the other is in a list view.

Is there a way to get a form to look more like a list?

Or is there a way to make a list view easier to navigate?

If these are bad questions, how to you recommend making the navigation of
the database simpler?

Thanks in advance for all the advice.