Porting an Excel application to Access

[chazrab]

I've already imported a master Excel sheet into Access. Same format except that Access has an ID key field. No problem.
I'm doing this, because on the previous and helpful suggestions from a few of you, this app would perform a lot better
if its underlying data was a database - not a sheet - reason being, that there are 31,103 sheet rows of data and Excel runs slower in running code
with sheet data rhis large

Here's the issue. My current Excel app has large amounts of text in several different textboxes on one userform.
I can easily display multiple rows of sheet data in one userform in Excel with the following code which works great
to display multiple rows of sheet data in ONE textbox in Excel:

Private Sub ListBox1_Click()
Dim n As Long
n = ListBox1.ListIndex
Me.TextBox1.Value = ListBox1.List(n, 3) _
& vbCrLf _
& ListBox1.List(n + 1, 3) _
& vbCrLf _
& vbCrLf + ListBox1.List(n + 2, 3) _
& vbCrLf _
& vbCrLf + ListBox1.List(n + 3, 3) _
& vbCrLf _
& vbCrLf + ListBox1.List(n + 4, 3) _
& vbCrLf _
& vbCrLf + ListBox1.List(n + 5, 3) _
& vbCrLf _
& vbCrLf + ListBox1.List(n + 6, 3) _
& vbCrLf _
& vbCrLf + ListBox1.List(n + 7, 3) _
& vbCrLf _
& vbCrLf + ListBox1.List(n + 8, 3) _
& vbCrLf _
& vbCrLf + ListBox1.List(n + 9, 3) _
& vbCrLf _
& vbCrLf + ListBox1.List(n + 10, 3) _
& vbCrLf _
& vbCrLf + ListBox1.List(n + 11, 3) _
& vbCrLf _

The application gets its data from Listbox1 in both Excel and Access. Up til now, I've only been able to figure out how to display one
row of data in the Access textbox.
The VBA Access code that does this is really simple - for only ONE row - the Selelected row in Listbox1:

Private Sub Command14_Click() 'Show record
If Listbox1.ListIndex = -1 Then
    MsgBox "Nothing Selected!"
    Exit Sub
End If
Me.Textbox1.Value = Me.Listbox1.Column(4, Listbox1.ListIndex)
End Sub

- how simple is that!
As you can see in the images below only the Selected item of the Access Listbox1 displays
If I can display one item in Listbox1 of Access, shouldn't it be possible to display multiple rows as in Textbox1 of The Access Textbox1 as
in theExcel app with the same code ? - but Access is not recognizing the Excel code that does this when I copy and paste it to a button in Access:

Excel code that works great:
Private Sub ListBox1_Click()
Dim n As Long
n = ListBox1.ListIndex
Me.TextBox1.Value = ListBox1.List(n, 3) _
& vbCrLf _
& ListBox1.List(n + 1, 3) _
& vbCrLf _
& vbCrLf + ListBox1.List(n + 2, 3) _
& vbCrLf _ ..etc

Sorry of being too ling and wordy - I wanted to be complete in describing this issue.
Up til now, I've only been helped with Excel related questions - if this is more of an Access
question, please excuse the rant and let me know where I can get the right help. I need to find someone who knows both Access and Excel well enough to help me with this.

Please help if you can. Would be greatly appreciated.

cr

 

[Micron]

You probably should review as many Access tutorials that you can in order to gain more insights to how things work. First, no I can't see in the pictures what you're describing - I see no selected item because it isn't highlighted. An Access listbox list is based on a query, table or value list. Each row is a separate list item and AFAIK, cannot be line wrapped. I've never tried that and never would because the lb is for displaying choices, not reams of data. To get line wrapped data you should use textboxes based on memo fields. You really need to abandon virtually everything you know about Excel when designing an Access db or you will struggle all the way through. Perhaps the thing that is of utmost importance is to never base table design with spreadsheet thinking and I suspect you have, based on

I've already imported a master Excel sheet into Access.

If I was one of those people who advised using a db, I probably already mentioned that.

 

[alansidman]

This may help you to understand DB layouts and management better

Box

app.box.com

Access Basics

 

[chazrab]

HI Alan - thanks. I briefly glanced over that Access Basics page. The Crystal lady who I guess authored this page and content looks like it drills down to give very helpful info. As seen, my issue is unique and goes a little beyond basic Access development yt vids. Starting to read and study these 100 pgs in hopes the solution I'm after will be achievable after more study.

Thks again, cr

 

[alansidman]

Also look at the links in the Box.net link I provided. Setting up a database properly is critical as Macron has indicated. Just taking data from Excel and dropping it into Access is not always the best way to get your expected results. A DB mindset is very different from a Spreadsheet mindset.

 

[chazrab]

You probably should review as many Access tutorials that you can in order to gain more insights to how things work. First, no I can't see in the pictures what you're describing - I see no selected item because it isn't highlighted. An Access listbox list is based on a query, table or value list. Each row is a separate list item and AFAIK, cannot be line wrapped. I've never tried that and never would because the lb is for displaying choices, not reams of data. To get line wrapped data you should use textboxes based on memo fields. You really need to abandon virtually everything you know about Excel when designing an Access db or you will struggle all the way through. Perhaps the thing that is of utmost importance is to never base table design with spreadsheet thinking and I suspect you have, based on

If I was one of those people who advised using a db, I probably already mentioned that.

Dear Micron...with my new and 2 week very limited knowledge of Access, this was not difficult:

Private Sub Listbox1_Click()
Dim result As String
With Me.Listbox1
    Dim index As Long
    For index = 0 To .ItemsSelected.Count - 1
        result = result & .Column(3, index) & ": " & .Column(3, index) & vbNewLine
    Next index
End With
Me.Textbox1.Value = result
End Sub

All I did was change the Multiselect property to single, selected the first 4 items(verses), added a vertical scroll bar,
and bam!, the Textbox not only displays all 4 verses(selected items) but allows using the scorll bar to move down the
Textbox.

You help and vast knowledge will always be appreciated. Please continue to critique as desired.
cr

 

[chazrab]

Also look at the links in the Box.net link I provided. Setting up a database properly is critical as Macron has indicated. Just taking data from Excel and dropping it into Access is not always the best way to get your expected results. A DB mindset is very different from a Spreadsheet mindset.

Alan, I don't know if you can see what I replied back to Micron, but here it is anyway:
Dear Micron...with my new and 2 week very limited knowledge of Access, this was not difficult:

Private Sub Listbox1_Click()
Dim result As String
With Me.Listbox1
    Dim index As Long
    For index = 0 To .ItemsSelected.Count - 1
        result = result & .Column(3, index) & ": " & .Column(3, index) & vbNewLine
    Next index
End With
Me.Textbox1.Value = result
End Sub

All I did was change the Multiselect property to single, selected the first 4 items(verses), added a vertical scroll bar,
and bam!, the Textbox not only displays all 4 verses(selected items) but allows using the scorll bar to move down the
Textbox. Now to add workable code to Next and Prev buttons to release dependency on using the scroll bar, which
does not always allow moving from each record to the next, as does Next and Prev buttons.

Thanks for all you input and help.
cr

You help and vast knowledge will always be appreciated. Please continue to critique as desired.
cr