Form Filter

[Guest]

I have a table with 2 fields--Diagnosis and Code. I want to have a form that filters this table after a few letters are placed in a text box at the top of the form.

So the user types the first few letters of diagnosis.....presses command button...the letters typed are used to filter table and return all diagnoses (with their linked codes) that begin with the letters typed.

Should I use a subform for this? Should it be based on the table or some query of the table?

Any help appreciated...

 

[Al Borges]

Hi Ron:

It would be best to use a subform for this and then update its RecordSource
property as such (you can place this in a clickbutton's OnClick event):

Dim xx As String
xx = [TextFieldValue]
SubForm1.RecordSource = "SELECT Table1.* FROM Table1 ORDER BY
[Table1].[ddate] WHERE Patients.LNAME Like " & chr(34) & xx & "*" & chr(34)
& ";"

Regards,
Al

I have a table with 2 fields--Diagnosis and Code. I want to have a form

that filters this table after a few letters are placed in a text box at the
top of the form.

So the user types the first few letters of diagnosis.....presses command

button...the letters typed are used to filter table and return all diagnoses
(with their linked codes) that begin with the letters typed.

 

[Guest]

Thanks, Al--will try it tonight and see what I get! I really want to get good at these SQL statements..I just have to keep practicing.

Ron

Hi Ron:

It would be best to use a subform for this and then update its RecordSource
property as such (you can place this in a clickbutton's OnClick event):

Dim xx As String
xx = [TextFieldValue]
SubForm1.RecordSource = "SELECT Table1.* FROM Table1 ORDER BY
[Table1].[ddate] WHERE Patients.LNAME Like " & chr(34) & xx & "*" & chr(34)
& ";"

Regards,
Al

I have a table with 2 fields--Diagnosis and Code. I want to have a form

that filters this table after a few letters are placed in a text box at the
top of the form.

So the user types the first few letters of diagnosis.....presses command

button...the letters typed are used to filter table and return all diagnoses
(with their linked codes) that begin with the letters typed.

Should I use a subform for this? Should it be based on the table or some query of the table?

Any help appreciated...

 

[Guest]

Well, it didn't quite work. Here is what I did:


Dim strDiagnosis As String
strDiagnosis = [txtDiagnosis] & "*"
subfrmICD9Codes.RecordSource = "Select subfrmICD9Codes.* FROM subfrmICD9Codes ORDER BY subfrmICD9Codes.Diagnosis,subfrmICD9Codes.Code WHERE excel2accessicd9.diagnosis Like &chr(34)& strDiagnosis;"

My error is" Method or Datamember not found" with. RecordSource highlighted. Do I have to declare the record source somehow?

Hi Ron:

It would be best to use a subform for this and then update its RecordSource
property as such (you can place this in a clickbutton's OnClick event):

Dim xx As String
xx = [TextFieldValue]
SubForm1.RecordSource = "SELECT Table1.* FROM Table1 ORDER BY
[Table1].[ddate] WHERE Patients.LNAME Like " & chr(34) & xx & "*" & chr(34)
& ";"

Regards,
Al

I have a table with 2 fields--Diagnosis and Code. I want to have a form

that filters this table after a few letters are placed in a text box at the
top of the form.

So the user types the first few letters of diagnosis.....presses command

button...the letters typed are used to filter table and return all diagnoses
(with their linked codes) that begin with the letters typed.

Should I use a subform for this? Should it be based on the table or some query of the table?

Any help appreciated...

 

[Van T. Dinh]

You haven't referred to the Table/Query "excel2accessicd9" in your FROM
Clause so JET doesn't know where to get it from.

There may be other problems also since the Sources should normally be from
Tables/other queries. In your case, it looks like you are trying to assign
the (sub)Form's RecordSource from the (sub)Form's existing Records and it
sounds very much like circular references.

My guess (of what you are trying to do) is to filter the Subform's
RecordSource (in the context of the Mainform) then you should use the
"Filter" and "FilterOn" Properties, NOT re-assigning the (sub)form's
RecordSource. If [Diagnosis] is a Field in the Subform's RecordSource, try
something like:

Form![MainForm]![SubformCONTROL].Form.Filter = _
"[Diagnosis] = """ & Form![MainForm].txtDiagnosis & "*"""
Form![MainForm]![SubformCONTROL].Form.FilterOn = True

Replace with appropriate names from your set-up. Note that the
SubFormCONTROL name may be different from "subfrmICD9Codes". You need to
check this out in the DesignView of your main Form.

--
HTH
Van T. Dinh
MVP (Access)

Well, it didn't quite work. Here is what I did:


Dim strDiagnosis As String
strDiagnosis = [txtDiagnosis] & "*"
subfrmICD9Codes.RecordSource = "Select subfrmICD9Codes.* FROM

subfrmICD9Codes ORDER BY subfrmICD9Codes.Diagnosis,subfrmICD9Codes.Code
WHERE excel2accessicd9.diagnosis Like &chr(34)& strDiagnosis;"

My error is" Method or Datamember not found" with. RecordSource

highlighted. Do I have to declare the record source somehow?