'Go To Record' button

[AndyCabbages]

I have a form at the moment that has next record and previous record buttons but I want to add a 'Go To Record' button. When this button is clicked it should open an input display which says 'Enter Quote Number' (Quote Number is a field, it is not the same as record number). The when an order number is entered it looks that up in the Order table and goes to the record with the corresponding order number.

What is the best way to go about this? The default Access command buttons dont do the right job

 

[JamesMcS]

How about making the gotorecord button open a form with a text box in it, and a button that sets the main form's filter to the value in the text box?

 

[ghudson]

Usually this is done with a combo box to restict only valid options to choose from and the After Update event of the combo box uses a FindFirst method to go to the selected record [I am searching for a match in two fields].

Here is a sample of one of my methods for a combo box named cboSearchDescription

     Dim rsRecordset As Recordset
     Dim sCriteria As String
     Set rsRecordset = Me.RecordsetClone
     sCriteria = "[Account] = '" & Me!cboSearchDescription.Column(0) & "' AND [AccountDescription] = '" & Me!cboSearchDescription.Column(1) & "'"
     rsRecordset.FindFirst sCriteria
     If Not rsRecordset.NoMatch = True Then
         Me.Bookmark = rsRecordset.Bookmark
     End If
     rsRecordset.Close

    Me.cboSearchDescription.Value = ""

 

[AndyCabbages]

Using a combo box does the trick, but for representation purposes it would be nice to have a command button with a pop up message asking for an input. Validation would be done after an incorrect value was entered, rather than restricting it all together

 

[dkinley]

So once you had 1000+ records in the database, you would expect the user to get it right every time? So the expectancy would be that the user would have to memorize every entry in order to use the db?

Or, you would right extra code to do a fuzzy search that responded with a Did you mean record n, n+1, n+2, .... and then have the user click on the record they meant? Or would the user be left guessing until they found a sequence of characters that hit on a record.

Or, to minimize the above, your input validation would be able to detect typos to say ... I don't know to a 89% closeness of a specific record and present them with a record which may or may not be the one they want?

Keep in mind if something is entered some people may use abbreviations for certain things and others won't, etc., etc. ....

If you want it to look cool, I'd stick with the combo box in a popup form. Other than that - you will have to be creative in coding the validation, or sending the users updated lists so they can refer to it to type in the record they want, etc., etc., until both sides are frustrated and you put in the combo box.

So, for short -sure, there's a way - use an input box and after they click the go button use the DLookup function to try and validate the input and then use the DLookup to fetch correct record ID.

HTH,

-dK

 

[AndyCabbages]

The form does not actually take all the records that exist in the database. It only takes ones with a 'status' of 'Awaiting Agreement'. It is a system which deals with quotes sent out to customers, once the quote is accepted ('Status' is updated to 'Confirmed') then they no longer appear on the form. The database does not deal with many quotations at the same time and they are normally agreed on and have their status changed within a few days. Therefore there is only ever really a max of about 30 records, all created within the space of a few days so the user knows the quote number most of the time.

It is more for convenience than the sole way of getting to a record. 80% of the time when the user knows the quote number they should be able to access it straight away, the other 20% it is not too much of a chore to go through manually.

Come to think of it I could always extend the 'list rows' value of the combo box and have it so it opens as a big list down the side of the form. Hmmm, is there any way to make this perminent so they are all there without having to click on the box?

However the prefered method is still to have a command button. If anyone could help me out with using DLookup it would be appreciated

 

[dkinley]

You could set up a query to refer to the input box when the user click's 'Go' and then use the result of the query to populate the form.

Also, you would need to provide error-trapping for this if no records were returned to alert the user of this fact.

The combo box idea has a feature called 'AutoExpand' that will automatically narrow down the choices as the user types them in and you can populate the recordset filtering only those records with the desired status. This would be the simplest implementation.

If there is a 'wow factor' presentation you want to give, I will point you here as another idea - or use to implement in the list down the side you referred to.

Another way is to just use the forms filter property (.Filter). Once the filter has ascertained the correct record, you can use the bookmark to set the correct record in the main form for the user to access.

Hope these help ...
-dK

 

[JamesMcS]

A filter wouldn't work then? You could use wildcards and everything

 

[gemma-the-husky]

the method I use is to emulate the code the binocular uses - as I find it is easier to do this, than use the binoculars.

so have a search field

then have a findfirst button (i also have a findnext button)

the find first then does this
a) sets the focus in the search field (in your case, the quote number)
b) then searches - so

requiredfield.SetFocus
DoCmd.FindRecord SearchString, acAnywhere, False, acSearchAll, , acCurrent, True


the code for find next is similar

requiredfield.SetFocus
DoCmd.FindRecord SearchString, acAnywhere, False, acDown, , acCurrent, False