Combo Box Search

[Icecream7]

I have a form that I wanted to create a way to search for a kit and have all the fields update to that kits information. When I created the combo box I did it according to the kit table so all the available kits are listed but it doesn't update the rest of the information that goes with it. Do I need to requery and if so how would I do the script for it?

This is the script so far
Private Sub cmdFindKit_Click()
On Error GoTo Err_cmdFindKit_Click


Screen.PreviousControl.SetFocus
DoCmd.FindRecord "Kit", acEntire, True, acSearchAll = _
True, True, acAll, True

Exit_cmdFindKit_Click:
Exit Sub

Err_cmdFindKit_Click:
msgBox Err.Description
Resume Exit_cmdFindKit_Click

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

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

 

[Icecream7]

Ok I found the mistake with this. Now my problem is that when you click on the new record >* and then use the combo box to find a different kit it won't update those fields. Should I not use the new record?

 

[Remou]

I don't quite get what you are saying. I suppose you are going with option 2 (Private Sub Combo46_AfterUpdate) from your first post? If so, do you mean that new records are not appearing in combo46? Forb this to happen, you need a requery. The After Insert event seems about right:

Private Sub Form_AfterInsert()
Me.Combo46.Requery
End Sub

Another problem with this type of Combo is that you need to synchronise it with your record in On Current, or blank it, otherwise it will keep showing the same value as you move through the form.
Coosing a different kit in a new record should move you away from new record and populate the form with the record chosen. If this is not happening, is there any other code on your form that could affect it?
Finally is any of the above relevant to your post? If not, can you explain a little more?

 

[ssatech]

Why do you need this code?
Private Sub cmdFindKit_Click()
On Error GoTo Err_cmdFindKit_Click
Screen.PreviousControl.SetFocus
DoCmd.FindRecord "Kit", acEntire, True, acSearchAll = _
True, True, acAll, True
Exit_cmdFindKit_Click:
Exit Sub

Err_cmdFindKit_Click:
msgBox Err.Description
Resume Exit_cmdFindKit_Click

End Sub


Just use this one...
Private Sub Combo46_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

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

 

[Icecream7]

When I first created this I tried to have a text box for them to enter the kit they are looking for and click on a search button to get the results or a message saying that the kit is invalid. I couldn't seem to do the correct code to get this to work right and I found a message on this site saying to try the combo box. It works great and updates the fields if the kit is in the list box. The form is based off of a query for the kits and when I click on the new record button it bugs out and won't update anything chosen that is valid. The is >* at the bottom of the sub form where you can navigate to different record numbers. I thought about putting a notice lable on saying that all new entries need to be done in the query but that will really defeat the purpose of them being able to see the kits and edit them.

The code that I added above is what was generated with the combo box. The top part was a boo-boo paste, I tried to use a search button and I think it was from that. At this point I'm affraid to delete it.

 

[Zion7]

Sorry IC7, I'm a little unsure about the whole objective.
I think I gathered that when a new record is created,
the search code fails.

Can you try this,

If Not Me.NewRecord Then....

 

[Icecream7]

Hi Zion7....

What you gather is what happens. The combo search works great but when I go to the end of the records and try to add one it won't perform the search any more. The only thing on the form that changes to the Kit option in the combo box is the Kit field, the rest of the fields stay the same.

So would I put the If Not Me.NewRecord Then...
below the If Not rs.EOF?

Set rs = Me.Recordset.Clone
rs.FindFirst "[Kit] = '" & Me![Combo46] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

 

[Zion7]

No, I would put it before,
why search or do any related actions, if it's going to fail?

If Not Me.NewRecord Then
Set rs = Me.Recordset.Clone
rs.FindFirst "[Kit] = '" & Me![Combo46] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
rs.close: Set rs = Nothing
End If


as you may realise, this doesn't really solve the problem,
just avoids it (which may be the intent?).

if not, Maybe you can force a save, requery, then retry code, if Me.NewRecord = True?

 

[Remou]

Can I draw your attention to a few lines in my first post?
"Choosing a different kit in a new record should move you away from new record and populate the form with the record chosen. [blue]If this is not happening, is there any other code on your form that could affect it?[/blue]"
Can you post any other code on the form that seems relevant?

 

[Icecream7]

Here's what I have on the form:
Private Sub cmdFindKit_Click()
On Error GoTo Err_cmdFindKit_Click

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

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


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

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

I commented out the combo49 because it was a combo box I deleted. The form itself has the first box as an autonumber and when I click on the >* it leaves the last kit looked at in the combo box and the above fields (kit information) are blank. The autonumber doesn't update. Should I put a new record button or save button or something?

I did delete out some old code from a search button and that seemed to help it from freezing on the last record when I would choose a different kit. What I need to know is if they enter in a new kit will that save on the form and on the query the form is based off of?

 

[Zion7]

Yes IC7, of course it will. Why wouldn't it?
If you create a new record, or new "Kit",
you've created a new record!!! Just like all the other
kits already created???
UNLESS WE"RE MISSING SOMETHING???

Why is the auto field not updating?
It won't show a value simply by moving the cursor,
to a new record, you must enter some data.

Does it still not update after that?

Just for arguments sake,
After completing the info, for a NEW kit,
In your code try ,


Private Sub Combo50_AfterUpdate()

Dim rs As Object

If Me.NewRecord Then
Docmd.RunCommand acSaveRecord
Combo50.Requery
Me.Requery
End If

Set rs = Me.Recordset.Clone
rs.FindFirst "[Kit] = '" & Me![Combo50] & "'"
Me.Bookmark = rs.Bookmark
rs.Close: set rs = Nothing
End Sub


YOU KNOW SOMETHING, maybe it was the "If rs.EOF"
that wasn't allowing you to "move" from a new record.
After all, you were EOF! (I believe?).

 

[Remou]

Can I mention another point from my first post?
"Another problem with this type of Combo is that you need to synchronise it with your record in On Current, or blank it, [blue]otherwise it will keep showing the same value[/blue] as you move through the form."

 

[ssatech]

Is this form being open by another form?
At the bottom of your form on the navigation controls, does it show this:

Record |< < 1 > >| >* of 1 (Filtered)

 

[Icecream7]

I'll try adding the code from Zion7 to see how that works.
I am interested in your point also Remou but I don't know how to synchronise, can you give me some help on it?

Yes ssatech the Record option is at the bottom of the form. It's based off of a query but it doesn't open throught a differnt form.

I was reading some of the posts lastnight and it brought up another wonder of mine. The post was "DAO vs ADO?" and I am wondering if I need to set database up as a ADO. The person I'm doing it for will be using it at his home office and work office. He will also have a couple other people accessing the database. I figured I would put it up on my ftp and create a folder for them to save the final bids in. Now I'm wondering if this will work or not. Any input on it?

Also....thanks for all the help and advice with this. I was pulling my hair out and doubting myself before I found this site. It's great to be able to come here and get answers.

 

[Icecream7]

Zion7 I tried the code you had suggested and it worked as far as being able to enter a new kit but.....the last auto number was 318. I tried to enter that number into the auto field and it wouldn't let me. When I tabbed to a different record and went back the system inserted 472. Doesn't it keep the sequence when you enter a new kit? Also when I use the drop down to go to a different record it doesn't update to the right information.

 

[Zion7]

IC7, it's an AUTO field, you can't update an Auto field,
BECAUSE, it's an Auto field!

If there APPEARS to be a gap between Auto records,
it may be because records have been deleted, or your
sort order is not based on the auto field.

Did your combo EVER, update to the righgt info?

The bound column on the Combo, must be the Kit Field,
If you want "Kit ='" & Combo50 & "'"?

If it's not the bound field,
then try,

"Kit ='" & Combo50.Column(1) & "'"

 

[Remou]

(This is an aside, it is in answer to a post dated 4 May 06 21:46, not a response to Zion7's post. I mentioned synchronise or blank. I think blank would be less confusing; it will make it clear that Combo50 is just a find box, not a part of the data. This will do it:
[tt]Private Sub Form_Current()
Me.Combo50 = ""
End Sub[/tt]
And that is the end of the aside, apologies for interrupting )

 

[Icecream7]

Yes Zion7 the form does display the correct information when you click on a kit from the combo box. All of the kits that are listed in the combo box are bound to a kit information query.

It's working good now...thanks for the help. Now on to the next portion.

Not a problem Remou....thanks for the input.