Execute command (1 Viewer)

[Gismo]

Hi all,

I have a subform where I select the transaction to be imported into the main form
After import, I need to run the below command

Me.Parent!txtExchange = ToUGX(Me.Parent!txtDate, Me.Parent!Currency)

This unfortunately does not work as for some reason it is giving me an error down the function coding of the exchange rate lookup

If I create a button on the main form with the below code, it works just fine

Me.txtExchange = ToUGX(Me.txtDate, Me.Currency)

Please could you guide me in the different direction.

The complete code when I run the code is below

Private Sub Select_AfterUpdate()
DoCmd.RunCommand acCmdSaveRecord
DoCmd.OpenQuery "Clear Transactions - Procurement - Temp", acViewNormal, acEdit
DoCmd.OpenQuery "Clear Transactions - Procurement - Temp - Header", acViewNormal, acEdit
DoCmd.OpenQuery "Update Transactions - Procurement Temp from Procurement - Header"
DoCmd.OpenQuery "Update Transactions - Procurement Temp from Procurement - Detail"
DoCmd.Close
DoCmd.OpenForm "PurchaseOrder_Order"
Me.Parent!txtExchange = ToUGX(Me.Parent!txtDate, Me.Parent!Currency)
DoCmd.OpenQuery "Update Transactions - Procurement - Temp - Last Purchase Price"
Forms![PurchaseOrder_Order].Form.Requery
Forms![PurchaseOrder_Order]![PurchaseOrderOrderSubForm].Form.Requery

End Sub

 

[Ranman256]

use the BUILDER to create the code for assigning the text box (it wont get it wrong),
Normally it would look like : = me.subformName!form!txtBox

or you would run a query to add data to the suform textbox.

can you send the code to the function ToUGX() ?

 

[Minty]

Basic debugging, inspect the values you are passing in and make sure they are correct.

Debug.Print "My Values:" & Me.Parent!txtDate, Me.Parent!Currency

 

[CJ_London]

why does everyone keep using the bang (!) rather than dot (.)? - bang does not use intellisense and only generates errors at runtime rather than compile time. There are times when bangs have to be used because the object does not exist at compile time (such as a recordset), but this is not one of them.

after 1200+ posts you should know by now that comments such as

for some reason it is giving me an error down the function coding of the exchange rate lookup

is totally meaningless without providing the error number and description

As far as the sample code is concerned:

• what is the parent form?
• where is the code (presumably on a subform)?
• What form/object are you closing?
• why are you opening the PurchaseOrder_Order form?
• is 'parent' supposed to be that form?
• what error do you get?

 

[Gismo]

why does everyone keep using the bang (!) rather than dot (.)? - bang does not use intellisense and only generates errors at runtime rather than compile time. There are times when bangs have to be used because the object does not exist at compile time (such as a recordset), but this is not one of them.

after 1200+ posts you should know by now that comments such as

is totally meaningless without providing the error number and description

As far as the sample code is concerned:

• what is the parent form?
• where is the code (presumably on a subform)?
• What form/object are you closing?
• why are you opening the PurchaseOrder_Order form?
• is 'parent' supposed to be that form?
• what error do you get?

I am getting a run time error 94, invalid use of null

PurchaseOrder_Order is the main form
PurchaseOrderOrderSubForm is the sub form

Did not think it was necessary to define the form which should close as it will close the form in focus, not so?

The code to extract the exchange rate:

Me.Parent!txtExchange = ToUGX(Me.Parent!txtDate, Me.Parent!Currency)

Below is the code

' arnelgp
' for gismo
'
Public Function GBP2Uganda(ByVal ExchangeDate As Variant) As Currency
Const TEXT_SOUGHT_FOR1 As String = "<td class=""tableCell""><a href=""currency/ugx/"">Ugandan shilling</a></td>"
Const TEXT_SOUGHT_FOR2 As String = "<td class=""tableCell"" align=""right"">"
Dim oXML As Object
Dim URL As String, the_file As String, ISODate As String
Dim strData As String, n As Long, xchng As Currency
' check if there is a valid date passed
If IsDate(ExchangeDate) = False Then
Exit Function
End If
ISODate = Format$(ExchangeDate, "yyyy-mm-dd")
URL = "http://www.floatrates.com/historical-exchange-rates.html?currency_date=" & ISODate & "&base_currency_code=GBP&format_type=html"
Set oXML = CreateObject("MSXML2.XMLHTTP")
With oXML
'.Open "GET", "http://www.floatrates.com/daily/usd.xml"
.Open "GET", URL
.setRequestHeader "User-Agent", "Mozilla/5.0"
.send
'Debug.Print .responsetext
'Exit Sub
Do While .ReadyState <> 4
'Debug.Print .ReadyState
DoEvents
Loop
strData = .responsetext
End With
Set oXML = Nothing
' find this value from strData:
' <td class="tableCell"><a href="currency/ugx/">Ugandan shilling</a></td>
n = InStr(1, strData, TEXT_SOUGHT_FOR1)
'Debug.Print n
If (n <> 0) Then
' find the exchange rate
n = InStr(n, strData, TEXT_SOUGHT_FOR2)
If (n <> 0) Then
strData = Trim$(Mid$(strData, n + Len(TEXT_SOUGHT_FOR2)))
n = InStr(1, strData, "</td>")
If (n <> 0) Then
xchng = CCur(Left$(strData, n - 1))
End If
End If
End If
strData = vbNullString
Debug.Print xchng
GBP2Uganda = xchng
End Function


Public Function ToUGX(ByVal ExchangeDate As Variant, ByVal CurrencyCode) As Double
Const TEXT_FIND As String = "<td class=""tableCell""><a href=""currency/@1/"">@2</a></td>"
Const TEXT_SOUGHT_FOR2 As String = "<td class=""tableCell"" align=""right"">"
Dim oXML As Object
Dim URL As String, the_file As String, ISODate As String
Dim strData As String, n As Long, xchng As Double
Dim TEXT_SOUGHT_FOR1 As String, sCurrencyDescription As String
TEXT_SOUGHT_FOR1 = Replace$(Replace$(TEXT_FIND, "@1", CurrencyCode), "@2", _
DLookup("Description", "Location_Currency", "Currency = '" & CurrencyCode & "'"))
' check if there is a valid date passed
If IsDate(ExchangeDate) = False Then
Exit Function
End If
ISODate = Format$(ExchangeDate, "yyyy-mm-dd")
URL = "http://www.floatrates.com/historical-exchange-rates.html?currency_date=" & ISODate & "&base_currency_code=UGX&format_type=html"
Set oXML = CreateObject("MSXML2.XMLHTTP")
With oXML
'.Open "GET", "http://www.floatrates.com/daily/usd.xml"
.Open "GET", URL
.setRequestHeader "User-Agent", "Mozilla/5.0"
.send
'Debug.Print .responsetext
'Exit Sub
Do While .ReadyState <> 4
'Debug.Print .ReadyState
DoEvents
Loop
strData = .responsetext
End With
Set oXML = Nothing
'WriteToText Environ("USERPROFILE") & "\Desktop\xchange.txt", strData
'Exit Function
' find this value from strData:
' <td class="tableCell"><a href="currency/ugx/">Ugandan shilling</a></td>
n = InStr(1, strData, TEXT_SOUGHT_FOR1)
'Debug.Print n
If (n <> 0) Then
' find the exchange rate
n = InStr(n, strData, TEXT_SOUGHT_FOR2)
If (n <> 0) Then
strData = Trim$(Mid$(strData, n + Len(TEXT_SOUGHT_FOR2)))
n = InStr(1, strData, "</td>")
If (n <> 0) Then
' get the second instance
n = InStr(n, strData, TEXT_SOUGHT_FOR2)
strData = Trim$(Mid$(strData, n + Len(TEXT_SOUGHT_FOR2)))
n = InStr(1, strData, "</td>")
xchng = StrToDbl(Left$(strData, n - 1))
End If
End If
End If
strData = vbNullString
Debug.Print xchng
Debug.Print ToUGX
ToUGX = xchng
End Function

Public Sub WriteToText(ByVal spath As String, ByVal the_content As String)
Open spath For Output As #1
Print #1, the_content
Close #1
End Sub

Public Function StrToDbl(ByVal s As String) As Double
Const Keys As String = ",.0123456789"
Dim sRet As String, char As String
Dim i As Long, ln As Long
ln = Len(s)
For i = ln To 1 Step -1
char = Mid$(s, i, 1)
If InStr(1, Keys, char) <> 0 Then
If char <> "," Then
sRet = char & sRet
End If
Else
Exit For
End If
Next
'Debug.Print sRet
sRet = Replace(sRet, ".", ",")
StrToDbl = CDbl(sRet)

 

[Gasman]

Well either make sure it is not Null or use NZ()

 

[ebs17]

I am getting a run time error 94, invalid use of null

Wouldn't it be great if the triggering line of code (which the debugger displays with a yellow background for sure) should also be mentioned with the error message?

A concrete fact is easier to analyze than a heap thrown out. That would also support your own troubleshooting.

 

[Gismo]

I think I need to ask arnelgp to have a look as this one, code supplied by arnelgp

The code works well in my other forms, I hade to make some amendments to this form as the setup has changed a bit

When I have a pre entered purchase requisition, I want to be able to select the transaction from the list to update the temporary files and do an exchange lookup

 

[Minty]

Did you try the debugging option I suggested?
I would put money on you not getting the values you think you are being passed to the function. In fact, that's what the error is telling you.
Arnelgp or anyone else can't make it work without the correct data.

 

[Gismo]

Did you try the debugging option I suggested?
I would put money on you not getting the values you think you are being passed to the function. In fact, that's what the error is telling you.
Arnelgp or anyone else can't make it work without the correct data.

I did the debugging as suggested
No I did not get an output as required
Although, the I do the process as before I do get an output

The change I made was to have the option to create a purchase order or to pull in a requisition if one was created

By creating a new PO, works perfect
when Importing the requisition, no exchange output

I assume I am overlooking something as the code work perfect when creating a new PO

Arnelgp went through this before with me and pointed out my issues

 

[Minty]

Stop hopping around, you are probably confusing yourself.

If you get nothing from the debugging then your references to the Parent form are incorrect, or those controls don't hold any value when the code runs. Start there. If you fix that then your function will return what you want.

Have you thought of moving your code to the parent form? is there any specific reason it has to run from the subform??

 

[Gismo]

Stop hopping around, you are probably confusing yourself.

If you get nothing from the debugging then your references to the Parent form are incorrect, or those controls don't hold any value when the code runs. Start there. If you fix that then your function will return what you want.

Have you thought of moving your code to the parent form? is there any specific reason it has to run from the subform??

I was thinking the same thing, I was trying different situations with the parent/child forms but had no success

So yes I 100% agree with you and by trying to solve this, I have a few confusing moments

If I run all my queries from the subform and then just click the command on the main form, it works fine
When I run the exchange command from the sub form there is no output

I select the transaction to update from the subform, hence the reason I am running exchange from the subfom

 

[Minty]

I'm guessing here, but that would tend to indicate that it doesn't have a record selected in the main form, hence no values?

 

[Solo712]

I did the debugging as suggested
No I did not get an output as required
Although, the I do the process as before I do get an output

The change I made was to have the option to create a purchase order or to pull in a requisition if one was created

By creating a new PO, works perfect
when Importing the requisition, no exchange output

I assume I am overlooking something as the code work perfect when creating a new PO

Arnelgp went through this before with me and pointed out my issues

I noted that your "requsition" setup reads:

Me.Parent.txtExchange = ToUGX(Me.Parent.txtDate, Me.Parent.Currency)

while arnel's code (that works) is:

Me.Parent!txtExchange = ToUGX(Me.Parent!txtDate, Me.Parent!Currency)

See the difference?

Best,
Jiri

 

[Pat Hartman]

I am getting a run time error 94, invalid use of null

It's time to learn some basic debugging techniques rather than asking arne to do your work for free. What line of code is highlighted when you hit cntl-break? That will identify the field that is empty.

 

[Gismo]

I noted that your "requsition" setup reads:

Me.Parent.txtExchange = ToUGX(Me.Parent.txtDate, Me.Parent.Currency)

while arnel's code (that works) is:

Me.Parent!txtExchange = ToUGX(Me.Parent!txtDate, Me.Parent!Currency)

See the difference?

Best,
Jiri

Hi

Yes I was acting on post 4, had no effect

 

[Gismo]

It's time to learn some basic debugging techniques rather than asking arne to do your work for free. What line of code is highlighted when you hit cntl-break? That will identify the field that is empty.

Hi Pat,

I did some debugging, yes there was no output
As mentioned before, the original code works well
I had to make an amendment to another form as I am importing this time and not raising a new PO
All the other date required for the code to run has been updated before I run this exchange code but I am not getting the exchange rate updated which is where I am stuck as I don't now where else to look, this is why I am asking arnelgp for guidance because obviously I am missing something somewhere

 

[arnelgp]

you test this one.

 

[Gismo]

you test this one.

Thank you so much arnelgp

Works perfect

 

[Minty]

So put us all out of our misery, what was the problem?
I tried to run your database but without the correct underlying tables it was impossible to use.