VBA Function

[antoncata]

I have a table that contains prices from several agents and from several airlines, they were divided into groups by weight charges.


Depending on the airport of departure (POL/C) and arrival (POD/C), I check all the prices and I have to use the best one. But to show the price alternatives.
Each airline has its own method of calculation, therefore I have to check.
table contains the following information:

ID = AutoNumber, Long Integer
A/CODE = Number, Long Integer
AGENT = Text, 
POL/C = Text, 
POL = Text, 
POD/C = Text, 
POD = Text, 
IATA = Text, 
Airline = Text, 
UPDATE = Date/Time, Short Date
EXPIRY DATE = Date/Time, Short Date
CURRENCY = Text, 
M/M = Number, Double (Minimum weight accepted)
-45 = Number, Double (price for the weight between 1 and 45)
+45 = Number, Double (price for the weight starting from 45 to 100)
+100 = Number, Double (price for the weight starting from 100 to 300)
+300 = Number, Double (price for the weight starting from 300 to 500)
+500 = Number, Double (price for the weight starting from 500 to 1000)
+1000 = Number, Double (price for the weight starting from 1000)
FSC = Number, Double
SSC = Number, Double
ScGw = Yes/No, Yes/No
FREQUENCY = Text, 
TT = Number, Long Integer
T/S = Yes/No, Yes/No

From the beginning it will have two weights as follows:
1. actual total weight (GW - gross weight)
2. calculated weight by volume (VW)
if GW > VW then..
calculation is based on the higher value (GW)
else
calculation is based on the higher value (VW)
example:
VW = 405 kgs and GW = 222 kgs then use higher value

FSC and SSC is added to the price if any.
Where is calculated on weight (VW) and If ScGw = Yes THEN the weight is different account and is calculated using (GW)
example:
Air freight = euro 0.25 / kgs (x 405 kgs VW)
Fuel + security = euro 1.1 / kgs (x 222 kgs GW)
If ScGw = No THEN calculate the normal VW
example:
Air freight = euro 0.25 / kgs (x 405 kgs VW)
Fuel + security = euro 1.1 / kgs (x 405 kgs VW)

If the calculation is made according to GW, then add the FSC and SSC automatically and without having to count, if ScGw = Yes / No


Values of GW and VW we have already calculated in another form and only need to be use. Airport of departure (POL/C) and arrival (POD/C) is already selected in another form.

If you can help me, as a few days simply fail to find any solution. I am writing full pages without any good result.
Thanks to all who respond.

    Public Sub CalculPret()
    
    Dim db As Database
    Dim rec As Recordset
    Dim PolCboV As String
    Dim PodCboV As String
    Dim strSQL As String
    Dim GrossWeight As Double
    Dim VolumeWeight As Double
    Dim CalcWeight As Double
    Dim CalcWeightScGw As Double
    Dim CalcPrice As Variant
    Dim TotalPrice As Double
    
    PolCboV = [Forms]![DimensionsQry]![PolCbo]
    PodCboV = [Forms]![DimensionsQry]![PodCbo]
    
    '"Prices_List" is a table to save all offers from all agents
    
    strSQL = "SELECT Prices_List.ID, Prices_List.[A/CODE], Prices_List.AGENT, " & _
             "Prices_List.[POL/C], Prices_List.POL, Prices_List.[POD/C], " & _
             "Prices_List.POD, Prices_List.IATA, Prices_List.AIRLINE, " & _
             "Prices_List.UPDATE, Prices_List.[EXPIRY DATE], Prices_List.CURRENCY, " & _
             "Prices_List.[M/M], Prices_List.[-45], Prices_List.[+45], " & _
             "Prices_List.[+100], Prices_List.[+300], Prices_List.[+500], " & _
             "Prices_List.[+1000], Prices_List.FSC, Prices_List.SSC, Prices_List.ScGw, " & _
             "Prices_List.FREQUENCY, Prices_List.TT, Prices_List.[T/S]"
    strSQL = strSQL & " FROM Prices_List"
    strSQL = strSQL & " WHERE (((Prices_List.[POL/C])='" & PolCboV & "') " & _
             "AND ((Prices_List.[POD/C])='" & PodCboV & "'));"
    
    Set db = CurrentDb
    Set rec = db.OpenRecordset(strSQL)
    
        If rec.RecordCount = 0 Then
            rec.Close
            Exit Sub
        Else
                GrossWeight = [Forms]![DimensionsQry]![Text34]
                VolumeWeight = [Forms]![DimensionsQry]![Text36]
            
            If GrossWeight > VolumeWeight Then
                CalcWeight = GrossWeight
            Else
                If ScGw = "Yes" Then
                    CalcWeight = GrossWeight
                Else
                    CalcWeight = VolumeWeight
                End If
            End If
            rec.MoveFirst
                Do Until rec.EOF
                    Select Case CalcWeight
                      Case 1 To 44
                        CalcPrice = IIf(rec![-45] Is Null, "No Value", rec![-45])
                      Case 45 To 99
                        CalcPrice = IIf(rec![+45] Is Null, "No Value", rec![+45])
                      Case 100 To 299
                        CalcPrice = IIf(rec![+100] Is Null, "No Value", rec![+100])
                      Case 300 To 499
                        CalcPrice = IIf(rec![+300] Is Null, "No Value", rec![+300])
                      Case 500 To 999
                        CalcPrice = IIf(rec![+500] Is Null, "No Value", rec![+500])
                      Case Is >= 1000
                        CalcPrice = IIf(rec![+1000] Is Null, "No Value", rec![+1000])
                    End Select
                    'This part is not completed
                    'not all agents have prices for all weight groups
                    'so may be empty fields
                    'This affects the calculation below
                   
                    If CalcWeight = GrossWeight Then
                        CalcPrice = CalcPrice + rec!FSC + rec!SSC
                        TotalPrice = CalcPrice * CalcWeight
                    Else
                        TotalPrice = (CalcPrice * CalcWeight) + ((rec!FSC + rec!SSC) * GrossWeight)
                    End If
                    'message is only for the moment to test results.
                    'results have to collect in a table of all price options
                    'sorted from lowest to highest
                    'for now I have not figured out how to make
                    MsgBox rec!AGENT & " - " & TotalPrice & " " & rec!CURRENCY & " " & rec!Airline
                rec.MoveNext
                Loop
        End If
    rec.Close
    
    End Sub

[recyan]

Hi,
Am not in to VBA, but till someone comes along, was wondering why you were not able to implement the logic in the query itself, something like below :

SELECT 
	Price_List.ID, 
	Price_List.AIRLINE, 
	Price_List.[M/M], 
	Price_List.[-45], 
	Price_List.[+45], 
	Price_List.[+100], 
	Price_List.[+300], 
	Price_List.[+500], 
	Price_List.[+1000], 
	Price_List.FSC, 
	Price_List.SSC, 
	Price_List.ScGw, 
	[Forms]![frmQryPrice_List]![GW] AS GW, 
	[Forms]![frmQryPrice_List]![VW] AS VW, 
	IIf([VW]>[GW],[VW],[GW]) AS WtForCalculation, 
	IIf([WtForCalculation]>=1000,[+1000],IIf([WtForCalculation]>=500,[+500],IIf([WtForCalculation]>=300,[+300],IIf([WtForCalculation]>=100,[+100],IIf([WtForCalculation]>=45,[+45],[-45]))))) AS RateForCalculation,
	.........,
	..........
FROM 
	Price_List
WHERE 
	..............;

Thanks

[antoncata]

Thank you very much, I do not want to appear in a special way but do not know SQL, if you could help me to replace the entire code sequence. I know only VBA and do not use frequently. Last time I did 10 years ago ... The SQL is copied from a Query... I only make the program in order to facilitate my work.

[recyan]

If you have access to the Query Design Window, just go on building the query the way I have done.
In fact it is pretty simple to use.

First thing would be to write the logic of your query in text the way I have done. Do not worry about the syntax. Something like :

SELECT
field1,
field2,
(if field 1 > field2 then use field 1 else use field2) AS WeightToBeUsedForCalculation,
field3,
field4,
(if field4 = "YES" then (field3 * WeightToBeUsedForCalculation) else 0) AS AmountToBeChargedForWeightOrVol,
......

Then start building the query in the Query Design window.

Post your SQL query if you are getting stuck somewhere. Someone should definitely be able to help you.

PS : Was wondering about columns -45, 45, 100, 500, 1000. Do not know your functional requirements, but what if the range changes to 75, 150, 250, 500, 750, 1000 after some time (All prices in my experience have a nasty habit of changing (in the upward direction) all the time).

Thanks

[antoncata]

I attached the database to get an idea of what's in my head .....
About columns -45, 45, 100, 500, 1000 fields contain prices depending on weight group Ex: cargo weighing less than 45kg will be at (-45) (0-44kg), 45kg will be at (+45), weighing 222kg will be at (+100).
Column M/M contains minimum weight accepted by the airline, this means that should be taken into account if the weight is below the minimum weight.
FSC and SSC containing fuel and security surcharges, and is add if there is a value on it.
Price can be and ALL/in this means that already contains everything included (FSC and SSC)
I'll come back with any details I missed.
For now I'm stuck do not know how check if the information is not in the field, because not all airlines have prices for all weight groups.
I have no imagination for days...
Thanks for the support, and patience.

[recyan]

Hi,
Cannot access database. But then I don't think that is important.

About columns -45, 45, 100, 500, 1000 fields contain prices depending on weight group Ex: cargo weighing less than 45kg will be at (-45) (0-44kg), 45kg will be at (+45), weighing 222kg will be at (+100).

Have understood this part. It is the way it is being stored in the table that has me puzzled.

Column M/M contains minimum weight accepted by the airline, this means that should be taken into account if the weight is below the minimum weight.

Let us say, an airline ABC has a minimum weight criteria of 20 kgs, the package being considered has an actual weight (GW) of 15 kgs but the Calculated Weight by Volume (VW) is 50 kgs. In this case, will the package be accepted by the airlines or not.

For now I'm stuck do not know how check if the information is not in the field, because not all airlines have prices for all weight groups.

What does "not all airlines have prices for all weight groups." mean?
Does it mean,
1) the prices have not been entered in the price list, but they exist
2) the airlines have different prices for different ranges
eg: ABC has prices for range 0 - 45, 45.1 - 100, 100.1 - 200, 200.1 - 500, 500.1 - 1000, Above 1000.1
eg: XYZ has prices for range 0 - 100, 100.1 - 500, 500.1 - 1000, Above 1000.1
3) the airlines does not carry those weights
eg: PQR has prices for range 100.1 - 500, 500.1 - 1000. It does not carry below 100 & above 1000.

If No 2) or No 3) is the case, then I think we need to relook at the way the Price & Weight Data is being stored.

Thanks

[antoncata]

I converted the database to Access 2000!

[Let us say, an airline ABC has a minimum weight criteria of 20 kgs, the package being considered has an actual weight (GW) of 15 kgs but the Calculated Weight by Volume (VW) is 50 kgs. In this case, will the package be accepted by the airlines or not.]

If volume is greater (GW < VW ) then volumetric weight is calculated, it is correct! computable weight will be (VW) 50kg so will fit to the price from (+45).

[What does "not all airlines have prices for all weight groups." mean?
Does it mean]

Function as loading areas, and aircraft assigned to these routes the groups of prices are there but the Airlines add or remove the price for a group. They offer and prices according to their marketing strategies.
Precisely because of this, I have to make a database so I can check quickly in all variants.
Fields are empty! where is no price for that weight group!
That check kept me in a loop that I still I have not come out ...


prices range is: 0-44, 45-99, 100-299, 300-499, 500-999, and >1000

Let me know if I missed something.
Thank you!

[recyan]

Don't know if you are thro with this problem, all the same, in case not:

but the Airlines add or remove the price for a group. They offer and prices according to their marketing strategies.

prices range is: 0-44, 45-99, 100-299, 300-499, 500-999, and >1000

Is there a possibility of the ranges being different for different airlines.
eg for ABC -> 0-44, 45-99, 100-299, 300-499, 500-999, and >1000
&
for
XYZ -> 0-99, 100-149, 150-499, 500-1000, >1000.
If yes then I think we should be thinking of an additional table.
Even otherwise, we should be thinking of
another table tblWeightRate to store the

ID | PriceListID | LowerWeight | UpperWeight | Rate

where PriceListID is the Foreign Key to the Price_List table ID

Fields are empty! where is no price for that weight group!

prices range is: 0-44, 45-99, 100-299, 300-499, 500-999, and >1000

With your present Price_List table,
Have you tried building the query.
For empty fields (I try to avoid these with default values),
the first option would be to update all those emty fields with 0,
the second option is to use the function Nz(..., 0).
You can use it with the fields in the query
or alternatively,
If you use it with the IIf in my first post, you should be able to see how it works.

NZ(IIf([WtForCalculation]>=1000,[+1000],IIf([WtForCalculation]>=500,[+500],IIf([WtForCalculation]>=300,[+300],IIf([WtForCalculation]>=100,[+100],IIf([WtForCalculation]>=45,[+45],[-45]))))),0) AS RateForCalculation

PS : If you spend some time building the query in the query builder window, you should be through with this problem, if not yet thro.

If you still face problem, show us where you have reached with the query & someone sure will help you along the way.

Edit :

Check if below gives some guidelines :

SELECT 
	Price_List.PriceListID, 
	Price_List.AIRLINE, 
	Price_List.[M/M], 
	Price_List.[-45], 
	Price_List.[+45], 
	Price_List.[+100], 
	Price_List.[+300], 
	Price_List.[+500], 
	Price_List.[+1000], 
	Price_List.FSC, 
	Price_List.SSC, 
	Price_List.ScGw, 
	[Forms]![frmQryPrice_List]![GW] AS GW, 
	[Forms]![frmQryPrice_List]![VW] AS VW, 
	IIf([VW]>[GW],[VW],[GW]) AS WtForCalculation,	
           NZ(IIf([WtForCalculation]>=1000,[+1000],IIf([WtForCalculation]>=500,[+500],IIf([WtForCalculation]>=300,[+300],IIf([WtForCalculation]>=100,[+100],IIf([WtForCalculation]>=45,[+45],[-45]))))),0) AS RateForCalculation,
	IIf([RateForCalculation]=0,0,IIf([ScGw]="Yes",(([FSC]*[GW])+([SSC]*[GW])),(([FSC]*[VW])+([SSC]*[VW])))) AS FuelSecurityCharges,
	[RateForCalculation]*[WtForCalculation]+[FuelSecurityCharges] AS FinalCost
FROM 
	Price_List
WHERE 
	...............;

Thanks

[antoncata]

Don't know if you are thro with this problem, all the same, in case not:
Is there a possibility of the ranges being different for different airlines.
eg for ABC -> 0-44, 45-99, 100-299, 300-499, 500-999, and >1000
& for
XYZ -> 0-99, 100-149, 150-499, 500-1000, >1000.
If yes then I think we should be thinking of an additional table.
Even otherwise, we should be thinking of
another table tblWeightRate to store the

ID | PriceListID | LowerWeight | UpperWeight | Rate

where PriceListID is the Foreign Key to the Price_List table ID

Is Not applicable because it is a standard and all Airlines offers the same weight groups.

SELECT
            Prices_List.ID,
            Prices_List.[A/CODE],
            Prices_List.AGENT,
            Prices_List.[POL/C],
            Prices_List.POL,
            Prices_List.[POD/C],
            Prices_List.POD,
            Prices_List.IATA,
            Prices_List.AIRLINE,
            Prices_List.UPDATE,
            Prices_List.[EXPIRY DATE],
            Prices_List.CURRENCY,
            Prices_List.[M/M],
            Prices_List.[-45],
            Prices_List.[+45],
            Prices_List.[+100],
            Prices_List.[+300],
            Prices_List.[+500],
            Prices_List.[+1000],
            Prices_List.FSC,
            Prices_List.SSC,
            Prices_List.ScGw,
            Forms![DimensionsQry]![GW] AS GW,
            Forms![DimensionsQry]![VW] AS VW,
            IIf([VW]>[GW],[VW],[GW]) AS WtForCalculation,
            Nz(IIf([WtForCalculation]>=1000,[+1000],IIf([WtForCalculation]>=500,[+500],IIf([WtForCalculation]>=300,[+300],IIf([WtForCalculation]>=100,[+100],IIf([WtForCalculation]>=45,[+45],[-45]))))),0) AS RateForCalculation,
            IIf([RateForCalculation]=0,0,IIf([ScGw]="Yes",(([FSC]*[GW])+([SSC]*[GW])),(([FSC]*[VW])+([SSC]*[VW])))) AS FuelSecurityCharges,
            [RateForCalculation]*[WtForCalculation]+[FuelSecurityCharges] AS FinalCost
FROM
            Prices_List
WHERE
            (((Prices_List.[POL/C])=[Forms]![DimensionsQry]![PolCbo]) AND ((Prices_List.[POD/C])=[Forms]![DimensionsQry]![PodCbo]));

I built the query as I have advised, but the result was not entirely ok.
I'll attach two photos from part of the results to be more explicit.
I honestly do not know what the problem is... As I said in the beginning, I'm lost in SQL.
So I need help!
Thank you!

[recyan]

You are not lost. I can understand since it is a new area for you.

If you look at your screenshot, both the first & 2nd, then it is clear
1) that the query is first failing at below code

 IIf([RateForCalculation]=0,0,IIf([ScGw]="Yes",(([FSC]*[GW])+([SSC]*[GW])),(([FSC]*[VW])+([SSC]*[VW])))) AS FuelSecurityCharges,

and then at the second field i.e Final Price, bcos it is dependent on above (FuelSecurityCharges)
2) the 2nd screen shot shows that it is working where rate is 0, but not working where it is not 0.

Now if you check the code

 IIf([RateForCalculation]=0,0,IIf([ScGw]="Yes",(([FSC]*[GW])+([SSC]*[GW])),(([FSC]*[VW])+([SSC]*[VW])))) AS FuelSecurityCharges,

the part where it is not 0 has

IIf([ScGw]="Yes"

try replacing the
"Yes"
with
Yes

and see what happens.

Thanks

[antoncata]

Now working.
But it has two columns that look nothing GW and VW.
I do not understand why ...
Attach photos, and one looks like: ScGw works.
Thank you!

[recyan]

Now working.
But it has two columns that look nothing GW and VW.

            
Forms![DimensionsQry]![GW] AS GW,
Forms![DimensionsQry]![VW] AS VW,

GW & VW are the Gross Weight & Weight By Volume which is being supplied by your form.

IIf([VW]>[GW],[VW],[GW]) AS WtForCalculation,

Is your WtForCalculation value in the query result correct. For eg: if you are supplying GW as 3015 & VW as 2500, then is the WtForCalculation showing correct value of 3015.

If that is correct, then it means GW & VW are being supplied to the query, but the values are not being displayed.

Don't know why it is happening.
Perhaps could be something to do with formatting.
Once I had by mistake changed the font color to white in excel sheets & then wondered where has all the data has dissapeared.

Upload your current db & perhaps someone should be able to help you.


Thanks

[antoncata]

If that is correct, then it means GW & VW are being supplied to the query, but the values are not being displayed.

Don't know why it is happening.
Perhaps could be something to do with formatting.

It is a formatting problem, I do realize that but I have nothing to do ....
My computer has support for Chinese characters.
But I asked and nobody seems to understand what those characters are ...
I attached a picture showing that the field still contains some ...
But there are values​​, so that calculations are correct.

Another problem:
I tried to sort by value to have the best prices first and go down to the biggest, but if I introduce filters do not seem to work anymore ...
Do you know why?

Thank you!

[recyan]

Another problem:
I tried to sort by value to have the best prices first and go down to the biggest, but if I introduce filters do not seem to work anymore ...

Am self taught & do not have any theoretical background, hence do not know if I am explaining things correctly or not, but all the same :
1) Order by the Calculated field - Difficult to do this directly as "ORDER BY C" below :
SELECT
a,
b,
a + b AS C
FROM
mytable
ORDER BY a + b

In your case the ORDER BY will become pretty complicated if you try to replace all the aliases.

2) Access the calculated field in another query & then use the ORDER BY:

SELECT
p.a,
p.b,
p.C
FROM
( SELECT
a,
b,
a + b AS C
FROM
mytable ) AS p
ORDER BY C;

3) Looking functionally at the values of your fields which make up the source for "FinalCost"
we can try to cheat our way thro :

FROM 
	.............
WHERE 
	.............
ORDER BY 
	Price_List.[-45], Price_List.[+45], Price_List.[+100], Price_List.[+300], Price_List.[+500], Price_List.[+1000], Price_List.FSC, Price_List.SSC

Hope someone corrects me or explains it better.

Thanks

[ssanfu]

I've been looking at your database and have a couple of concerns.
You are using special characters, spaces and reserved words in your object names . See
http://www.databasejournal.com/featu...uggestions.htm
http://accesstips.datashark.co.uk/namingconventions.htm
http://allenbrowne.com/AppIssueBadWord.html

(UPDATE and CURRENCY are reserved words)


For the field names for the weights, I would use LT45, GT45, GT100, ...
(LT = less than, GT = greater than)

I would default the field value to 0 (zero) instead of allowing NULLs into the data.



I don't understand what you are trying to do, but I did get the code you posted to execute. See the lines in blue.

Public Sub CalculPret()
    
    Dim db As Database
    Dim rec As Recordset
    Dim PolCboV As String
    Dim PodCboV As String
    Dim strSql As String
    Dim GrossWeight As Double
    Dim VolumeWeight As Double
    Dim CalcWeight As Double
    Dim CalcWeightScGw As Double
    Dim CalcPrice As Double
    Dim TotalPrice As Double
    
    PolCboV = [Forms]![DimensionsQry]![PolCbo]
    PodCboV = [Forms]![DimensionsQry]![PodCbo]
    
    '"Prices_List" is a table to save all offers from all agents
    
    strSql = "SELECT Prices_List.ID, Prices_List.[A/CODE], Prices_List.AGENT, " & _
             "Prices_List.[POL/C], Prices_List.POL, Prices_List.[POD/C], " & _
             "Prices_List.POD, Prices_List.IATA, Prices_List.AIRLINE, " & _
             "Prices_List.UPDATE, Prices_List.[EXPIRY DATE], Prices_List.CURRENCY, " & _
             "Prices_List.[M/M], Prices_List.[-45], Prices_List.[+45], " & _
             "Prices_List.[+100], Prices_List.[+300], Prices_List.[+500], " & _
             "Prices_List.[+1000], Prices_List.FSC, Prices_List.SSC, Prices_List.ScGw, " & _
             "Prices_List.FREQUENCY, Prices_List.TT, Prices_List.[T/S]"
    strSql = strSql & " FROM Prices_List"
    strSql = strSql & " WHERE (((Prices_List.[POL/C])='" & PolCboV & "') " & _
             "AND ((Prices_List.[POD/C])='" & PodCboV & "'));"
'    Debug.Print strSql
    Set db = CurrentDb
    Set rec = db.OpenRecordset(strSql)
    
        If rec.RecordCount = 0 Then
            rec.Close
            Exit Sub
        Else
            GrossWeight = [Forms]![DimensionsQry]![Text34]
            VolumeWeight = [Forms]![DimensionsQry]![Text36]
            
            If GrossWeight > VolumeWeight Then
                CalcWeight = GrossWeight
            Else
                If ScGw = "Yes" Then
                    CalcWeight = GrossWeight
                Else
                    CalcWeight = VolumeWeight
                End If
            End If
            rec.MoveFirst
                Do Until rec.EOF
                    Select Case CalcWeight
                      Case 1 To 44
                        CalcPrice = Nz(rec![-45], 0)
                      Case 45 To 99
                        CalcPrice = Nz(rec![+45], 0)
                      Case 100 To 299
                        CalcPrice = Nz(rec![+100], 0)
                      Case 300 To 499
                        CalcPrice = Nz(rec![+300], 0)
                      Case 500 To 999
                        CalcPrice = Nz(rec![+500], 0)
                      Case Is >= 1000
                        CalcPrice = Nz(rec![+1000], 0)
                    End Select
                    'IIf(rec![-45] Is Null, No Value, rec![-45])
                    'IIf(rec![-45] Is Null, rec.MoveNext, rec![-45])
                    'This part is not completed
                    'not all agents have prices for all weight groups
                    'so may be empty fields
                    'This affects the calculation below
                   
                    If CalcWeight = GrossWeight Then
                        CalcPrice = CalcPrice + rec!FSC + rec!SSC
                        TotalPrice = CalcPrice * CalcWeight
                    Else
                        TotalPrice = (CalcPrice * CalcWeight) + ((rec!FSC + rec!SSC) * GrossWeight)
                    End If
                    'message is only for the moment to test results.
                    'results have to collect in a table of all price options
                    'sorted from lowest to highest
                    'for now I have not figured out how to make
                    MsgBox rec!AGENT & " - " & TotalPrice & " " & rec!Currency & " " & rec!Airline
                    rec.MoveNext
                Loop
        End If
    rec.Close
    
End Sub