Results 1 to 2 of 2

Thread: appending code

  1. #1
    Join Date
    Nov 2004
    Posts
    12

    Unanswered: appending code

    Hi,

    Is it possible to write VBA code that looks at a database table, and appends the value from the previous record if it matches?

    ie. after importing a file, there will be records with the first 2 fields empty, the values i need to populate it are on the previous record?

    So i need to be able to look at the previous record for a certain value and if it matches, copy those values down until it reaches a null value in the field.?

    thanks

  2. #2
    Join Date
    Feb 2004
    Posts
    59

    basic response

    Hi there- i'm pretty new, so this may not be 100% but hopefully it'll get you on your way...

    if we break it into parts, i think you could use something like this

    (declare your database & recordset - set the database and recordset)
    setup temp variables (DIM) (I like to use strings) for:
    Field1Data, Field2Data, etc.. etc..

    for example, say we're searching by Last Name... and we want to pull up a users first name and phone number...

    I've got a table in my database as: tblCustomerInfo, with fields FirstName, LastName, PhoneNumber.

    My recorset is rs , and the index is the last name (just per say), and I've got a textbox on my form for the "user" to type in last name...

    so, i'd run something like this...
    rs.Index = "LastName"
    rs.Seek "=", Me.txtLastName.Value (or .Text if its not VB in Access)... this will search the recordset (for the value that the users typed into the textbox)
    If rs.NoMatch Then MsgBox "No Records found", vbokonly
    Else: Field1Data = rs!FirstName (copies the first name from the database into the temp variable
    Field2Data = rs!PhoneNumber (copies the phone number)
    rs.AddNew
    rs!LastName = Me.txtLastName.Value (creating a new recordset with the same last name in the textbox)
    rs!FirstName = Field1Data (creates the first name from the last recordset info.)
    rs!PhoneNumber = Field2Data (ibid with the phone number)
    rs.Update (updates the new recordset)
    rs.Close (closes the recordset)
    End If

    I know the example's probably not the most clear/concise, but maybe it'll give you some ideas....
    i took a few liberties, assuming you already know a bit about VB / VBA, but if you need more help figuring out what we've got up there.... just re-post...

    Being a beginner myself, it may not be 100%, but like i said earlier, a decent start perhaps?
    -Matt H-
    www.comperfection.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
  •