none
Access 2013 - Table Relationship Issue RRS feed

  • Question

  • I have a new database. I've imported data from an Excel 2013 spreadsheet to a Table (Contact) & obviously not being, it would appear, at all proficient I seem to have caused an issue with 'pulling up' data stored in the tables via various forms.   This issue is not limited to the Table I appended the data to and seems to impact on the Relationships on a number of tables.  It would appear that when I attempt to pull up data from a Table(s) via a dropdown combo box in a form it now only seems that a blank form is 'recovered' albeit the code is still correct (unchanged).  I have noticed that when I access the table directly there is now a COLUMN with a + sign at the start of each record.  When you depress the + sign it 'pops-up' the relationship table, not the whole table but a NEW Record blank line.  I can only think that I have changed the format of my Access database. I have made this assumption as when I have accessed previous databases since this is also happening to them. Incidentally, I cleared out ALL data in the hopes that in starting again this would fix the issue.  By the way the Excel spreadsheet was virus checked and import was to a new table (since removed and then Appended to the Contact Table.

    I know that someone will have the answer as would have come across this before but I can't find the 'fix'.

    With much thanks.

    Friday, February 5, 2016 12:52 PM

Answers

  • Glad I could help, Keith. You might check your Contact form to see if it had the data entry property set to Yes. That would make it open to a new record.

    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    • Marked as answer by PilotKarKei Friday, February 12, 2016 11:50 AM
    Monday, February 8, 2016 3:13 PM
  • Bill,

    EXCELLENT. All working now but for the contact form (Select Contact) that is used to select the Contact and should open up the named contact within a form <Contact> even though the table is fine now. Can't explain it as it still insists on opening a New blank contact form. It'll probably be easier just to write a new form.  Everything else is fine.  Again, very grateful. Have a usable database again.

    Keith

    • Marked as answer by PilotKarKei Friday, February 5, 2016 10:04 PM
    Friday, February 5, 2016 10:04 PM

All replies

  • That "+" indicates a sub datasheet. Those darn things can really slow up a database.

    1. Open the table in design view.
    2. Open the Properties sheet.
    3. Replace the sub datasheet [Auto] with [none]. That will remove the sub data sheet.
    4. Save the table and compact the database.

    This should be done for all tables and for any new ones you add. An easy way to do this for all tables is to run this function:

    Public Function dev_TbrTurnOffSubDataSheets()
    'Purpose  : Access sets all tables' SubDataSheets to Auto which slows up loading time.
    '           This function will set the property to [none].
    'DateTime : 1/14/2004 09:20
    'Author   : Bill Mosca
        Dim db As DAO.Database
        Dim prop As DAO.Property
        Dim propName As String, propVal As String
        Dim propType As Integer, i As Integer
    
        On Error Resume Next
    
        Set db = CurrentDb
    
        propName = "SubDataSheetName"
        propType = 10
        propVal = "[NONE]"
    
    
        For i = 0 To db.TableDefs.Count - 1
    
            If (db.TableDefs(i).Attributes And dbSystemObject) = 0 Then
    
                If db.TableDefs(i).Properties(propName).Value <> propVal Then
                    db.TableDefs(i).Properties(propName).Value = propVal
                End If
    
                If Err.Number = 3270 Then
                    Err.Clear
                    Set prop = db.TableDefs(i).CreateProperty(propName)
                    prop.Type = propType
                    prop.Value = propVal
                    db.TableDefs(i).Properties.Append prop
                Else
                    If Err.Number <> 0 Then
                        Resume Next
                    End If
                End If
    
            End If
        Next i
    
        MsgBox "The " & propName & _
                " value for all non-system tables has been updated to " & propVal & "."
    
        Set prop = Nothing
        Set db = Nothing
    
    End Function
    

    Do run the function, press Alt+F11 to open the code window. Create a new standard module. Copy the function into it. Press Ctrl+G to open the Immediate window. Type dev_TbrTurnOffSubDataSheets in the IW and press Enter. That will clear sub datasheets out of every table you have.


    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    Friday, February 5, 2016 4:41 PM
  • Hi Bill,

    Many thanks for this.  Looks very comprehensive so will make a start.  In anticipation, I'm very grateful for your detailed mailing.

    Keith

    Friday, February 5, 2016 7:24 PM
  • Keith - You are very welcome. I wrote several functions for cleaning up databases. I've posted the entire module at my Yahoo Group, MS_Access_Professionals. You can find it in the Files>3_Code Snippets folder and is named basDevToolbar.txt.

    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    Friday, February 5, 2016 8:09 PM
  • Bill,

    EXCELLENT. All working now but for the contact form (Select Contact) that is used to select the Contact and should open up the named contact within a form <Contact> even though the table is fine now. Can't explain it as it still insists on opening a New blank contact form. It'll probably be easier just to write a new form.  Everything else is fine.  Again, very grateful. Have a usable database again.

    Keith

    • Marked as answer by PilotKarKei Friday, February 5, 2016 10:04 PM
    Friday, February 5, 2016 10:04 PM
  • Glad I could help, Keith. You might check your Contact form to see if it had the data entry property set to Yes. That would make it open to a new record.

    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    • Marked as answer by PilotKarKei Friday, February 12, 2016 11:50 AM
    Monday, February 8, 2016 3:13 PM