Combobox display order

[jzwp11]

I’m not sure why, but the ‘Client Medications List’ loads on the form for the first client when opened. When a new client is selected the Med List clears then when the new client meds are selected they do not load into the ‘Client Med List’ summary field like when the first clients did. No sure why.

You had the main form bound to a table (tblClientProfilesInput) and you were essentially viewing the subform for only the first client. Since you are adding records via code the form must be unbound. I've made the necessary corrections in the attached DB.

[Duncan]

Many thanks for your continued help. I think the difficult thing of which I think I'm getting better at is thinking 3 dimensionally from a relational database point of view instead of an Excel point of view thus the confusion in my database design thinking.
I agree I have put the Appendix Category table back in with the relationship. I was also thinking that I would have two separate interfaces; 1 what we have already done for the initial assessment when a client is admitted and if a new Clinical Domain needs to be entered, And 2 for editing previously entered Clinical Domains. I will start working on this when I get back home. Should I use list boxs that load the previous Clinical Domain data or a qry in a subfrm type scenario (not sure where to start).
Am I right in saying that the 255 character limit could be reached within say 10 clients or so (as you mentioned) for ‘qryClientAssessActions’ thus making the qry unavailable for use. Is the ‘basConcStuff’ VBA module a way around this (thats what the website suggested)?

[jzwp11]

Am I right in saying that the 255 character limit could be reached within say 10 clients or so (as you mentioned) for ‘qryClientAssessActions’ thus making the qry unavailable for use. Is the ‘basConcStuff’ VBA module a way around this (thats what the website suggested)?

The 255 character limit is for a field in a query (or table), so when you use the function, it will truncate all characters after 255. The basConcStuff is just the name of the module that holds the function that I pulled from the site mentioned earlier. So you will have a 255 character limit when you concatenate the issues into the issues summary field, another 255 character limit when you concatenate the actions/considerations into the actions summary field etc.

Should I use list boxs that load the previous Clinical Domain data or a qry in a subfrm type scenario (not sure where to start).

I would probably use a main form with subforms (based on individual tables). You might consider using the tab control to help with organization.

[Duncan]

Sorry to bother you again. I’m a little confused. If the Concentrate record does not work for above 255 characters which there is always going to be, how am I going to get the inputted selected data in a usable format. I’m assuming this can be done through reports, (I have not entered the realm of reports yet as I’m doing each part of Access at a time). So is the other way not having a qry with all the details in together but have a Summary Form mimicking the Input Form that will only load the clients previous selection in original Input Assessment Form, that can be changed and edited. If this is the only way I can see this working however it will require a qry to load the data into the list boxs capturing the previously selected data on the Summary Form (I think). Just thinking, how would the original data be changed on this Summary Form instead of making duplicate as you put it. If my thinking is correct would you be able to help like previously creating a very generic form with say just the issues list box and I can replicate same. On the side, I think I have solved the Medication form issue where the previously selected medications were not changing when the clients were changing. I got an Idea from the Northwind Database (still working on it). Many thanks

[jzwp11]

If the Concentrate record does not work for above 255 characters which there is always going to be, how am I going to get the inputted selected data in a usable format. I’m assuming this can be done through reports

You are stuck with the 255 character limit even in a report. Typically you would have separate rows (one for each record) rather than trying to put everything in one field. I don't know if you have any flexibility to do things differently.

The only way that I know of would be to do everything with VBA code and then dump it to an Excel spreadsheet. I don't know what Excel's limitations are in that regard.

[Duncan]

In my last post I might not explained myself correctly. I was thinking of dismissing the concentrate records ideas as it’s clearly not appropriate and having the data displayed on separate records in a summary form. Within this form the client’s previously selected CDs would only be shown and available for edit. How any of this is achieved is beyond me.

[June7]

Probably longest thread I've ever seen and only reviewed these last few posts in attempt to get up to speed. I gather that you are not binding forms for data entry but instead using VBA code to save data. I find this odd, to say the least.

The 'concentrate records' idea is definitely unworkable.

What is it about showing and editing existing records that confuses you? How to open form to existing record?

[jzwp11]

As June7 suggests, using bound forms with subforms would be the way to go. You can use the form wizard to help you out with these. The one-side of a one-to-many relationship would generally be the main form while the many sides of the relationship would constitute the subform(s). If I haven't already suggested it in a previous post, Crystal's site has a tutorial for making forms that should help.

[June7]

Yes, wizards can help you quickly build forms, reports, controls. However, I don't use them because they do things I don't like and make it harder to do design edits. The only wizards I use are the special query wizards.

[jzwp11]

I've taken the previous DB from a few posts ago and added an example mainform/subform to help get you started. With the form that opens, just select a client then an assessment date and the mainform/subform should open.

[Duncan]

G-day. I have spent some time finishing the medication and Infection control forms as well as working on the form you sent re – continued CD assessments. The only issue that I have spent hrs searching the net for to no avail is on the ‘frmClientInfections’, ‘ Organism Notes’ = How to change the 255 character limit in the txt box (the data in the txt box is coming from a memo field) as the data is getting cut off. Any advice?
In your last post you sent a form that I changed the name to ‘frmClientCDOngoingAssessment’ which opened from a form that the user would pick an assessment date from. I was thinking that the nurses would not know the correlation between an assessment date and clinical domain so I was working on combining forms, using the ‘frmClientCDOngoingAssessment’ as the main form. My idea; when a client is selected a list of CDs already entered would be listed against the person’s name in a list box. When a CD is selected from this box the corresponding assessment date would show and the CD issues, cause etc which can now be changed along with the assessment date (thus changing existing records). What do you think?
The nurse can then change multiple CD details and assessment dates without having to leave the form. Would you be able to help in getting started when you have time. Many Thanks

[jzwp11]

Because you were pulling the value from the combo box, Access was treating it as a text field and thus was limiting it to 255 characters. I changed it using a Dlookup() back to the table (rather than a query) in both the after update event of the combo box and in the on current event of the form. The amended DB is attached. I also noticed that you had a combo box set up for the fkInfectionID field at the table level (tblClientAssessmentCDInfection). Combo/list boxes at the table level are not a good idea as I pointed out in post 6 of the thread.

My idea; when a client is selected a list of CDs already entered would be listed against the person’s name in a list box. When a CD is selected from this box the corresponding assessment date would show and the CD issues, cause etc which can now be changed along with the assessment date (thus changing existing records). What do you think?
The nurse can then change multiple CD details and assessment dates without having to leave the form

I don't think that you can do that on the same form as you describe because the recordsets would need to be different. The other option is to use unbound cascading combo boxes in the header and have the user make their selection there and then in the after update event move to the corresponding record in the form. This we have done before.

[Duncan]

Thank you for the Dlookup() idea re: infection form and the reminder about combo/list boxes at table level. I have used the Dlookup() format in other aspects of my database. I have spent considerable time working on the last form being the ongoing assessment form (frmClientCDOngoingAssessment). As per the information in your last post I think I have gotten absolutely nowhere which is due to the complexity of the tables and their relationships. I cannot seem to get two things functioning one is a list of a clients CDs already completed (in a list box or combobox) and the other is the corresponding data showing in the subform (issue, cause etc.) when the CD is selected. I have tried everything and am stumped – and I thought I was getting good at the simple stuff!! . I have added the data base to show where I’m up to (that’s not very far)

[jzwp11]

I cleaned up the form/subform/subsubforms; the revised DB is attached. I removed the search combo boxes in the header. If you really want them, they will be somewhat tricky to set up depending on how far down in the subforms you want to filter.

[Duncan]

G-day, thank you for the last post. I have completed the whole database and was going to move onto producing reports then I noticed a small glitch with the frmClientCDOngoingAssessment. I have a possible answer to the problem but I just wanted to pass the idea by you re: best database/form design. In the form frmClientCDOngoingAssessment the first assessment date 7/7 has one CD selected and the second 31/7 has two (as per tblAssessments). When reviewing the second assessment date (31/7) with two CDs on form frmClientCDOngoingAssessment the clinical domain name changes but the details in the issue, cause etc does not for the subform on the left. With the subform on the right, which is an exact copy from your last post, the details do change but are not shown.
I'm thinkng my solution would be (for the subform on the left) to have a new line in tblAssessments for each Clinical Domain selected in frmClientAssessmentDetails as I think the problem is stemming from having the two CDs under the same date (for the subform on the left). Not sure what to do for the sunform on the right if this was the one to use. What do you think? Apart from that I think it is finished. Thank you so much.