• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 407
  • Last Modified:

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
0
perennial
Asked:
perennial
  • 4
  • 3
1 Solution
 
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
Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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
 
harfangCommented:
Oh, remove the "Me.Caption = Me.CurrentRecord - 1" after you tested it. It was meant for debugging :)

Cheers :)
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now