Combo box help

I have a combo box for selecting records. The following codes are generated by microsoft.

Private Sub Combo20_AfterUpdate()
    ' Find the record that matches the control.
    Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[ItemNo] = " & str(Nz(Me![Combo20], 0))
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark

End Sub

This work fine, but if I have two or three records of the same thing with different qty and date order--it only bring the first record in. I can see the records in my combo box but it just doesn't update the form to the specific record that I am choosing.

Any ideas?

Please let me know if you have other question...

perennial
LVL 1
perennialAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
this line
    rs.FindFirst "[ItemNo] = " & str(Nz(Me![Combo20], 0))
is the one responsible for bringing the first record.


the simplest way is to enable the Navigations Button and use it to view the next record.

0
perennialAuthor Commented:
capricorn1;

Thanks for the respond.

Is there a way that  I can make it so that it will select another records of the same ItemNo?

perennial
0
harfangCommented:
In this case, you probably already display the additional information as additional columns in your combo. for example, if column 2 is the date, you could:
    rs.FindFirst "[ItemNo] = " & str(Nz(Me![Combo20], 0)) & " And [DateOrder] = " & Format(Me.Combo.Column(2), "\#m\/d\/yyyy\#")

This can be expanded to more columns, but it becomes less and less readable...

Another better solution, although more complex, it to set bound column to 0 in the combo box. This will work only as long as both the combo box and the form are perfectly synchronized (same number of records, same filter, same order). In that case, replace the after update event with:

    Me.RecordSource.AbsolutePosition = Me.Combo20

It looks easier, but you will need to check for perfect synchronization at many places... :)

Cheers :)
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

perennialAuthor Commented:
harfang,

My combo box is coming directly from the table as the form. Here is an example of my records:

ItemNo      FirstOfPlantName                     Qty          Source       DatRevd
507050      BUDDLEIA  'Pink Delight'      50      FDA      11/11/04
507450      CAMPANULA  'Dickson's Gold'      300      STK      10/28/04
507450      CAMPANULA  'Dickson's Gold'      365      STK      11/4/04
507450      CAMPANULA  'Dickson's Gold'      400      STK      11/16/04
507450      CAMPANULA  'Dickson's Gold'      1,080      STK      11/17/04
508070      CARYOPTERIS  'First Choice'      50      FDA      11/11/04

Notice itemNo 507450, I have four records...In my combo box I need to show all fields except "ItemNo", so that I user can distingish one record from another. I understand that I could hide the "ItemNo" field. As of right now, if I chose the first CAMPANULA 'Dickson's Gold' this will update my record but after that if I choose another Campanula it will not update. Could you go into a little more detail so that I could bring the record out.

Thank

perennial
0
harfangCommented:
Does the table have a unique identifier. Either an autonum ID, or a combination of fields that is always unique? This would help. As of now, I notice that the 5th column is DatRevd. You can use that with the rs.FindFirst method like this:

rs.FindFirst "[ItemNo] = " & str(Nz(Me![Combo20], 0)) & " And [DatRevd] = " & Format(Me.Combo.Column(5), "\#m\/d\/yyyy\#")

This works even if the combo does not display that column, by the way. However, this combination might not be unique, so this would not work in all cases.

It would be easier to have a unique field. If not, we can try the .AbsolutePosition method...

Cheers :)
0
perennialAuthor Commented:
No, the table does not have a unique field.

perennial
0
harfangCommented:
Ok, I created the following module for a form, on which I have an unbound combo box -- cboSearch -- based on exactly the same recordset as the form, with bound column: 0. This is meant for Access 2000, in Access 2003, we could get a little fancier :)
The ideas is to synchronize the combo box's number of row (0 to n-1) and the forms CurrentRecord (1 to n). This is done through the form's recordset.AbsolutePosition (0 to n-1). It is best if AllowFilters is turned off.
Again, this works only on a record number basis, which is rather unstable in database systems...
Anyway, good luck!

----------------------------------------------------------------------------------------------
Option Compare Database
Option Explicit

Private Sub cboSearch_AfterUpdate()
On Error Resume Next
    Me.Recordset.AbsolutePosition = cboSearch.Value
    If Err Then
        cboSearch.Requery
        Err.Clear
        cboSearch.Value = Me.CurrentRecord - 1
    End If
End Sub

Private Sub Form_AfterDelConfirm(Status As Integer)
    cboSearch.Requery
    cboSearch.Value = Me.CurrentRecord - 1
End Sub

Private Sub Form_AfterInsert()
    cboSearch.Requery
    cboSearch.Value = Me.CurrentRecord - 1
End Sub

Private Sub Form_Current()
    cboSearch.Value = Me.CurrentRecord - 1
    Me.Caption = Me.CurrentRecord - 1
End Sub
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
harfangCommented:
Oh, remove the "Me.Caption = Me.CurrentRecord - 1" after you tested it. It was meant for debugging :)

Cheers :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.