Solved Query using two tables doesn't allow update of combo box (1 Viewer)

[Brother Arnold]

Hi,

I've got an appointment history continuous form populated with a query that lists all the appointments for a client and shows the counsellor name for each appointment. The counsellor name is found on the counsellor table via counsellor_ID that's stored on the appointment table. Sometimes admin want to change the counsellor name so I've made the counsellor name on the form a combo box and the dropdown lists all the names. However if I go to change the name it won't let me select a name. I suspect it's because I've got two tables in the query. If I simplify the query to one table and change the counsellor name to counsellor ID the combo works fine. Any ideas how I get around this?

The SQL for the query is:

SELECT tblAppointment.*, tblCounsellor.counsellor_name
FROM tblAppointment, tblCounsellor
WHERE tblAppointment.client_ID = 21 AND tblAppointment.counsellor_ID = tblCounsellor.counsellor_ID_pk
ORDER BY tblAppointment.appointment_date DESC;

For the combo I use a simple select in the Row Source and the Control Source is counsellor_name from the query

 

[theDBguy]

Hi. I would say simplify the query and store the counsellor_ID and simply adjust the combobox to display the name instead.

 

[Pat Hartman]

Having multiple tables in a query isn't a problem and does not in itself make a query not updateable. Open the query you are using as the RecordSource of the form using the QBE. Can you update the counsellor_ID there? If you can, the problem is with the form. If you cannot, the problem is with the query. Let us know the results of the test.

 

[Brother Arnold]

Having multiple tables in a query isn't a problem and does not in itself make a query not updateable. Open the query you are using as the RecordSource of the form using the QBE. Can you update the counsellor_ID there? If you can, the problem is with the form. If you cannot, the problem is with the query. Let us know the results of the test.

Thanks Pat, when I double checked the RecordSource on the form I noticed that the RecordType was Dynaset so I changed it to Dynaset (inconsistent updates) and I can now change the name so that it updates. However, instead of updating the appointment record with the counsellor ID of the new name it updates the counsellor table by changing the name on the original ID instead. This makes sense that it updates the current counsellor record but it's not what I want so I'm not sure how to correct this. I think in the After Update event I could reset the name on the counsellor table back to the original then update the appointment table with the new counsellor ID but it doesn't seem very efficient. Or, if I use the Before Update event I want to stop the update of the counsellor table and just change the counsellor ID on the appointment table but I don't know how to do that. Any ideas?

 

[GPGeorge]

Thanks Pat, when I double checked the RecordSource on the form I noticed that the RecordType was Dynaset so I changed it to Dynaset (inconsistent updates) and I can now change the name so that it updates. However, instead of updating the appointment record with the counsellor ID of the new name it updates the counsellor table by changing the name on the original ID instead. This makes sense that it updates the current counsellor record but it's not what I want so I'm not sure how to correct this. I think in the After Update event I could reset the name on the counsellor table back to the original then update the appointment table with the new counsellor ID but it doesn't seem very efficient. Or, if I use the Before Update event I want to stop the update of the counsellor table and just change the counsellor ID on the appointment table but I don't know how to do that. Any ideas?

When we start adding code to a form to compensate for unanticipated and unwanted results, we should stop and ask ourselves if the original form design really is optimal. I call this "code wadding" because the form module begins to build up layers ("wads") of compensatory code to handle this and that anomaly. Step back and examine what is REALLY needed here. You want to change the counselor ID for a given appointment, but the appointment needs to be identified first by the client. That sounds like classic cascading combo boxes to me. Select a Client & appointment in the first combo box, then, in the second, show the currently assigned counselor. Changing the assigned counselor in that second combo box is then straightforward. Not as elegant visually, perhaps, as what you have described, but functional and simple to implement.

 

[mike60smart]

Can you upload the database with no confidential data?

 

[Brother Arnold]

Hi. I would say simplify the query and store the counsellor_ID and simply adjust the combobox to display the name instead.

Not sure that I understand. Are you suggesting that I make the counsellor name combo box unbound but populate it with the name using code then check that whenever it changes I run a SQL script to update the counsellor ID on the appointment to match the name

 

[GPGeorge]

That's the idea, sort of. Not the name, but the Primary Key, which is going to be CounselorID. You actually STORE the Primary Key anyway, not the name. Look up Cascading Combo Boxes. You'll find many examples of the technique.

 

[Brother Arnold]

When we start adding code to a form to compensate for unanticipated and unwanted results, we should stop and ask ourselves if the original form design really is optimal. I call this "code wadding" because the form module begins to build up layers ("wads") of compensatory code to handle this and that anomaly. Step back and examine what is REALLY needed here. You want to change the counselor ID for a given appointment, but the appointment needs to be identified first by the client. That sounds like classic cascading combo boxes to me. Select a Client & appointment in the first combo box, then, in the second, show the currently assigned counselor. Changing the assigned counselor in that second combo box is then straightforward. Not as elegant visually, perhaps, as what you have described, but functional and simple to implement.

I completely agree about the code wadding. I don't know enough about Access yet to code efficiently all the time but I know when it doesn't feel right like my suggestions above don't. However your suggestion to revisit the design has got me thinking. I could have a text box that shows the current name and have it locked and a combo box for the new name. Not as elegant but the best alternative I can come up with if I can't make the combo work.

 

[Brother Arnold]

Can you upload the database with no confidential data?

Unfortunately not but it is a simple query. I have an appointment table with a counsellor Id which links to a counsellor table which holds the counsellor name. In a continuous form I display all the appointments for a client using the above query which brings in the counsellor name. Sometimes the counsellor name changes due to sickness for example. When the name changes I want to update the appointment with the new counsellor ID and it is this update that I don't know how to do efficiently.

 

[Pat Hartman]

@Brother Arnold Changing the type from dynaset to dynaset, inconsistent updates is a red flag. There is something wrong with your query or your join.

Based on what you say is happening, you are not doing this correctly.

Start by looking at the table definition. Are you using table level lookups for the counsellorID. If so, remove the lookup. That will mean that the query shows the ID rather than the name which is what you actually want to avoid confusion.

Now, in your query, normally you would not need to join to the counsellor table. The only reason to do that is because you want to pick up several data fields that go along with the CounsellorID and display them. Doing this is fine EXCEPT that all the controls these fields from the lookup table are bound to MUST be defined as Locked to avoid accidental changes. You absolutely do not want to change counsellor data on the appointment form. This is an accident waiting to happen and it seems to have happened to you. What you want on the appointment form is a combo box bound to the CounsellorID from the appointment table. Then when you change the CounsellorID, you are changing the ID the appointment POINTS to, not anything about the Counsellor. Access automagically updates the ancillary data fields being pulled from the counsellor table by the join, you don't need to do anything to make that happen.

If you use the wizard to add the CounsellorID combo, Access helps you to set it correctly. The query used for the RowSource will normally contain only the CounsellorID and a concatenated value showing the Counsellor's name. either as first & space & last or as last & comma, space & first. Set the bound column to 1, the column count to 2, and the column lengths to 0"; 2". The 0 tells Access to hide the ID. Then the combo will show the first non-hidden field which will be the name.

 

[theDBguy]

Not sure that I understand. Are you suggesting that I make the counsellor name combo box unbound but populate it with the name using code then check that whenever it changes I run a SQL script to update the counsellor ID on the appointment to match the name

No, I'm not suggesting to use an unbound combobox. In your original post, you said this:

If I simplify the query to one table and change the counsellor name to counsellor ID the combo works fine.

So, I was suggesting to simply do that. Then, to show the names on the combobox, I said:

...and simply adjust the combobox to display the name instead.

Which is what Pat just explained how to do above.

 

[Brother Arnold]

@Brother Arnold Changing the type from dynaset to dynaset, inconsistent updates is a red flag. There is something wrong with your query or your join.

Based on what you say is happening, you are not doing this correctly.

Start by looking at the table definition. Are you using table level lookups for the counsellorID. If so, remove the lookup. That will mean that the query shows the ID rather than the name which is what you actually want to avoid confusion.

Now, in your query, normally you would not need to join to the counsellor table. The only reason to do that is because you want to pick up several data fields that go along with the CounsellorID and display them. Doing this is fine EXCEPT that all the controls these fields from the lookup table are bound to MUST be defined as Locked to avoid accidental changes. You absolutely do not want to change counsellor data on the appointment form. This is an accident waiting to happen and it seems to have happened to you. What you want on the appointment form is a combo box bound to the CounsellorID from the appointment table. Then when you change the CounsellorID, you are changing the ID the appointment POINTS to, not anything about the Counsellor. Access automagically updates the ancillary data fields being pulled from the counsellor table by the join, you don't need to do anything to make that happen.

If you use the wizard to add the CounsellorID combo, Access helps you to set it correctly. The query used for the RowSource will normally contain only the CounsellorID and a concatenated value showing the Counsellor's name. either as first & space & last or as last & comma, space & first. Set the bound column to 1, the column count to 2, and the column lengths to 0"; 2". The 0 tells Access to hide the ID. Then the combo will show the first non-hidden field which will be the name.

No lookups. I have a 1 to 1 relationship on counsellorID between the two tables. I've got the combo restricted so you can only select from the list. It is also set up with two columns - name and ID. However the query was wrong as you suggested, I only needed a simple query for the appointment data, a change back to Dynaset and then bind the combo box to the appointment.counsellorID. It worked perfectly thanks.

 

[Brother Arnold]

No, I'm not suggesting to use an unbound combobox. In your original post, you said this:

So, I was suggesting to simply do that. Then, to show the names on the combobox, I said:

Which is what Pat just explained how to do above.

Now I realise you were spot on but I'm still learning so it took me a little while to get there. Cheers

 

[mike60smart]

I would query a 1 to 1 relationship, these are rarely used.

 

[Pat Hartman]

Why do you think you need a 1-1 relationship? Seems to me that the relationship is 1-m. Are you sure that each counsellor can only be associated with one appointment? You're going to run through your counsellors at an alarming rate

 

[theDBguy]

Now I realise you were spot on but I'm still learning so it took me a little while to get there. Cheers

Glad to hear you got it sorted out. Good luck with your project.

 

[Brother Arnold]

Why do you think you need a 1-1 relationship? Seems to me that the relationship is 1-m. Are you sure that each counsellor can only be associated with one appointment? You're going to run through your counsellors at an alarming rate

Sorry you're both right , over tired, I meant I'm not keeping a history of any change of counsellor attached to the appointment