-2

I need to iterate through a record set but when it gets to a user name field I want to do a lookup to a user ID table to pull in the actual name. Then end loop when cusip column is null?

braX
  • 9,702
  • 5
  • 16
  • 29
Aaron Anderson
  • 1
  • What have you tried so far? – Warcupine Sep 18 '19 at 17:09
  • Private Sub OpenRecordset() Dim i As Integer Dim db As Database Dim rs As Recordset Set db = CurrentDb Set rs = db.OpenRecordset("tblimport") Do Until Cusip Is Null Loop – Aaron Anderson Sep 18 '19 at 17:34
  • What is `cusip`? – Lee Mac Sep 18 '19 at 17:37
  • That's not really an attempt... It's a bunch of declarations and a loop that won't work. https://stackoverflow.com/questions/5864160/code-to-loop-through-all-records-in-ms-access This will get you started. – Warcupine Sep 18 '19 at 17:41
  • Am new to VBA. But what am trying to do is import a table. Then upload to SharePoint. However, I only need about 5 fields from the table and then I need to add another column called Processor that pulls in based on the user doing the upload. I was able to create the VBA to import the table. I know how to append to the SharePoint list table. I have 2 issues 1. table pulls in a bunch of blank records that I don't need. 2. I need an additional column added based on User who is doing the upload. – Aaron Anderson Sep 18 '19 at 17:43

1 Answers1

0

You can iterate over a recordset which already includes the actual name by opening a recordset for a SQL statement such as:

select * from tblImport inner join tblUser on tblImport.UserID = tblUser.ID

The code might look something like:

Sub MySub()
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim flg As Boolean

    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("select * from tblImport inner join tblUser on tblImport.UserID = tblUser.ID")

    With rst
        If Not .EOF Then
            .MoveFirst
            Do Until .EOF Or flg ' No short-circuit evaluation in VBA
                flg = IsNull(!Cusip)
                .MoveNext
            Loop
        End If
        .Close
    End With
    Set rst = Nothing
    Set dbs = Nothing
End Sub

You'll need to change the field & table names to suit your data.

You haven't stated what you actually want to do with the data within the loop.

Lee Mac
  • 14,469
  • 6
  • 23
  • 70
  • The data within the loop I want to create a table that I can append to a linked sharepoint list table. Here is the query I have to append the tables. I will need to add the new column once I am able to create it. INSERT INTO [CMO Tracking LOCAL] ( [Receive Date], [Product Type], Cusip, [Redemption Date], [Publication Date], [Total Amount Called] ) SELECT Date(), [CDO/SFS], Cusip, [RED# DATE], [PUB# DATE], [PRINCIPAL PAYMENT] FROM tblimport WHERE cusip is not null and cusip <> ""; – Aaron Anderson Sep 18 '19 at 17:54