Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    St. Marys, Ontario, Canada
    Posts
    270
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Updating some fields, but not others (2000)

    We are putting a database together for all the years of a Junior B hockey team in St. Marys, Ontario called the Lincolns (just in case some of you played for the team), and I have put together a database of the scant information I have. I have contact fields (name, address, etc.) and a field for every year of the team and have sent out Excel spreadsheets to some players to help fill in the missing information and get it back to me. How do I update my records of each player replacing only the missing fields in the Access database with information that has been added to the Excel spreadsheet? I can't replace the whole record with the updated information, because players often are on the team for several years, and that would erase some vital information. Also, I don't sent every field to each individual.

    I hope the answer is simple. I am not at the level of writing macros.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Updating some fields, but not others (2000)

    You can import or link the Excel spreadsheets in your database, but how are you going to match the data in the spreadsheets to records in your Access table? Have you assigned each player a unique ID, or should records be matched on the basis of name and address fields?

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    St. Marys, Ontario, Canada
    Posts
    270
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Updating some fields, but not others (2000)

    I purposely didn't give each player a number, so there is no primary key field. They have to be matched via their name and address. Should I have given each player a number and set a primary key field? I still can do this, although I would prefer not to, since the first round of information has already been sent out.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Updating some fields, but not others (2000)

    A unique ID would have been more convenient, but a combination of name and address fields that is guaranteed to be unique will work too. There should be a unique index on this combination of fields.

    The following procedure will update blank fields in your "master" table, but leave non-blank fields unchanged. If you also want to update non-blank fields (corrections), you will have to find a way to determine which non-blank fields should be updated and which ones shouldn't.

    Import or link an Excel spreadsheet.
    Create a query based on your master table and on the imported/linked table.
    Join them on the fields needed to match records.
    Add the fields from the master table that you want to be updated.
    Select Query | Update Query.
    In the 'Update to' line, enter an expression like the following under each field:

    Nz([MasterTable].[FieldName],[ExcelTable].[FieldName])

    with the correct names of the master table, Excel table and field substituted. The Nz function will result in [MasterTable].[FieldName] if that is non-blank (so the field will remain unchanged), and in [ExcelTable].[FieldName] is [MasterTable].[FieldName] is blank (to update the field with the value from Excel).
    Select Query | Run to execute the query.

    It is a good idea to make a backup of the database before trying this, so that you can go back if things get messed up.

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    St. Marys, Ontario, Canada
    Posts
    270
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Updating some fields, but not others (2000)

    Tried it and I got the error message.

  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Updating some fields, but not others (2000)

    Check for validation rules in the destination table.

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    St. Marys, Ontario, Canada
    Posts
    270
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Updating some fields, but not others (2000)

    Checked all the fields and none has a validation rule.

  8. #8
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Updating some fields, but not others (2000)

    Have you checked the Required property of each field in that table?

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Updating some fields, but not others (2000)

    If you can't find the cause, you might attach a stripped down copy of the database to a reply. See <post#=401925>post 401925</post#> for instructions.

  10. #10
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    St. Marys, Ontario, Canada
    Posts
    270
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Updating some fields, but not others (2000)

    Yes, and I assume that the required field should be set at "no".

  11. #11
    Star Lounger
    Join Date
    Jan 2003
    Location
    Manchester, Lancashire, England
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Updating some fields, but not others (2000)

    The thing that often trips me up when appending is trying to append to a table on the 'many' side of a relationship before appending to the 'one' side. In other word the append fails 'cos the linked field has no corresponding field in the linked table.

  12. #12
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    St. Marys, Ontario, Canada
    Posts
    270
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Updating some fields, but not others (2000)

    Here is the stripped-down copy of the database.

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Updating some fields, but not others (2000)

    The problem is that the imported table contains zero-length strings, and the text fields in the master table don't allow zero-length strings. The following VBA procedure will replace zero-length strings with null values. After running it, the update query will execute correctly.

    Sub ReplaceEmptyStrings()
    Dim cnn As ADODB.Connection
    Dim rst As New ADODB.Recordset
    Dim fld As ADODB.Field
    Set cnn = CurrentProject.Connection
    rst.Open "tbleUpdatedInfo", cnn, adOpenKeyset, adLockOptimistic, adCmdTableDirect
    Do While Not rst.EOF
    For Each fld In rst.Fields
    Select Case fld.Type
    Case adBSTR, adChar, adLongVarChar, adLongVarWChar, adVarChar, adVarWChar, adWChar
    If fld.Value = "" Then
    fld.Value = Null
    End If
    End Select
    Next fld
    rst.Update
    rst.MoveNext
    Loop
    rst.Close
    Set fld = Nothing
    Set rst = Nothing
    Set cnn = Nothing
    End Sub

    See attached file (Access 2000 format, zipped)

  14. #14
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    St. Marys, Ontario, Canada
    Posts
    270
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Updating some fields, but not others (2000)

    By the way, Hans, thanks for all the help. Not really knowing where to stick the VBA procedure, would it be possible to set all the text fields in the master table so that they will accept zero length strings?

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Updating some fields, but not others (2000)

    As shown in the version I attached in my previous reply, the VBA procedure should go into a standard module. You can call it from a command button on a form, or from a macro, for example. You could combine deleting the zero-length strings and executing the update query.

    Yes, you could allow zero length strings in all text fields in the master table. The update query should work then.

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •