Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Mar 2003
    Location
    Beddau, Mid Glamorgan, Wales
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Auto entry of missing info (97)

    A friend has come to me with a problem which I thought might have been answered before, but I can't find anything helpful in search.

    She has download from a mainframe of several hundred thousand records - say three relevent fields: company, account, code.

    If the account and/or code is the same as for the previous record, the field(s) is left blank, but she wants each record to have values in each field. Is there a way in Access VBA (of which my knowledge is very limited) to run through the records and copy down account and/or code values where appropriate but not, of course, where values are already present? I can see non-code methods to fill in the missing data, but with so many records to deal with, a manual approach would be very time-consuming.

    Tony.
    Regards,

    Tony
    [s] [/s]
    www.SylviArtist.com

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

    Re: Auto entry of missing info (97)

    Does the table have a primary key? If not, it might be tricky, since you can't be certain that the records are stored in the order they are displayed.

    Anyway, you could use code like this. Replace tblName, Account and Code by the appropriate names.

    Sub FillMissing()
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim varAccount As Variant
    Dim varCode As Variant
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("tblName", dbOpenDynaset)
    Do While Not rst.EOF
    rst.Edit
    If IsNull(rst![Account]) Then
    rst![Account] = varAccount
    Else
    varAccount = rst![Account]
    End If
    If IsNull(rst![Code]) Then
    rst![Code] = varCode
    Else
    varCode = rst![Code]
    End If
    rst.Update
    rst.MoveNext
    Loop
    rst.Close
    Set rst = Nothing
    Set dbs = Nothing
    End Sub

  3. #3
    3 Star Lounger
    Join Date
    Mar 2003
    Location
    Beddau, Mid Glamorgan, Wales
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto entry of missing info (97)

    Hans:

    Many thanks (once again) for your prompt reply. I will have to ask her re. primary key.

    I can follow most of the code. However I am a bit puzzled over when varAccount gets its value. When an empty field is encountered, I can see that its value is taken from varAccount:

    If IsNull(rst![Account]) Then
    rst![Account] = varAccount

    but I don't see where, above that, varAccount is given a value?

    Tony.
    Regards,

    Tony
    [s] [/s]
    www.SylviArtist.com

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

    Re: Auto entry of missing info (97)

    In the Else part:

    Else
    varAccount = rst![Account]

    If the Account field is NOT empty, the varAccount variable is set to the value of this field, so that it can be used for the next record or records in which Account is blank. In the first record, Account is not empty (I hope), so the Else part is executed and varAccount is set. If Account is empty in the next record, the If part is executed. Etc.

  5. #5
    3 Star Lounger
    Join Date
    Mar 2003
    Location
    Beddau, Mid Glamorgan, Wales
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto entry of missing info (97)

    Hans.

    Yes - when you point it out, its so obvious. D'oh.

    Thanks again.

    Tony.
    Regards,

    Tony
    [s] [/s]
    www.SylviArtist.com

  6. #6
    3 Star Lounger
    Join Date
    Mar 2003
    Location
    Beddau, Mid Glamorgan, Wales
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto entry of missing info (97)

    . . . and I see my inability to see it first time has promoted me! . . .

    Tony.
    Regards,

    Tony
    [s] [/s]
    www.SylviArtist.com

Posting Permissions

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