How to change the code below (vba macro working ok in Excel) for an Access report?

[Jen]

I have a vba macro in an excel invoice that collects data from 4 cells,
calculates the necessary checksums and produce a barcode in a specified
cell.

How do I change the code below (vba macro working ok in Excel) for an Access
report (called "invoiceprint"?

Where in Excel the amount is retrieved by "amount =
CDbl(Worksheets("Sheet1").Range("A1").Value)" / in my access report this
value is in a textbox named "text_amount"
Where in Excel the account is retrieved by "account =
Trim(Worksheets("Sheet1").Range("A2").Value)" / in my access report this
value is in a textbox named "text_account"
Where in Excel the reference is retrieved by "reference =
Trim(Worksheets("Sheet1").Range("A3").Value)" / in my access report this
value is in a textbox named "text_reference"
Where in Excel the duedate is retrieved by "duedateStr =
Worksheets("Sheet1").Range("A4").Value" / in my access report this value is
in a textbox named "text_duedate"
and where in Excel the produced barcode is printed in the cell A10;
"...Worksheets("Sheet1").Range("A10").Select..." / in my access report this
value should be generated in a textbox named "text_barcodeoutput"

Jen


Sub CreateBarCode()

Dim amount As Double
amount = CDbl(Worksheets("Sheet1").Range("A1").Value)

Rem In this version, the currency is hard-coded as EUR.
Dim currenc As String
currenc = "EUR"

Dim account As String
account = Trim(Worksheets("Sheet1").Range("A2").Value)

Dim reference As String
reference = Trim(Worksheets("Sheet1").Range("A3").Value)

Dim duedateStr As String
duedateStr = Worksheets("Sheet1").Range("A4").Value

Dim duedate As Date, tmdate As tm
'This would use system locale:
'duedate = CDate(duedateStr)
'But instead we use the fixed format 'd.m.yyyy'
Dim dateparts As Variant
dateparts = Split(duedateStr, ".")
duedate = DateSerial(Val(dateparts(2)), Val(dateparts(1)),
Val(dateparts(0)))
tmdate = AsTMDate(year(duedate), month(duedate), day(duedate))

Dim handle As Long
handle = BCL_Invoice_create(0)

Dim resLONG As Long, res As String

resLONG = BCL_Invoice_convert(handle, amount, currenc, account, reference,
tmdate)
res = BCL_convertStringToBSTR(resLONG, -1)

If res <> "" Then
Worksheets("Sheet1").Range("A10").Select

Dim fontLONG As Long, font As String
fontLONG = BCL_getFont(handle)
font = BCL_convertStringToBSTR(fontLONG, -1)
Dim fontSize As Integer
fontSize = BCL_getHeight(handle, 11.3)
Selection.font.Name = font
Selection.font.Size = fontSize
Selection.Value = res
Else
Dim errLONG As Long, err As String
errLONG = BCL_getError(handle)
err = BCL_convertStringToBSTR(errLONG, -1)
MsgBox ("Error = " + err)
End If

BCL_release (handle)
End Sub

 

[Douglas J. Steele]

See whether the sample code at
http://www.mvps.org/access/modules/mdl0006.htm at "The Access Web" can nudge
you in the right direction.