• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 221
  • Last Modified:

Combo box locked

Was...
My form includes a Combo Box. The source data for the form is a table. I can use the Combo box to change values in the underlying table.

Now...

I changed the form's data source to a Query, and the Combo box will no longer write back to the table. The query links the original data table with another query, thereby adding further info for display in the form.

What have I missed here, please? How do I get my Combo box back to the way it was?

Vaughan
0
VaughanM
Asked:
VaughanM
  • 3
  • 2
1 Solution
 
peter57rCommented:
Hi VaughanM,

Looks like your changes have turned the form's recordsource into a non-updateable query.

Run the recordsource query on its own and see if you can change the value. ( I suspect not).

You will need to revise your form's recordsource to get it back to being updateable, but you will need to post the SQL of the recordsource (including the sql of the  linked query) to get help on that.


Pete
0
 
harfangCommented:
Hello VaughanM

On the other hand, if you *can* edit all other controls on the form, the problem might be one of conflicting names. For instance, if the combo was editing the field EmployeeID, and if you have added the table tblEmployee as second table for your main form, you could very well have now two different fields called EmployeeID (each prefixed with the table name).

Simply select the combo and open the dropdown of the "Control Source" property. Is the field still available? If it is, but prefixed with the table name, we should correct your query.

Good luck!
(°v°)
0
 
VaughanMAuthor Commented:
Hi Pete,

Correct - I can't change the value in the recordsource.

So here is the sql of the recordsource

SELECT tblBasic.[Customer ID], qrySessionsCount.No_of_Sessions, tblBasic.[Canvassed By], tblBasic.Title, tblBasic.[First Name], tblBasic.Surname, tblBasic.Status, tblBasic.[CallBack Date], tblBasic.[Area Code], tblBasic.[Tel No], tblBasic.[Mobile no], tblBasic.Prize, tblBasic.[Address 1], tblBasic.[Address 2], tblBasic.[Address 3], tblBasic.County, tblBasic.[Post Code], tblBasic.[Data Collected], tblBasic.Notes
FROM tblBasic LEFT JOIN qrySessionsCount ON tblBasic.[Customer ID] = qrySessionsCount.[Customer ID];

and of the linked query

SELECT Count([tblSession Details].[Customer ID]) AS No_of_Sessions, [tblSession Details].[Customer ID]
FROM [tblSession Details]
GROUP BY [tblSession Details].[Customer ID]
ORDER BY Count([tblSession Details].[Customer ID]) DESC , [tblSession Details].[Customer ID];

Best wishes :-)

Vaughan
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
peter57rCommented:
A recordsource which involves a group-by query can never be updatebale. (Even if the bits you want to update are not part of the group-by source).

In this case it looks like the easiest solution is tto remove the group-by query from the source and use an textbox with a DLookup() as its calculated source.
= Dlookup("No_of_Sessions","qrySessionsCount.No_of_Sessions","[Customer id] = " & me.txtCustomerid)

where txtCustomerid is the name of the control on the form containing the id.  
This also assumes that the id is a number.

Pete
0
 
VaughanMAuthor Commented:
Hi Pete,

Thanks very much for exactly the right solution. It works just fine.

Vaughan
0
 
peter57rCommented:
Fine

Thanks

Pete
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now