Dropdown A, B, C... to jump to names

[paludi]

Hello,

I am using Access 2002. I created a database where names are stored.
Now I want to add a drop down menu which contains the alphabet, means A, B, C... Y, Z. By selecting H for example, the form should jump to the first name in the database starting with H.
I tried the following code but there was no effect on the form. Nothing happened.
[fullname] is a field in the database as well as in the form.

Thank you for helping.

Tobias

Private Sub gotoselect_Change()

    Dim strCriteria As String

    Dim rst As DAO.Recordset
       
    Set rst = Me.RecordsetClone
 
    strCriteria = "[fullname] = '" & Me![gotoselect] & "'"
  
    rst.FindFirst strCriteria
  
    Me.Bookmark = rst.Bookmark
        
End Sub

[missinglinq]

I'm confused! A standard combobox, with AutoExpand set to Yes (the default) will take you to the first name beginning with a H.

Hitting <Enter> will then retrieve that record, and you'll be in the H's!

[puppydogbuddy]

Hello,

I am using Access 2002. I created a database where names are stored.
Now I want to add a drop down menu which contains the alphabet, means A, B, C... Y, Z. By selecting H for example, the form should jump to the first name in the database starting with H.
I tried the following code but there was no effect on the form. Nothing happened.
[fullname] is a field in the database as well as in the form.

Thank you for helping.

Tobias

Private Sub gotoselect_Change()

    Dim strCriteria As String

    Dim rst As DAO.Recordset
       
    Set rst = Me.RecordsetClone
 
    strCriteria = "[fullname] = '" & Me![gotoselect] & "'"
  
    rst.FindFirst strCriteria
  
    Me.Bookmark = rst.Bookmark
        
End Sub

Try this:

Private Sub gotoselect_AffterUpdate()
 
    Dim strCriteria As String

    Dim rst As DAO.Recordset

    Set rst = Me.RecordsetClone
    
    strCriteria = "Me![fullname] = '" & Me![gotoselect].Value & "'"
  
    rst.FindFirst strCriteria
  
     If rst.NoMatch Then
        MsgBox "No match was found."

     Else

        Me.Bookmark = rst.Bookmark

     End If
   rst.Close        
End Sub

[paludi]

Try this:

Private Sub gotoselect_AffterUpdate()
 
    Dim strCriteria As String

    Dim rst As DAO.Recordset

    Set rst = Me.RecordsetClone
    
    strCriteria = "Me![fullname] = '" & Me![gotoselect].Value & "'"
  
    rst.FindFirst strCriteria
  
     If rst.NoMatch Then
        MsgBox "No match was found."

     Else

        Me.Bookmark = rst.Bookmark

     End If
   rst.Close        
End Sub

Thank you for your idea.
Unfortunately I get runtime error 3070
because Me![fullname] is not correct, only [fullname]....

Maybe something else makes problems. So probably easier to use a simple text field.

[puppydogbuddy]

From your original post.
[fullname] is a field in the database as well as in the form.

Try this:
strCriteria = "Me![fullname].Value = '" & Me![gotoselect].Value & "'"

[paludi]

From your original post.
[fullname] is a field in the database as well as in the form.

Try this:
strCriteria = "Me![fullname].Value = '" & Me![gotoselect].Value & "'"

I think the problem is that I show only one record in the form. So Access compares the value of my combo box with the value of that particular value and not with all values in the database.

Now I always get the message "No match found" even if I select e.g. "B" and the shown name is "Binu"...

How can I search the whole DB?

Thanks for your help.

[puppydogbuddy]

I think the problem is that I show only one record in the form. So Access compares the value of my combo box with the value of that particular value and not with all values in the database.

Now I always get the message "No match found" even if I select e.g. "B" and the shown name is "Binu"...

How can I search the whole DB?

Thanks for your help.

Are you trying to dowildcard finds?
Go back to using change event
Try this:
strCriteria = "[fullname] Like '" & Chr(34) & "*" & Chr(34) & Me![gotoselect].Value & "'"

[puppydogbuddy]

Are you trying to dowildcard finds?
Go back to using change event
Try this:
strCriteria = "[fullname] Like '" & Chr(34) & "*" & Chr(34) & Me![gotoselect].Value & "'"

oops! wildcard should be at the end of the string.
strCriteria = "[fullname] Like '" & Me![gotoselect].Value & Chr(34) & "*" & Chr(34) & "'"

[puppydogbuddy]

oops! wildcard should be at the end of the string.
strCriteria = "[fullname] Like '" & Me![gotoselect].Value & Chr(34) & "*" & Chr(34) & "'"

I am not sure about the quotes around the wildcard, so try this way if above doesn't work:

strCriteria = "[fullname] Like '" & Me![gotoselect].Value & Chr(34) & * & Chr(34) & "'"

[paludi]

oops! wildcard should be at the end of the string.
strCriteria = "[fullname] Like '" & Me![gotoselect].Value & Chr(34) & "*" & Chr(34) & "'"

Thank you so much!
This is the correct solution:

strCriteria = "[fullname] Like '" & Me![gotoselect].Value & "*" & "'"

no need for Chr(34) because then I get double quotes. So for string search I have to use LIKE...
Thanks again!

[missinglinq]

I have to ask again, why don't you use a standard combobox, with AutoExpand set to Yes? You enter one letter, hit <Enter> and you'll be taken to the first record where the name starts with that letter. You'll then be able to navigate thru all records starting with that letter.

[paludi]

I have to ask again, why don't you use a standard combobox, with AutoExpand set to Yes? You enter one letter, hit <Enter> and you'll be taken to the first record where the name starts with that letter. You'll then be able to navigate thru all records starting with that letter.

standard combobox means a dropdown list, isn't it? that is what I am using. I can select only one letter. The thing is that I find it easier to use only the mouse and not the keyboard in many cases when I only want to look up some data entries.

My problem was the code behind it. For your solution I also need the same code or is there any other way?

[Bluejay906]

I went through the thread quickly, so I might have missed this. If I wanted a dropdown list of first letters of names, I would create a query where all unique first letters from the names field. This way I wouldn't have any letters in the dropdown that don't occur in the data. When a specific letter is selected, I would have my browse criteria.