By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,483 Members | 3,229 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,483 IT Pros & Developers. It's quick & easy.

If Statement with a Do Until Statement

P: 7
I am using Access 97 (I know) and am creating a lost and found database for materials in our company. I have it all set up so an employee can enter a sales order and pick a specific line/multiple lines from that sales order to choose what has been lost. If the quantity of the Sales order line is 30, they can choose with an input box how many was lost.
My problem is, if the quantity of the sales order is 30, they can still say they lost 40 - which is an obvious problem. If they put in more than what is possible to be lost, I need a message box to explain what they did and then for the input box to appear again and ask them how many was lost. I can get this to happen once, but if they put a greater number again, it just accepts it. I've messed around with lots of VBA, but here is what I have at the moment for just this section.

For Informational purposes....lstbxLostItems has all of the info from the sales order. They can multi select to choose multiple items that are lost. I have invisible text boxes (the controls that start with lst) that will be used to insert into a table. lstQtyLost is the text box that needs to be equal or less than lstbxLostItems.Column (5,i). I hope that helps.
Expand|Select|Wrap|Line Numbers
  1. Dim i
  2. If Me.lstbxLostItems.Visible = True Then
  3. For i = 0 To Me.lstbxLostItems.ListCount - 1
  4.     If Me.lstbxLostItems.Selected(i) Then
  5.         Me.lstDeliveryNumber = Me.lstbxLostItems.Column(1, i)
  6.         Me.lstDLineItem = Me.lstbxLostItems.Column(2, i)
  7.         Me.lstDLineItem = Format(Me.lstDLineItem, "000000")
  8.         Me.lstSalesOrder = Me.lstbxLostItems.Column(3, i)
  9.         Me.lstSLineItem = Me.lstbxLostItems.Column(4, i)
  10.         Me.lstSLineItem = Format(Me.lstSLineItem, "000000")
  11.         Me.lstMaterialNumber = Me.lstbxLostItems.Column(6, i)
  12.         Me.lstMaterialNumber = Format(Me.lstMaterialNumber, "000000000000000000")
  13.         Me.lstMaterialDescription = Me.lstbxLostItems.Column(7, i)
  15.         Dim strInput As String, strMsg As String
  16.         If Me.lstbxLostItems.Column(5, i) = 1 Then
  17.             Me.lstQtyLost = 1
  18.         Else
  19.             strMsg = "The quantity of material " & Me.lstbxLostItems.Column(6, i) & " " & Me.lstbxLostItems.Column(7, i) & " on the Sales Order was " & Me.lstbxLostItems.Column(5, i) & ". How many were lost?"
  20.             strInput = InputBox(Prompt:=strMsg, Title:="Quantity Lost")
  21.             Me.lstQtyLost = strInput
  22.                 'If Me.lstQtyLost > Me.lstbxLostItems.Column(5, i) Then
  23.                 '    Do Until Me.lstQtyLost <= Me.lstbxLostItems(5, i)
  24.                 '    MsgBox ("You entered " & Me.lstQtyLost & " as lost but there are only " & Me.lstbxLostItems.Column(5, i) & " of Material " & Me.lstbxLostItems.Column(6, i) & " " & Me.lstbxLostItems.Column(7, i) & " on the order. Please enter a new quantity.")
  25.                 '    strInput = InputBox(Prompt:=strMsg, Title:="Quantity Lost")
  26.                 '    Me.lstQtyLost = strInput
  27.                 '        If Me.lstQtyLost <= Me.lstbxLostItems.Column(5, i) Then Exit Do
  28.                 'Loop
  29.                 'Else
  30.                 'End If        End If
  31.         DoCmd.RunSQL ("INSERT INTO LostDetail (LDLostHeaderID, LDCustomerName, LDSalesOrder, LDSLineItem, LDDeliveryNumber, LDDLineItem, LDMaterialNumber, LDMaterialDescription, LDTotalLost, LDStillLost, LDWhenLost) VALUES ([Forms]![FrmLostItems]![txtLostNumber],[Forms]![FrmLostItems]![txtCustomerName],[Forms]![FrmLostItems]![lstSalesOrder],[Forms]![FrmLostItems]![lstSLineItem],[Forms]![FrmLostItems]![lstDeliveryNumber],[Forms]![FrmLostItems]![lstDLineItem],[Forms]![FrmLostItems]![lstMaterialNumber],[Forms]![FrmLostItems]![lstMaterialDescription],[Forms]![FrmLostItems]![lstQtyLost],[Forms]![FrmLostItems]![lstQtyLost],[Forms]![FrmLostItems]![txtDateLost])")
  32.     Else
  33.     End If
  34. Next i
The Bold and Italics section is what I've tried. It didn't work so I've commented it out for now. My message box wouldn't appear and the input box would contine to ask sometimes and then all of the sudden not show up, even though I would continue to enter a quantity greater than was on the sales order.

I hope this all makes sense. I just need the input box to say "there were XXX total on the order. How many were lost?" And not accept any answer that is greater than XXX. Please help any way you can and ask any questions you need to. Thanks a ton.

Dec 19 '08 #1
Share this Question
Share on Google+
4 Replies

P: 7
One more thing. Why does Access show a number like 128 as less than 68? It seems it will only check two numbers correctly if they are the same amount of characters (ie 46 and 87, 198 and 145, 3 and 8). Is there a way around this?
Dec 19 '08 #2

P: 36
Have you considered moving the values you are comparing into variables? That might make it easier to read when you step through it in debug mode.

The only problem I've had with numbers not reading as they should was converting a string to an integer. If the number isn't explicitly an integer odd things can happen. You can force Access to see it as an integer by defining a variable, or doing math with it where you do the comparison.

Expand|Select|Wrap|Line Numbers
  1. Dim intQtyLost as Integer
  2. Dim intLostItems as Integer
  4. intQtyLost = Me.lstQtyLost
  5. intLostItems = Me.lstbxLostItems.Column(5, i)
or -->

Expand|Select|Wrap|Line Numbers
  1. If Me.lstQtyLost + 1 > Me.lstbxLostItems.Column(5, i) + 1 Then
Dec 19 '08 #3

P: 675
Number Problem: When Access compares 2 strings, the strings are compared character-by-character. If the 2 1st characters are identical, then Access proceeds to the 2nd characters, and so on. Therefore, all strings beginning with "1" are less than those beginning with "2". This will give the results you are seeing.

Assume txtA = "27" and txtB = "4". txtA is less than txtB, because "2" is less than "4". You can use the Val function to compare strings starting with simple numbers. Val(txtB) is less than Val(txtA). See Help - Type Conversion Functions for other numeric conversions.
Dec 19 '08 #4

P: 7
Thanks a ton you two. I kind searched with what you've both said and come up with a solution to solve the number problem.
Expand|Select|Wrap|Line Numbers
  1. If CInt(Me.lstQtyLost) > CInt(Me.lstbxLostItems.Column(5, i)) Then
  2.                 Do Until CInt(Me.lstQtyLost) <= CInt(Me.lstbxLostItems.Column(5, i))
  3.                 MsgBox ("You entered " & Me.lstQtyLost & " as lost but there are only " & Me.lstbxLostItems.Column(5, i) & " of Material " & Me.lstbxLostItems.Column(6, i) & " " & Me.lstbxLostItems.Column(7, i) & " on the order. Please enter a new quantity.")
  4.                     strInput = InputBox(Prompt:=strMsg, Title:="Quantity Lost")
  5.                     Me.lstQtyLost = strInput
  6.                     Loop
  7.                 Else
  8.                 End If
Cint() did the trick and even the loop seems to be working ok. I haven't broke it yet at least, ha. Thanks again, let me know if you have any other suggestions and I'll let you know if I break something else.

Dec 19 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.