Solved InputBox Help

[slharman1]

I have this in a public function:

    Dim varInput As Variant, neworder As Long
    varInput = InputBox("Please enter quote number for new order.")
    If IsNumeric(varInput) = False Then
        MsgBox varInput & " is not a valid quote number."
        Exit Function
    End If

    neworder = CLng(varInput)

I need to change the code to do the following:
I need to check one table and make sure the user enters a number that is in the QuoteNumber field for a record in the table AND check another table and make sure the OrderNumber field does not already have that number in any of the records in it.
Can some one tell me how to add the code to my function.

 

[MajP]

I would make my own input box with with a combobox. It would be limited to the above choices. Then in the after update of the combo give them a message that value is not valid. I would not waste my time on a a standard input box. You cannot validate without closing. That would be super annoying in my opinion.

 

[MajP]

See simple way to make a pop up form and return a value.

Access VBA - Prompt a form and return value to caller

Hi, A common feature my apps require is getting a user selected value from a universal popup form and using that value in a sub. I have been lousely designing such forms where they are passed an input (open args) and based on that arg, the form passes to the next function in the stack. Heres a...

www.access-programmers.co.uk

Also for the combox the query selects distinct quote number from table A that is not in the order number field in table B. This can be done with a simple query.

 

[slharman1]

See simple way to make a pop up form and return a value.

Access VBA - Prompt a form and return value to caller

Hi, A common feature my apps require is getting a user selected value from a universal popup form and using that value in a sub. I have been lousely designing such forms where they are passed an input (open args) and based on that arg, the form passes to the next function in the stack. Heres a...

www.access-programmers.co.uk

Also for the combox the query selects distinct quote number from table A that is not in the order number field in table B. This can be done with a simple query.

MajP my fear is that the combo box list will be extremely long but it might be ok. Just not sure how to populate the combo with quote numbers from quotes table that are not already in the order number field in the order table.

 

[MajP]

When you say long how long. Thousands, tens of thousands? Remember you can always type in a combobox. You do not have to do a combobox.
If you do a textbox, you can do a lookup to validate. Either way I would build the query to return the viable records.
Can you post a screen shot of the table fields? The easiest may be to drop those two tables into a query so you can see the field names.
So you can either use that query in a combobox or do your dlookup against it.

 

[MajP]

This article shows how to do a Not In query.\
You need a query that returns all quote numbers in A not in (already used) in table B.

If you make the above query then your validations is something like

If Dcount("*","yourquery","quoteNumber = " & me.txtBoxSearch) > 0 then
'It is found which means it is in A and not in B
end if

 

[MajP]

However it may be important to the user to know why it is invalid. Then you might want your code to first check table A and report if not in A. Then another check to report in A but also in B.

 

[slharman1]

When you say long how long. Thousands, tens of thousands? Remember you can always type in a combobox. You do not have to do a combobox.
If you do a textbox, you can do a lookup to validate. Either way I would build the query to return the viable records.
Can you post a screen shot of the table fields? The easiest may be to drop those two tables into a query so you can see the field names.
So you can either use that query in a combobox or do your dlookup against it.

10K and growing in quotes and probably 50 a month in orders

 

[slharman1]

I will have to do some educating and give this a try, just not sure where to start, I guess build the dialog form and query then try to figure out the code.
Might take me a few days to get to it.
Thanks MajP

 

[isladogs]

When a combo is several thousand records it can be very difficult to use.
One solution is to use cascading combo boxes. See my example Cascading Combo Boxes - Mendip Data Systems

 

[slharman1]

When a combo is several thousand records it can be very difficult to use.
One solution is to use cascading combo boxes. See my example Cascading Combo Boxes - Mendip Data Systems

Not sure how that would help. The combo box is just a list of quote numbers. All from the same table.

 

[isladogs]

If you looked at my example, all postcode records are from the same table.
The approach I used is to split the data into several sections.
Perhaps that will work for your situation, perhaps not...impossible to tell as you haven't posted any example data

 

[arnelgp]

I need to change the code to do the following:
I need to check one table and make sure the user enters a number that is in the QuoteNumber field for a record in the table AND check another table and make sure the OrderNumber field does not already have that number in any of the records in it.

you can also create a Query and use it in datasheet/continuous subform so user can select a record on this subform:

select QuoteNumber from tblQuote Right Join tblOrder On tblQuoute.QuoteNumber = tblOrder.OrderNumber
where ((tblOrder.Ordernumber) Is Null);

 

[MajP]

Here is a demo doing the way you described. As discussed there are lots of ways to do this. I prefer to have a visual of availble choices often in a filter as you type (FAYT) listbox or combobox. So as you type 12 your list narrows down to those records starting with 12.... However these can be resource instensive and may only work well in under 20k records. I have demoed some FAYT lists with 10k and they are still extremely fast.

So this demonstrates an extremely simple way to get a value from a popup form.
1. Drop in the module
2. add an OK and cancel button to your popup
3. call the popup function to get your value. Here is all the code you need

Private Sub cmdqUOTE_Click()
  Dim quote As Variant ' handle a null
  quote = getValueFromPopUp("frmQuoteNumber", "txtinput", Nz(Me.val1, ""))
  If Not IsNull(quote) Then Me.val1 = quote
End Sub

frmQuoteNumber is name of pop up
txtInput is name of control on popup that you want the value from
me.val1 is the control on the calling form and only needed if you want to pass into the popup the value in the control

For the demo I have tblQuote with quote numbers from 1000 - 12763. In tblOrders I have already used numbers 1000 to 2500. So the only valide numbers are 2501 to 12763. In this example if you hit ok it tells you that 2499 is a real quote number, but already been used.

Here is the function to validate the number. I did not really need the unused quote query but did it as a demo.

Public Function isValid()
  Dim quote As String
  Dim quoteExists As Boolean
  Dim quoteNotUsed As Boolean
  If Not IsNull(Me.txtInput) Then
    quote = Me.txtInput
    quoteExists = DCount("*", "tblQuotes", "QuoteNumber = '" & quote & "'") > 0
    If quoteExists Then
      quoteNotUsed = DCount("*", "qryUnusedQuotes", "QuoteNumber = '" & quote & "'") > 0
      If quoteNotUsed = False Then
            MsgBox "The quote number " & Me.txtInput & " exists but has already been used. Hit cancel or enter a valid quote", vbCritical
      End If
    Else
        MsgBox "The quote number " & Me.txtInput & " does not exist. Hit cancel or enter a valid quote", vbCritical
    End If
    isValid = quoteNotUsed
 End If
End Function

 

[slharman1]

I am finally able to get back to this. Might take me a bit to sort through it all.
MajP, your info in Post #14 sounds promising. Just wore out from all the other issues this weekend. Need to learn how to accomplish the error handling in my database.
Can anyone steer me to some info on that as well.
Thanks