Displaying a selected record in a multicolumn Listbox1 in Textbox1. Only first two column values display?

chazrab

Well-known Member
Joined
Oct 21, 2006
Messages
912
Office Version
  1. 365
Platform
  1. Windows
Problem as above in title
Here's the simple code:
Code:
If Listbox1.ListIndex = -1 Then
    MsgBox "Nothing Selected!"
    Exit Sub
End If
Me.Textbox1.Value = Me.Listbox1.Column(2, Listbox1.ListIndex) --->only column that displays.  Table has 7 columns
End Sub
Ist col = ID. That displays with Me.Listbox1.Column(0, Listbox1.ListIndex) -->set to 0 - i.e., 1st column
2nd col VERSE. That also displays with Me.Listbox1.Column(1, Listbox1.ListIndex) -->set to 1 - i.e., 2nd column
3rd col KJV. That, column does display.
For any col index >=,3, the textbox is left blank for whatever reason.
Me.Listbox1.Column(3, Listbox1.ListIndex - col. index >= 3 does not display
As mentioned, this table has 5 columns.
What could possibly be the issue here. This doesn't make sense!.
Image of table below.
Anyone's help greatly appreciated. Has to be something very simple.
Thanks, cr
car1946@gmail.com
 

Attachments

  • 7 COL ACCESS TABLE.png
    7 COL ACCESS TABLE.png
    43.2 KB · Views: 9
Last edited:

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
from me, the sender...I knew it was something simple. I had the column count in the Properties box set to 2. I changed it to 8
and it works. cr :)
 
Upvote 0
Thanks for posting your solution. You never know, it could help someone else. That is, if they take the trouble to search first. :)
 
Upvote 0
Thanks for posting your solution. You never know, it could help someone else. That is, if they take the trouble to search first. :)
You're very welcome, sir - just an added comment - If you would look at my later post, I have a little greater issue for lack of a better term.
It sure would have been nice if one could just copy and paste Excel code to Access events and have them work the same way.
The fact that an Excel app is being ported to a database from a sheet format to an Access table format should not make any difference, should it ?
New to Access, but need a db since current Excel app as is has 31,103 rows across 4 cols in one sheet. Excel app is having trouble
processing and running the code with that many rows in a FIND method or anything else for that matter - cr :)
 
Upvote 0
"The fact that an Excel app is being ported to a database from a sheet format to an Access table format should not make any difference, should it ?"
Yes it does. The underlying object models of Excel and Access are different.
Strongly recommend you research Normalization -- it is fundamental to relational database.
If you are moving to Access, then I recommend this tutorial from RogersAccessLibrary. It has an into, a problem description/scenario, steps to lead you to a solution and a solution. It will take you less than an hour to work through the tutorial, But you will learn about Normalization and design through experiencing them in this tutorial.
There are many related links at RogersAccessLibrary.
 
Upvote 0
"The fact that an Excel app is being ported to a database from a sheet format to an Access table format should not make any difference, should it ?"
Yes it does. The underlying object models of Excel and Access are different.
Strongly recommend you research Normalization -- it is fundamental to relational database.
If you are moving to Access, then I recommend this tutorial from RogersAccessLibrary. It has an into, a problem description/scenario, steps to lead you to a solution and a solution. It will take you less than an hour to work through the tutorial, But you will learn about Normalization and design through experiencing them in this tutorial.
There are many related links at RogersAccessLibrary.
Hi JackD. Thanks for helping. I know spreadheets and databases operate on different planets and in difference time reference frames to use a physics analogy. (I'm being cute, to try to keep the humor in this and everything else, these days in this crazy world). I solved the major issue I posted earlier. No table normalization, referential integrity, etc. needed. THis is my reply to Micron's reply earlier in the thread:
Just a few lines of code:
Code:
Dear Micron...with my new and 2 week very limited knowledge of Access, this was not difficult:
Code:
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.
Your help and vast knowledge will always be appreciated. Please continue to critique as desired.

JackD: the next challenge is to build Next and Prev buttons to move one record back and forth at a time.    When I can get any selected
item(row) in Access isolated and the focus set to it, I can perform any FIND method search on that row, item = verse and add Notes  to it
that will write back to that item on the same row in the Table.  I did this easily in Excel, and with a little more studying of Access relationships,
I think this can be done.  This probably is where Table Normalization and Referential Integrity comes in with the ID Primary keys. 

Thanks again for your response and guidance.   
cr :)
 
Upvote 0

Forum statistics

Threads
1,223,249
Messages
6,171,031
Members
452,374
Latest member
keccles

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top