Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2005

    Unanswered: function with two variables... problem

    A pretty simple function call just doesn't seem to wrok.

    I have a function nykopierfakfore, which creates an excel sheet, and fills it out with values according to the parameters passed on to the function.

    However, the function call fails, expecting = even though the function doesn't return a value.

    If I add a simple X = nykopierfakfore(faknr, abbonfaktureret) I get another error.

    I've also tried explicitely assigning the parameters with nykopierfakfore(X:=faknr, X:=abbonfaktureret)


    Cheers, Trin


    nykopierfakfore(faknr, abbonfaktureret)

    Public Function nykopierfakfore(Faktura As Double, abbonfak As Boolean)
        ' ***********************************
        ' * Faktura på forestillinger       *
        ' * NY kopiering                    *
        ' * Senest rettet 25/10 06 KET      *
        ' ***********************************
        Dim objXL As Object
        Dim objActiveWkb As Object
        Dim dbxl As DAO.Database
        Dim rapskema_RS As Recordset
        Dim rapskema_DB As Database
        Dim rapskema_Sql As String
        Dim rapskema_rek As Integer
        Dim rapskema_kol As Integer
        Dim i As Integer
        Dim stivalg As Integer
        Dim sql As String
        Dim Curdb As Database
        Dim stirst As Recordset
        Set Curdb = CurrentDb()
        Dim udvalgtforestilling As Integer
        DoCmd.Hourglass True
        ' ID for stien hentes fra formularen
        Set qdfsti = Curdb.CreateQueryDef("")
        ' ID fra formularen bruges til at indhente stien på netværket hvor excelarket ligger
        With qdfsti
           .sql = "SELECT sti FROM md_tbl_stier where sti_id = 20"
           Set stirst = .OpenRecordset()
        End With
        Set rapskema_DB = CurrentDb()
        ' Prismatrice for ny kopieringsordning
        pris_Sql = "SELECT * FROM md_tbl_kopordning"
        Set pris_RS = rapskema_DB.OpenRecordset(pris_Sql, dbReadOnly)
    ' Excel åbnes og det valgte ark indlæses
        Set objXL = CreateObject("Excel.Application")
        objXL.Visible = False
        objXL.Application.Workbooks.Open FileName:=stirst!sti.Value
        Set objActiveWkb = objXL.Application.ActiveWorkbook
    ' De relevante poster hentes fra detaljeposten
        rapskema_Sql = "SELECT * FROM md_vw_forestilling_kopfak_adr WHERE faknr = " & Faktura
        Set rapskema_RS = rapskema_DB.OpenRecordset(rapskema_Sql, dbReadOnly)
    ' FØrste side af arket udfyldes
        With objActiveWkb
            .Worksheets(1).Cells(22, 2) = rapskema_RS.RecordCount
            .Worksheets(1).Cells(15, 2) = rapskema_RS!Titel
            .Worksheets(1).Cells(24, 2) = rapskema_RS!Titel
            .Worksheets(1).Cells(16, 2) = rapskema_RS!Adresse1
            .Worksheets(1).Cells(17, 2) = rapskema_RS!Postnummer
            .Worksheets(1).Cells(17, 2).HorizontalAlignment = xlLeft
            .Worksheets(1).Cells(18, 2) = rapskema_RS![By]
            .Worksheets(1).Cells(19, 2) = rapskema_RS!Kontaktperson
            .Worksheets(1).Cells(26, 2) = rapskema_RS!kunde2
        End With
        With objActiveWkb
                .Worksheets(2).Cells(5, 3) = (rapskema_RS!fakreelsek - rapskema_RS!totsek)
                .Worksheets(2).Cells(5, 5) = pris_RS!prispersek
        End With
                rekketeller = 10
                While rapskema_RS.EOF <> True
                    With objActiveWkb
    ' Beregning af vederlag
                        .Worksheets(2).Cells(rekketeller, 1) = rapskema_RS!Forestilling
                        .Worksheets(2).Cells(rekketeller, 3) = rapskema_RS![Ialt sekunder]
                        .Worksheets(2).Cells(rekketeller, 4) = "sek"
                        .Worksheets(2).Cells(rekketeller, 5) = CDec(pris_RS!prispersek)
                        .Worksheets(2).Cells(rekketeller, 6) = CDec(rapskema_RS![Ialt sekunder] * pris_RS!prispersek)
                        .Worksheets(2).Rows(rekketeller + 1).Insert xlDown
    ' Udfyldning af faktura
                        .Worksheets(5).Cells(rekketeller + 26, 1) = rapskema_RS!Forestilling
                        .Worksheets(5).Cells(rekketeller + 26, 4) = rapskema_RS![Ialt sekunder]
                        .Worksheets(5).Cells(rekketeller + 26, 5) = "sek"
                        .Worksheets(5).Cells(rekketeller + 26, 6) = CDec(pris_RS!prispersek)
                        .Worksheets(5).Cells(rekketeller + 26, 8) = CDec(rapskema_RS![Ialt sekunder] * pris_RS!prispersek)
                        .Worksheets(5).Rows(rekketeller + 26 + 1).Insert xlDown
                    End With
                    rekketeller = rekketeller + 1
    ' Recordset nulstilles OG
    ' der åbnes et nyt recordset over de relevante rapporteringer for forestillingen
                Set rapskema_RS = Nothing
        MsgBox "Udfyldning af Excelark er gennemført. Excel åbnes", vbOKOnly
        DoCmd.Hourglass False
        objXL.Visible = True
    End Function
    IT squid: networks, servers, firewalls, routers and I dabble a little with SQL-server and Access as well....

  2. #2
    Join Date
    Feb 2004
    One Flump in One Place
    If it doesn't return anything, make it a Sub - the only distinction between a sub and a function it that a function returns a value. Otherwise call it like this:
    Call nykopierfakfore(faknr, abbonfaktureret)
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Apr 2004
    Derbyshire, UK
    Provided Answers: 1

    Have you tried it without parentheses

    ie. nykopierfakfore faknr, abbonfaktureret

    You use parentheses when there is a return a value be assigned, hence the expected = sign (or with the Call keyword).

    A typical examle is MsgBox which returne a value when you use parentheses depending on the button clicked.



  4. #4
    Join Date
    Feb 2004
    Chicago, IL
    The problem I see is in your function declaration:

    Public Function nykopierfakfore(Faktura As Double, abbonfak As Boolean)

    After the close parenthesis you need to tell Access what kind of data you are returning:

    Public Function nykopierfakfore(Faktura As Double, abbonfak As Boolean)
    as String

    Means that a string will be returned. Also, you can call a function two ways. The first is with parenthesis in which case the assumption is you are going to use the value that is returned (so you need to use an = and assign it to an appropriate variable) or without the parenthesis in which case no value is returned and you don't need the =.

Posting Permissions

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