.Additem problem

[moke123]

My db is not relational. Everything is in one table so I've no need for P or F keys, and their complications.

And you wonder why your struggling?

 

[MajP]

My db is not relational. Everything is in one table so I've no need for P or F keys, and their complications. One table is so easy to update and maintain. You asked How are you relating tracks to albums. There's no relationship, the tarcks *are* the album.
So yes, this may be different to what is conventional, but will be better for us than any "nicely Formatted" music database as it's features are purpose built.

Not trying to be a jerk, but trying to give some tough-love. it seems you are trying to outsmart everyone who has ever built a proper Access application. By making it less "complicated" you have come up with some very complicated way of doing things. I have made thousands and helped people with thousands of Access databases, but I cannot make heads or tails of what you are trying to do. I think what you want to do is a trivial application, and you are wasting a ton of time trying to come up with completely new ways to do things.
If you want to do this correctly we can help you come up with a very slick application with all kinds of Bells and Whistles, but you have to start over from scratch, IMO. People here will bend over backward to help you do things correctly. but sorry to say you will not get much support here on this because most people do not want to help people do things the wrong way. You need to get properly structured tables with good naming conventions. You can use standard Access bound forms done properly. Sure you can put a band-aid on what you have, but it is going to be one band-aid after another. What should be trivial will always be overly complicated.
It looks to me like you are building a Rube Golberg machine. You might get it to work, but is not easy.

 

[MajP]

You set list colums to 2, the first may have a width of 0. Not sure why that might be yet. A part of the code exits if lst, arr(I))

I wrote this as a generic function so that anyone can build a wrap-able listbox. It actually works better than I thought, so maybe the Idea is not so far fetched. However to make it make any sense, you need to know that the subsequent lines belong to the same record. All records in a database have a PK. Sorry that is not disputable. In most comboboxes you display a readable text but bind it to a hidden column that holds the PK.
If I had a combo box of your albums it would show

King and I
Carousel
Oklahoma

But the first hidden column would be the PK
2726
2727
2728

In this wrap idea the track PK is repeated in the subsequent lines. Look at thread 14 and see how this can work. I can click any line for that track and return that records PK and know which record it is.

A part of the code exits if lst, arr(I)) > visWidth. I doubt this would ever happen but could a space be inserted/used so it can't happen ?

The code was written to break on full words or break within a word. The latter was assuming you might have one continuous long string. In your case you will never likely have a single word bigger than the visible display. The code could be modified easily to break on whole words where you can and if a single word is bigger than the visible display then break within that word. I cannot think of any reasonable data that would have those properties. Maybe something like

"Your account number is: 1231jvap98ear[45=sd8fwuqer;ioafus-98we54;q3 "

which would resolve to something like
"Your account number is:
-- 1231jvap98ear[45=sd
-- 8fwuqer;ioafus-98we54;
-- q3 "

 

[MajP]

What most people would recommend in this case is to use a subform so you can wrap it. This requires almost no code and will act just like a listbox with a little configuring. The only drawback from this is that every row is given a height for two lines in this case. You cannot make some rows smaller and others larger.
You can also use a datasheet instead of a tabular subform. This would allow you to dynamically stretch and shrink the row heights with the mouse.

 

[kirkm]

Maj, I wouldn't really say I'm meaning to outsmart anyone, I defer to everyone on here. I know what you mean by no-one wants to help me do things the wrong way... which of course means my way ! They don't have the full picture of what I'm doing? You say you cannot make heads or tails of what I'm trying to do. I say to myself how can this be when this guy knows far more than me. Maybe I place too much empahis on getting the desired result anyhow but I am enjoying developing this and there is a 'master plan'. Some days there's even progress!

Just to mention this PK thing, there's a field in the table which is an identifier for every record. Isn't this equivalent to a PK? The listbox will only ever have one album at a time. It's not a list of records but a list of tracks within that record. Later each entry in the list will have events (which is why a textbox is no good). I did try a listview as well. The idea of adjusting column width was okay but word wrapping was better.

You wrap-able listbox has heaps of potential. I'm looking forward to trying it out. I had a bug that's held me up. (qryFTSourceTemp wasn't working and I incorrectly blamed the calculated fields. That's sorted now)

It just wouldn't work to start as you suggest from scratch some other way, apart from the structure being set in stone I'd lose my involvement and it's far too much to ask anyone else to (in effect) do for me. I ask too many questions already in this forum, usually something very specific and get things back like "Why do want to do that"? or "I've never done that" etc. So I have to filter them out, they're no help at all. But then every so often a really useful reply. I do accept there may be better methods than I'm using and as or when they show up I'll change to them if they fit. Hey, this just a hobby.. I'm retired with plenty of spare time, and overall Maj, I do respect and appreciate yours (and others) advice.

 

[MajP]

Maybe I place too much emphasis on getting the desired result anyhow but I am enjoying developing this and there is a 'master plan'.

Based on experience I have seen a lot of people get enamored with building fancy forms and focus on that and not worry about the underlying data structure. To me that is like worrying about getting the cabinets and tile in before getting the house properly framed. If your house is not framed square, plum and level everything after that gets real hard to do. Spending the time to get proper normalized tables will save a ton of time in the long run and make everything much easier. If not the workarounds begin to snowball and everything gets every more complicated to do simple tasks. I have seen two tables, which seem like they should be somehow related. One looks like a list of albums (tblMain) and the other kind of looks like the beginning of a tracks table, although the data in it makes no sense.
Maybe if you explain the big picture we can give better help. If not things look strange and it is hard to tell if you are making a mistake or just doing things in a non-standard way. One big thing that can really help you and everyone else, is naming conventions. Most people can look at my tables, queries, and vba code without any comments and understand what I am doing. I use long descriptive and consistent variable and object names. You have some seriously bad names. This causes serious confusion in coding, debugging, and general understanding. I name things always with the though that someone else should be able to read it, or if I come back years later I can look at it and know what is going on.

Bad:
CH, 40, 10, #, Year, Number

Better
WeeksOnChart, WeeksTop40, WeeksTop10, WeeksPeak, ReleaseYear, AlbumNumber

No spaces, no special characters except underscore, no pure numeric. No reserved words. You have lots of reserved words.

List of reserved words in Access - Microsoft 365 Apps

Lists the reserved words that you should not use in field, object, and variable names. It is not practical to provide a list of all reserved words, such as built-in function names or Access user-defined names.

docs.microsoft.com

Same thing in your vba. There is not benefit with having short meaningless variables. Use descriptive variable names.

 

[kirkm]

Seeing you've asked... The real big picture is this will be an interface between the data and the music and used by an interested bunch of collectors, who have gathered the songs, images (label scans etc) and lyrics.

There's about 44,000 entries so far. I'd cobbled this togther in Excel initially, using the worst VBA code you can imagine. I didn't have a clue, it as the first time I'd ever seen Excel and my only coding experience was with Commodore basic Eventually I'd crash Excel spectacularly with an error no-one could fix (53191 from memory), so it was binned.. but kept as 'data only' hence the odd structure, which is really a spreadsheet. This may explain the choice of names as they were to fit the layout (column widths) in Excxel. We still want that structure, if it were altered, some way to recreate it woulbe be needed which kinds of makes you wonder why change it.
I know the field names aren't what you'd normally use in Access, but I wanted to keep them for compatibilty, and they worked. The short variable names are a throwback to CBM, but I am improving a bit in that area.

So it's not really an Access database, it just uses Access as means to an end. You know, I did dabble in making it relational, we even had a sort of diagram full of PK and FK, but I didn't like it, or how to maintain or update when it was a bunch of meaningless numbers. Seeing the actual text data seemed far more user friendly.
There is one change planned though. There's 194 individual fields holding chart positions (because that's how many were needed to plot each songs chart history, using one column per week in Excel). That's to make it a CSV. Or even a separate table.. but that proved a sticky road to go down, potentially needing many more changes.

So it will eventually, be a sort of slidehow for pics and lyrics and a music player, plus offer some quite extensive search facilities if I can nail all the sql. There's also another (Access) db using that structure which accepts data additions from the group and validates it before its added.
Is that enough Maj ?

 

[Pat Hartman]

I know the field names aren't what you'd normally use in Access, but I wanted to keep them for compatibilty

The thing that makes Excel so difficult to use as a "database" is that the presentation layer and data layer are merged. So the users see the same names you code with. That isn't the way that a relational database works. You use meaningful names when you are coding to avoid confusion and to minimize the learning curve for your successor and you use whatever the users wants as label names. Not sure why the users would want obscure abbreviations for labels either when they are no longer necessary. Keep in mind that the vast majority of all application development is done by people who are NOT domain experts. You are a domain expert but you are in over your head with Access. Access is like any other tool. If you use it the way it was intended to be used, it will serve you well. If you use it incorrectly, it will cut off your toes.

So it's not really an Access database, it just uses Access as means to an end.

That is the heart of your problem. You will fight with Access over everything and Access will win. When you start using Access as a RAD tool rather than a spreadsheet, you will have a much easier time. Many of the display formats you like can be replicated using crosstabs but that is not how you would store your data. You can even export the data back to Excel to make reports if that works better.

 

[NauticalGent]

You will fight with Access over everything and Access will win.

Each and every time...

 

[kirkm]

Hello MajP not sure if you want to do anything more with your wrappable list box ?
I've been applying my data to it and finding some issues.
But before going into detail I'll will wait to see if you get this or want to reply,
Cheers

 

[MajP]

If you really believe this is the best approach, I will take a look.

 

[kirkm]

Yes, this has potential. Perhaps first an image showing the differences with the raw output from WordWrapListbox2.accdb and how I'm wanting to display it. I have a routine that formats this up to a point but yours may be better and is certainly more compact.
I've identified some issues, the need for 2 columns you've explained but can that be utilised without
special handling for ";" and "," (which are used). Is this why you removed the comma? I don't have a PK so only one column is needed.
The array split on spaces - if a CRLF is embedded in arr(x) it seems to be ignored.
I haven't as yet struck or tested a case where longString is null to see what that section does.
My hunch is if GetTextLength() could return a single paragraph wrapped where/if necessary it'd be job done.
What do you think?

 

[MajP]

I do not understand that image. How is any of that related?
If you are asking me about your code, I have no interest in looking at that. Its a thousand lines of code to do what I did in 30.
The add item method will have issues with commas and semicolons. If that is needed I would change the code. I would have a temp table and load that in the same way I load the actual list. Then bind the temp table. The code would be as short or shorter.

 

[MajP]

This temp table idea works great. This demo has both versions, but the temp table gives advantage. In the temp table you can pad spaces to the front of a string which is not doable in additem. You can also handle ", " and ";" easily. To show how flexible this is I added a button to narrow and widen the list. This demos regardless of the width the code handles it correctly. I would just get rid of all that code you have and use this.

The code is relatively small

Private Function GetTextLength(pCtrl As Control, ByVal str As String, _
                              Optional ByVal Height As Boolean = False)
    Dim lx As Long, ly As Long
    ' Initialize WizHook
    WizHook.Key = 51488399
    ' Populate the variables lx and ly with the width and height of the
    ' string in twips, according to the font settings of the control
    WizHook.TwipsFromFont pCtrl.fontName, pCtrl.fontSize, pCtrl.FontWeight, _
                          pCtrl.FontItalic, pCtrl.FontUnderline, 0, _
                          str, 0, lx, ly
    If Not Height Then
        GetTextLength = Abs(lx)
    Else
        GetTextLength = Abs(ly)
    End If
End Function
Public Sub ClearTempTable()
  CurrentDb.Execute "delete * from tblTempList"
End Sub

Public Function LoadWrapListTable(lst As Access.ListBox, longString As Variant, Optional PK As Variant = "0", Optional wholeWords = True) As String
  Dim visWidth As Long
  Dim visWidthInches As Long
  Dim arr() As String
  Dim I As Integer
  Dim txt As String
  Dim txtLength As Long
  Const buffer = 400
  'I think this makes sense only with one visible column little more complicated if not
  lst.RowSourceType = "Table/Query"
  lst.RowSource = "SELECT tblTempList.ID, tblTempList.Display FROM tblTempList ORDER BY tblTempList.Sort"
  visWidth = lst.Width - buffer
  lst.ColumnCount = 2
  lst.ColumnWidths = "0;" & visWidth / 1440 & " in"


  
   If Not IsNull(longString) Then
       arr = Split(longString, " ")
      For I = 0 To UBound(arr)
        If txt = "" Then
          txt = txt & Trim(arr(I))
        Else
          txt = txt & " " & Trim(arr(I))
        End If
        txtLength = GetTextLength(lst, txt)
        If GetTextLength(lst, arr(I)) > visWidth Then
          MsgBox "Whole words are greater than visible length. Exiting", vbInformation
          Exit Function
        End If
        If txtLength >= visWidth Or arr(I) = vbCrLf Then
           If txtLength > visWidth Or arr(I) = vbCrLf Then
             ' roll back
            
             txt = Left(txt, Len(txt) - Len(arr(I)))
             If I > 0 Then I = I - 1
            End If
           ' Debug.Print PK & "; " & txt
            txt = Replace(txt, "'", "''")
            CurrentDb.Execute "Insert into tblTempList (ID,Display) values ('" & CStr(PK) & "', '" & txt & "')"
           txt = "    "
         End If
      Next I
      
      If txt <> "" Then
        ' Debug.Print PK & "; " & txt
         txt = Replace(txt, "'", "''")
         CurrentDb.Execute "Insert into tblTempList (ID,Display) values ('" & CStr(PK) & "', '" & txt & "')"

      End If
   End If
End Function