Query By Form without Drop Down

[ClickerClaude]

Experts: I have an unbound text box that has an AfterUpdate which opens the Order Form for the specific Order # (Unique ID) that was entered. Only problem is that when they enter a number that is not valid (not in the table) it still opens the form and shows a blank record. I would like it to recognize that the number is not valild, show a warning message, and not open the form.

I used to have drop-downs linked to the table's Unique ID's but we have too many records and too many users always refreshing the data so the drop downs are not an option. Here is the code I'm currently using:

Private Sub uniqueacct_AfterUpdate()
If Me![uniqueacct] >= 0 Then DoCmd.OpenForm "frm-accounting", , , "[Unique ID] = " & Me![uniqueacct]
[Forms]![search]![uniqueacct] = ""
End Sub
 

Thanks for any help!!

[Rabbit]

Please use code tags.

What you can do it use the DCount() function to see how many records match that ID number.

[nico5038]

Why not change the textbox into a combobox with a "Select Distinct [Unique ID] from tblOrders" as recordsource?
Thus only existing values can be chosen and the autoappend might save some typing...

Nic;o)

[Rabbit]

Why not change the textbox into a combobox with a "Select Distinct [Unique ID] from tblOrders" as recordsource?
Thus only existing values can be chosen and the autoappend might save some typing...

Nic;o)

I don't think the problem was duplicate records but the volume of the records.

[nico5038]

Hi Rabbit, the "Distinct" is only added for "just in case" there are multiple ID's. The main idea is the fact that the user can only select ID's that are found in the table :-)
Saves the testing for an empty recordset.....

Nic;o)