clickable search results? OR show record of record

[tish]

I want to be able to search a database by "contract #" but then be able to
see if there are other records with the same "contract #" and if so, how
many. The contract # is created by the user, and there may be more than one
entry for any given contract if there are modifications of some kind.

I have a contract data entry form (Form #1). I then created Form #2, where
the user will enter what contract # they are looking for. Click the button
and now the results show up in Form #3. Is there a way to make it so that the
user can click on the entry they want to bring up in the data entry form
(Form #1)?

I'm using Access 2003. Basically, Form #1 runs off of Table #1. I created
Table #2, whereby the user will enter via Form #2 the contract number. Form
#3 then uses that data entry (via Form #2) to pull from Table #1 only items
that match (ie, are contract 09ABCD, for example). I'd like to know if it's
possible to click into whichever record I want to look at closer, then pull
up into the Form #1 (to see in further detail). In this Form #3 screen, I am
using the ID key assigned by Access, so there would be a way to uniquely ID
exactly which record I want to see.

Is this an overly convoluted way to go about doing this? Is there a better
way to get the results I want, that being to know when there are other
entries for a specific contract in the database? I would even "settle" for
just being able to see, after searching for a contract #, "record X of Y".. I
had seen a post earlier that was almost there, but it just didn't work for
me, and the post was several years old.

Thanks for any help!

 

[Albert D. Kallal]

Here is some screen shots of some ideas:

http://www.members.shaw.ca/AlbertKallal/Articles/Grid.htm

here is some ideas on how searching could/should work:

http://www.members.shaw.ca/AlbertKallal/Search/index.html

and, if you download my super easy word merge, it has an sample search form
much like the above example. You can find that here:
http://www.members.shaw.ca/AlbertKallal/msaccess/msaccess.html

 

[tish]

wow! thanks..! the word merge search is perfect.. of course, I'm having
issues getting the code correct.. This piece in particular is vexing me
(where you click on the eyeglasses to open a specific record for
editing/viewing, the below is my edited piece):

Private Sub cmdEdit_Click()
If IsNull(Me!contractid) = False Then
DoCmd.OpenForm "TotalsQuery", , , "contractid = " & Me!contractid
Forms!TotalsQuery.NavigationButtons = False
End If
End Sub

Where the access-numbered key field is called "contractid" and the form to
open up is called "totalsquery".. What else am I missing? Specifically, when
I click the eyeglasses, I get a prompt for "contractid".. driving me mad, as
this lil' piece of code seemed to be easy to follow (famous last words.)

Thanks!!

 

[Albert D. Kallal]

Private Sub cmdEdit_Click()

If IsNull(Me!contractid) = False Then
DoCmd.OpenForm "TotalsQuery", , , "contractid = " & Me!contractid
Forms!TotalsQuery.NavigationButtons = False
End If
End Sub

Where the access-numbered key field is called "contractid" and the form to
open up is called "totalsquery".. What else am I missing? Specifically,
when I click the eyeglasses, I get a prompt for "contractid".. driving me
mad,
as this lil' piece of code seemed to be easy to follow (famous last
words.)

'
The 3rd parameter in above is what we call a "where" clause in access. This
is simply a correctly formed "sql" query "where" clause without the word
where.

So, we have two parts:

"contractid = "

the above needs to be legitimate field in the TARGET form you are about to
open. So, as you have written the code, we are to assume you have a field
called contractdi in the target form "TotalsQuery" ? So, I would simply
re-check the legal list of fields you have for the data source in the form
called TotalsQuery. Replace the above contractID with the name of the
primary key that identifies the record you want to open.


2nd part:

& Me!contractid

The above is our current form and current record. We need a field (usually a
primary key) that uniquely identifies the record we are currently on and
this is the record "id" that the target form will open to. So, do you have a
field in continues search form called contractid ? (if not, simply replace
contractID with the name of your primary key field, or at the very least a
field that uniquely identifies the row we are on. so, this value is from our
current form.

so:
"contractid = " & Me!contractid
^^ that above part is the name of the field in the TARGET form


"contractid = " & Me!contractid

^^ that is the name of a field in the seach form

 

[tish]

ARGH!! Thank you. Totally stupid mistake on my part.

But NOW I have yet another problem. I type in the search criteria, hit enter
and.. nothing happens! Peviously I was getting a prompt to enter a paramater
and while not right, at least that was something. I was also getting various
"debug" boxes but now nothing... I am missing something tiny here, too.
aren't I?

Private Sub MySearch()
Dim strSql As String
Dim strWhere As String

If IsNull(Me.txtcontractsearch) = False Then
strWhere = "Contract like '" & Me.txtcontractsearch & "*'"
End If

If strWhere <> "" Then strWhere = " where " & strWhere

strSql = "select * from contracts" & strWhere

Me.RecordSource = strSql
Debug.Print strSql
End Sub
The search box is called txtcontractsearch, source table is contracts. I
only need the "FirstName" search field (called txtcontractsearch here) not
the search box for LastName. I just deleted the references to LastName -- has
this messed up the code?

Please help! Thanks!!

 

[Albert D. Kallal]

ARGH!! Thank you. Totally stupid mistake on my part.

If IsNull(Me.txtcontractsearch) = False Then
strWhere = "Contract like '" & Me.txtcontractsearch & "*'"
End If

The above says we have field named Contract in our table, and we going to
earch whatever was typed into a text box called txtContractSearch on our
form.

Last time I looked, wew had two text boxes called txtFirstName, and
txtLastName.

Did you add a text box called txtContractSearch?

Also, BEFORE you run your code, do a debug->compile after you make any and
all changes to your code...you want to ensure it compiles...

If IsNull(Me.txtcontractsearch) = False Then
strWhere = "Contract like '" & Me.txtcontractsearch & "*'"
End If

If strWhere <> "" Then strWhere = " where " & strWhere

strSql = "select * from contracts" & strWhere

Me.RecordSource = strSql
Debug.Print strSql
End Sub
The search box is called txtcontractsearch, source table is contracts. I
only need the "FirstName" search field (called txtcontractsearch here) not
the search box for LastName. I just deleted the references to LastName --
has
this messed up the code?

If you seaching for your text to be "like" the first name, then

strWhere = "Contract like '" & Me.txtcontractsearch & "*'"

has to become:


strWhere = "FirstName like '" & Me.txtcontractsearch & "*'"

 

[tish]

I actually fiddled some more and got it working the way I want.

Thank you again for your base code and help... It looks great..!