Database relationships for Client>Episdoe>Service

[AussieGal]

I seem to have forgotten some very basic rule with design that has me stumped. I am wanting the database to show one client can have many Episodes (programs), and each program they are in can have many services. I am trying to stop staff from entering clients that are were not in an open program during their service. Can someone try and help me with my relationships? I think I am confused because I want the data entry form to be able to select a staff at the top and then enter the sessions into a subform where each particular client and program they were seen in is choosen. I have attached the relationships table as well as the form I am looking to use. I had it working at one point, but I think I see now that the Sessions don't really seem to link to anything so I took it off and now don't know what to do. (I have taken many of the other linked tables that were linking to the episode off to save confusion so we can see the main problem easily).



Thanks in advance

[June7]

So tblEpisode will have records for each combination of Client and Program?

Maybe instead of ClientID, save EpisodeID in tblSessions.

What are all the address fields in tblEpisode for?

[AussieGal]

So tblEpisode will have records for each combination of Client and Program?

Maybe instead of ClientID, save EpisodeID in tblSessions.

What are all the address fields in tblEpisode for?

I think you might be on the right track. The episode table contains a field called program (there are 6 programs to choose from), with a start date and end date. So as I list them in the Episode table I am really assigning them to a program, a couple of different programs or sometimes opening them in a program they may have been in a while ago. So really the ClientID and Program is not enough of an identifier. An example of data currently in the episode table is as such:
ClientID Program Date in Date out
770098 Housing 7-7-11 8-30-11
676767 Employment 8-8-12
770098 Housing 4-1-11
778788 Employment 12-1-11 4-4-12

You can see some have programs they are still in, (676767 and 770098) although client 770098 has been in the same program 2 times, another the first one closed in 8-30-11. So I think what I need to do while the staff enter a new session, they choose the program they are working with ,and I want the next combo box to list only the clients that are currently in (and not closed in) that program, when they select that client, I should capture the Episode ID instead of the ClientID. Do you agree with that? Does that make sense?

Re the address fields in the episode table....I know this is not normal... I am linking this database to another we are currently using to send billing to the county. The county episode forms need to have the Address at opening and closing remain stagnet...they are to never change. I set that part up so when we print the episode form, the county will still have the address at opening and/or closing listed correctly. In the client table I will be keeping the current address that will change as the client moves around as usual.

I really appreciate your help. Please let me know your thoughts.

[June7]

Yes, that makes sense to me.

Do you know how to set up dependent (cascading) comboboxes? http://datapigtechnologies.com/flash...combobox2.html

[AussieGal]

OK, I got the table structure working well now. The Espidoe ID number is going into the Sessions table. Thank you for your help.

I went on to depecndant Comboboxs, and it seems I have hit another stump. I have the dataentry form when a staff memeber selects there Staff nubmer. this main form is called frmDSLEntry. I then have a subform showing all the sessions that Staff member has completed. The Combo boxs both sitting on a subform called frmSubSessions. Now I guess because they are sitting on a subform it changes the was the Cascading Combo box work. In my Row Source of the episodeID I have a query selecting the EpisodeID where Program equals either [Forms]![frmDSLEntry].[frmsubSessions].[cmbProgram] or [Forms]![frmsubSessions].[cmbProgram]. Both ways are asking me to enter a parameter value. I am stuck here. I have spent over 2 hours on playing with this and researching and have not get any closer. I hope you can provide some explanation or answer as to why I can't get this to work.

[June7]

The query is recordsource for the subform?

Do you want to provide db for analysis? Follow instructions at bottom of my post.

[AussieGal]

Hi, I have provided a copy of the database with a little test data. The form I am using 'frmDSLEntry"is using the one Table (tblSessions), yet I broke it up so half most of the questions went on a subform. I want a staff member to choose who they are and then enter data on services (Sessions). I was originally thinking to have the staff selection on a seperate form that opened to the session data, yet I ended up with this thinking it was easier...maybe not so I just want them not to have to select themeselves everytime they put in a session. Again thank you for working with me. I am very rusty at the moment, it has been nearly 3 years since I have really worked on a database.

The query is recordsource for the subform?

Do you want to provide db for analysis? Follow instructions at bottom of my post.

[June7]

Consider this RowSource for cmbEpisode:
SELECT tblEpisode.EpisodeID, tblEpisode.ClientID & " | " & tblEpisode.OpenDate & " | " & tblEpisode.CloseDate FROM tblEpisode WHERE (((tblEpisode.Program)=[cmbProgram]));

Then select Program 4 and you will see both records for client 1002232. One is open and one is closed. Do you want to exclude the closed records? Could a client have more than one open record for each program?

[AussieGal]

I tried the above code but it is not updating the Combo box after I select a program in the first combo box. I am stumped. I have reattached the database and have the frmDSLEntry2 form with your code, and the frmDSLEntry form showing what I want in my drop down. I only want to show clients names that are in a matching program that was selected in cmbProgram that are open and not closed. frmDSLEntry shows what I am wanting as a result. I just can't understand why the usual cascading combobox rowsource won't work.

[June7]

You don't have VBA code to requery cmbEpisode. The following worked:

Properties for cmbProgram:
RowSource - SELECT tblProgramRU.RUID, tblProgramRU.Program, tblProgramRU.ReportingUnit FROM tblProgramRU ORDER BY tblProgramRU.Program;
BoundColumn - 1
ColumnCount - 3
ColumnWidths - 0";1";1"
AfterUpdate event - [Event Procedure]; code in the VBA procedure Me.cmbEpisode.Requery

Properties for cmbEpisode
RowSource - SELECT tblEpisode.EpisodeID, [LastName] & ", " & [FirstName] & " (" & [tblClients.clientID] & ") / " & [tblProgramRU].[Program] AS Client, tblEpisode.Program FROM tblClients RIGHT JOIN (tblProgramRU RIGHT JOIN tblEpisode ON tblProgramRU.RUID = tblEpisode.Program) ON tblClients.ClientID = tblEpisode.ClientID WHERE (((tblEpisode.CloseDate) Is Null) AND ((tblEpisode.Program)=[cmbProgram]));
BoundColumn - 1
ColumnCount - 2
ColumnWidhs - 0";2"

However, you are using dependent combobox with lookup values in a Datasheet View subform. Dependent comboboxes with lookups don't work well in Continuous or Datasheet view. This is because when you filter the RowSource the filter is reflected for all instances of the combobox. So if the filter is for program 9 then only Lookup records related to program 9 are available for viewing and comboboxes will appear blank. There is no clean and easy way to handle this situation. The issue is a common topic. https://www.accessforums.net/access/...orm-17150.html

[AussieGal]

Here I am again. I really must thank you for your help. I was going to come back saying problem has been resolved, as it seemed to all be working. Then I went into the Database today and it keeps shutting down on me. I am unsure why, I can't recall doing anything to it to break it. I changed the form to run off the Staff table as I want individual staff to just see and enter details that belong to them. The form I have been using is called frmDSL. When you select a program and tab to select the client (episodeID)_ it crashes access and it closes down. I hope you can help me.

[June7]

Yes, it also did that to me at first.

Then I played around with the Episode combobox and the overlaid textbox. I sized the textbox smaller then larger. I changed the Locked property to No then back to Yes. Saved and reopened form. Suddenly working okay. Very mysterious.

[AussieGal]

I tried your way many times and couldn't get it to fix once. I ended up making a copy of the form, that also didn't work, but when I deleted the text box and recreated it and that finally worked. Yet hours later I went in to show someone else how it is working and it is now crashing again. I am at a loss. Is there another work around for the Cascading box on the continuos form that may be better than the way I am going about it. I just can't have the system crashing every few hours like this. Oh thank you again for your help.

[June7]

I don't know of any other fix. As stated, dependent combobox with lookup doesn't work nice on continuous or datasheet view forms. However, I have never heard of this crashing issue before.

The only time I setup a dependent cb with lookup on a continuous form, I arranged the controls vertically and sized the subform so only one record at a time was viewable. Suppose I could have just made it a single form view.

[AussieGal]

I think I worked it out! It seems I had 2 Code box's going on somehow. I deleted all the code on one, and when I went to close it another one popped open. So I deleted that and started again and now it seems to be working fine. Fingers crossed however. I never want to face that issue again. I was ready to re-desgn the whole database. Thank you for your help. Great forum!