Solved Cannot find the data?

[Gasman]

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?

My form frmCorrection

If I run a query to see what crew are not in the Links table
with

SELECT Crew.ID, Crew.Surname, Crew.Initials
FROM Crew LEFT JOIN Links ON Crew.ID = Links.Crew
WHERE (((Links.Crew) Is Null));

I get

 

[Gasman]

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]

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]

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]

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]

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.

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]

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]

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]

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.

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]

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]

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.

 

[Gasman]

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.

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