if Then with Yes/No

[chimp8471]

My Goal



I want to make sure the user is aware they are entering the right qty to be booked out.

I therefore want this sort of procedure to run on click of a button


If the user enters a
Remove Qty > Qty when they
click add to cart

I want a message to flag up warning
them that they are trying to
book out more than available with
a YES / NO option to continue .

If the user select Yes then do
what it should if they click no to
focus on the remove qty box


I currently have this on the onclick button so have no idea where to enter the code now... any pointers would be really appreciated

Option Compare Database




Private Sub AddToCart_Click()
 Dim strSQL  As String
  Dim db      As DAO.Database
  Dim rs      As DAO.Recordset


  Set db = CurrentDb()
  
  strSQL = "SELECT * FROM tTempPartsIssued WHERE 1=0"
  Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)


  rs.AddNew
  rs!PartNumber = Me.PartID
  rs!QTY = Me.RemoveQty
  rs.Update


  rs.Close
  Set rs = Nothing
  Set db = Nothing
 Forms!frmStocksearch.ListBoxcart.Requery


 Forms!frmStocksearch.Text93.Requery
 
    DoCmd.Close acForm, Me.Name
 
  
End Sub

[Minty]

Something like (aircode untested)

If MsgBox("Qty to remove is greater than available qty!" & vbcrlf & "Press Yes to continue or No to Cancel",VbInformation + VbYesNo, "Qty Check?") = VbNo Then 
    Me.txtRemove.Setfocus
    Exit Sub
End if

Replace txtRemove with your control name

[Micron]

So qty is bound and the 40 shown was not entered by the user and withdraw is also bound to a table? Then perhaps use the BeforeUpdate event of the withdraw control and present you message box if withdraw > qty. If user selects no, cancel the update, undo the entry and set focus back to the withdraw control.

EDIT - I would not do this in the button click event as the user could move off of the record and the entered value would remain.

[Welshgasman]

Why can they add more than available?

[chimp8471]

there maybe a stock discrepancy....i have a plan to deal with a Yes response but need to get this right first

Example...

If there was 70 in the box... the system may say 61 but they physically counted the 70 to remove

hope that makes sense

[chimp8471]

this works to a point thanks.... however i get the message every time i click the button regardless, if it's greater, level or below, i only need message if its actually more than the available qty...

Something like (aircode untested)

If MsgBox("Qty to remove is greater than available qty!" & vbcrlf & "Press Yes to continue or No to Cancel",VbInformation + VbYesNo, "Qty Check?") = VbNo Then
    Me.txtRemove.Setfocus
    Exit Sub
End if

Replace txtRemove with your control name

[Welshgasman]

Well you have to enclose that code with the test for >, though if I counted 70, then I would add the correction to get 70?

[Micron]

You have no decision block - an outer IF block that makes the comparison between the controls?

[chimp8471]

Not going to Lie.... I have no idea what you just said Sorry!

I am trying hard to get my head around this, I know what's in my head but can't seem to translate it to get it to work.

You have no decision block - an outer IF block that makes the comparison between the controls?

[chimp8471]

Correct, 100% in a normal world!!! however this a site where they don't want just anyone adjusting the qty's ... so i have to track the poor systems they have in place.

Well you have to enclose that code with the test for >, though if I counted 70, then I would add the correction to get 70?

[Micron]

I said the same thing as Welshgasman only he said it simpler. Something like

If Me.Withdraw > Me.Qty Then
  If MsgBox("Qty to remove is greater than available qty!" & vbcrlf & "Press Yes to continue or No to Cancel",VbInformation + VbYesNo, "Qty Check?") = VbNo Then
    Me.Withdraw.Setfocus
    Exit Sub
  End If

'whatever should happen if user chose Yes goes here?
End if

Again, I would not do this in the button click event, but what the hay?

EDIT - Can't imagine you'll get a whole lot of records with this Select statement
"SELECT * FROM tTempPartsIssued WHERE 1=0"

[chimp8471]

now that does exactly what i need thanks... one final question.

is there a way to delete the qty previously entered in this field when it sets focus?

Me.Withdraw.Setfocus

[Welshgasman]

Me.Withdraw = 0

[chimp8471]

Many thanks... sure i seem a spoon...

Thanks all

Me.Withdraw = 0

[Micron]

Or me.withdraw = Null
Zero can be used (accidentally or not) by the user whereas Null not so much.
I've never 100% understood the claims that nothing can ever equal null, yet we can create an expression like that and it works.