Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    AccessPower's Avatar
    AccessPower is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2016
    Posts
    165

    UI Design Ideas (Just for Fun)

    Hello All,

    I do a lot of database design, everything from architecture to UI. I was wondering how involved you guys are on the UI side of things. Do you make things user-friendly/pretty. Do you have a particular style that you like to use?

    I would love to see the work of others, and I'll contribute a few screen shots as well. This is just a fun post to get the creative juices going.

    Here's one of my latest:

    Click image for larger version. 

Name:	Kemper Compliance Database.png 
Views:	46 
Size:	49.6 KB 
ID:	26987



    Click image for larger version. 

Name:	Kemper Compliance Database2.png 
Views:	44 
Size:	53.0 KB 
ID:	26988

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    I usu have a continuous list so user can see all records.
    here they can filter too,via combo ,or text box.
    when user wants detail,they select the record then click a DETAIL button,
    this opens a single record form of that 1 item, via:

    docmd.openForm "frmDetail",,"[id]=" & me.txtID

  3. #3
    AccessPower's Avatar
    AccessPower is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2016
    Posts
    165
    Quote Originally Posted by ranman256 View Post
    I usu have a continuous list so user can see all records.
    here they can filter too,via combo ,or text box.
    when user wants detail,they select the record then click a DETAIL button,
    this opens a single record form of that 1 item, via:

    docmd.openForm "frmDetail",,"[id]=" & me.txtID
    I like this idea. That way they can see everything at-a-glance, then get details if they need them.

  4. #4
    AccessPower's Avatar
    AccessPower is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2016
    Posts
    165
    What do you think of this:

    -Make a form continuous and use it as a subform.
    -Open a recordset in VBA and count the number of records, divide that by 10 or 20.
    -If you have 200 records, the end result will be 10 if you divide by 20, so you'll know you need 10 pages.
    -Then add 20 records to each page.
    -Have vba create a number of radio boxes that navigate between the pages. Or setup the pages so that they look like radio buttons.

    Now users don't have to scroll. It's a bit more work, but for the user experience, it may be worth it in some cases.

    Is there a faster way to do this? Maybe. Haven't really thought about it actually.

    Click image for larger version. 

Name:	Listbox Pages.png 
Views:	42 
Size:	108.3 KB 
ID:	26990

    Something like this. I didn't create this one, but I'm taking notes.

  5. #5
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    What do you think of this:
    I do that quite frequently, not for prettiness but to reduce network traffic. but my method is to calculate the number of visible rows on the form and use that to determine the maximum number of records to bring through per page. I may also display nothing initially until the user has entered a search string to find what they are looking for (even 1 character can reduce the number of records brought through by 95%). Many of my clients have tables with 1m+ records - you really don't want a form based just on that table

    You need to be careful with graphs - they can take some time to render if based on a very large dataset.

    I'm moving more and more towards designing UI's which have a touch screen friendly form view. In simple terms this means finger sized controls and larger font sizes which reduces the amount that can appear on screen (particularly as the typical touchscreen is smaller), so I have developed some modules that resize controls and modify the layout of the form that users can activate if a touchscreen is detected. I'm also developing some routines which work for touchscreen actions like swipes - so for example swiping a scrollbar (not using the standard offering) the form will continue to scroll and eventually slow to a stop or is stopped by the user touching the form somewhere. The acceleration/deceleration calculations are a nightmare though! still work in progress.

    Other features include 'latest viewed' - a bit like the tabs at the top of IE/Chrome/Firefox and favourites so users don't have to navigate menus for frequently referenced pages.

    I have also tried to make transitions more interested - for example when moving from one form to another, the old form slides off to the left (and maybe parks itself as an icon to be called back later) and the new one flies in from the right or bottom. But important this does not slow the user down. They are more interested in getting the job done than my cleverness.

    Another effect is using low transparency forms to highlight sections of the underlying form.

  6. #6
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Click image for larger version. 

Name:	Main2.png 
Views:	39 
Size:	111.2 KB 
ID:	26999
    I was introduced to a form design like this many, many years ago. The details section is in continuous form view of the main table.
    There are list boxes that are activated/updated are cascading list boxes.

    So you would start out by selecting a client in the details section. The first list box "1 - Division" is updated. Selecting a record in that list box updates the following list box...... Double clicking a list box brings up a details form.

    The buttons (some are text boxes configured to act like buttons - able to change bg colors) bring up forms for different actions.
    I used to use a main form with a lot of buttons, but the current design is better - data is instantly visible, can drill down by double clicking list boxes.
    Many UDFs to provide values in the black section/ yellow text boxes.

    List boxes show data, take up less space than sub forms and double clicking allows for form to be opened to add/edit/delete data, select reports or perform maintenance (if you have permissions).

  7. #7
    AccessPower's Avatar
    AccessPower is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2016
    Posts
    165
    Quote Originally Posted by Ajax View Post
    I do that quite frequently, not for prettiness but to reduce network traffic. but my method is to calculate the number of visible rows on the form and use that to determine the maximum number of records to bring through per page. I may also display nothing initially until the user has entered a search string to find what they are looking for (even 1 character can reduce the number of records brought through by 95%). Many of my clients have tables with 1m+ records - you really don't want a form based just on that table

    You need to be careful with graphs - they can take some time to render if based on a very large dataset.

    I'm moving more and more towards designing UI's which have a touch screen friendly form view. In simple terms this means finger sized controls and larger font sizes which reduces the amount that can appear on screen (particularly as the typical touchscreen is smaller), so I have developed some modules that resize controls and modify the layout of the form that users can activate if a touchscreen is detected. I'm also developing some routines which work for touchscreen actions like swipes - so for example swiping a scrollbar (not using the standard offering) the form will continue to scroll and eventually slow to a stop or is stopped by the user touching the form somewhere. The acceleration/deceleration calculations are a nightmare though! still work in progress.

    Other features include 'latest viewed' - a bit like the tabs at the top of IE/Chrome/Firefox and favourites so users don't have to navigate menus for frequently referenced pages.

    I have also tried to make transitions more interested - for example when moving from one form to another, the old form slides off to the left (and maybe parks itself as an icon to be called back later) and the new one flies in from the right or bottom. But important this does not slow the user down. They are more interested in getting the job done than my cleverness.

    Another effect is using low transparency forms to highlight sections of the underlying form.
    Wow... I would really like to see examples of this if you have anything available. I like the latest viewed idea, and how you set up the pages. I haven't played around with pages much. How do you typically go about doing it?

  8. #8
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I like the latest viewed idea, and how you set up the pages
    At its simplest it is a listbox.

    If you are using any sort of change log, instead of just inserting new records, insert a record every time the user visits a form (i.e. in the form load or current event).

    The latest view rowsource is based on the log table, selecting say the top 10 or 20 records for that user and ordered by timestamp desc. It is requeried to update it as part of the routine to update the log table.

    If you are not using a change log, create a simple one

    tblUserActivity
    ActivityPK autonumber
    UserPK long link to user table
    FormName text - might be 'List Customers' for a navigation form or 'XYZ Ltd' for a specific record
    PKName text - name of the PK field in the form recordsource - or blank if form is a navigation form
    PKLink long (id of the record viewed, 0 or null if form is a navigation form)

    In the afterupdate event of the listbox you would have something like

    with lstActivity
    docmd.openform .column(2),,,iif(nz(.column(4))<>0,.column(3) & " = " & .column(4),"")
    end with

  9. #9
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I haven't played around with pages much. How do you typically go about doing it?
    you use TOP in the select query

    Page 1
    SELECT TOP 20
    FROM myTable

    Page 2
    SELECT TOP 20
    FROM myTable T LEFT JOIN (SELECT TOP 20 FROM myTable) P ON T.ID=P.ID
    WHERE P.ID is null

    Page 3
    SELECT TOP 20
    FROM myTable T LEFT JOIN (SELECT TOP 40 FROM myTable) P ON T.ID=P.ID
    WHERE P.ID is null

    etc

    You need some code to track the page number

  10. #10
    AccessPower's Avatar
    AccessPower is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2016
    Posts
    165
    Quote Originally Posted by Ajax View Post
    you use TOP in the select query

    Page 1
    SELECT TOP 20
    FROM myTable

    Page 2
    SELECT TOP 20
    FROM myTable T LEFT JOIN (SELECT TOP 20 FROM myTable) P ON T.ID=P.ID
    WHERE P.ID is null

    Page 3
    SELECT TOP 20
    FROM myTable T LEFT JOIN (SELECT TOP 40 FROM myTable) P ON T.ID=P.ID
    WHERE P.ID is null

    etc

    You need some code to track the page number
    Very interesting. I like the idea. I hadn't thought about using TOP in that way, but it makes sense.

    I can see being able to do this dynamically without page number selections. Have a next page or previous page button. Something like this in vba maybe:

    Code:
    'For the NextPage button
    
    
    Dim pNumber as Integer
    Dim rSource as String
    
    
    'pNumber grabs from textbox with initial value of 1 when the form loads
    pNumber = Me.txtpNumber.value
    
    
    Select Case pNumber
        Case 0
            rSource = "SELECT TOP 20 FROM myTable"
            'Pretty much do nothing here because it's already at it's end
        Case 1
            rSource = "SELECT TOP 20 FROM myTable"
        Case Else
            rSource = "SELECT TOP 20 FROM myTable T LEFT JOIN (SELECT TOP " & pNumber * 20 - 20 & " FROM myTable) P ON T.ID=P.ID WHERE P.ID is null"
    End Select
    
    Me.subFormName.Form.RecordSource = rSource
    Me.subFormName.Requery

  11. #11
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    something like that, there are a number of ways, just depends on how your form is set up

    I would also use something like TOP (pagenum*20)

    no need for the requery though

  12. #12
    AccessPower's Avatar
    AccessPower is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2016
    Posts
    165
    So I set up a form with a next button and have a subform displaying two records at a time. When I hit next the fields get populated with #Name?

    Will be actually really interesting to get it working. Nice proof of concept.

    Here's the code:

    Code:
    Private Sub cmdNext_Click()
    'For the NextPage button
    
    
    Dim pNumber As Integer
    Dim rSource As String
    
    
    'pNumber grabs from textbox
    pNumber = Me.txtpNumber.Value
    pNumber = pNumber + 1
    Me.txtpNumber.Value = pNumber
    
    
    Select Case pNumber
        Case 0
            rSource = "SELECT TOP 2 * FROM tblFullNames;"
            'Pretty much do nothing here because it's already at it's end
        Case 1
            rSource = "SELECT TOP 2 * FROM tblFullNames;"
        Case Else
            rSource = "SELECT TOP " & (pNumber - 1) * 2 & " * FROM tblFullNames T LEFT JOIN (SELECT TOP " & pNumber * 2 & " * FROM tblFullNames) P ON T.ID=P.ID WHERE P.ID is null;"
            Debug.Print rSource
    End Select
    Me.subFullNames.Form.RecordSource = rSource
    End Sub
    
    
    Private Sub Form_Load()
    'Sets pNumber to 1
    Me.txtpNumber.Value = 1
    
    
    'Sets record source of subform to grab the first two records when the page loads
    Me.subFullNames.Form.RecordSource = "SELECT TOP 2 * FROM tblFullNames;"
    End Sub

  13. #13
    AccessPower's Avatar
    AccessPower is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2016
    Posts
    165
    I've attached the database for proof-of-concept. Still not working. Not quite sure what the issue is. Definitely an issue with the left join, but not sure how to fix it.

    What do you guys think?

    Multi-Page Continuous Forms - Upload.zip

  14. #14
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    you are overly complicating it

    Code:
    Option Compare Database
    Option Explicit
    
    Const Rows =2
    
    private sub getPage()
    Dim rSource As String
    
       rSource = "SELECT TOP " & Rows & " * FROM tblFullNames
       if txtpNumber>1 then rsource=rsource & " T LEFT JOIN (SELECT TOP " & (txtpNumber-1 * rows) & " * FROM tblFullNames) P ON T.ID=P.ID WHERE P.ID is null"
       Me.subFullNames.Form.RecordSource = rSource
    
    end sub
    
    Private Sub cmdNext_Click()
    
       txtpNumber=txtpNumber+1
       getpage
    
    End Sub
    
    Private Sub cmdPrevious_Click()
    
        if txtpnumber>1 then
           txtpNumber=txtpNumber-1
           getpage
       end if
    
    End Sub
    
    
    Private Sub Form_Load()
    
    txtpNumber= 1
    getpage
    
    End Sub

  15. #15
    AccessPower's Avatar
    AccessPower is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2016
    Posts
    165
    I like your version quite a bit more. I did a bit of tweaking. Added an else to the if statement and added an load event to the form. Works like a charm.

    Actually really cool, hopefully someone here finds it useful. Nice collaborating with you.

    Code:
    Option Compare Database
    Option Explicit
    
    
    Const Rows = 2
    
    
    Private Sub getPage()
    Dim rSource As String
    
    
    rSource = "SELECT TOP " & Rows & " * FROM tblFullNames"
    
    
    If Me.txtpNumber > 1 Then
        rSource = rSource & " LEFT JOIN (SELECT TOP " & (Me.txtpNumber - 1) * Rows & " * FROM tblFullNames) P ON tblFullNames.ID=P.ID WHERE P.ID is null;"
        Debug.Print rSource
        Me.subFullNames.Form.RecordSource = rSource
        
    Else
        Me.subFullNames.Form.RecordSource = rSource
    End If
    
    
    End Sub
    
    
    Private Sub cmdNext_Click()
    txtpNumber = txtpNumber + 1
    getPage
    End Sub
    
    
    Private Sub cmdPrevious_Click()
    If txtpNumber > 1 Then
        txtpNumber = txtpNumber - 1
        getPage
    End If
    End Sub
    
    
    Private Sub Form_Load()
    getPage
    End Sub
    Any other ideas?

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Design Ideas?
    By Voodeux2014 in forum Database Design
    Replies: 3
    Last Post: 10-26-2015, 11:24 AM
  2. Replies: 1
    Last Post: 05-13-2015, 02:25 PM
  3. Ideas?
    By Voodeux2014 in forum Forms
    Replies: 8
    Last Post: 12-18-2014, 04:11 PM
  4. Design Ideas
    By ajs112 in forum Access
    Replies: 4
    Last Post: 10-08-2014, 02:35 PM
  5. Need your ideas and help...
    By Daryl2106 in forum Access
    Replies: 8
    Last Post: 04-05-2012, 01:00 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums