unusual code problem

[oohmygod831]

just finishing my database and cant get my head around this.

i am using cascading combo boxes to filter tools and DLookup to calculate stock levels of the tool in the final combo box and depending on the results either writing the record or undoing it.

here is the code:

Dim cust As Variant
Dim bvalue As Variant
Dim stotal As Variant


cust = Nz(DLookup("[Factory_Stock]", "tblTool", "[Tool_ID] =" & Forms![frmBookToolOut]!Tool_ID), 0)

bvalue = Nz(DSum("[Book_Value]", "tblToolBooking", "[Tool_ID] =" & Forms![frmBookToolOut]!Tool_ID), 0)

stotal = cust + bvalue

If stotal < 1 Then
MsgBox "The tool is already booked out or hasn't been returned"
Cancel = True
Me.Undo
DoCmd.Close
Else
MsgBox "Tool Booked Out"
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
DoCmd.Close
End If

The problem i have is in the calculation not the DLookup, the code runs and depending on the stock levels it displays one of the two messages, it then either saves the record or clears it but heres the problem, when the form closes it throws up two messages boxes prompting you to enter the information required for the cascading combo boxes.

if i remove both of the (docmd.close) it runs smoothly but i want the form to save (or not) and then close via one control button and not two

 

[doco]

It seems to me you want DoCmd.Close regardless of the boolean test. So why not place it outside of your IfThenElse struct or even in another event like after_update?

 

[gemma-the-husky]

the problem is clearly in the code you have not shown us, not this code

when you execute the save record line

(and incidentally
runcommand accmdsaverecord is better syntax now than docmd.domenuitem, as the latter is now outdated)

this is trying to save the record, and I would think this line/action is causing the problem, because you are doing something with the cascading combo boxes that is probably breaking your referential integrity

 

[oohmygod831]

dont know why but i had a play with the code and it now works

Dim cust As Variant
Dim bvalue As Variant
Dim stotal As Variant


cust = Nz(DLookup("[Factory_Stock]", "tblTool", "[Tool_ID] =" & Forms![frmBookOut]!Tool_ID), 0)

bvalue = Nz(DSum("[Book_Value]", "tblToolBooking", "[Tool_ID] =" & Forms![frmBookOut]!Tool_ID), 0)

stotal = cust + bvalue

If stotal < 1 Then
MsgBox "The tool is already booked out or hasn't been returned"
Cancel = True
Me.Undo
DoCmd.Close
Else
MsgBox "Tool Booked Out"
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
DoCmd.Close
End If