External Name not defined (1 Viewer)

AlexN

Registered User.
Local time
Today, 16:39
Joined
Nov 10, 2014
Messages
302
Hi everyone,

I use the following code behind the BeforeUpdate event of a control, to validate input.

Code:
Private Sub StockPrice_BeforeUpdate(Cancel As Integer)
'strings used for the MsgBox
Dim strTitle As String
Dim strMsg1 As String
Dim strMsg2 As String
Dim strMsg3 As String
Dim strMsg4 As String
Dim strMsg As String
'buttons to display on the MsgBox
Dim intMsgDialog As Integer
'result returned from the MsgBox
Dim intResult As Integer
'Comparison object
Dim LPrice As Variant
Dim HPrice As Variant

LPrice = DLookup("[Low]", "SubQryActiveStockPrices", "[PStockID]=" & [StockID] & " AND [TradeDate]=#" & Me.Parent!TabletDate & "#")
HPrice = DLookup("[High]", "SubQryActiveStockPrices", "[PStockID]=" & [StockID] & " AND [TradeDate]=#" & Me.Parent!TabletDate & "#")

If Me.StockPrice > HPrice Or Me.StockPrice < LPrice Then
' Display a message box asking for retry with the proper value
strTitle = "Price Check"
intMsgDialog = vbOKOnly + vbExclamation + vbDefaultButton1
strMsg1 = "Price not reached!" & " "
strMsg2 = "Retry using a price between " & " "
strMsg3 = LPrice & " and " & " "
strMsg4 = HPrice
strMsg = strMsg1 + strMsg2 + strMsg3 + strMsg4
intResult = MsgBox(strMsg, intMsgDialog, strTitle)

    If intResult = vbOK Then
    Cancel = True
    Me.StockPrice.Undo
    End If
End If
End Sub


There are two different forms that use that control and this code behind the BeforeUpdate event.
It works on one but not on the other, allowing to input anything in the control with checking and without bringing up any msgbox.
Searching what's the problem in the immediate window, brings up a message saying "External Name not defined'. I couldn't find the meaning of this and it seems I can't find any solution.

Anyone with a good idea?


Thanks!!!
 

AlexN

Registered User.
Local time
Today, 16:39
Joined
Nov 10, 2014
Messages
302
Can you indicate which line causes the error.

Let me be a little bit more clear.
I have two input forms, BuyTransactions and SellTransactions. When I register a sell price, code works perfectly alright, and if price is unreal, it brings up the proper message.
Same thing does not happen in BuyTransactions form. There, it accepts anything without making the comparisons, and of course without bringing up some message.
So I put the following lines in the immediate window and checked and came with the message "External Name not defined" which I can't find what it means.

Code:
LPrice = DLookup("[Low]", "SubQryActiveStockPrices", "[PStockID]=" & [StockID] & " AND [TradeDate]=#" & Me.Parent!TabletDate & "#")
HPrice = DLookup("[High]", "SubQryActiveStockPrices", "[PStockID]=" & [StockID] & " AND [TradeDate]=#" & Me.Parent!TabletDate & "#")
Take in mind this is the first sub I completely wrote on my own. I'm not that familiar with vba.


Thanks for your reply!
 

vbaInet

AWF VIP
Local time
Today, 14:39
Joined
Jan 22, 2010
Messages
26,374
Oh I see. You can't run that sub in the immediate window. Only public functions or subs in a Standard Module (not a Form Module) can be run from the immediate window. You can however put a break point in the sub and test the initialised variables in the immediate window when it breaks.
 

AlexN

Registered User.
Local time
Today, 16:39
Joined
Nov 10, 2014
Messages
302
Oh I see. You can't run that sub in the immediate window. Only public functions or subs in a Standard Module (not a Form Module) can be run from the immediate window. You can however put a break point in the sub and test the initialised variables in the immediate window when it breaks.

I'm afraid I don't how to do this but hopefully I'll learn....thanks.
 

vbaInet

AWF VIP
Local time
Today, 14:39
Joined
Jan 22, 2010
Messages
26,374
If you Google "how to set a breakpoint in VBA" you should find lots of useful information. Might be best looking for videos on it.
 

AlexN

Registered User.
Local time
Today, 16:39
Joined
Nov 10, 2014
Messages
302
If you Google "how to set a breakpoint in VBA" you should find lots of useful information. Might be best looking for videos on it.

Still nothing doing. I did find out how breakpoints work but it doesn't apply to my case because this is a form's control code, and it doesn't break if there is no error. My control brings up no error, it just accepts everything I input in, because code doesn't make the comparisons.
I'm perfectly certain that the DLookups don't work but I can't find why :(
Same code in another form works ok though.
 

AlexN

Registered User.
Local time
Today, 16:39
Joined
Nov 10, 2014
Messages
302
Well,

Where there's a will, there's a way


These :
Code:
LPrice = DLookup("[Low]", "SubQryActiveStockPrices", "[PStockID]=" & [StockID] & " AND [TradeDate]=#" & Me.Parent!TabletDate & "#")
HPrice = DLookup("[High]", "SubQryActiveStockPrices", "[PStockID]=" & [StockID] & " AND [TradeDate]=#" & Me.Parent!TabletDate & "#")


should be like this :
Code:
LPrice = DLookup("[Low]", "SubQryActiveStockPrices", "[PStockID]=" & [StockID] & " AND [TradeDate]=#" & Format(TabletDate,"yyyy\/mm\/dd") & "#")
HPrice = DLookup("[High]", "SubQryActiveStockPrices", "[PStockID]=" & [StockID] & " AND [TradeDate]=#" & Format(TabletDate,"yyyy\/mm\/dd") & "#")


Thank you for helping!
 

Users who are viewing this thread

Top Bottom