Unbound text field in continuous form: update one record, they all get the same value

[Dannygars]

Hi,

I want to show some text from another table based on the values of the bound fields in each record of a continuous form. I thought it would be easy, but I can't get it to work.

I step through the records in the bound recordset and use some of the values to query another table. Then I use the value from the query to populate the unbound text field.

It works fine for each record. But every time the unbound field is updated, all the records in the continuous form are updated.

I'm stuck on this one. It's probably something simple to fix - if you know what to do!

Do you have any suggestions?

Thanks!
-Daniel

 

[RuralGuy]

Sorry but the only way to get a control on a continuous form to respond uniquely is to bind it to a field in the underlying table/query.

 

[Dannygars]

Thanks, RG, for your concise answer. I was afraid that the solution was "you can't get there from here", but I kept banging my head against the wall on this one because it would have given an elegant solution.

This is the problem I was trying to solve. We need a form to record patients' medical history. I want to give the medical worker a page with about 20 general health conditions, stored in the Conditions table.

Each condition would have a check box to indicate if it was applicable and it would have an associated text field to record a detailed comment. The checked conditions and the comment would be stored in the History table.

I could probably do it easily using a combo box, or something like that, but it would be much less intuitive. The users would probably be more at ease if it looked like a paper form.

How would you do it?

Thanks!
-Daniel

 

[RuralGuy]

Hi Daniel,
Can't you join the two tables together in a query for the Continuous form?

 

[Dannygars]

I tried joining the two tables but there is a complication because I need all the rows in the Conditions table, so I Left join it to the History table.

This is the SQL.

SELECT Med_History.Res_ID,
Med_History.Condition_ID,
Med_Conditions.Condition,
Med_History.Comment,
Med_Conditions.Display_Order
FROM Med_Conditions LEFT JOIN Med_History ON
Med_Conditions.Condition_ID = Med_History.Condition_ID
WHERE (((Med_History.Res_ID)='1'));

It doesn't work as I expected. I thought I'd get all the rows from Med_Conditions, plus the values from Med_History that join on the Condition_ID field. What I get are same rows I'd get from an equijoin.

Have you been able to base forms on Left or Right join queries?

Thanks for your interest and help with this.

 

[RuralGuy]

Hi Daniel,
I'm sorry but queries are not one of my strengths. Have you tried using the Query Builder?

 

[RuralGuy]

Hi Again Daniel,
You may want to review your WHERE condition:
WHERE (((Med_History.Res_ID)='1')); seems to require a Med_History record for each returned record. I would try it in the query builder without the WHERE condition and see what comes back. Is Res_ID a TEXT field? If not, you need to remove the single quote marks from the 1.

 

[Dannygars]

Hi RG,

If I remove WHERE (((Med_History.Res_ID)='1')) from the query, I do get all the rows from the Med_Conditions table, which is what I want.
However, I need to find the medical conditions and the comments that apply to individual residents - so the use of the WHERE condition is essential.

BTW, I used the query builder to get the query I posted. I played around with the query structure quite a bit but I couldn't get anything usable. That's why I tried using unbound fields in the first place.

This is my second project using Access. The first one was simpler and the application is now in use. I am using Access because all the PCs at the hospital already have it as part of Office. I am a volunteer there.

During the first project, I found that almost everything with Access is either very easy or impossible. If this project is to succeed, I'm going to have to stop being stubborn about how the forms 'should' be implemented and just do it the easy way.

Thanks again for your interest and for your help.

-Daniel

 

[RuralGuy]

How about a two stage query? The first one just pulls the records you want from the Med_History table:
SELECT Res_ID, Condition_ID, Comment
FROM Med_History
WHERE ((Med_History.Res_ID)='1');
And the second query just does the unconditional LEFT JOIN?

 

[Dannygars]

That's what I'm going to do this morning. The top of the form will show all the medical conditions. This will be based on the Med_History table.

When the user selects one of the conditions, either by checking an unbound check box (if possible) or by clicking a button, a subform based on the Med_History table will open to let him/her enter their comments.

If it works out, it'll be pretty good, even if it isn't what I first envisioned.

Thanks again for your help. I needed a confirmation that there is no way to solve the unbound field problem before I could let it go.

-Daniel

 

[RuralGuy]

It sounds like you are on your way. Good luck and have fun!