alphabetize the field

[Dick7Access]

I am trying to upgrade my main db. Eliminate fields not any longer needed and add existing fields to new forms. It is very time consuming to scroll down many fields in table to find a particular field. Is there any way to alphabetize the fields? I didn't find anything googling it.

 

[June7]

Alphabetize the Fields List? No. Fields are in order shown in table or query design. Can click into list then type a character and that will take you to first field beginning with that character.

 

[Dick7Access]

Alphabetize the Fields List? No. Fields are in order shown in table or query design. Can click into list then type a character and that will take you to first field beginning with that character.

I have table open, where do I type first letter

 

[Dick7Access]

Thanks Pat,

Nomination is part of my goal. This DB I made over 24 years ago, when I knew next to nothing about access and access was not quite so sophisticated. I started with the flat db that came with my computer and was desperate to bring some organization to my minister. I am of course using a copy to try and fix it, because if I loose it I am in big trouble. I do have some of the fields at the bottom with XXX.

I also have from time to time moved some fields to the top that I need to work on the mail log I am building. I am not familiar with the auto correct (or auto corrupt) turned OFF that you mention but I will google it.

 

[Dick7Access]

The Name AutoCorrect Options are on the Current Database section of the Access options. This tool is intended to help you manage name changes. So if you change a field name or a table name, the changes get propagated to all the places the changed name was used. Problem is that it doesn't work the way people expect it to and therefore, it can get you into serious trouble. Best action is to always have it off. If you are embarking on a name changing campaign, you can turn it on and also turn on the log name changes option so you can see what got changed but you need to understand what it is doing. I've written about it here. If you can't find anything, Post back and I'll attach a document that explains how it works. Basically people think that when you change column name abc to abd, all references are immediately changed. NOPE. The change is logged but it isn't applied until the next time an affected object gets opened and that could be weeks later and not even until after you have distributed the FE. So the short of it is, YOU need to figure out what needs to be changed and make sure you open ALL objects that would be affected by the change to ensure that you don't distribute the FE with changes not applied.

Thanks. Planning a trip up your way in May.

 

[MajP]

You can alphabetize a tabledef setting the ordinal position to a constant value for all fields

Field.OrdinalPosition property (DAO)

Office developer client VBA reference documentation

learn.microsoft.com

 

[Gasman]

Thanks Pat,

Nomination is part of my goal. This DB I made over 24 years ago, when I knew next to nothing about access and access was not quite so sophisticated. I started with the flat db that came with my computer and was desperate to bring some organization to my minister. I am of course using a copy to try and fix it, because if I loose it I am in big trouble. I do have some of the fields at the bottom with XXX.

I also have from time to time moved some fields to the top that I need to work on the mail log I am building. I am not familiar with the auto correct (or auto corrupt) turned OFF that you mention but I will google it.

Well, hopefully you are taking frequent multiple backups as you try and make these changes?

 

[Dick7Access]

@MajP Good find However, changing the ordinal order on the table might have a ripple effect that is undesirable so Dick has to consider that also. For example, any query where you Select * would be affected by this change. Probably not a big deal since usually we only care about the order if we are exporting the data to Excel and in that case, Dick can just rewrite the export query.


Don't forget to give me a heads up and I'll try to connect with you if possible I always look forward to actually meeting people I've been talking to for years

Yes, I got the idea when our brit friends healed a get together at a local pub. For a while I put out an invite to AWF people in every city, I would be in. Only two took me up on it. I can remember his name but a young guy from Jacksonville, FL. He has since moved and i don't know where to. Also doc twice in the bayous.

 

[The_Doc_Man]

Also doc twice in the bayous.

Speaking of which... if you ever DO come back this way, Dick, and can give us at least a few days of warning, my dear wife will make some authentic Cajun-style jambalaya (rice mixed with chopped sausage and other chopped meats) OR a Cajun-style gumbo, which is usually done with okra, pork sausage and shredded chicken... though it could also be done with shrimp. Unfortunately, we don't have a way to do a hunter's gumbo, which would require venison sausage and duck meat to go with the more traditional components.

 

[Gasman]

Red beans and rice would do me.

 

[Dick7Access]

Speaking of which... if you ever DO come back this way, Dick, and can give us at least a few days of warning, my dear wife will make some authentic Cajun-style jambalaya (rice mixed with chopped sausage and other chopped meats) OR a Cajun-style gumbo, which is usually done with okra, pork sausage and shredded chicken... though it could also be done with shrimp. Unfortunately, we don't have a way to do a hunter's gumbo, which would require venison sausage and duck meat to go with the more traditional components.

Thanks for the invite. Since some people consider me a Cajun it would be hard for me to pass that up. As of right now it looks like I will be going thru during the week of March 12 to 18. I will let you know more accurate day when we get closer. All depends on how many meetings I pick up.

 

[MajP]

Option Compare Database
Option Explicit

Public Sub AlphabetizeTable(TableName As String)
  Dim tdf As TableDef
  Dim colSort As New Collection
  Dim fld As DAO.Field
  Dim db As DAO.Database
  Dim i As Integer
 
  Set db = CurrentDb
  Set tdf = db.TableDefs(TableName)
  For Each fld In tdf.Fields
   If colSort.Count = 0 Then
      colSort.Add fld.Name, fld.Name
   Else
   For i = 1 To colSort.Count
      If IsPK(tdf, fld.Name) Then
        colSort.Add fld.Name, fld.Name, i
        Exit For
      ElseIf fld.Name < colSort(i) And Not IsPK(tdf, colSort(i)) Then
        colSort.Add fld.Name, fld.Name, i
        Exit For
      ElseIf colSort.Count = i Then
        colSort.Add fld.Name, fld.Name
        Exit For
      End If
    Next i
  End If
  Debug.Print "fld " & fld.Name
  Next fld
  For i = 1 To colSort.Count
    Debug.Print colSort(i)
    tdf.Fields(colSort(i)).OrdinalPosition = i
  Next i

End Sub
Public Function IsPK(tdf As TableDef, fldName As String) As Boolean
  Dim idx As DAO.Index
  Dim fld As DAO.Field
  For Each idx In tdf.Indexes
    'Debug.Print "IDX" & idx.Name
    If idx.Name = "Primarykey" Then
       For Each fld In idx.Fields
        If fld.Name = fldName Then
          IsPK = True
          Exit Function
        End If
      Next fld
    End If
  Next idx
End Function
Public Sub testit()
 AlphabetizeTable "customers"
End Sub
Public Sub TestAll()
  Dim tdf As TableDef
  For Each tdf In CurrentDb.TableDefs
    If Left(tdf.Name, 4) <> "MSYS" Then AlphabetizeTable (tdf.Name)
  Next tdf
End Sub

This leaves the PKs at the beginning of the table, I doubt you want those not at the beginning.
If for some reason you want to switch between the original order and the alphabetized order then you could create a custom property for a field "OrginalOrdinalPosition" and store that value. Then you could reorder it back to the original position.

 

[Dick7Access]

Option Compare Database
Option Explicit

Public Sub AlphabetizeTable(TableName As String)
  Dim tdf As TableDef
  Dim colSort As New Collection
  Dim fld As DAO.Field
  Dim db As DAO.Database
  Dim i As Integer

  Set db = CurrentDb
  Set tdf = db.TableDefs(TableName)
  For Each fld In tdf.Fields
   If colSort.Count = 0 Then
      colSort.Add fld.Name, fld.Name
   Else
   For i = 1 To colSort.Count
      If IsPK(tdf, fld.Name) Then
        colSort.Add fld.Name, fld.Name, i
        Exit For
      ElseIf fld.Name < colSort(i) And Not IsPK(tdf, colSort(i)) Then
        colSort.Add fld.Name, fld.Name, i
        Exit For
      ElseIf colSort.Count = i Then
        colSort.Add fld.Name, fld.Name
        Exit For
      End If
    Next i
  End If
  Debug.Print "fld " & fld.Name
  Next fld
  For i = 1 To colSort.Count
    Debug.Print colSort(i)
    tdf.Fields(colSort(i)).OrdinalPosition = i
  Next i

End Sub
Public Function IsPK(tdf As TableDef, fldName As String) As Boolean
  Dim idx As DAO.Index
  Dim fld As DAO.Field
  For Each idx In tdf.Indexes
    'Debug.Print "IDX" & idx.Name
    If idx.Name = "Primarykey" Then
       For Each fld In idx.Fields
        If fld.Name = fldName Then
          IsPK = True
          Exit Function
        End If
      Next fld
    End If
  Next idx
End Function
Public Sub testit()
AlphabetizeTable "customers"
End Sub
Public Sub TestAll()
  Dim tdf As TableDef
  For Each tdf In CurrentDb.TableDefs
    If Left(tdf.Name, 4) <> "MSYS" Then AlphabetizeTable (tdf.Name)
  Next tdf
End Sub

This leaves the PKs at the beginning of the table, I doubt you want those not at the beginning.
If for some reason you want to switch between the original order and the alphabetized order then you could create a custom property for a field "OrginalOrdinalPosition" and store that value. Then you could reorder it back to the original position.

Thanks, I will give it a try as soon as I have time.

 

[MajP]

Thanks, I will give it a try as soon as I have time.

There may be a better interface to do this that could be built rather easily. Depends on what you have and what you want to save. Do you know what you are looking for when you are deleting? You could have a form where you select a table and in a listbox it shows the fields and maybe the data type. Instead of really sorting the table you can sort the listbox. Make that a find as you type listbox. Once you find a field you could have a delete field button.

If it is the other way around and you are only selecting a few fields to save, then I would have a multiselect listbox to select all the fields. Then a button to run a make table query with the saved fields. Would likely have to save the autonumber PK to be able to relink.

 

[Dick7Access]

There may be a better interface to do this that could be built rather easily. Depends on what you have and what you want to save. Do you know what you are looking for when you are deleting? You could have a form where you select a table and in a listbox it shows the fields and maybe the data type. Instead of really sorting the table you can sort the listbox. Make that a find as you type listbox. Once you find a field you could have a delete field button.

If it is the other way around and you are only selecting a few fields to save, then I would have a multiselect listbox to select all the fields. Then a button to run a make table query with the saved fields. Would likely have to save the autonumber PK to be able to relink.

I may try that also. Unfortunately, I don't have a lot of time to work on it. right now, I am scheduled to be in SC in a few days, then Seffner, Fl then Tampa.