Set CheckBox Values From Other Tables

[David123]

Good afternoon ladies and gentlemen, I had a question.

I am writing a database for an hospital, and right now I am working on one of the forms. On this form (called main form after this), there are several buttons, leading to other forms (called subforms after this), and the client wants to have a check box next to the button, which is checked off when the form has been filled in (a form counts as filled in if the date has any value other than Null).

The problem is that there are several forms that need to be filled in with every visit, for instance, a questionnaire which is the same every time. The problem I am having is adding a check box, which only checks off if the right form is filled in. It needs to detect the right patient number, then the right type of form, and then the right serial number of the form. I am completely stuck, and that's why I'm asking for help here.

Many thanks in advance, I've tried a bunch of things already but none seem to work, and if there is any additional information you need or want to solve this question, I'd be happy to supply it.

Kind regards,

David.

[NeoPa]

That doesn't really make your situation very clear David. I suspect you may do better explaining the situation first off from the standpoint of the data structure you're working within. That will give us a clearer understanding of what your form is looking to do than just trying to describe the form's behaviour.

It may be that a restart is required and your best approach will be to have subforms (not what you describe as subforms - which aren't) on tabbed pages of your main form. This depends largely on the exact requirement though, and we have barely a glimpse of that as yet.

[David123]

Certainly, I will try to be more clear.

The database is about patients. All the other information is connected to the patient ID. I now have a form, which I call the main form. It is the form you get when you start up the database. On the first few lines of said form, it shows the general patient data, Patient_ID, Firstname, Lastname, Date_Of_Birth and Sex. Underneath that is a field, with several buttons. The buttons lead to other forms (indeed not subforms, as you said), which give additional information about the patient, for instance there is a button which says MRI, and if you click that button, you get to fill in data about the patients MRI.

Now, the main use of this database is for a study, done on specific patients. All patients that are included in this study, have to come back several times over a period of time (once every half year, for about three years). Every time they come back for a check up, several tests are done and a few questionnaires are filled in. Every check up, the same tests are done, and the same questionnaires are filled in. So, in the main form, you have a grid of buttons. On the vertical axis are the different types of tests and questionnaires, and on the horizontal axis is time.

What you now end up with, is 6 buttons, all saying 'Questionnaire 1'. This is because Questionnaire 1 has to be filled in every time the person checks in. I dealt with this by making clicking of the button assign a value to a hidden field called "Serial_Nr". So, clicking the button which is in the column '18 months' assigns a 3 to the value "Serial_Nr".

Now, mind you, this all works. This all works very well actually. The problem is, that my client has asked for an additional feature, namely, that if one of the forms has been filled in, that it will show behind the button. So, if there's a check box behind the button for Questionnaire 1, at 18 months, and it is filled in, that it shows up as checked.

This is kind of a problem, and I am unsure how to proceed. The problem I walk into is that I don't seem to be able to make the checkbox be dependent on the serial number. If I do it the way I think it was supposed to be, I get that either all, or none of the checkboxes are checked.

I have tried working with queries, where it shows the date if the serial number is the desired value, and the patient ID is also correct, but the problem there is that if one of the forms is not filled in (which is going to be often the case), it shows no records.

I hope this helped, and I would appreciate it if you helped me. I'm sorry if I said it wrong again, would it help if I made some pictures of what I am trying to explain?

Thanks on beforehand,

David

[NeoPa]

David, I wasn't thinking you'd made a bad stab at it first time. It's just that describing something like this is a lot harder than people realise. My hint about posting the table design first was supposed to help, but I'm not sure you noticed it. As it's weekend though, and they're generally quite slow times, why don't you attach a copy for me to look at instead. That way I don't need to rely on your explanation skills (Trust me when I say I know how rare it is to find members with them) so heavily and I can find any information I need.

That will rely on you following the instructions found in Attach Database (or other work) very precisely though.

Remember, this is more about how complex your situation is than about any inability to explain things. Very few can do that at the best of times and this is not the most straightforward of situations to explain.

[David123]

Alright, I worked on trying to strip my database of unnecessary data, plus translating it (It is written in my native language, Dutch), but I can't seem to get it to work. The database is huge, and rather complicated, and I can't really show you properly what I mean without sizing it down.

I know it's kind of stupid, but, to try to make it clear regardless, I am submitting pictures anyway.

The first picture is the main form I was talking about. You can see the buttons, the important ones are in the red square, namely the questionnaires' buttons.

The problem I am facing, is trying to make nice little check-boxes behind the buttons. The places where the Checkboxes need to be are marked with a little green circle.

Then, the next picture is of one of the questionnaire forms.

"Volgnummer" means serial number, and is the number that defines which of the questionnaires belongs to which time. If you look at the previous picture, this form, with serial number 1, is the form you get when the button with the yellow dot next to it is pressed. The form with serial number 2 is the one with the blue dot, etc.

The issue at hand is getting the check-box to do the following things:

- Get checked if ALL the following events are true:
- The sourcetable of the form this button leads to has a record for this Patient-ID
- The Serial number in said table matches the serial number connected to this button.

Right now, I have tried the following things:

- Making the source for the main form a query. This worked for a little bit, but the problem I came across is that if one of answers for the hypothetical checkboxes would be false, it would not show false, it would instead not show any records. I would want it to show the record I'm coming from, and just show me which ones are true, and which ones are false. If you could tell me how to do that, it would probably solve my problem too!

-Trying to make the solutions of a query the source of an item in a table. This was a disaster, I won't elaborate.

And I tried many more things, but most of them were just exploring Access, and finding out that a lot of things aren't possible. I am definitely a noob to this program, but I have learned a lot about it, and I like the project I am working on now.

Alright, this was my explanation, I hope it helps, if you want, I can still send the database, but it is a rather complicated database, even though I am sure you have dealt with those before :) (maybe not in an obscure foreign language though?)

And about the explaining, I am aware that it is a lot harder. I am used to explain problems just face to face, along the lines of "Look, when I press this button, this doesn't happen, and I want it to happen!" I also realize that it is difficult to understand something like this through text. It probably also doesn't really help that I never got any official teachings in this, and more or less just taught myself, and therefore am not completely using the proper terminology. I apologize for that on beforehand, and I wanted to thank you on beforehand too. For spending so much time on of the project of someone you don't even know, and from which you will probably never gain anything except gratitude. I think in today's world, this is getting increasingly rare, and I therefore appreciate it a lot.

Thanks :D

David.

[NeoPa]

David:
Making the source for the main form a query. This worked for a little bit, but the problem I came across is that if one of answers for the hypothetical checkboxes would be false, it would not show false, it would instead not show any records. I would want it to show the record I'm coming from, and just show me which ones are true, and which ones are false. If you could tell me how to do that, it would probably solve my problem too!

I'm still trying to work my way through all the explanations, but this particular problem looks like you are simply connecting the record sources in your query (QueryDef) using an INNER JOIN instead of a LEFT or RIGHT JOIN. See SQL JOINs for a better understanding of that side of things. To understand where the SQL fits in when you were just talking about queries see Extracting SQL from a QueryDef.

Let me know where you are when you've read this. If that gets you nowhere we'll see what we can do.

PS. Yes. Send the database anyway. It can't hurt, and I expect I can make more sense of it than you may realise.

[NeoPa]

Another problem with explanations David, is that even face to face, it is hard for most people to realise how important the technical details are to someone who is trying to understand things at a technical level. Most people work from the top down. Starting with the overview. What we need is to build up from the bottom in many cases (particularly this one), so I need table structures; I need relationships between tables; I need all sorts of things that most people don't seem to realise are necessary. Having access to your database will enable me to know things without the necessity of you telling me.

When I can see the technical structure of the form, as well as how the tables fit together, I will be in a much better position to advise on how best to proceed.

[David123]

Hmm, I started on your hint with the query, but it turned out that they were already all inner joins. However, then I tried a left join, and that seems to give the result I need! :D

The only problem now is that when I try to execute the Query, I get the error (Freely translated): The Query is too complex.

The SQL code is as follows:

SELECT [Vaste Patient Gegevens].ZIS, [Vaste Patient Gegevens].Voornaam, [Vaste Patient Gegevens].Achternaam, [Vaste Patient Gegevens].Geslacht, [Vaste Patient Gegevens].Geboortedatum
FROM ((((((((((((((((((((((((((((((((((((((((((((((((([Vaste Patient Gegevens] LEFT JOIN Checkbox_A ON [Vaste Patient Gegevens].ZIS = Checkbox_A.ZIS) LEFT JOIN Checkbox_AF_1 ON [Vaste Patient Gegevens].ZIS = Checkbox_AF_1.ZIS) LEFT JOIN Checkbox_AF_2 ON [Vaste Patient Gegevens].ZIS = Checkbox_AF_2.ZIS) LEFT JOIN Checkbox_AF_3 ON [Vaste Patient Gegevens].ZIS = Checkbox_AF_3.ZIS) LEFT JOIN Checkbox_AF_4 ON [Vaste Patient Gegevens].ZIS = Checkbox_AF_4.ZIS) LEFT JOIN Checkbox_AF_5 ON [Vaste Patient Gegevens].ZIS = Checkbox_AF_5.ZIS) LEFT JOIN Checkbox_B1 ON [Vaste Patient Gegevens].ZIS = Checkbox_B1.ZIS) LEFT JOIN [Checkbox_B2,1] ON [Vaste Patient Gegevens].ZIS = [Checkbox_B2,1].ZIS) LEFT JOIN [Checkbox_B2,2] ON [Vaste Patient Gegevens].ZIS = [Checkbox_B2,2].ZIS) LEFT JOIN [Checkbox_B2,3] ON [Vaste Patient Gegevens].ZIS = [Checkbox_B2,3].ZIS) LEFT JOIN [Checkbox_B2,4] ON [Vaste Patient Gegevens].ZIS = [Checkbox_B2,4].ZIS) LEFT JOIN [Checkbox_B2,5] ON [Vaste Patient Gegevens].ZIS = [Checkbox_B2,5].ZIS) LEFT JOIN Checkbox_C ON [Vaste Patient Gegevens].ZIS = Checkbox_C.ZIS) LEFT JOIN Checkbox_D1 ON [Vaste Patient Gegevens].ZIS = Checkbox_D1.ZIS) LEFT JOIN [Checkbox_D2,1] ON [Vaste Patient Gegevens].ZIS = [Checkbox_D2,1].ZIS) LEFT JOIN [Checkbox_D2,2] ON [Vaste Patient Gegevens].ZIS = [Checkbox_D2,2].ZIS) LEFT JOIN [Checkbox_D2,3] ON [Vaste Patient Gegevens].ZIS = [Checkbox_D2,3].ZIS) LEFT JOIN [Checkbox_D2,4] ON [Vaste Patient Gegevens].ZIS = [Checkbox_D2,4].ZIS) LEFT JOIN [Checkbox_D2,5] ON [Vaste Patient Gegevens].ZIS = [Checkbox_D2,5].ZIS) LEFT JOIN Checkbox_Device_1 ON [Vaste Patient Gegevens].ZIS = Checkbox_Device_1.ZIS) LEFT JOIN Checkbox_E1 ON [Vaste Patient Gegevens].ZIS = Checkbox_E1.ZIS) LEFT JOIN Checkbox_E2 ON [Vaste Patient Gegevens].ZIS = Checkbox_E2.ZIS) LEFT JOIN Checkbox_E3 ON [Vaste Patient Gegevens].ZIS = Checkbox_E3.ZIS) LEFT JOIN Checkbox_E4 ON [Vaste Patient Gegevens].ZIS = Checkbox_E4.ZIS) LEFT JOIN Checkbox_E5 ON [Vaste Patient Gegevens].ZIS = Checkbox_E5.ZIS) LEFT JOIN Checkbox_E6 ON [Vaste Patient Gegevens].ZIS = Checkbox_E6.ZIS) LEFT JOIN Checkbox_ECG_1 ON [Vaste Patient Gegevens].ZIS = Checkbox_ECG_1.ZIS) LEFT JOIN Checkbox_Echo_1 ON [Vaste Patient Gegevens].ZIS = Checkbox_Echo_1.ZIS) LEFT JOIN Checkbox_Functioneel_1 ON [Vaste Patient Gegevens].ZIS = Checkbox_Functioneel_1.ZIS) LEFT JOIN Checkbox_Functioneel_2 ON [Vaste Patient Gegevens].ZIS = Checkbox_Functioneel_2.ZIS) LEFT JOIN Checkbox_Functioneel_3 ON [Vaste Patient Gegevens].ZIS = Checkbox_Functioneel_3.ZIS) LEFT JOIN Checkbox_Functioneel_4 ON [Vaste Patient Gegevens].ZIS = Checkbox_Functioneel_4.ZIS) LEFT JOIN Checkbox_Functioneel_5 ON [Vaste Patient Gegevens].ZIS = Checkbox_Functioneel_5.ZIS) LEFT JOIN Checkbox_Holter_1 ON [Vaste Patient Gegevens].ZIS = Checkbox_Holter_1.ZIS) LEFT JOIN Checkbox_HRV_1 ON [Vaste Patient Gegevens].ZIS = Checkbox_HRV_1.ZIS) LEFT JOIN Checkbox_HRV_2 ON [Vaste Patient Gegevens].ZIS = Checkbox_HRV_2.ZIS) LEFT JOIN Checkbox_HRV_3 ON [Vaste Patient Gegevens].ZIS = Checkbox_HRV_3.ZIS) LEFT JOIN Checkbox_HRV_4 ON [Vaste Patient Gegevens].ZIS = Checkbox_HRV_4.ZIS) LEFT JOIN Checkbox_HRV_5 ON [Vaste Patient Gegevens].ZIS = Checkbox_HRV_5.ZIS) LEFT JOIN Checkbox_Lab_1 ON [Vaste Patient Gegevens].ZIS = Checkbox_Lab_1.ZIS) LEFT JOIN Checkbox_Lab_2 ON [Vaste Patient Gegevens].ZIS = Checkbox_Lab_2.ZIS) LEFT JOIN Checkbox_Lab_3 ON [Vaste Patient Gegevens].ZIS = Checkbox_Lab_3.ZIS) LEFT JOIN Checkbox_Lab_4 ON [Vaste Patient Gegevens].ZIS = Checkbox_Lab_4.ZIS) LEFT JOIN Checkbox_Lab_5 ON [Vaste Patient Gegevens].ZIS = Checkbox_Lab_5.ZIS) LEFT JOIN Checkbox_Medicatie_1 ON [Vaste Patient Gegevens].ZIS = Checkbox_Medicatie_1.ZIS) LEFT JOIN Checkbox_Medicatie_2 ON [Vaste Patient Gegevens].ZIS = Checkbox_Medicatie_2.ZIS) LEFT JOIN Checkbox_Medicatie_3 ON [Vaste Patient Gegevens].ZIS = Checkbox_Medicatie_3.ZIS) LEFT JOIN Checkbox_Medicatie_4 ON [Vaste Patient Gegevens].ZIS = Checkbox_Medicatie_4.ZIS) LEFT JOIN Checkbox_Medicatie_5 ON [Vaste Patient Gegevens].ZIS = Checkbox_Medicatie_5.ZIS) LEFT JOIN Checkbox_VG_1 ON [Vaste Patient Gegevens].ZIS = Checkbox_VG_1.ZIS;

I guess that it is quite complex. I'm going to try if it is possible to simplify it due to splitting it in 2 queries, and then adding those 2 together into one covering query, but that might not work, since I still need all the fields in one query, but, I'll see if I can make it work :)

[NeoPa]

David:
Hmm, I started on your hint with the query, but it turned out that they were already all inner joins. However, then I tried a left join, and that seems to give the result I need! :D

I'm pleased to hear that David, but if you look at my hint again, I think you'll find that's exactly what I was suggesting ;-)

Your query does, frankly, look horribly complex. Why not consider making them unbound CheckBoxes with a formula in each that reflects the status that needs to show. DLookup() should provide the information your formula can be based upon.

I'm afraid working through that SQL is more than I'm prepared to do on a volunteer basis. It would likely take hours of determined work.

PS. I had a quick look through your SQL. Is it true you have loads of tables whose names are of the type [Checkbox_D2,3]? If so, that is likely to cause you a great deal of problems. Tables should be separate when their design is different. When data is in the same format it should be held in the same table generally. EG. If you have sales data over many years, it is poor design to create Sales tables for each year. The correct approach is to create a single table design but to include a year field to differentite between the data for each year. The same applies here (You may find this related Insight - Database Normalisation and Table structures - very helpful).

PPS. It is generally true (I would say >99% likelihood all else being equal) that when you come across any SQL of complexity even approaching yours it indicates there is something fundamentally very wrong with the logic or design behind that SQL.

PPPS. The fields returned in the SELECT clause (Those available to your form) do not include any of the CheckBox information. I imagine that would necessary if you wanted to use their values to control the states of the CheckBoxes on your form.

[David123]

I've been away for a week, but indeed, there is something fundamentally wrong with the SQL. Since I'm so new to Access, I had no idea what Dlookup() actually was, but now that I've looked a little bit into it, I saw that was indeed the solution I was looking for. I'm sorry I wasted so much of your time trying to find a solution to my problem in other ways, which were obviously not the right ones.

Now that I'm working on a little DLookup, I can't seem to get the syntax right. I use the following expression:

= DLookUp("[Datum]", "[Voorgeschiedenis]", _ "[Voorgeschiedenis.ZIS] =" & [forms!Psyche_Hoofdformulier1!ZIS] And "[Voorgeschiedenis.Volgnummer] = 2")

What I am trying to do, is to look up the Date (datum) from the table Voorgeschiedenis (Medical History). However, I only want the ones that have comply with the following conditions: The ZIS (Patient number, it's an integer) connected to the aforementioned date has to be the same as the ZIS on the current form (Forms!Psyche_Hoofdformulier1!ZIS), and the Volgnummer (Serial number) has to be equal to 2.

However, whenever I fill in this, Access keeps telling me I'm doing it wrong. Whenever I'm trying to save, it gives a general syntax error. I've tried to google about this, but the examples I find are contradictory, so that's of no use. Help?

Kind regards,

David.

PS: About the tables named along the lines [Checkbox_D2,3], those are actually queries. My idea was nesting the queries in other queries, so that I could have one overarching query which would run whenever I started the form. This is ofcourse kind of stupid, but I thought it was necessary for my plan. The tables are the same. For instance, there is one table D1 and one D2, not three D2's for every different serial number. As long as the data is the same, it's in the same table :)

[NeoPa]

No worries on that score. You learning is good news as far as we're concerned. If we helped with that then all the better - however it came about.

You have the syntax a little wrong in your DLookup() code. Try instead :

=DLookUp('[Datum]', '[Voorgeschiedenis]', '([ZIS] = ' & Forms!Psyche_Hoofdformulier1.ZIS & ') AND ([Volgnummer] = 2)')

If ever you struggle with such syntax then create the string first (separately) and look at it to see if it reflects what you intended. So, in the same query you could have a separate field which simply displays the value of the string :

='([ZIS] = ' & Forms!Psyche_Hoofdformulier1.ZIS & ') AND ([Volgnummer] = 2)')

[David123]

Wooooooooo! :D I did it! :D I finally got it to work! :D I did have to change one thing, I had to replace the comma's by these things ; , but other than that, it worked like a charm! Thanks so much for everything!

Woow, I'm just going to take the rest of the day off now, I'm excited that this worked :P Have a good day too!

[NeoPa]

Yes. Some countries use semi-colons (;) in place of commas (,) that's true.

You're welcome and I hope you enjoy the rest of your Sunday :-)