Solved Cannot find the data? (1 Viewer)

Gasman

Enthusiastic Amateur
Local time
Today, 10:47
Joined
Sep 21, 2011
Messages
14,038
Hi all,
In my very first DB I used table lookups, before realising that they can cause more problems than they are worth.
However I do understand what they are doing, and so for this DB have left as is.

I am now in the process of trying to correct any typos, link errors in it.
So I have two J.Rose. One is an Electrical Officer and the other the Purser. I initially added another character after their name, but want to remove that and just have their name.
So I amended the DB to take into account, not only the department they worked in, but also their cabin, to diferentiate between Officers and Crew.

So I have J.Rose twice in my crew table, one with an ID of 37, and another with an ID of 3843. This latter one I presuambly added again in my attempts on correction. :(
However I amended any with ID of 3843 to 37, and if I look at them in my correction form, I see 16 entries for Rose 37
Yet, if I look at the Crew table direct and filter for Rose, I get 3 records that 'appear' to be on 3845, not 37 via the link icon + ?

For the life of me, I cannot see why?

So what am I missing please?

1647254858790.png


My form frmCorrection
1647254924470.png

1647255686845.png

If I run a query to see what crew are not in the Links table
with
Code:
SELECT Crew.ID, Crew.Surname, Crew.Initials
FROM Crew LEFT JOIN Links ON Crew.ID = Links.Crew
WHERE (((Links.Crew) Is Null));
I get
1647255492866.png
 

Gasman

Enthusiastic Amateur
Local time
Today, 10:47
Joined
Sep 21, 2011
Messages
14,038
Isn't always the way. You spend ages trying to solve something, cannot, so ask for help. :(
Just after you do so, you have a Eureka moment, and solve it yourself. :)

The linkage was not to my Links table but a copy of Links I had save ages ago. :)
So I would ask, is there a way to see what that linkage points to in the above picture
 

bob fitz

AWF VIP
Local time
Today, 10:47
Joined
May 23, 2011
Messages
4,717
So I would ask, is there a way to see what that linkage points to in the above picture
Do you mean on one of the forms
 

Gasman

Enthusiastic Amateur
Local time
Today, 10:47
Joined
Sep 21, 2011
Messages
14,038
No Bob,
When you look at a table, if that table has links to other tables, you get a + sign on the left and expanding that get to see the relate records.
My mistake was thinking I was looking at the links table, when I was actually looking at a saved copy from before my changes.
 

GPGeorge

Grover Park George
Local time
Today, 03:47
Joined
Nov 25, 2004
Messages
1,775
I think it is unwise to trust the subdatasheet dealee in tables views in datasheet view. It's one of those "Hey, here's a great idea." things that turns out to be not so great (along with lookup fields in tables, I guess).
They are a hit on performance because dragging that entire child tables recordset into the main table just so you can view it adds to the load time. But as you found, the property which supports it is not terribly intelligent, and can lead to problems like the one you describe. Call me old school, but if I need to see records from parent and child tables, I rely on good, old-fashioned SQL in queries.
 

Mike Krailo

Well-known member
Local time
Today, 06:47
Joined
Mar 28, 2020
Messages
1,030
I'm with GPGeorge, I always turn subdatasheets off for performance reasons.

If you want to see the path to linked tables in your form, use allenbrowne's function.

Code:
Public Function GetDataPath(strTable As String) As String
On Error GoTo Err_Handler
    'Purpose:   Return the full path of the file from the Connect property of this tabledef.
    'Return:    Full path and file name for attached MDB.
    '           Just the path for some other types (e.g. attached text.)
    '           Zero-length string for local table (not attached), or of argument is zero-length.
    '           "#Error" on error, e.g. table not found.
    'Requires:  Split() function for Access 97 or earlier.
    Dim varArray As Variant
    Dim i As Integer
    
    If Trim$(strTable) <> vbNullString Then
        varArray = Split(CurrentDb.TableDefs(strTable).Connect, ";")
        For i = LBound(varArray) To UBound(varArray)
            If varArray(i) Like "DATABASE=*" Then
                GetDataPath = Trim$(Mid$(varArray(i), 10))
                Exit For
            End If
        Next
    End If
Exit_Handler:
    Exit Function

Err_Handler:
    Call LogError(Err.Number, Err.Description, conMod & ".GetDataPath", strTable, False)
    GetDataPath = "#Error"
    Resume Exit_Handler
End Function
 

SHANEMAC51

Active member
Local time
Today, 13:47
Joined
Jan 28, 2022
Messages
310
I am now in the process of trying to correct any typos, link errors in it.
So I have two J.Rose. One is an Electrical Officer and the other the Purser
in such forms, I search for an arbitrary combination of the main fields (department, cabin,service number, surname, first name, position, city, ........- for all the main fields of the table / query), I highlight them in a ribbon (tabular) form for visual final selection

when you click on the desired line - showing complete data on the selected person
example in one of the links at the bottom of the message
 

Gasman

Enthusiastic Amateur
Local time
Today, 10:47
Joined
Sep 21, 2011
Messages
14,038
I think it is unwise to trust the subdatasheet dealee in tables views in datasheet view. It's one of those "Hey, here's a great idea." things that turns out to be not so great (along with lookup fields in tables, I guess).
They are a hit on performance because dragging that entire child tables recordset into the main table just so you can view it adds to the load time. But as you found, the property which supports it is not terribly intelligent, and can lead to problems like the one you describe. Call me old school, but if I need to see records from parent and child tables, I rely on good, old-fashioned SQL in queries.
Hi George,
Yes, I realise that now and I as I mentioned it is my very first DB, and have never removed the Lookup Fields. Even if I did in this case, the fact that it was linked to another copy of the links table would not have helped, I would just see the ID instead of the name. :)

This DB is static in that once I have corrected anything I may find, then it should not get touched again, unless I decide to enhance it. :)

It is merely to support www.bibby-gazette.co.uk.
 

Gasman

Enthusiastic Amateur
Local time
Today, 10:47
Joined
Sep 21, 2011
Messages
14,038
I'm with GPGeorge, I always turn subdatasheets off for performance reasons.

If you want to see the path to linked tables in your form, use allenbrowne's function.

Code:
Public Function GetDataPath(strTable As String) As String
On Error GoTo Err_Handler
    'Purpose:   Return the full path of the file from the Connect property of this tabledef.
    'Return:    Full path and file name for attached MDB.
    '           Just the path for some other types (e.g. attached text.)
    '           Zero-length string for local table (not attached), or of argument is zero-length.
    '           "#Error" on error, e.g. table not found.
    'Requires:  Split() function for Access 97 or earlier.
    Dim varArray As Variant
    Dim i As Integer
   
    If Trim$(strTable) <> vbNullString Then
        varArray = Split(CurrentDb.TableDefs(strTable).Connect, ";")
        For i = LBound(varArray) To UBound(varArray)
            If varArray(i) Like "DATABASE=*" Then
                GetDataPath = Trim$(Mid$(varArray(i), 10))
                Exit For
            End If
        Next
    End If
Exit_Handler:
    Exit Function

Err_Handler:
    Call LogError(Err.Number, Err.Description, conMod & ".GetDataPath", strTable, False)
    GetDataPath = "#Error"
    Resume Exit_Handler
End Function
Thanks for that Mike,
I'll give it a whirl later on.
 

Gasman

Enthusiastic Amateur
Local time
Today, 10:47
Joined
Sep 21, 2011
Messages
14,038
in such forms, I search for an arbitrary combination of the main fields (department, cabin,service number, surname, first name, position, city, ........- for all the main fields of the table / query), I highlight them in a ribbon (tabular) form for visual final selection

when you click on the desired line - showing complete data on the selected person
example in one of the links at the bottom of the message
Not a form though?, just datasheet view of the table
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 06:47
Joined
Feb 19, 2002
Messages
42,970
I can't say for sure what happened with your links but I can suggest what probably happened.

The NameAutoCorrect property in the Current Database section of the Access Options is a very dangerous "helper" and it defaults to on. Its purpose is to propagate changes to names you make in tables to queries and forms. When you renamed the table, this "helpful" feature propagated the change so now you were connected to ChangeTableName instead of OriginalTableName and when you disconnected it, your link stayed with the disconnected table.

My advice and the advice of any expert who has been burned by this "feature" is to turn it OFF and leave it OFF. Unless you understand exactly how it works, it is simply too dangerous to leave active. If you want to change some column names and want its help, you can turn it on temporarily. Just remember to open EVERY SINGLE OBJECT you want changed BEFORE you turn the feature off again. I'll attach a presentation I did some years ago that you should read before you ever use this feature as well as the source document I based it on.
 

Attachments

  • NameAutoCorrectDOC.zip
    81.3 KB · Views: 166
  • NameAutocorrectPPT.zip
    182.7 KB · Views: 215

Gasman

Enthusiastic Amateur
Local time
Today, 10:47
Joined
Sep 21, 2011
Messages
14,038
I'm with GPGeorge, I always turn subdatasheets off for performance reasons.

If you want to see the path to linked tables in your form, use allenbrowne's function.

Code:
Public Function GetDataPath(strTable As String) As String
On Error GoTo Err_Handler
    'Purpose:   Return the full path of the file from the Connect property of this tabledef.
    'Return:    Full path and file name for attached MDB.
    '           Just the path for some other types (e.g. attached text.)
    '           Zero-length string for local table (not attached), or of argument is zero-length.
    '           "#Error" on error, e.g. table not found.
    'Requires:  Split() function for Access 97 or earlier.
    Dim varArray As Variant
    Dim i As Integer
   
    If Trim$(strTable) <> vbNullString Then
        varArray = Split(CurrentDb.TableDefs(strTable).Connect, ";")
        For i = LBound(varArray) To UBound(varArray)
            If varArray(i) Like "DATABASE=*" Then
                GetDataPath = Trim$(Mid$(varArray(i), 10))
                Exit For
            End If
        Next
    End If
Exit_Handler:
    Exit Function

Err_Handler:
    Call LogError(Err.Number, Err.Description, conMod & ".GetDataPath", strTable, False)
    GetDataPath = "#Error"
    Resume Exit_Handler
End Function
Mike,
There is a variable missed off that, conMod ?
I am presuming a constant, curious as to what it would be.

Plus, my tables are not current linked. I brought them into a single DB to upload for a previous problem.
So I assume no connect property ATM, as Ubound is -1

TIA
 

Users who are viewing this thread

Top Bottom