Non updateable check box

[GK in the UK]

Related to my last question https://www.access-programmers.co.uk/forums/showthread.php?t=309030
I have a SQL query which has an IIF which sets a form checkbox according to whether a record exists in a table. Like this:


IIf(([tblTransLines].[TransLineID]=[tblSelectLines].[SelectLinesID]),True,False) AS Selected


The query was built in design view and the SQL copied into the RecordSource in the form code - largely because I need to specify a parameter and I haven't yet found a way to get the parameter into the query but that's a separate issue.
I put some records in to tblSelectLines, PK same as the records that I want to see checked, and it's all working.
The checkbox is displayed correctly but it's not updateable.
I put a second checkbox on the form and set the datasource to: =[Selected]. It still won't update (duh)
I removed the datasource completely and now I can click it and AfterUpdate fires (no use at all of course)

This is what's meant to happen:
When the check box is checked, it inserts a single record into tblSelectLines.
When the check box is unchecked, it deletes that same single record.

Is there a workaround for this ?

 

[Deleted member 147267]

Was this checkbox part of the original query (thus it was/is bound) and were you able to update it in that query? Do both tables have an index?
What you posted results in True or False and assigns a Boolean value to an Alias- not sure how that is supposed to do some kind of update. Maybe it would be obvious if the related part was shown too.

 

[GK in the UK]

The query which was built in the query designer, isn't updateable (just tested it)
Both tables are indexed. tblSelectLines has only one column, the PK, and it's indexed. It has 1-1 relationship with tblTransLines.
The insertion/deletion of records into tblSelectLines wouldn't happen by the query, I would code it in VBA in the AfterUpdate event of the check box, if I could update it.

 

[theDBguy]

Hi. Bottom line is you cannot update a "calculated" column. So, you could add a separate control (checkbox) and use code to perform the actual update. Or, perhaps, just add a bound checkbox to the Yes/No field, that should be updatable.

 

[GK in the UK]

The check box is in a datasheet (so a checkbox for every record shown). I could put a control on the parent form which would do what I want for the highlighted sub form record but it's not really what I want. I tried the second checkbox bound to the yes/no but unfortunately that won't update either.


Think I may have to go with the parent form control method. Thank you.

 

[Pat Hartman]

If the selected value is different for each row, how would putting it in the parent record solve the problem?

If you want the calculated chekbox to update, change the value of one of the two fields in the calculation.

 

[GK in the UK]

Not sure if I can do what I want. The check box is initially set by the SQL according to whether a record exists in a table. It doesn't exist as a field in either table. So there's nothing to update.
Once the check box has displayed, I wanted to be able to click it. Then, if it's checked, insert a record in a table, and requery the recordset which would re-set the check box.
Putting a command button on the form would work I think because I could do the insert as part of the button code (for the highlighted line) and requery but it's not as intuitive as just clicking the check box.

Don't know if you followed my other thread but the point is to create a list of records (just the ID numbers) which have been selected arbitrarily by the user, preferably by showing a check box against each record in a datasheet.
I don't want to write a flag to the record that is 'selected', it won't work.

As DbGuy suggested I was hoping another checkbox with the control source set to the query select would work but that can't be clicked either.

I'm thinking maybe go to a continuous form might enable it to be done.

 

[theDBguy]

Putting a command button on the form would work I think because I could do the insert as part of the button code (for the highlighted line) and requery but it's not as intuitive as just clicking the check box.

Hi. Could you put a transparent button on top of the check box? Just a thought...

 

[Deleted member 147267]

I've re-read and re-read and have decided that the checkbox is unbound. Then no, you cannot do what you want - not in a datasheet, not in a continuous form as checking one will check all. But I'm not 100% certain that's your situation because nowhere do I see that you have indicated you have experienced that behaviour and you answered the question "is it in the original query" by telling me it's in the datasheet. That doesn't answer my question. If it's not and you don't want it to be and even if it was in the query it would be calculated anyway, then you need a work around or you need to have the checkbox as a table AND query field.
A work around would be a table of temporary records where the check is a field; you do whatever after making the selection, then update or delete the original record based on the ID value of the temp record, which is the PK value from the main table.

Aside from that, it seems no one has asked what you're really doing and why. The whole exercise might be pointless and unnecessary.

 

[GK in the UK]

Thanks for your further input.

Here's the query which was generated in the query builder and tweaked, but it's now in the the VBA code for the parent form:

strSQL = _
    "SELECT tblTransLines.TransLineID, tblTransLines.tlTransHeaderFK, tblTransLines.tlNominalFK, tblNominalAccounts.nominalName, tblNominalAccounts.nominalDescription, tblTransLines.tlDescription1, tblTransLines.tlDescription2, tblTransLines.tlSalePrice, IIf(([tblTransLines].[TransLineID]=[tblSelectLines].[SelectLinesID]),True,False) AS Selected " & vbCrLf & _
    "FROM tblTransHeaders INNER JOIN (tblNominalAccounts INNER JOIN (tblTransLines LEFT JOIN tblSelectLines ON tblTransLines.TransLineID = tblSelectLines.[SelectLinesID]) ON tblNominalAccounts.NominalID = tblTransLines.tlNominalFK) ON tblTransHeaders.TransHeaderID = tblTransLines.tlTransHeaderFK " & vbCrLf & _
    "GROUP BY tblTransLines.TransLineID, tblTransLines.tlTransHeaderFK, tblTransLines.tlNominalFK, tblNominalAccounts.nominalName, tblNominalAccounts.nominalDescription, tblTransLines.tlDescription1, tblTransLines.tlDescription2, tblTransLines.tlSalePrice, IIf(([tblTransLines].[TransLineID]=[tblSelectLines].[SelectLinesID]),True,False), tblSelectLines.SelectLinesID, tblTransLines.tlCtrlPosting " & vbCrLf & _
    "HAVING (((tblTransLines.tlTransHeaderFK)=" & Me.TransHeaderID & ") AND ((tblTransLines.tlCtrlPosting)=False)) " & vbCrLf & _
    "ORDER BY tblTransLines.TransLineID;"

ctlSourceDsheet.Form.RecordSource = strSQL

The checkbox is in ctlSourceDsheet datasheet and it's called chkSelected. The control source is Selected (from the query above).


Selected returns true if there is a record in tblSelectLines with the same PK as the record in the datasheet. It works.

What am I really doing and why? I thought I had covered that, partly in this thread but in the thread linked in the first post.

I have a be table tblTransLines and I present a sub set of the records in datasheet format. On each line, I want to present a checkbox which the user checks to select some of the records for further processing. I don't want to set a flag in the actual record as each user could be presented with an overlapping subset of records from tblTransLines and may want to make their own independent selections.

So I have a fe table called tblSelectLines. It has a 1-1 relationship with tblTransLines and a single column, the PK. When the user checks a record in the datasheet, if that record has a PK of 123, I insert a record in tblSelectLines with an in-code PK assignment of 123. The user may repeatedly select and unselect a record which will result in repeated deletions and insertions into tblSelectLines of a record with a PK of 123.

"When the user checks a record in the datasheet". Well that's the aim but it can't happen because the check box is not updateable. I know my model otherwise "works" because I put some records into tblSelectLines and the records with matching PK appeared as checked in the datasheet. But I can't click the check box. Clicking the check box was meant to insert or delete records from tblSelectLines but I understand WHY it's not updateable, the check box doesn't exist in either of the two tables.

So: Is there a way to copy the value of chkSelected into an unbound check box in the datasheet? Then I would use the AfterUpdate event of the unbound check box to do the record inserts and deletions, and then re-query the datasheet which would then show the bound checkbox with the correct value.

I did try the unbound checkbox trick and it too wasn't updateable if I set the controlsource to '=Selected', but without it, you are correct it checked/unchecked ALL the records.

Or: Is there some other way to create a list of selected records, independently of each user (or seat) without writing to the record itself?

I did have a look at the FMS site and it is looking like I will need to use a MakeTable query, or an Append query, if I can't do it as I've described. Which seems to be what you're saying.

 

[Pat Hartman]

It doesn't exist as a field in either table. So there's nothing to update.

Correct. That is why you are getting the error. However updating either of the fields being compared will force the compare to be reevaluated and therefore reset the checkbox. If it doesn't make sense to update either of the fields in the compare, then I don't understand what you want to do.

If your process is putting selected IDs in a "selected" table then joining the original table to the "selected" table gives you all the information you need since the join will only return selected records. If you want to select additional records, return to the select process. If you want to de-select items, return to the select process. You cannot do it with this query.

 

[Pat Hartman]

Our posts overlapped. I see you are doing what I thought you might be. There seems to be some confusion between the selection process and using the selected items. I still don't get the problem.

PS - why is your query a totals query? The join should not duplicate data since the relationship is 1-1.

 

[GK in the UK]

Thinking aloud about your work around suggestion:
When the datasheet is presented to the user, insert a new record in tblSelectLines for EVERY datasheet record, and have an additional column called Selected.
Put the actual field Selected in the datasheet and check/uncheck that real field in the record.
Process the records only if the check box is checked.

Might be a better way to do it. I wouldn't have to delete and insert records into tblSelectLines, just set and unset the field.

 

[GK in the UK]

Pat, I guess I need to revist the query or have a rethink. When you say you don't get the problem, you mean, in principle, there should be a way to do this ? Not that you don't get why I want to do it ?

However updating either of the fields being compared will force the compare to be reevaluated and therefore reset the checkbox. If it doesn't make sense to update either of the fields in the compare, then I don't understand what you want to do.

Well, it's the clicking of the check box that is going to update the value on one side of the compare - by inserting or deleting a record in tblSelectLines

 

[Pat Hartman]

Well, it's the clicking of the check box that is going to update the value on one side of the compare - by inserting or deleting a record in tblSelectLines

The issue is becoming clearer. Lets go back to the selection of items and then using the items. I think you are somehow trying to merge the two processes and that isn't going to work easily.

I'll explain a process I have that might give you some ideas. I can't post the database and I can't extract the part you need because there are a lot of moving parts.

I have an application that manages drawings for construction projects. At certain times throughout the build drawings need to be sent various places for review, approval, etc. The form that does this has two multi-select listboxes. The left box is the unselected drawings, the right box is the selected drawings. There are a bunch of filters which can be used to get a smaller set of drawings on the left box. There are arrow keys that show which direction the drawings move. Either left box to right box or right box to left box. The right box is based on a table so selected records are added to the selection table or removed from it which seems to be similar to what you are doing. However, in my case, the selection table is in the BE and used by all. The keys on the selection table restrict the selected items to the transmittal the form is working on so one user doesn't interfere with another. and Two users can select the same drawing and send it to two different places at the same time.

Once the drawings for the transmittal are selected, we move on and create the transmittal and send it where ever it needs to go. This is the piece that seems confused in your process.

Anyway, for what it's worth, here's a picture of the form.

 

[GK in the UK]

It's worth a lot Pat although it's now late so I'll need to come back to it and study it. I've fixed the query (don't know why I had Group By in there) and Micron has seeded an idea so I'm hopeful I can get this working.


I'll report back.

 

[GK in the UK]

OK I have the solution I need.

Thanks to Micron for seeding an idea. I modified my tblSelectLines to include the check box field, so now I have an actual record to save the check box state.
Thanks to Pat for spotting the query totals error (fixed) and for further help. The query is further modified for the table field change.
I can now tick the check box and it sets/unsets the field in tblSelectLines.

This is a really neat solution. It's working with no code and I don't quite understand how. The check box has no code, no event handler for anything. Yet when I click it, a record is inserted into tblSelectLines and the field is checked. How ? There is no Insert or Update in the SQL statement. Access magic I guess.

Thanks again

 

[Deleted member 147267]

when I click it, a record is inserted into tblSelectLines and the field is checked. How ?

By inserting a record I think you mean if you click on the checkbox in the 'new record' row of a form, table or query that it adds a record. That's because making the checkbox control bound to a table field makes it part of the record. As soon as you start to edit this new line, you are creating a record that gets saved as soon as you move off of it. If you uncheck the field it won't remove the record so I don't know if that's important as it's been a while since I suggested anything here and the original issue is a bit fuzzy without reviewing it all. Glad you got something solved though.

 

[Pat Hartman]

Access magic will insert a row when you populate a field in a joined table. However, it will not delete a row so you need to test very carefully. This may not be working the way you think it is.

 

[GK in the UK]

Micron, it inserts a checked record into the selections table immediately when clicked, against an already existing transaction line. Which I wasn't expecting to happen automatically but as Pat has explained it's because the tables are joined. I did put a me.dirty=false in the checkbox click event so it happens instantly. I did wonder if both tables are updated or just the selections table (haven't tested that).

Unchecking the checkbox, immediately clears the flag but leaves the record. Which is fine, I'd more or less decided that behaviour would be ok, I may build in a housekeeping routine to delete the records periodically.

When I run the routine that depends upon the checkbox, I will act only on those records that have a matching PK in the transactions recordset and are checked.