Problem Displaying Search Result

[JohnL7]

Hi all,

First timer here, so please forgive any daftness on my part. I'm a novice to access setting up my first database. This forun has been brilliant and helped me a great deal. However despite lots of searching I've not been able to sort out this problem.
I have a Form (New Client Details) with a primary key (ClientID), bound to a table (Client Details) and a Subform (Episode of Care Subform1) with a primary key (EpisodeofCareID) bound to another table (Episode of Care). They have a Master/Child link e.g. a client can have multiple episodes of care.
Using a search method I found on this forum I have created a search form (Client Search) which searches on First Name, Surname and Date of Birth. The search is operated by a command button (CmdSearch) with results shown in a Listbox (SelectSearchClientInfo). So the list could contain a number of entries for the same client if they have had multiple episodes of care.This works fine, however I also wanted to be able to select from the list and display all details for that selected record on the 'New Client Details' Form. I've used code found on this site but when I run it the form opens but will only display the first record for that particuler client. This is the code I've tried.
Can anyone help me out and show me whay I'm doing wrong?

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "New Client Details"
stLinkCriteria = "[ClientID]LIKE" & "'*'&" & "'" & Me.[ClientID] & "'" & "&'*'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.OpenForm stDocName, , , "[ClientID] = " & [Forms]![Client Search]![SelectSearchClientInfo], , acDialog

Many Thanks, This is such a good site!!

John

 

[RuralGuy]

Why do you have two OpenForm commands?

 

[JohnL7]

Hi RG,

I'm afraid I'm not knowledgeable enough to know the why's and wherefore's. The code was copied from this site (I'm trying to find the reference again without much luck at the moment). I've tried using only one openform command and end up with just a blank form without any record shown.

I'm guessing the code needs to refer to the 'episodeofCareID' somewhere but all my trials lead nowhere.

Do you think this code is leading in the right direction or is something new needed in order to display the selected record?

John

 

[RuralGuy]

What table/query is the "New Client Details" bound to? Is the [ClientID] field numeric or text. How would "New Client Details" move or show additional records? If [ClientID] a ForeignKey for the table/query of the "New Client Details" form?

 

[JohnL7]

Thanks for getting back RG,

The Form "New Client Details" is bound to Table "Client Details". The [ClientID] field is the primary key for that table so is unique numeric.

The subform "Episode of Care Subform1" is bound to the Table "Episode of Care". The [EpisodeofCareID] field is the primary key for that table. The [ClientID] field is a Foreign key in the "Episode of Care" table.

Hope I'm making some sense here.

John

 

[RuralGuy]

If you pass a PrimaryKey to another form in the WhereCondition argument such as your code suggests, then the most you can expect is one record, since PrimaryKeys are by definition unique. Without more details I can only suggest that if the form you are opening has the ability to display more than one record, then you need to pass it a ForeignKey so there will be more than one record available to display.

 

[JohnL7]

I only want one record to be displayed.

The trouble is that when I make a selection from the listbox (which contains the result of my search) the wrong record is sometimes displayed.

If the search finds clients all of whom have only one episode of care (e.g. every find on the list has a different value for [ClientID]), the right record is displayed when I select from the listbox.

However if the search finds clients with more than one episode of care (e.g.some finds on the list have the same value for [ClientID] ) and lets say I want to see the record for their second episode of care, when I select that item I still end up with their first record (their first episode of care). The column in the listbox which will always have unique entries is the [EpisodeofCareID] however whenever I try to use that in the code in place of [ClientID] I end up with messages that the field could not be found.

My listbox displays the following data...

ClientID ...............From Table "Client Details"
EpisodeofCareID.....From Table "Episode of Care"
FirstName.............From Table "Client Details"
Surname...............From Table "Client Details"
Date of Birth ........From Table "Client Details"
Date of referral......From Table "Episode of Care"

John

 

[RuralGuy]

John, I'm afraid you have me just a little confused.

The Form "New Client Details" is bound to Table "Client Details".

But it sounds like you wish to display "Episode of Care" details which come from a different table. Is your "New Client Details" form based on a query that combines two tables rather than being based on a table as you described in the quote?

 

[JohnL7]

Apologies RG, This is all new territory for me and I'm kind of thumbling through at the moment.

The form "New Client details" is bound to the table "Client Details". The form is made up of a number of Tab pages some of which have subforms on them. The subform "Episode of Care subform1" is on the first tab page. Each page contains some controls bound to the table "Client Details", (these cover demographic details e.g. names, contact details etc...) in addition to a subform.

All the subforms on these pages are bound to the table "Episode of Care" and contain controls detailing information concerning the referral e.g. date of referral, source of referral, treatment etc... I thought creating two tables would allow for multiple referrals e.g. A clients demographic details could be linked to more than one episode of care.

I wanted the search form "Client Search" to search existing records for name and date of birth in order to check if a new referral has been seen previously. If the search confirms that the client is know, I then wanted to be able to view the past episodes of care e.g. look at the form "New client Details" which would include looking at the subforms. So the records that I want displayed would have information coming from both the "Client Details" and the "Episode of Care" tables. As all episodes of care for the same client would have the same [ClientID] value I assume that selecting a record to display from my listbox would need to be based on the [EpisodeofCareID] value. The problem is I just can't get this to work with the code I'm using.

Thanks for your patience RG.

John

 

[RuralGuy]

I'm now wondering if you have the Link Child/Master Fields set properly for your SubForms. If done properly, selecting an existing [ClientID] should display *all* of the Episodes of Care for that [ClientID]. Maybe if you could post a stripped down version of your db with enough sample records to demonstrate the problem, we could solve this particular issue.

 

[JohnL7]

Thanks for sticking with me RG.

I've tried to sort out a cut down version of the Database, please forgive various things not working, it's all draft stage stuff. Hopefully the attached will shed some light on my problem.

John

 

[RuralGuy]

John,
To start, go to Tools>Options>General tab and turn OFF Name Auto-Correct and turn ON Compact on close. I keep it this way during development. You'll see it shrink quite a bit!

 

[RuralGuy]

John,
I haven't solved it yet but there *must* be something wrong somewhere since your Episode of Care table FK field is not complete. For your system to function correctly there needs to be a ForeignKey (ClientID) for *every* record. Wherever you are adding these records is not set up correctly. If it is done through a SubForm and the LinkChild/MasterFields are ClientID then Access will fill in the FK field correctly for you.

 

[JohnL7]

RG, Thanks for perservering.

Most of the data in the Episode of Care table is just experimental. At one point I was having trouble synchronising all the subforms together, at that time a lot of trial and error learning was occurring which, I think, meant data wasn't always being entered into the foreign key [ClientID].

John

 

[RuralGuy]

JohnL,

Here's your db back with a few changes. You still have quite a ways to go but I think you will be able to use the search sort of.

 

[JohnL7]

Thanks RG

Your right, I do have a way to go but I'm enjoying the journey!!

I tried your alterations but I get an error message...

"Syntax error (missing operator) in query expression '[ClientID] ='."

I'm using access 2003 with windows XP, does that make any difference?

John

 

[RuralGuy]

Hi John,

I didn't fix everything. Try the DoubleClick in the SubForm. That's the one I started with and worked on. Which method did you use to get the error? Modify that procedure so it works in a manner consistant with the DoubleClick code. Post back if you still need assistance.

 

[JohnL7]

Hi RG,

Sorry to keep pestering you. Oddly when I double click on the subform I get the same record displayed (Where EpisodeofCareID = 1) regardless of my selection. I'm puzzled as to why it works at your end but not at mine???

John

 

[RuralGuy]

Hi John,

It looks like I commented out one too many lines before returning the db.

Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "New Client Details"
'-- The next line should *not* be commented out!!
stLinkCriteria = "[ClientID] = " & Me.SelectSearchClientInfo

This is from the:
Private Sub SelectSearchClientInfo_DblClick(Cancel As Integer)
code.

 

[JohnL7]

Thanks RG,

That did make a difference, however it's not quite happening at this end. What's occuring now is that when I double click on the listbox the correct client details are displayed but I always get his first "episode of care" and not the one I select. e.g. it seems to be always stopping at the first [EpisodeofCareID] for the selected client.

John