#Error on (New) record line

[Gasman]

Hi all again, sorry for the amount of questions recently, but I seem to be hitting one stumbling block after another. :banghead:

I have a table tblStock Traded that holds stock info, stock name and date are the relevant fields.
I realised that in my DB, I was not showing the total amount for stock traded.
I tried using the form with a query that had the table linked to a group query but that made the form unupdatable.

So I created a UDF and that works fine, except for the fact that the (New) line that is shown in datasheets (I'm using another of those split forms) shows #Error

I tried surrounding the code with the ISNULL function, but it appears to make no difference? I'd rather see a zero in the control than #Error

Is there a way to suppress this #Error please?

I've also tried
=IIF(IsError(CommTotal([TradeDate],[Stock])),"",CommTotal([TradeDate],[Stock])) from a post in this forum, but could not get that to work either.

My code is as follows

Public Function CommTotal(pdtDate As Date, pstrStock As String)
Dim sngRate As Single
Dim CurTotal As Currency
' Get the rate from the tblStockTraded table
If Not IsNull(pdtDate) Then
    sngRate = Nz(DLookup("CommRate", "tblStockTraded", "Stock='" & pstrStock & "' AND TradeDate = " & Format(pdtDate, strcJetDate)), 0) / 100
    CurTotal = DSum("NetCost", "tblSVSTrades", "Stock='" & pstrStock & "' AND BuySell = 'Buy' AND TradeDate = " & Format(pdtDate, strcJetDate))
    CommTotal = CurTotal * sngRate
Else
    CommTotal = 0
End If

End Function

TIA

 

[MarkK]

The error occurs because your function's parameter list requires a date

Public Function CommTotal([COLOR="DarkRed"]pdtDate As Date[/COLOR], pstrStock As String)

...but you are passing it a null. To allow the function to receive the null without error, do this...

Public Function CommTotal([COLOR="DarkRed"]pdtDate As Variant[/COLOR], pstrStock As String)

...and then change...

If Not IsNull(pdtDate) Then

...to...

If IsDate(pdtDate) Then

 

[Gasman]

Thank you Mark, I'll give that a go in the morning.

 

[Gasman]

Mark,

I amended the code as below, but still get the #Error on the (New) line?

Public Function CommTotal(varDate As Variant, pstrStock As String)
Dim sngRate As Single
Dim CurTotal As Currency
' Get the rate from the tblStockTraded table
If IsDate(varDate) Then
    sngRate = Nz(DLookup("CommRate", "tblStockTraded", "Stock='" & pstrStock & "' AND TradeDate = " & Format(varDate, strcJetDate)), 0) / 100
    CurTotal = DSum("NetCost", "tblSVSTrades", "Stock='" & pstrStock & "' AND BuySell = 'Buy' AND TradeDate = " & Format(varDate, strcJetDate))
    CommTotal = CurTotal * sngRate
Else
    CommTotal = 0
End If

End Function

 

[sneuberg]

If pstrStock is null on the (New) line then this would cause an error too. I suggest making that a variant too then then just exit the function if either are Null something like:

Public Function CommTotal(varDate As Variant, pstrStock As Variant) as Variant
Dim sngRate As Single
Dim CurTotal As Currency

If IsNull(varDate) Or IsNull(pstrStock) Then
      CommTotal = Null
      Exit Function
End if

...

 

[Gasman]

Sneuberg,

TBH there should always be a valid value in both parameters, but I'll try that as well.
I'm puzzled as to why Access would send the new record to the function.

 

[Minty]

I've never used a split form, but can you control the datasheet part to not allow additions, that would avoid the issue?

 

[sneuberg]

I'm puzzled as to why Access would send the new record to the function.

That seems to be just the nature of the way things work. I put a simple calculation, e.g., =Len([FieldName]), in textbox and it shows the length of FieldName as soon as it is enter in a new record. Some people might consider this desirable.

 

[Gasman]

[Solved]Re: #Error on (New) record line

I've never used a split form, but can you control the datasheet part to not allow additions, that would avoid the issue?

Minty,

I did not realise that this was split form behaviour. I just regarded the datasheet portion as a normal datasheet.

The datasheet part only has Allow Edits or Read Only. Changing to read only did not fix the problem.

Setting both variables to Variant did.

Another lesson learnt.

 

[Gasman]

If pstrStock is null on the (New) line then this would cause an error too. I suggest making that a variant too then then just exit the function if either are Null something like:

Public Function CommTotal(varDate As Variant, pstrStock As Variant) as Variant
Dim sngRate As Single
Dim CurTotal As Currency

If IsNull(varDate) Or IsNull(pstrStock) Then
      CommTotal = Null
      Exit Function
End if

...

Sneuberg,

Yes setting both to variant and checking for Null solved the problem.

Thank you.

 

[Gasman]

The error occurs because your function's parameter list requires a date

Public Function CommTotal([COLOR="DarkRed"]pdtDate As Date[/COLOR], pstrStock As String)

...but you are passing it a null. To allow the function to receive the null without error, do this...

Public Function CommTotal([COLOR="DarkRed"]pdtDate As Variant[/COLOR], pstrStock As String)

...and then change...

If Not IsNull(pdtDate) Then

...to...

If IsDate(pdtDate) Then

Sorry Mark,

I should have realised that the other variable would need to be variant as well. Not used those before, as I read they were inefficient.

 

[MarkK]

Hey, no worries. Glad you got it figured out!