Message PopUpBox

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi I am using Access 2002 to create a Technical Library Data base. I need
help with coding that will have a form pop up when the particular search
criteria has been replaced with a different procedure or quality.

For Example - If I am searching for a "Standard" as a type of publication
that has a category of "Gear Cutting" I would like the form to pop up and
advise that that category has been replaced by "Burning"(there is another
form that filters the search critera with 2 buttons on it "Search" and
"Apply")

Thanks
 
Need more information. What is it (what table, field, etc.) that has to be
checked to determine whether a category has been "replaced"?
 
Hi,

It would be determined by the Primary key "ID No." basically if there is
text in the "Replaced By" field then it would pop up and advise you what it
has been replaced by. This is just so that if someone asks for a particular
book or standard we are still able to identify what it used to be and what it
is now.

Thanks 4 your help
 
Try something like this:

In the OnClick events of both "Search" and "Apply" buttons, you need code
that checks the category box to see if the indicated category has something
in its "ReplacedBy" field. There's probably an easier way to do this, but
the way I would do it is to open a recordset on the table containing category
information and query it to find the category indicated in the text box, then
check the related "replacedby" field. Maybe something like this:


Dim theDB as DAO.Database
Dim recSet as Variant
Dim query as String
set theDB = CurrentDB

query = "SELECT * FROM [tblCategories] WHERE [categoryName]='" &
Me.CategoryTextbox & "';"

Set recSet = theDB.OpenRecordset(query, dbOpenSnapshot)

If recSet.EOF Then
MsgBox("Category doesn't exist")
recSet.Close
Exit Sub
End If

If Not IsNull(recSet("ReplacedBy")) Then
MsgBox("This cateogory has been replaced by " & recSet("ReplacedBy") )
recSet.Close
Exit Sub
End If


It may not be the most efficient way to go about it, but that code, or
something like it, should give you what you want. Let me know if it works
out for you. Good luck.

-ndalton
 
Thank you so much for your help

But

I am a little confused as to where I need to paste this info to:

Do I paste it into the event builder of both the "Search" and "Apply" buttons

Or

go to the recordset (what is a record set?), where would I paste the code?

I am sorry I am quite new at this. Thanks
 
The code should be run whenever either the 'Search' or 'Apply' buttons are
clicked. Access makes it easy to find where this code should be. Open up
the form in design view, right click on one of the two buttons, and select
"Build event...". This opens up the Visual Basic editor and takes you right
to the place where code for the "OnClick" event for the button should
go--that is, the code that is run every time the button is clicked.

The name of the function you see in the Visual Basic editor might be
something like "SearchButton_Click()". You would paste the code between the
name of the fuction and the line that reads "End Sub". Of course, my code
makes guesses at the names of your forms, tables, and fields; you should
replace these with the names you actually gave your objects.

A 'Recordset' is a data structure of DAO (direct access object) [DAO is
almost considered outdated nowadays, and has been largely replaced by ADO,
but I use DAO because it's easy and suits my needs]. This data structure is
used within Visual Basic code (in this case) to interact with Access objects,
namely records. All you're doing when you create and open a recordset is
creating a convenient way for Visual Basic to manipulate Access records. The
'query 'string, an SQL statement, tells the OpenRecordset command exactly
which data to pull from the database.

Hope that answers your questions. Good luck.

-ndalton
 
Back
Top