Make a combobox a search tool...

[Guest]

Hi all,
I had a request this morning from one of the users of an Inventory system
I setup that is an Access app. linked to a SQL Server database. They have
asked me if I could put a combo box on the form for looking up a stock item
no. from the stock table. In other words, instead of looking up a stock item
for a new record, they want for looking for an existing record. I put a
Combo Box on the form and made it unbound. I looks up the stock item no.,
stock description. When the select the stock item # and the combo closes up,
in the Change Event I wrote the following code:

Private Sub cmboFindStockNo_Change()
'Find the stock#
Dim varStockNo As Variant
varStockNo = Me.CmboFindStockNo.Value
DoCmd.FindRecord varStockNo, , ,acSearchAll, , ,True
End Sub

This errors every time??? Can anyone help?

Tks,
Don

 

[Al Camp]

Donald,
Use the AfterUpdate event of the combo... (use your own names)
Private Sub cmboFindStockNo_AfterUpdate()
DoCmd.GoToControl "StockNo"
DoCmd.FindRecord cmboFindStockNo
End Sub

Also... LimitToList = Yes will prevent bogus StockNo entries.
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

 

[Guest]

Al,
I rewrote the subroutine but still receive an error "A macro set to one of
the current field's properties failed because of an error in the FindRecord
action argument."
Doesn't make sense. The field I'm searching is a integer field. I
rewrote the routine 2 ways, but can't seem to figure out why the FindRecord
method won't work. Here's my latest stab at it:

Private Sub cmboFindStockNo_AfterUpdate()
Dim varStockNo As Variant
On Error GoTo Err_cmboFindStockNo_AfterUpdate
varStockNo = Me.cmboFindStockNo.Value
If varStockNo = 0 OR varStockNo = Null Then
Exit Sub
End If
' Me.cmboFindStockNo.SetFocus 'This doesn't make any difference...
DoCmd.GoToControl "cmboFindStockNo"
DoCmd.FindRecord varStockNo
' DoCmd.FindRecord cmboFindStockNo 'This produces the same error...
Exit_cmboFindStockNo_AfterUpdate:
Exit Sub
Error_cmboFindStockNo_AfterUpdate:
MsgBox Err.Description
Resume Exit_cmboFindStockNo_AfterUpdate
End Sub

Any suggestions,
Don
----------------------------------

 

[Al Camp]

Donald,
I don't see anything obviously wrong with the code...
I have a little test database I tried out my code on. The combo selects
a key Long Integer (IDNo), and finds that specific record.
These 2 lines of code work...
Private Sub cboFindIDNo_AfterUpdate()
DoCmd.GoToControl "IDNo"
DoCmd.FindRecord cboFindIDNo
End Sub

Just try my method "exactly" as a test.

Still have trouble?
Respond back here to let me know, but... do a new repost of your
problem... this one is getting a bit "buried". Perhaps someone else has
some ideas.
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

 

[Guest]

Al,
I was tinkering with this yesterday evening and found the solution by
accident. If you have your Access Wizard turned on and create a new combo
box which I was doing for another form, at the bottom there was an option for
creating the combo box as a search tool. Check it out, I just copied the
code created and modified my AfterUpdate event and works like a charm...
Code in my afterupdate procedure:

Dim rs As Object
Set rs = Me.RecordSet.Clone
rs.FindFirst "[StockNo] = " & Str(Nz(Me.cmboFindStockNo,0))
If Not rs.EoF Then Me.Bookmark = rs.Bookmark

I also did a search on FindRecord in the formscoding area and found others
having the same error as mine. One dude said he executes a FindRecord just
fine on a form that has the data control = to a table but on a form with the
data control = to a query, he get's an error just like mine. Oh well, the
above works well.

Tks for the help,
Don