If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
list form
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. |
#2
|
|||
|
|||
list form
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 -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Zanstemic" wrote in message ... 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. |
#3
|
|||
|
|||
list form
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" wrote: 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. |
#4
|
|||
|
|||
list form
Thanks so much for all the help. This will make for a fantastic improvement.
"Sprinks" wrote: 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" wrote: 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. |
#5
|
|||
|
|||
list form
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 "Zanstemic" wrote: Thanks so much for all the help. This will make for a fantastic improvement. "Sprinks" wrote: 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" wrote: 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. |
Thread Tools | |
Display Modes | |
|
|