Combo box search -> display fields

[Cristodul]

Hello everybody

I’m having problems with the combo box.
I have a db with the following fields: OLD_PN; NEW_PN; ECN; CHANGED_BY.
When I’m doing a search, if I enter a part number that is not in the db I get a message that says that the item is not in the list. That’s great, but when I have a part number that exists, after I hit enter nothing displays. I would like to display the other fields related to that part number.
I spend some time yesterday and I could not figure out how to do it. I tried suggestions from the forum (queries, code) but for some reason nothing worked.

Please give me some guidance.

Thank you in advance,

Cristian

 

[Remou]

Is your form based on the same table that the combo is based on? If so, you can use the wizard to create a combobox that will find a record on your form. If not, please explain a little more.

 

[Cristodul]

Thank you Remou for the fast response.
Yes, my form is based on the same table that the combo is based on.
I create the form with option “Find a record on my form based on the value I select in my combo box”, and still not working.

Cristian

 

[Remou]

Please post the code generated by the wizard.

 

[Cristodul]

I hope this is what you need.

Name Old Part Number
Row Source Type Table/query
Row Source SELECT [Superceded Items].OLD_PN, [Superceded Items].NEW_PN, [Superceded Items].ECN_NO, [Superceded Items].CHANGED_BY FROM [Superceded Items];
Column Count 4
Column Heads Yes
Column widths 1;1;1;1
Bound Column 1
List rows 10
List width 4
Limit to list yes
auto expand yes


Thank you

 

[Remou]

No, its not, but it may come in useful. When you run the wizard, it generates code that you can see by looking at the code sheet. You can get there by highlighting the form and then choosing View->Code from the menu bar. Cut amd paste the code here. Thanks.

 

[Cristodul]

These is some of that code. I looked over all and nothing apeares as a procedure or function. I'm looking in the wrong place?



Private Sub Detail_Click()
End Sub

Private Sub Detail_DblClick(Cancel As Integer)
End Sub

Private Sub Detail_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
End Sub

Private Sub Detail_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)
End Sub

Private Sub Detail_MouseUp(Button As Integer, Shift As Integer, X As Single, Y As Single)
End Sub

Private Sub Form_Activate()
End Sub

Private Sub Form_AfterDelConfirm(Status As Integer)
End Sub

 

[Remou]

Ok. That is why the combo is not working. Try adding a combo again, using the wizard.

 

[Cristodul]

Ok. I've done that, but now instead of having 1 line I have as many lines as I have in the table. I search a record and if is found all the lines are filled with the record name. The other properties are not shown.

This is the code I have:

Option Compare Database

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

Set rs = Me.Recordset.Clone
rs.FindFirst "[OLD_PN] = '" & Me![Combo4] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub
Private Sub Combo6_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[OLD_PN] = '" & Me![Combo6] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

 

[Remou]

So you did have code relating to the first combo! It seems that you have a continuous form or datasheet, not a single form. 'Find a record on my form' is more suited to a single form, as is a combo that references the table on which the form is based. Would a single form suit?

 

[Cristodul]

Yes, it will.
I create a form using the wizard. For Tables/Queries I used table. I added all the available fields and modified the form in design view. Deleted first 2 fields and replace them with combo boxes with option "Find a record on my form..."
I manage to create a single form, but when I find the record the rest of the fields remains empty and don't populate.

I'm willing to try and don't give up!

Cristian

 

[PHV]

For me, a combo used as a navigation tool should NOT be bound ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Remou]

Starting from the beginning.

1. Click on the table you want to use and choose Insert->Form from the menubar.
2. Select Autoform: Columnar from the listed options and click OK.
3. Choose File->Save to save the form you have just created.
4. Choose View->Design
5. Add a combobox using the wizard. Choose Find a record on my form ... (Option 3).
6. Select the ID field and any other necessary descriptive field.
7. At the last screen, choose Remember this value for later use (Option 1).
8. Tell me/us how you get on!

 

[Cristodul]

One step closer to the goal.

I could not find the option 1 (Remember this value for later use). I don't know if this is important, but I use Access 2003.

Is working great with some exceptions:

1. I create 2 combo boxes in the same form. Looks like they are independent. I search an old part no, all the rest of the fields show the corect information (from the table) but the new part field stayes blank (which is the second combo box) (and vice-versa).

2. I type the part number I want to find.
If is not in the table I don't get a message "not found" or something, but is showing information in the other fields, information from the table.

Cristian

 

[PHV]

1. In the Current event procedure of the form you may assign the current relevant values to the combos to keep them sync.
2. Change the generated code from this:
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
to this:
If Not rs.NoMatch Then
Me.Bookmark = rs.Bookmark
Else
MsgBox "not found"
End If

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Cristodul]

I've done it but nothing happens.

Cristian

 

[Remou]

Please post the code showing the suggested change.

 

[Cristodul]

I restart everything from the beginning and I get the message not found.

I'm sorry to ask so many questions... to much espresso...
But how do I sync the 2 combos and when I open the form all the fields to be empty?

Cristian