Execute command

[ebs17]

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

If this line causes the error, look in the function definition:

Public Function ToUGX(ByVal ExchangeDate As Variant, ByVal CurrencyCode) As Double
' ...
     TEXT_SOUGHT_FOR1 = Replace$(Replace$(TEXT_FIND, "@1", CurrencyCode), "@2", _
     DLookup("Description", "Location_Currency", "Currency = '" & CurrencyCode & "'"))
' ...

CurrencyCode is not declared, i.e. a variant, and therefore also accepts NULL. Replace cannot handle NULL and throws the described error.

? Replace("abcde", "c", NULL)

Me.Parent.Currency will therefore not contain any value at the time of the call, without checking and handling you will run into a runtime error.

 

[Gasman]

you test this one.

And O/P is none the wiser?

 

[Pat Hartman]

I did some debugging, yes there was no output
As mentioned before, the original code works well

I gave you a specific instruction to find the field that is empty. I cannot tell from this response whether you ignored the suggestion and did something else or if you followed the instruction and found the field and so now know how to proceed.

No one really cares if the original code works well. you changed it and now it is broken. That means another thing you need to do is to go back to the original code and test with the current data to see if the original code actually worked. Maybe, if this is a data problem, the initial code never really worked.

If you can't go back to the previous version of the code, you need to rethink your development practices. You should make frequent backups when you are changing code so you have recovery points you can fall back to when things go wrong. Once the procedure you are modifying works to the new specification. Close the database and make another backup before you start the next change. Date and time the intermediate copies because if you need to review one, Access will change the last update date and that will completely confuse you because you won't be able to easily identify the order in which the backups were created.

 

[Gismo]

In my code after I made the alterations to the new form I only had the below code to Extract the exchange rate

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"
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

When I looked at the sample arnelgp provided, I noticed that I did not requery the main form after I have updated the required date to enable the code to run for extracting the exchange rate

I infarct made a few attempts to equerry the form but it was inadecuite

below is the code I ended up with which made a huge difference, thanx to arnelgp

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"
Me.Parent.Requery
'DoCmd.Close
If Me.Parent!Currency & "" <> "UGX" Then
Me.Parent.txtExchange = ToUGX(Me.Parent.txtDate, Me.Parent.Currency & "")
Else
Me.Parent.txtExchange = 1
End If
DoCmd.OpenQuery "Update Transactions - Procurement - Temp - Last Purchase Price"
Forms![PurchaseOrder_Order].Form.Requery
Forms![PurchaseOrder_Order]![PurchaseOrderOrderSubForm].Form.Requery

So the original code works as expected, after I made the change on the new form, I did not put all my sequences in place to prepare for the code to run successfully.

To all of you, every time I have an issue, I do learn quite a bit from all the comments' and I thank you for that.