VBA which adds to a temp table to export is pulling all the data through

[shabbaranks]

Hi all,

Im trying to get my code to pull through some data from another table in order to export as a csv. Some data is coming through but other fields arent. Whats happening is a button (the code is below) copies to a holder table - at this point the fields are getting lost. Theres no error it just copies everything apart from costcode and description.

Private Sub ExportCSV_Click()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim sHour As String
    Dim sName As String
    Dim sPath As String
    sName = Environ("Username")
    
    'CHANGE THIS PATH IF YOU WANT A SPECIFIC LOCATION FOR THE CSV FILE
    sPath = "d:\test\Output.csv"
 
    Set db = CurrentDb()
    Set rs = db.OpenRecordset("ExportCSVTable", dbOpenDynaset)
    With rs
        .AddNew
        Select Case Me.Hour.Value
        Case Is <> 0
            sHour = "CC"
        Case Else
            sHour = "CD"
        End Select
        !fldType = Nz(sHour, "")
        !fldProjActRef = Me.Project.Column(2) & Me.SubProjectCode
        !fldCostCode = Me.CostCode
        !fldTaskDate = Me.DatePicker
        !fldDescription = Me.Description
        !fldName = Me.LoggedInUser
        !fldQuantity = Me.Hour
        !fldRate = 0
        .Update
        
        .MoveLast
        
        DoCmd.TransferText acExportDelim, , "ExportToCSVQuery", sPath
        
    End With
    
    rs.Close
    db.Close
    Set rs = Nothing
    Set db = Nothing

End Sub

Any ideas please? Or if I havent explained clearly please specifiy what else you need to know.



Thanks

[aytee111]

I'm confused - why are you using code instead of a query? An append query that takes the information from the form as you are doing above.

[pbaldy]

I don't see anything offhand that would drop those two fields. Have you set a breakpoint, stepped through the code and checked the value coming from those 2 controls?

http://www.baldyweb.com/Debugging.htm