select case statement help

  • Thread starter Thread starter Ray Gibson
  • Start date Start date
R

Ray Gibson

I am a Access VB newbie and need some basic help writing this code. What I
am trying to produce is an error message on a form before it updates if a
certain case exists.

ie. There is not enough QTY in Tbl_StudData. to fullfill your request.

Here's what I have so far:

Select Case Location
Case "Vault"
if (Select [Tbl_StudData].[Qty-Vault] from [Tbl_StudData] where Me.Sku
= [Tbl_StudData].sku)< Me.Qty.Value Then
Cancel = True
MsgBox "Not enough Qty."
Me.Qty.Value = Null
End If
Case "Memo"
if (Select [Tbl_StudData].[Qty-Memo] from [Tbl_StudData] where Me.Sku
= [Tbl_StudData].sku)< Me.Qty.Value Then
Cancel = True
MsgBox "Not enough Qty."
Me.Qty.Value = Null
End If
End Select


I get A Run Time Error '2465' each time I try to save the record. VB
highlights this line:if (Select [Tbl_StudData].[Qty-Vault] from
[Tbl_StudData] where Me.Sku = [Tbl_StudData].sku)< Me.Qty.Value Then
 
if (Select [Tbl_StudData].[Qty-Memo] from [Tbl_StudData]
where Me.Sku >= [Tbl_StudData].sku)< Me.Qty.Value Then

Are You trying to get value from query and to equal this
value to Me.Qty.Value?

You need to get this value in different way, like this:
retVal = DLookup("Qty-
Memo","Tbl_StudData","((Tbl_StudData.sku) < " Me.Qty.Value)

if retVal < Me.Qty.Value then ...
 
No. I just want to check to see if there is enough Qty in Tbl_StudData for
the specific sku . ie. I can't sell it from a location if there is no Qty.
Table looks like this: Sku, Qty-Vault, Qty-Case etc.

The form that this funciton is going on updates a Transaction log table.
Has Sku, Qty, Location (values such as Case, Vault); Date, etc.

How does the DLookup function know which Product(Based on sku) to lookup?

Where Me.Sku = [Tbl_StudData].sku ? How do you incorporate it into the
Dlookup function?



losmac said:
if (Select [Tbl_StudData].[Qty-Memo] from [Tbl_StudData]
where Me.Sku >= [Tbl_StudData].sku)< Me.Qty.Value Then

Are You trying to get value from query and to equal this
value to Me.Qty.Value?

You need to get this value in different way, like this:
retVal = DLookup("Qty-
Memo","Tbl_StudData","((Tbl_StudData.sku) < " Me.Qty.Value)

if retVal < Me.Qty.Value then ...
-----Original Message-----
I am a Access VB newbie and need some basic help writing this code. What I
am trying to produce is an error message on a form before it updates if a
certain case exists.

ie. There is not enough QTY in Tbl_StudData. to fullfill your request.

Here's what I have so far:

Select Case Location
Case "Vault"
if (Select [Tbl_StudData].[Qty-Vault] from [Tbl_StudData] where Me.Sku
= [Tbl_StudData].sku)< Me.Qty.Value Then
Cancel = True
MsgBox "Not enough Qty."
Me.Qty.Value = Null
End If
Case "Memo"
if (Select [Tbl_StudData].[Qty-Memo] from [Tbl_StudData] where Me.Sku
= [Tbl_StudData].sku)< Me.Qty.Value Then
Cancel = True
MsgBox "Not enough Qty."
Me.Qty.Value = Null
End If
End Select


I get A Run Time Error '2465' each time I try to save the record. VB
highlights this line:if (Select [Tbl_StudData].[Qty- Vault] from
[Tbl_StudData] where Me.Sku = [Tbl_StudData].sku)< Me.Qty.Value Then



.
Click to expand...
Click to expand...
 
IF DLookup("[Qty-Vault]", _
"[Tbl_StudData]", _
"Sku=" & chr(34) & Me.Sku & Chr(34))
< Me.Qty Then

Cancel = True
Msgbox "..."
...
 
Thanks John,

That's exactly what I was looking for!!!

I entered the code, and the correct Message Appears, then an additonal
message box appears and says "No Current Record". Here's a snipit of my
code, can you tell me what its doing?

Select Case Location
Case "Vault"
If DLookup("[Qty-Vault]", "[Tbl_StudData]", "Sku=" & Chr(34) & Me.Sku &
Chr(34)) < Me.Qty Then
Cancel = True
MsgBox "There is not enought Qty in the VAULT to do this."

End If





John Spencer (MVP) said:
IF DLookup("[Qty-Vault]", _
"[Tbl_StudData]", _
"Sku=" & chr(34) & Me.Sku & Chr(34))
< Me.Qty Then

Cancel = True
Msgbox "..."
...



Ray said:
I am a Access VB newbie and need some basic help writing this code. What I
am trying to produce is an error message on a form before it updates if a
certain case exists.

ie. There is not enough QTY in Tbl_StudData. to fullfill your request.

Here's what I have so far:

Select Case Location
Case "Vault"
if (Select [Tbl_StudData].[Qty-Vault] from [Tbl_StudData] where Me.Sku
= [Tbl_StudData].sku)< Me.Qty.Value Then
Cancel = True
MsgBox "Not enough Qty."
Me.Qty.Value = Null
End If
Case "Memo"
if (Select [Tbl_StudData].[Qty-Memo] from [Tbl_StudData] where Me.Sku
= [Tbl_StudData].sku)< Me.Qty.Value Then
Cancel = True
MsgBox "Not enough Qty."
Me.Qty.Value = Null
End If
End Select

I get A Run Time Error '2465' each time I try to save the record. VB
highlights this line:if (Select [Tbl_StudData].[Qty-Vault] from
[Tbl_StudData] where Me.Sku = [Tbl_StudData].sku)< Me.Qty.Value Then
Click to expand...
Click to expand...
 
NOPE! Something later in your code is probably causing that message to apear.

Ray said:
Thanks John,

That's exactly what I was looking for!!!

I entered the code, and the correct Message Appears, then an additonal
message box appears and says "No Current Record". Here's a snipit of my
code, can you tell me what its doing?

Select Case Location
Case "Vault"
If DLookup("[Qty-Vault]", "[Tbl_StudData]", "Sku=" & Chr(34) & Me.Sku &
Chr(34)) < Me.Qty Then
Cancel = True
MsgBox "There is not enought Qty in the VAULT to do this."

End If

John Spencer (MVP) said:
IF DLookup("[Qty-Vault]", _
"[Tbl_StudData]", _
"Sku=" & chr(34) & Me.Sku & Chr(34))
< Me.Qty Then

Cancel = True
Msgbox "..."
...



Ray said:
I am a Access VB newbie and need some basic help writing this code. What I
am trying to produce is an error message on a form before it updates if a
certain case exists.

ie. There is not enough QTY in Tbl_StudData. to fullfill your request.

Here's what I have so far:

Select Case Location
Case "Vault"
if (Select [Tbl_StudData].[Qty-Vault] from [Tbl_StudData] where Me.Sku
= [Tbl_StudData].sku)< Me.Qty.Value Then
Cancel = True
MsgBox "Not enough Qty."
Me.Qty.Value = Null
End If
Case "Memo"
if (Select [Tbl_StudData].[Qty-Memo] from [Tbl_StudData] where Me.Sku
= [Tbl_StudData].sku)< Me.Qty.Value Then
Cancel = True
MsgBox "Not enough Qty."
Me.Qty.Value = Null
End If
End Select

I get A Run Time Error '2465' each time I try to save the record. VB
highlights this line:if (Select [Tbl_StudData].[Qty-Vault] from
[Tbl_StudData] where Me.Sku = [Tbl_StudData].sku)< Me.Qty.Value Then
Click to expand...
Click to expand...
Click to expand...
 
Back
Top