stuck using vba

[oohmygod831]

not sure how to get my database to do what i need.

i have a form that uses cascading combos to filter records before they are entered on a form. When the last combo box has had its value selected i need something like an AfterUpdate event to either trigger vba.

i need a simple calculation performed and depending on the results of that calculation either display a message and clear the current record or save the current record.

using vba i had thought along these lines:

Dim bkValue As String
Dim facStock As String
Dim totStock As String


bkValue = "Select Booked_Value from tblBooked"
facStock = "Select Factory_Stock from tblTool"
totStock = facStock + bkValue

if totStock <1
then "Display Error Message"
clear current record
Endif



i am very new to this and i am trying to get my head around vba any help pointing me in the right direction would be great.

 

[boblarson]

and depending on the results of that calculation either display a message and clear the current record or save the current record.

For that you would need the form's BEFORE UPDATE event, not the after update. You can set

Cancel = True
Me.Undo

if you want to cancel the save of the record if the calculation requires it. Otherwise you just let it go through and the record will be updated.

 

[oohmygod831]

i tried it in BeforeUpdate but that made a popup box appear and ask for the value to be entered into the combobox.

i dont know if i said this before but i am using cascading combo boxes to filter the data and the calculation that is performed is dependant on the results of the last ombo box.

I also tried it in the afterUpdate but then i got an error 'Run-Time error 13 type mismatch
and it highlighted the 5th row.

Private Sub cboToolName_AfterUpdate()
Dim bkValue As Integer
Dim facStock As Integer
Dim totStock As Integer
bkValue = "Select Booked_Value from tblBooked" ---- this is the line that is highlighted
facStock = "Select Factory_Stock from tblTool"
totStock = facStock + bkValue
If totStock < 1 Then
MsgBox "The tool is already booked out or hasnt been returned"
Cancel = True
Me.Undo
End If
End Sub

 

[boblarson]

This would be your problem with that line:

Dim bkValue As Integer

You can't assign a string to an Integer.

So, perhaps you should have

Dim bkValue As String

 

[oohmygod831]

sorry if im sounding a bit thick but i thought a String was for characters and Integer was for numbers because the fields im trying to access are numeric

 

[boblarson]

I think you need to re-read your own code:

bkValue = "Select Booked_Value from tblBooked" ---- this is the line that is highlighted

"Select Booked_Value from tblBooked" appears to be text to me and not numeric

 

[WayneRyan]

Bob,

I think he wants a DLookUp

bkValue = "Select Booked_Value from tblBooked"

bkValue = DLookUp("[Booked_Value]", "tblBooked")

But, I have no idea as to the proper criteria to use.

Wayne