Problems with report on open event

[bodmin]

Hello,

I am trying to print a report with a cheque to be printed on the bottom of a one page report, for this cheque the amount needs to be input in numbers which I can do easily but the cheque also requires that amount to be converted string amounts for each unit type (e.g. Pounds, Tens, Hundreds, Thousands so on and on).

I have attempted to calculate the string values by using VBA in the reports on open event, the code for this is shown below.
Private Sub Report_Open(Cancel As Integer)
On Error GoTo Err_Report_Open

Dim localConnection As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strSql As String
Dim AmountStr As String
Dim chequenum As Integer
Dim Message As String
Dim Amount As Currency
Dim strAmount As String
Dim Convert As String
Dim DecimalPoint As String
Dim HundredThousands As String
Dim TensThousands As String
Dim Thousands As String
Dim Hundreds As String
Dim Tens As String
Dim units As String
Dim Pence As String
Dim adrs As ADODB.Recordset


Set localConnection = CurrentProject.AccessConnection
Set rs = New ADODB.Recordset
Set adrs = New ADODB.Recordset

chequenum = Forms!Main!ChequeNumBox
strSql = "select Amount from Cheque where Cheque_No=" & chequenum & ";"

rs.Open strSql, localConnection, adOpenDynamic, adLockOptimistic

Message = "Please save the current cheque details before printing, please use the save button on the main form"

If rs.EOF Then
MsgBox Message
Else
Amount = rs("Amount")
rs.Close

Reports!ChequePrint.RecordSource = "SELECT Cheque.Cheque_No, Cheque.Descrip AS Cheque_Description, Cheque.RemittanceAdvice, Cheque.ProducedDate AS ProducedDate, Cheque.Amount AS Cheque_Amount, Cheque.Payee, Cheque.PaymentCurrency, Debits.DebitAccount, Debits.Amount AS Debits_Amount, Debits.Description AS Debits_Description FROM Cheque INNER JOIN Debits ON Cheque.Cheque_No=Debits.Cheque_No where Cheque.Cheque_No=" & chequenum & ";"

If Len(Amount) = 1 Then

strAmount = CStr(Amount)
Convert = Mid(strAmount, 1, 1)
Pence = Value(Convert)

End If

If Len(Amount) = 2 Then

strAmount = CStr(Amount)
Convert = Mid(strAmount, 1, 1)
units = Value(Convert)
Convert = Mid(strAmount, 2, 1)
Pence = Value(Convert)
Reports!ChequePrint!HundredThousBox = "Zero"
Reports!ChequePrint!TenThousBox = "Zero"
Reports!ChequePrint!ThousBox = "Zero"
Reports!ChequePrint!HundredBox = "Zero"
Reports!ChequePrint!TensBox = "Zero"
Reports!ChequePrint!UnitsBox = units

strSql = "Update Admin Set Admin.Pence='" & Pence & "', Admin.Units='" & units & "';"
localConnection.Execute strSql
End If

If Len(Amount) = 3 Then

strAmount = CStr(Amount)
Convert = Mid(strAmount, 1, 1)
Tens = Value(Convert)
Convert = Mid(strAmount, 2, 1)
units = Value(Convert)
Convert = Mid(strAmount, 3, 1)
Pence = Value(Convert)
Reports!ChequePrint!HundredThousBox = "Zero"
Reports!ChequePrint!TenThousBox = "Zero"
Reports!ChequePrint!ThousBox = "Zero"
Reports!ChequePrint!HundredBox = "Zero"
Reports!ChequePrint!TensBox = Tens
Reports!ChequePrint!UnitsBox = units

strSql = "update Admin Set Admin.Pence='" & Pence & "', Admin.Units='" & units & "', Admin.Tens='" & Tens & "';"
localConnection.Execute strSql
End If

If Len(Amount) = 4 Then

strAmount = CStr(Amount)
Convert = Mid(strAmount, 1, 1)
Hundreds = Value(Convert)
Convert = Mid(strAmount, 2, 1)
Tens = Value(Convert)
Convert = Mid(strAmount, 3, 1)
units = Value(Convert)
Convert = Mid(strAmount, 4, 1)
Pence = Value(Convert)
Reports!ChequePrint!HundredThousBox = "Zero"
Reports!ChequePrint!TenThousBox = "Zero"
Reports!ChequePrint!ThousBox = "Zero"
Reports!ChequePrint!HundredBox = Hundreds
Reports!ChequePrint!TensBox = Tens
Reports!ChequePrint!UnitsBox = units

strSql = "update Admin set Admin.Pence='" & Pence & "', Admin.Units='" & units & "', Admin.Tens='" & Tens & "', Admin.Hundreds='" & Hundreds & "';"
localConnection.Execute strSql
End If

If Len(Amount) = 5 Then

strAmount = CStr(Amount)
Convert = Mid(strAmount, 1, 1)
Thousands = Value(Convert)
Convert = Mid(strAmount, 2, 1)
Hundreds = Value(Convert)
Convert = Mid(strAmount, 3, 1)
Tens = Value(Convert)
Convert = Mid(strAmount, 4, 1)
units = Value(Convert)
Convert = Mid(strAmount, 5, 1)
Pence = Value(Convert)

Reports!ChequePrint!HundredThousBox = "Zero"
Reports!ChequePrint!TenThousBox = "Zero"
Reports!ChequePrint!ThousBox = Thousands
Reports!ChequePrint!HundredBox = Hundreds
Reports!ChequePrint!TensBox = Tens
Reports!ChequePrint!UnitsBox = units

strSql = "update Admin set Admin.Pence='" & Pence & "', Admin.Units='" & units & "', Admin.Tens='" & Tens & "', Admin.Hundreds='" & Hundreds & "', Admin.Thousands='" & Thousands & "';"
localConnection.Execute strSql
End If

If Len(Amount) = 6 Then

strAmount = CStr(Amount)
Convert = Mid(strAmount, 1, 1)
TensThousands = Value(Convert)
Convert = Mid(strAmount, 2, 1)
Thousands = Value(Convert)
Convert = Mid(strAmount, 3, 1)
Hundreds = Value(Convert)
Convert = Mid(strAmount, 4, 1)
Tens = Value(Convert)
Convert = Mid(strAmount, 5, 1)
units = Value(Convert)
Convert = Mid(strAmount, 6, 1)
Pence = Value(Convert)

Reports!ChequePrint!HundredThousBox = "Zero"
Reports!ChequePrint!TenThousBox = TensThousands
Reports!ChequePrint!ThousBox = Thousands
Reports!ChequePrint!HundredBox = Hundreds
Reports!ChequePrint!TensBox = Tens
Reports!ChequePrint!UnitsBox = units

strSql = "update Admin set Admin.Pence='" & Pence & "', Admin.Units='" & units & "', Admin.Tens='" & Tens & "', Admin.Hundreds='" & Hundreds & "', Admin.Thousands='" & Thousands & "', Admin.TenThousands='" & TensThousands & "';"
localConnection.Execute strSql
End If

If Len(Amount) = 7 Then

strAmount = CStr(Amount)
Convert = Mid(strAmount, 1, 1)
HundredThousands = Value(Convert)
Convert = Mid(strAmount, 2, 1)
TensThousands = Value(Convert)
Convert = Mid(strAmount, 3, 1)
Thousands = Value(Convert)
Convert = Mid(strAmount, 4, 1)
Hundreds = Value(Convert)
Convert = Mid(strAmount, 5, 1)
Tens = Value(Convert)
Convert = Mid(strAmount, 6, 1)
units = Value(Convert)
Convert = Mid(strAmount, 7, 1)
Pence = Value(Convert)

Reports!ChequePrint!HundredThousBox = HundredThousands
Reports!ChequePrint!TenThousBox = TensThousands
Reports!ChequePrint!ThousBox = Thousands
Reports!ChequePrint!HundredBox = Hundreds
Reports!ChequePrint!TensBox = Tens
Reports!ChequePrint!UnitsBox = units

strSql = "update Admin set Admin.Pence='" & Pence & "', Admin.Units='" & units & "', Admin.Tens='" & Tens & "', Admin.Hundreds='" & Hundreds & "', Admin.Thousands='" & Thousands & "', Admin.TenThousands='" & TensThousands & "', Admin.HundredThousands='" & HundredThousands & "';"
localConnection.Execute strSql
End If

If Len(Amount) = 8 Then

strAmount = CStr(Amount)
Convert = Mid(strAmount, 1, 1)
TenMillions = Value(Convert)
Convert = Mid(strAmount, 2, 1)
Millions = Value(Convert)
Convert = Mid(strAmount, 3, 1)
HundredThousands = Value(Convert)
Convert = Mid(strAmount, 4, 1)
TensThousands = Value(Convert)
Convert = Mid(strAmount, 5, 1)
Thousands = Value(Convert)
Convert = Mid(strAmount, 6, 1)
Hundreds = Value(Convert)
Convert = Mid(strAmount, 7, 1)
Tens = Value(Convert)
Convert = Mid(strAmount, 8, 1)
units = Value(Convert)
End If

End If

Reports!ChequePrint!Cheque_No = 1000004
strSql = "insert into Admin (Test1, Test2, Test3, Test4, Test5, Test6, Test7, Test8) Values ('" & TenMillions & "','" & Millions & "','" & HundredThousands & "','" & TensThousands & "','" & Thousands & "','" & Hundreds & "','" & Tens & "','" & units & "');"
localConnection.Execute strSql

Exit_Report_Open:
Exit Sub

Err_Report_Open:
MsgBox Err.Description
Resume Exit_Report_Open

End Sub


Public Function Value(ByVal MyDigit)
Select Case Val(MyDigit)
Case 1: Value = "One"
Case 2: Value = "Two"
Case 3: Value = "Three"
Case 4: Value = "Four"
Case 5: Value = "Five"
Case 6: Value = "Six"
Case 7: Value = "Seven"
Case 8: Value = "Eight"
Case 9: Value = "Nine"
Case Else: Value = "Zero"
End Select

End Function

The problem is that when I open the report it displays the message: You cant assign a value to this object, my problem is that I can not see where I am attempting to do this assignment. This message is then followed by a request for a date field which should be collected using an embeded SQL query and also requests a value for Admin which is only used within SQL queries as a table name.

Hope someone can help, sorry for the long garble but felt that it was the best way I could explain.

Cheers

 

[dhookom]

You are running all this code just to convert a number to a string? There is code at

http://www.mvps.org/access/modules/mdl0001.htm

that converts to dollars etc but should be easily modified. The code should reside outside of the report and report events. All you should have to do is bind a control to something like:
=MyFunction([currencyfield])

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[bodmin]

Im not actually trying to convert the amount into a string representation, but to split each type of unit such as the amount of hundred thousands or amount of hundreds into a string and then fill a textbox with the relevant string, to be printed on a cheque.

The problem therefore is how to split these values from a normal currency amount, which is the value that is available in the reports recordsource.

Cheers

 

[dhookom]

It looks like you are attempting to insert a value into a table. What's wrong with create a generic function or functions that just return the appropriate value(s). Did you look at the link I provided.

BTW: I am going out of town for several days (no computer) so maybe someone else will check this thread if you need more assistance.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[bodmin]

Yeah I have had a look at the link and dont seem to be able to get anything working from that, I have now fixed the parameters problem and the report opens and prints without any errors, but the string values for the cheque are not being calculated by my Value function, have you any tips into how to discover where this function is failing and I would be most grateful.

Cheers

BTW enjoy your vacation dhookom

 

[dhookom]

Hi,
Just got back from a few days on Mackinac Island. Great time even though I didn't see any computers.

I don't even want to begin looking through your code since there are no comments and I don't have time to reverse engineer what you are attempting to accomplish. Maybe you can just describe what you have stored in a table and what you want to appear on a report.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[bodmin]

I have managed to fix this prob, was to do with the function I was using to convert to string with a case select. Cheers for the help, and sorry for the masses of uncommented code I still meaning to get around and do that :->