Results 1 to 4 of 4
  1. #1
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel to Access (2k)

    Hi,
    I have an excel workbook that is effectively performs calculations.
    There is a control on it called cmdCalc with the code below this post.

    Basically it returns a Grand Total that I need to export to a field within Access and I'm not too sure how to do it.

    I've attached the original excel document for reference.

    Private Sub cmdCalc_Click()
    BPD = 0: BPU = 0: FNR = 0: FNU = 0: PAW = 0: PWU = 0: PAH = 0: PHU = 0
    DTW = 0: DWU = 0: DTH = 0: DHU = 0: DSE = 0: DSU = 0: SHR = 0: SHU = 0
    OHA = 0: OHU = 0: PAP = 0: PPU = 0: ACV = 0: ACU = 0: ACC = 0: ASE = 0
    ASU = 0: ASC = 0: CSE = 0: CSU = 0: CSC = 0: BWA = 0: NEF = 0: BAP = 0
    BPDR = 0: BPUR = 0: FNRR = 0: FNUR = 0: PAWR = 0: PWUR = 0: PAHR = 0: PHUR = 0
    DTWR = 0: DWUR = 0: DTHR = 0: DHUR = 0: DSER = 0: DSUR = 0: SHRR = 0: SHUR = 0
    OHAR = 0: OHUR = 0: PAPR = 0: PPUR = 0: ACVR = 0: ACUR = 0: ACCR = 0: ASER = 0
    ASUR = 0: ASCR = 0: CSER = 0: CSUR = 0: CSCR = 0: BWAR = 0: NEFR = 0: BAPR = 0
    Total = 0: GTotal = 0: PPEA = 0: PPE1A = 0: PPE2A = 0
    Flag = False
    CheckErrors
    If Flag = True Then
    Exit Sub
    Else
    A = cboCaseType.Value
    B = cboOffence.ListIndex
    C = cboAdvocate.Value
    If A = 6 And C = "F" Then
    NotingJunior
    Exit Sub
    ElseIf A <> 6 And C = "F" Then
    MsgBox "Noting Juniors only applies to Trials (code 6)", vbOKOnly + vbInformation, "Incorrect case type"
    Exit Sub
    End If
    Call Populate(A, B, C)
    End If
    Range("M9:P39").ClearContents
    Range("M9:P39").Font.Color = RGB(0, 0, 0)
    If Not FlagIt Then Exit Sub
    If A = 5 Or (A = 8 And Period = 1) Then
    Calculate58
    ElseIf A = 8 And Period > 1 Then
    Calculate8
    ElseIf A = 6 Then
    Calculate6
    Else
    Calculate9
    End If

    End Sub
    Attached Files Attached Files

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

    Re: Excel to Access (2k)

    What is supposed to go where? Details, please.

  3. #3
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel to Access (2k)

    <P ID="edit" class=small>(Edited by armitageshanks on 13-Nov-07 09:00. )</P>When the user enters data onto the excel spreadsheet and clicks on CmdCalculate it returns a Grand Total value in column P. It is this value that I need to export to an Access database with the path of:
    P:TRIALS UNITGFS.FISTBE.FE GFCGFS Database (Back Up).mdb
    The access database is split into front end and back end, the above path being the front end.

    It needs to go onto FrmMain/FrmPayments&MainSub. Into the field Estimate.
    This field exists in TblPayments linked to TblMain via a field called URN.

    I hope this is enough info, if not let me know.
    Attached Images Attached Images

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

    Re: Excel to Access (2k)

    Is the form open in the database at the correct record when you want to do this?

Posting Permissions

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