Function, with Recordset, In Combo Box (1 Viewer)

[AJR]

Hi

I apologize if I sound a little incoherent but I have scrambled my thought processes somewhat; working on this the last few hours.

The following is a simplification of what I am trying to accomplish but will stand to the question without over complicating it.

I do business from Canada and buy products in three currencies and pay for them from accounts in two banks.

The currencies are Canadian Dollars (CAD), U.S. Dollars (USD), and Euro (EUR)

The two banks are a Canadian Bank with separate CAD and USD accounts and a European bank with accounts in Euro.

I list all my purchases on a--continuous-- form [Transactions] showing [Date], [Account], [Item], [Price], [Currency], [FXRate] and [PriceCAD]

I use two tables to accomplish this:

Transactions Table with fields for:
[Date], [Account], [Item], [Price] and [FXRate]

BankAccount Table with fields for:
[AccountName], [Currency]

The RecordSource for the form is a query using the two tables, linked by [Account]-[AccountName], which provides all the data required for the form except for [PriceCad] which is calculated by [Price]*[FXRate] (FXRate is 1 if the account is in CAD).

All this works nicely.

Now.

I enter quite a few transactions in at one time. They cover many dates and each date has many transactions. Currently I have to manually enter the [FXRate] for each transaction which is cumbersome.

I would like the [FXRate] to be populated by a third table [tblFX] that contains:
[Date], [Currency], [FXRate].

It would be quite simple for me to set this up and have the data entry form that I now use populate the [FXRate] field in Transactions table with the correct number. However, this would mean that I would be storing the [FXRate] in each transaction as well as in the [FX] table and, as I understand it, according to rules of Data Normalization, that is not good form. As I am using this Database to teach myself Access and VBA I would like to learn how to do it properly.

My Idea is to eliminate the [FXRate] field from the Transactions table and change the [FXRate] control in the Transactions Form so it contains a call to a Public Function that would use a RecordSet to Query the [tblFX] Table using the form's [Date] and [Currency] controls as criteria in the SQL that would define the query.

I am trying (In the [FXRate] control in the Transactions form) to pass the Date and Currency to a Public Function, include them in the SQL and return the proper FXRate. I have gotten to this point.

In the [FXRate] control of the Transactions Form I put

FXRate([Date],[Currency])

Then in a Standard Module I define the function (trying to ) as

Public Function FXRate(ByVal dtDate As Date, ByVal strCur As String) As Long

Define a recordset -Can't figure out how to do this!

FXRate = SELECT FX.FXrate FROM tblFX WHERE (((FX.Date)=dtDate) AND ((FX.Currency)=strCUR));

End Function

curCur is defined as a String as it would Contain CAD,EUR or USD.

Because I am unsure of how to procede on each level it is impossible for me to work this out.

Specifically

1) Can I use the [Date] and [Currency] fields as arguments in the Function FXRate which is in the [FXRate] control of the Transactions form? FXRate([Date],[Currency])

2) How do I define the Recordset to run the query

3) How do I get the results of the query to be returned by the function?

Sorry for being so long winded. Any assistance would be greatly appreciated!

AR

 

[JHB]

Don't use keywords (reserved words) for field names, (Date and Currency), here is a link to the words.
https://support.microsoft.com/en-us/kb/286335
Create a query which return exactly what you want and use that in the recordset.
What you show here won't work, because you are referring to table and fields you haven't included in the from part, also the Where part is wrong. Where do you declare "strCUR"?

SELECT [B][COLOR=Red]FX.FXrate[/COLOR][/B] FROM tblFX WHERE ((([B][COLOR=Red]FX.Date[/COLOR][/B])=dtDate) AND (([B][COLOR=Red]FX.Currency[/COLOR][/B])=[B][COLOR=Red]strCUR[/COLOR][/B]));

 

[arnelgp]

Public Function FXRate(ByVal dtDate As Date, ByVal curCur As String) As Long

Dim strSQL as string
Dim db As Dao.Database

Set Db = Currentdb
strSql = "SELECT tblFX.FXrate FROM tblFX WHERE tblFx.[Date] = #" & Format(dtDate, "mm/dd/yyyy") & "# AND " & _
"tblFX.[Currency] = '" & strCUR & "';"

FXRate = NZ(db.OpenRecordSet(strSQL)(0), 0)

Set db = Nothing

End Function

 

[sneuberg]

This is arnelgp's code translated into a simpler dlookup. The parameter curCur is also changed to strCur.

Public Function FXRate(ByVal dtDate As Date, ByVal strCur As String) As Long

FXRate = Nz(DLookup("tblFX.FXrate", "tblFX", "tblFx.[Date] = #" & Format(dtDate, "mm/dd/yyyy") & "# AND " & _
"tblFX.[Currency] = '" & strCur & "';"))

End Function

 

[sneuberg]

On the other hand you could add the FXRate to your form's Record Source query by joining to the rate table as I shown in the attached screen shot. I believe this would be more efficient than using a function. Some of the names have been changed from what you have to avoid using reserve words. You see this query work in the attached database.

However, if performance isn't or doesn't become an issue and you feel more comfortable using the function I suggest going with that.

 

[AJR]

Don't use keywords (reserved words) for field names, (Date and Currency), here is a link to the words.
https://support.microsoft.com/en-us/kb/286335
Create a query which return exactly what you want and use that in the recordset.
What you show here won't work, because you are referring to table and fields you haven't included in the from part, also the Where part is wrong. Where do you declare "strCUR"?

SELECT [B][COLOR=Red]FX.FXrate[/COLOR][/B] FROM tblFX WHERE ((([B][COLOR=Red]FX.Date[/COLOR][/B])=dtDate) AND (([B][COLOR=Red]FX.Currency[/COLOR][/B])=[B][COLOR=Red]strCUR[/COLOR][/B]));

JHB

Thanks for pointing that out. I should have picked up on it myself. I did declare strCur when I defined the function but there was a typo so it showed curCur. I have edited that.

 

[AJR]

Public Function FXRate(ByVal dtDate As Date, ByVal curCur As String) As Long

Dim strSQL as string
Dim db As Dao.Database

Set Db = Currentdb
strSql = "SELECT tblFX.FXrate FROM tblFX WHERE tblFx.[Date] = #" & Format(dtDate, "mm/dd/yyyy") & "# AND " & _
"tblFX.[Currency] = '" & strCUR & "';"

FXRate = NZ(db.OpenRecordSet(strSQL)(0), 0)

Set db = Nothing

End Function

Arnelgp

Thanks again for the help. I don't quite get what the NZ thing is. If you have time can you translate that to English so I can look it up? Also, why do you set db to nothing?

 

[AJR]

On the other hand you could add the FXRate to your form's Record Source query by joining to the rate table as I shown in the attached screen shot. I believe this would be more efficient than using a function. Some of the names have been changed from what you have to avoid using reserve words. You see this query work in the attached database.

However, if performance isn't or doesn't become an issue and you feel more comfortable using the function I suggest going with that.

Sneuberg

Thanks for both of these really helpful replies. I did try to do it with a simple query but could not get it to work. I was not setting up the relationships properly. I think that your second post is probably the easiest way to approach this. However, as I mentioned it's a learning experience so I want to pursue both solutions.

As I said to Arnellg I can't figure out the NZ. What does that stand for?

 

[sneuberg]

As I said to Arnellg I can't figure out the NZ. What does that stand for?

I'm not sure what the letters stand for but the function (see http://www.techonthenet.com/access/functions/advanced/nz.php ) is used a lot to avoid assigning null values to variables which VBA will complain about unless they are variants. So in this specific case if there is no rate in the table for a specified currency and date the DLookup returns null. The Nz function substitutes the null with the second parameter which was 0 in arnelgp's code and in the revise DLookup code that follows. I missed that before.

Public Function FXRate(ByVal dtDate As Date, ByVal strCur As String) As Long

FXRate = Nz(DLookup("tblFX.FXrate", "tblFX", "tblFx.[Date] = #" & Format(dtDate, "mm/dd/yyyy") & "# AND " & _
"tblFX.[Currency] = '" & strCur & "';"), 0)

End Function

You will probably want to make sure this doesn't occur through error checking when the data is entered. Obvious a rate equal to zero wouldn't be right.

 

[AJR]

Sneuberg

It didn't take long to get the query to work! The two files you posted made it pretty simple. I don't know why I couldn't figure this out in the first place but no matter--it's working!

Thanks Again

AR