Part Number Lookup

M

Mr. Smiley

Hello. I have an Access 2003 question. I have prgrammed a button to open a
pop up box. Within this box there is only one one text box, a "Go!" button,
and a cancel button. What I am trying to do is allow the user to enter a
part number into the text box, then press the "Go!" button to open a form
displaying data about that part number. I am not sure how to do this. Also,
I know that if there is no data for the part number, it needs to say "No data
found". I am guessing that I need to create a 'part number' variable that
will temporarily store the part number, then some sort of a query. I'm
really not sure though. Any help on this question is greatly appreciated.
Thank you so much... :)
 
S

strive4peace

FindRecord
---


rather than a popup form to find a part, why not put a FindPart combo at
the top of your Parts form?

~~~~~~~~~~~~~~~~~~~~~~~

Make one or more unbound combos on your form. Let the first column be
invisible and be the primary key ID of the recordsource of your form and
then, on its AfterUpdate event...

=FindRecord()

this code goes behind the form:

'~~~~~~~~~~~~~~~~~~~~
Private Function FindRecord()

'if nothing is picked in the active control, exit
If IsNull(Me.ActiveControl) Then Exit Function

'save current record if changes were made
If me.dirty then me.dirty = false

'declare a variable to hold the primary key value to look up
Dim mRecordID As Long

'set value to look up by what is selected
mRecordID = Me.ActiveControl

'clear the choice to find
Me.ActiveControl = Null

'find the first value that matches
Me.RecordsetClone.FindFirst "IDfield = " & mRecordID

'if a matching record was found, then move to it
If Not Me.RecordsetClone.NoMatch Then
Me.Bookmark = Me.RecordsetClone.Bookmark
End If

End Function

'~~~~~~~~~~~~~~~~~~~~

Warm Regards,
Crystal
*
:) have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*
 
S

strive4peace

where
IDfield is the Name of the primary key field, which is in the
RecordSource of the form


Warm Regards,
Crystal
*
:) have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*
 
M

Mr. Smiley

Crystal, I am confused. You mentioned putting the larger section of code
"behind the form". How do I do this? The way I understand it, you are
saying to put the "=FindRecord()" beside of the words After Update in the
combo box properties. If I do that, it won't let me go into the abk end of
VBA to paste the rest of the code. Please work with me :)
 
S

strive4peace

code behind form, Access Basics
---


to put code behind the form (every form has a Class module for code that
is saved along with the form -- this is what I am calling code behind
the form), choose
View, Code
from the menu in design view of your form

then, you will need to change
IDfield
to the name of the field with your long integer (or autonumber) primary key
this field needs to be in the underlying recordset for the form

I like to use this method because you can put several search combos on
the form and, as long as the bound column is this primary key you are
using in the FindRecord code, you can use the same code.

I do have generic FindRecord code that goes into a general module but I
gave you this because it is easier to understand.

~~~

"The way I understand it, you are saying to put the "=FindRecord()"
beside of the words After Update in the combo box properties"

yes, this is true -- without the quote marks of course <smile>

~~~

to help you understand Access a bit better, send me an email and request
my 30-page Word document on Access Basics (for Programming) -- it
doesn't cover VBA, but prepares you for it because it covers essentials
in Access. I do also send out the first 3 chapters of a book I am
writing on VBA to all who request it.

Be sure to put "Access Basics" in the subject line so that I see your
message...


Warm Regards,
Crystal
*
:) have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*
 
Top