Question on using of a Serach Form (with a Text Box)?

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

Guest

I have a 9 table hierarchy database, I am wanting to create a search form
with a Combo Box to search for a particular record (as put in the Text Box)
and then display the requested record in a different form.

I created the search form with a Text Box with a command button. On the
properties menu of the command button, under the Event Tab, I added the
following code to the On Click event:

Private Sub Command19_Click()

DoCMD.OpenForm fmSearch, acNORMAL
WHERECONDITION=â€[taPROPERTY.CLT]=†& me.TextBox1

End Sub


taPROPERTY is the ‘top’ table in the database and CLT is the primary key
which connects the remaining tables.

This does not work, I am sure my coding is not correct as I’m not that
confident in my ability to properly code this.
 
Ray,

Modify your code as follows:

Private Sub Command19_Click()
DoCMD.OpenForm fmSearch, acNORMAL, , "CLT=" & me.TextBox1
End Sub

if CLT is a numeric field, or:

Private Sub Command19_Click()
DoCMD.OpenForm fmSearch, acNORMAL, , "CLT='" & me.TextBox1 & "'"
End Sub

if CLT is text.

HTH,
Nikos
 
Thanks to both Nikos and Klatuu for their help. Your postings have lead me
in the correct direction, but I am still having a problem.

The code as it is now entered is as follows:


Private Sub Command7_Click()

DoCmd.OpenForm "taSERVICESUMMARY", acNormal, , "CLT='" & Me.Text8 & "'"

End Sub

taSERVICESUMMARY is the form that I want opened with the searched data.
CLT being the field to search by.

CLT is indeed text.

When I enter in the data on the fmSEARCH textbox and then click on the
command button two things happen:

First, I get an "Enter Parameter Value" Box where I have to input the CLT
again., when I click 'ok' on the "Enter Parameter Value" box, I am taken to
the taSERVICESUMMARY form, however, the requested record is NOT displayed.
The first record displayed is the first record in the table.




Nikos Yannacopoulos said:
Ray,

Modify your code as follows:

Private Sub Command19_Click()
DoCMD.OpenForm fmSearch, acNORMAL, , "CLT=" & me.TextBox1
End Sub

if CLT is a numeric field, or:

Private Sub Command19_Click()
DoCMD.OpenForm fmSearch, acNORMAL, , "CLT='" & me.TextBox1 & "'"
End Sub

if CLT is text.

HTH,
Nikos
I have a 9 table hierarchy database, I am wanting to create a search form
with a Combo Box to search for a particular record (as put in the Text Box)
and then display the requested record in a different form.

I created the search form with a Text Box with a command button. On the
properties menu of the command button, under the Event Tab, I added the
following code to the On Click event:

Private Sub Command19_Click()

DoCMD.OpenForm fmSearch, acNORMAL
WHERECONDITION=â€[taPROPERTY.CLT]=†& me.TextBox1

End Sub


taPROPERTY is the ‘top’ table in the database and CLT is the primary key
which connects the remaining tables.

This does not work, I am sure my coding is not correct as I’m not that
confident in my ability to properly code this.
Click to expand...
Click to expand...
 
Ray,

Either CLT is not the correct field name (not control-on-the-form name,
field-in-the-tyable name if different), or the field is not part of the
form's recordsource.

Nikos
Thanks to both Nikos and Klatuu for their help. Your postings have lead me
in the correct direction, but I am still having a problem.

The code as it is now entered is as follows:


Private Sub Command7_Click()

DoCmd.OpenForm "taSERVICESUMMARY", acNormal, , "CLT='" & Me.Text8 & "'"

End Sub

taSERVICESUMMARY is the form that I want opened with the searched data.
CLT being the field to search by.

CLT is indeed text.

When I enter in the data on the fmSEARCH textbox and then click on the
command button two things happen:

First, I get an "Enter Parameter Value" Box where I have to input the CLT
again., when I click 'ok' on the "Enter Parameter Value" box, I am taken to
the taSERVICESUMMARY form, however, the requested record is NOT displayed.
The first record displayed is the first record in the table.




:

Ray,

Modify your code as follows:

Private Sub Command19_Click()
DoCMD.OpenForm fmSearch, acNORMAL, , "CLT=" & me.TextBox1
End Sub

if CLT is a numeric field, or:

Private Sub Command19_Click()
DoCMD.OpenForm fmSearch, acNORMAL, , "CLT='" & me.TextBox1 & "'"
End Sub

if CLT is text.

HTH,
Nikos
I have a 9 table hierarchy database, I am wanting to create a search form
with a Combo Box to search for a particular record (as put in the Text Box)
and then display the requested record in a different form.

I created the search form with a Text Box with a command button. On the
properties menu of the command button, under the Event Tab, I added the
following code to the On Click event:

Private Sub Command19_Click()

DoCMD.OpenForm fmSearch, acNORMAL
WHERECONDITION=â€[taPROPERTY.CLT]=†& me.TextBox1

End Sub


taPROPERTY is the ‘top’ table in the database and CLT is the primary key
which connects the remaining tables.

This does not work, I am sure my coding is not correct as I’m not that
confident in my ability to properly code this.
Click to expand...
Click to expand...
Click to expand...
 
Nokos:

Thanks for you help!!!!

I am still having the same problem. Here is a cut/paste of the recordsource
from the two forms and the single query.

Form: fmSEARCH2
RecordSource: qrySEARCH2

Query: qrySEARCH2
SELECT [taPROPERTY].[CLT], [taPROPERTY].[PropertyAddress],
[taPROPERTY].[CourtDataID],
[taPROPERTY].[Court Parcel Number], [taPROPERTY].[Due Date],
[taPROPERTY].[Taxes Due],
[taPROPERTY].[AbstractorID], [taCODEFENDANT].[FirstName],
[taCODEFENDANT].[LastName],
[taCODEFENDANT].[DOB], [taCODEFENDANT].[Address1],
[taCODEFENDANT].[Address2],
[taCODEFENDANT].[City], [taCODEFENDANT].[State], [taCODEFENDANT].[Zip],
[taCODEFENDANT].[ProcessServerID], [taCODEFENDANT].[ServiceStatusOptionsID],
[taCODEFENDANT].[ServiceDate], [taCODEFENDANT].[ServiceTypeID],
[taCODEFENDANT].[CertifiedMailingDate], [taCODEFENDANT].[Deceased],
[taCODEFENDANT].[ServiceReattempt], [taCODEFENDANT].[CodefendantTypeID],
[taPROPERTYLIENS].[LienHolderID], [taPROPERTYLIENS].[Lien Type],
[taPROPERTYLIENS].[Lien Amount],
[taPROPERTYLIENS].[Instrument No], [taPROPERTYLIENS].[Book No],
[taPROPERTYLIENS].[Page No],
[taPROPERTYLIENS].[ProcessServerID],
[taPROPERTYLIENS].[ServiceStatusOptionsID],
[taPROPERTYLIENS].[ServiceDate], [taPROPERTYLIENS].[ServiceTypeID],
[taPROPERTYLIENS].[CertifiedMailingDate], [taPROPERTYLIENS].[Deceased],
[taPROPERTYLIENS].[ServiceReattempt], [taPROPERTYOWNER].[FirstName],
[taPROPERTYOWNER].[LastName], [taPROPERTYOWNER].[DOB],
[taPROPERTYOWNER].[Address1],
[taPROPERTYOWNER].[Address2], [taPROPERTYOWNER].[City],
[taPROPERTYOWNER].[State],
[taPROPERTYOWNER].[Zip], [taPROPERTYOWNER].[ProcessServerID],
[taPROPERTYOWNER].[ServiceStatusOptionsID], [taPROPERTYOWNER].[ServiceDate],
[taPROPERTYOWNER].[ServiceTypeID], [taPROPERTYOWNER].[CertifiedMailingDate],
[taPROPERTYOWNER].[Deceased], [taPROPERTYOWNER].[ServiceReattempt]
FROM ((taPROPERTY LEFT JOIN taCODEFENDANT ON
[taPROPERTY].[CLT]=[taCODEFENDANT].[CLT]) LEFT
JOIN taPROPERTYLIENS ON [taPROPERTY].[CLT]=[taPROPERTYLIENS].[CLT]) LEFT
JOIN taPROPERTYOWNER
ON [taPROPERTY].[CLT]=[taPROPERTYOWNER].[CLT];

Form: taSERVICESUMMARY
RecordSource: SELECT [taPROPERTY].[Property Index], [taPROPERTY].[CLT] AS
taPROPERTY_CLT, [taPROPERTY].[PropertyAddress],
[taPROPERTY].[CourtDataID],
[taPROPERTY].[Court Parcel
Number], [taPROPERTY].[Due
Date], [taPROPERTY].[Taxes
Due],
[taPROPERTY].[AbstractorID],
[taPROPERTYOWNER].[Owner
ID], [taPROPERTYOWNER].[CLT]
AS taPROPERTYOWNER_CLT,
[taPROPERTYOWNER].[FirstName
],
[taPROPERTYOWNER].[LastName]
,
[taPROPERTYOWNER].[Address1],
[taPROPERTYOWNER].[Address2]


I realize that I am doing something wrong and unfortunately, I just see
don't see it...

Thanks,

Ray.
 
Nikos:

Again, thanks!!!!

I have been playing and reading and *think* I have the field reference
problem resolved. instead of putting the field as [CLT] or [taPROPERTY.CLT]
I changed it to [taPROPERTY_CLT] since the record source references that
field AS. Since making this change, it no longer pops up with a parameter
entry box.

HOWEVER, this code does bring up the taSERVICESUMMARY form, the record that
I had entered in the text box does not come up. A blank record is displayed.


In the taSERVICESUMMARY form, the data entry property is 'off' and the allow
filter property is 'on'.

The code as I currently have it is as follows:

Private Sub Command2_Click()

DoCmd.OpenForm "taSERVICESUMMARY", , , "[taPROPERTY_CLT]="" me.combo0 """

End Sub


Again, thanks,

Ray.
 
I have been playing and reading and *think* I have the field reference
problem resolved. instead of putting the field as [CLT] or [taPROPERTY.CLT]
I changed it to [taPROPERTY_CLT] since the record source references that
field AS. Since making this change, it no longer pops up with a parameter
entry box.
Click to expand...
Good job!


HOWEVER, this code does bring up the taSERVICESUMMARY form, the record that
I had entered in the text box does not come up. A blank record is displayed.
Click to expand...
Probably a problem in the filter syntax.

In the taSERVICESUMMARY form, the data entry property is 'off' and the allow
filter property is 'on'. Good.


Private Sub Command2_Click()
DoCmd.OpenForm "taSERVICESUMMARY", , , "[taPROPERTY_CLT]="" me.combo0 """
Click to expand...
Try this syntax:
DoCmd.OpenForm "taSERVICESUMMARY", , , "[taPROPERTY_CLT]='" & me.combo0
& "'"
(in one line, in case it gets wrapped in your newsreader).

HTH,
Nikos
 
Nikos,

That did the trick. I am able to pull up the correct requested record in
the correct form.

Thanks for the help...

Ray.
 
Welcome!
Nikos,

That did the trick. I am able to pull up the correct requested record in
the correct form.

Thanks for the help...

Ray.


Nikos Yannacopoulos said:
Try this syntax:
DoCmd.OpenForm "taSERVICESUMMARY", , , "[taPROPERTY_CLT]='" & me.combo0
& "'"
(in one line, in case it gets wrapped in your newsreader).

HTH,
Nikos
Click to expand...
Click to expand...
 
Back
Top