code to search in form

[laurat]

When a form is opened I would like an input box to appear, promtpting the user to enter a Sales Order Num. If that number is in the table that the form is based off of I would like the form to open to that record, if not, a message box or another imput box appears telling the user that the number could not be found and to please try again. If the number is still not found a message box appears telling them it could not be found and the form will close. I am not really sure how to do this with code, any help would be appreciated. Thank you!

 

[chrismcbride]

Why not just put an unbound navigateable combo box on the form that holds all the Sales Order Numbers? Then the user would not have to type the whole number and there would be less chance for typos. ..
Chris

 

[laurat]

Well I did think of that and tried it. The only field filled in for a record in the table is the Sales Order Num (it is passed to the table from the previous form) so when I would go in the form find the correct Sales Order Num and type the data in for it, I would get a message saying I was creating a duplicate record. I would really like to do it the other way, with the input box, but if a combo box would work better how do I correct the problem I have been having?

 

[chrismcbride]

Well I am not sure that I understand exactly what the problem is, but it sounds like the Combo box is bound to the table field when it shouldn't be. Perhaps you might want to post the db?

If you want to go the route you initally described, you will need to take a few more steps.
1. create a dialog form with a control to enter the SO # in and a button.
2. On the click event of the button, check the value in the control against the existing values in the table. THis can be done in a number of ways. I would typically use a recordset that uses the control values as a parameter, then check the recordcount of the rrecordset to see if there are matches. Pretty sure you can use the Seek method, but I am not up on the particulars.
3. Once you find a match, then open the data entry form by using DoCmd.OpenForm and include the control value as an open parameter.
4. In order to handle the flag that determines how many attemts to offer the user, I think that it is easier to skip this and simply include a Cancel button on the dialog form. You could initialize a form level varible on open of the form and increase it's value each time the button is pressed. Then condition the code behind the button so that it warns the user of too many attempts and closes once the maximum amount of tries is reached.
As I say you could do it this way, but it is much harder.
Chris

 

[laurat]

Ok this may be a dumb question but here goes, how do I go about creating an unbound combo box and still have it store the data in the correct record in the table? It sounds as if a combo box will be easier but I am a little lost right now.

 

[chrismcbride]

Let me give you an example of how I have a number of forms in my db set up (there may be better ways!).

On my Quote form I have a number of records produced by a number of different users. These records are identified by their UID numbers (autonumbers) and by a String Prefix that indicates the company division to whom the record belongs. I want to give the users a way to look up existing Quotes and display them on the form. I also want them to be able to Add new Quotes.

So I have an Unbound combo box (no RecordSource). It's Rowsource would look like this...

SELECT DISTINCTROW tblQuote.quote_no, [id_string] & [original_no] & ' (R-' & [ver_no] & ')' AS Expr1, [id_string] & [original_no] & ' (R-' & [ver_no] & ')' AS [New Code], tblQuote.ver_no FROM tblQuote ORDER BY tblQuote.original_no, tblQuote.ver_no, tblQuote.quote_no;

You can easily use the Query builder to make this string, starting it from the property page of the Combo box ( if you don't use the combo box wizard). The Wizard will also do this for you.

... Ignore the version number, it is not significant to this discussion. So the user will be able to scroll through the list of existing Quotes and select the Quote that they want to look up. In the AfterUpdate of the combo box, you can use the RecordsetClone object and Bookmark Property to navigate to the selected record...

Me.RecordsetClone.FindFirst "[quote_no] = " & Me![cboQuote]
Me.Bookmark = Me.RecordsetClone.Bookmark

As the bound column in the combo box holds the quote_no, this code will look up the record based on that value.

In order to allow the user to add new records, I simply hide the Combo box in the Current Event of the Form when Me.NewRecord = True.

I could send a breif example if you are still having trouble.
Chris