Excel with Access

[accessjpm]

All of the code below works except for the
"DoCmd.GoToRecord , , acNewRec" line. I get an error message saying I can't use GoToRecore in design view. However, I didn't think i was in design view. I need a solution, anyone will do. I just need to create a new record so the data from the spreadsheet can be loaded into it. It is a loop so it just needs to add a new form to the end every time. Please help as I must deliver this tomorrow AM. Thanks

Function Opnxcel()
On Error GoTo Err_Opnxcel_Click

Dim oApp As Object
Dim wb As Workbook
Dim ws As Worksheet
Dim i, roes As Integer
Dim letA, letB, letD, letE, letF, letG, letH, letI, letJ, letK, letL, letM, letN, letO, letP, cel As String
Dim cpname, instyp, doctyp, colat, csachk, dwngrd, nav, csaapr, offmem, imagmt, certinc, incfmt, nego, cdgcom As String
Dim sentdt As Date
Dim strMsg, strInput As String

letA = "A"
letB = "B"
letD = "D"
letE = "E"
letF = "F"
letG = "G"
letH = "H"
letI = "I"
letJ = "J"
letK = "K"
letL = "L"
letM = "M"
letN = "N"
letO = "O"
letP = "P"

Set oApp = CreateObject("Excel.Application")
oApp.Visible = False
Set wb = oApp.Workbooks.Open("C:\Ginzberg\derivupdt.xls")
Set ws = wb.ActiveSheet

strMsg = "Enter the number of documents to be uploaded from legal"
strInput = InputBox(Prompt:=strMsg, Title:="Number of Docs", XPos:=2000, YPos:=2000)
roes = CInt(strInput)


For i = 2 To roes
    
    DoCmd.GoToRecord , , acNewRec
    
    cel = letA & i
    cpname = ws.Range(cel).Text
    Me.CounterpartyName = cpname
    cel = letB & i
    instyp = ws.Range(cel).Text
    Me.InstitutionType = instyp
    cel = letD & i
    doctyp = ws.Range(cel).Text
    Me.DocumentType = doctyp
    cel = letE & i
    colat = ws.Range(cel).Text
    Me.Collateral = colat
    cel = letF & i
    csachk = ws.Range(cel).Text
    Me.CSACheckList = csachk
    cel = letG & i
    dwngrd = ws.Range(cel).Text
    Me.DowngradeTrigger = dwngrd
    cel = letH & i
    nav = ws.Range(cel).Text
    Me.NAVTrigger = nav
    cel = letI & i
    csaapr = ws.Range(cel).Text
    Me.CSAApproval = csaapr
    cel = letJ & i
    offmem = ws.Range(cel).Text
    Me.OfferMemo = offmem
    cel = letK & i
    imagmt = ws.Range(cel).Text
    Me.InvestmentManagementAgrmnt = imagmt
    cel = letL & i
    certinc = ws.Range(cel).Text
    Me.CertificateOfIncorporation = certinc
    cel = letM & i
    incfmt = ws.Range(cel).Text
    Me.IncomingFormat = incfmt
    cel = letN & i
    nego = ws.Range(cel).Text
    Me.Negotiator = nego
    cel = letO & i
    sentdt = ws.Range(cel).Text
    Me.SentToCDG = sentdt
    cel = letP & i
    cdgcom = ws.Range(cel).Text
    Me.LegalComments = cdgcom
    
Next i


Exit_Opnxcel_Click:
    Exit Function

Err_Opnxcel_Click:
    MsgBox Err.Description
    Resume Exit_Opnxcel_Click

End Function

 

[mohammadagul]

can you explain more clearly what are u trying to do here?

 

[accessjpm]

I have a database that will need to be updated from an excel spreadsheet daily. However, the sheet only has part of the data that will be entered in a given record, i.e. some fields will be blank. So i have this function opening the spreadsheet and reading the data form the spreadsheet and puting it into the form in access, i just can't seem to make a new form, everything else works.

 

[Mile-O]

Dim oApp As Object
Dim wb As Workbook
Dim ws As Worksheet
Dim i, roes As Integer
Dim letA, letB, letD, letE, letF, letG, letH, letI, letJ, letK, letL, letM, letN, letO, letP, cel As String
Dim cpname, instyp, doctyp, colat, csachk, dwngrd, nav, csaapr, offmem, imagmt, certinc, incfmt, nego, cdgcom As String
Dim sentdt As Date
Dim strMsg, strInput As String

Why have you defined 30 of these variables as Variants?

 

[accessjpm]

Don't understand your question, sort of a self taught programmer and a newbie in access. If you mean as opposed to using an array or some more efficient data structure, its because I have to hand this off in a few hours and I don't have time to mess with that. Any help on the new record thing would be terriffic.

 

[Mile-O]

Don't understand your question, sort of a self taught programmer

So am I.

This is what I mean.

 

[accessjpm]

i had no idea, i though if you put "as data type" at the end that meant that they were all that data type. But anyway, any ideas about my new record problem? I am freakin' out over here