Search drop down not pulling correct record

[Gina Maylone]

Hello all! I have a dropdown search field, based on the name of a project. It has 2 columns, projectname and projectnumber. My code is as follows:


Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[ProjectNo] = '" & Me![Combo118].Column(1) & "'"
Debug.Print Me![Combo118].Column(1)
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

As you can see I am referencing Column(1) to actually pull the project number (as it cannot be duplicated). However, the user has duplicated project names. No matter which project name she selects, it only pulls the "other" project. I've debugged it, and get the "other" project number, even when specifically choosing the project that she wants. Clear as mud? So, it seems to not be paying attention to the Column(1) reference. What can I do to fix this?

I thank you in advance for any light you can shed.

[moke123]

try a dot instead of the bang

Is ProjectNo a string? or number?

Dim rs as DAO.Recordset
set rs = me.RecordsetClone


rs.FindFirst "ProjectNo = '" & Me.Combo118.Column(1) & "'"


if not rs.NoMatch then
     Me.BookMark = rs.BookMark
end if

[Gina Maylone]

It's a string, they look like this: 10-02-20-017. Thanks for looking!

[moke123]

I would use an autonumber for the PK. You can still have a project number as written but just not as a PKey.

[davegri]

Make sure the rowsource for the combobox agrees with the available records in the recordset clone.

[Micron]

I wonder if part of the issue is that you're not moving first in the recordset (which I would declare as such and not use Object) but that's just a guess because I would be using an autonumber pk field as the bound column - I use one in every table, except perhaps those that I test with to help others out with some issue. Also, by not using a NoMatch test, your bookmark will be arbitrarily set if a match isn't found. I realize you should have a match based on your data, and I can't explain why it doesn't seem to find it. Also, I have read that if you used a wizard to make this combobox, it uses some combination of .FindFirst and .EOF that is invalid, which was a bug that may not have been fixed.

BTW, you shouldn't need a recordset object anyway. You should be able to do this with the clone:

With Me.RecordsetClone
    .FindFirst "fieldNameHere =  '" & Me.comboTextValueHere & "'" 
     If Not .NoMatch Then
     Me.Bookmark = .Bookmark
End With

[Gina Maylone]

I've tried setting the value of an unbound text box to the value of column 1 and searching by that, and it's always the wrong value. The right project number is 07-18-18-017, the wrong one is 10-02-20-017 (the one that always comes up). Debug.print shows the wrong value too. The data from the query looks like this:




ProjectName ProjectNo BidDate
Verizon Store Hagerstown 10-02-20-017 10/19/2020
Verizon Store Hagerstown 07-18-18-017 7/27/2018




Is there something in the RS function the grabs the first record no matter what? I'm going a little bit nuts. Thank you again for your time!

[Micron]

Is there something in the RS function the grabs the first record no matter what?

Pretty sure you cannot use combo column reference in a query but does that apply to a recordset as well? I would not think so, but as I mentioned, the use of a numeric pk field should avoid your issue. In fact, you cannot even rely on a table sort since you're using text as numbers. In that case if sorting ascending 100 comes before 10 so what are you going to do when your data raises that issue?

[Gina Maylone]

Micron! You are a Rockstar. That worked slick! I'd still like to know why it wouldn't pull the right record, but that's just my brain. I thank you my friend from the bottom of my heart! Be well.

[Micron]

moke123 was the first to suggest .NoMatch (and numeric pk, which I didn't see) so please click the star button below his post and boost his cred. The forum might double his salary if you do that.

Not sure why the code I wrote fixed it though. Perhaps making a recordset out of a clone? rs as Object instead of Recordset? I'd be interested to see what you have now that works.

[Gina Maylone]

I really have no idea. Maybe a combination of things. Before update (when choosing from the combo box) I have an event to set the value of an unbound text box (txtpid) and I use that

With Me.RecordsetClone    .FindFirst "projectno =  '" & Me.txtpid & "'"
     If Not .NoMatch Then
     Me.Bookmark = .Bookmark
    End If
    End With

and it works and I'm not going to complain! :-) Stars for all of you!