Deselect check box in previous records

[matt704]

I have a form that among other things, requires users to deselect a checkbox if an item is no longer present at a particular location. Fields in the form are auto populated with the most recent data based on a combination of the item and location Once an item at a particular location is no longer present, I would like previous records that had that item at that location to be changed from present to not present (i.e., the checkbox to be deselected in previous records since the item is no longer present). Ideally this would occur on click of the check box within the form.



Below is an example of what the data table currently looks like after deselecting the checkbox(present y/n) for a record on 1/7/16.

date	Item	present	location
1/1/2016	a	yes	1
1/2/2016	a	yes	1
1/3/2016	a	yes	1
1/4/2016	a	yes	1
1/5/2016	a	yes	1
1/6/2016	a	yes	1
1/7/2016	a	no	1

Below is what I want the data table to look like when I deselect the checkbox for a record on 1/7/16.

date	Item	present	location
1/1/2016	a	no	1
1/2/2016	a	no	1
1/3/2016	a	no	1
1/4/2016	a	no	1
1/5/2016	a	no	1
1/6/2016	a	no	1
1/7/2016	a	no	1

[aytee111]

When the user deselects an item, run and update query which updates all the other records for that item.

[matt704]

How do I specify the current item/location in the query? Since the form allows users to select an item/location from a combo box, I need the query to change based on the item/location that is initially selected.

[aytee111]

In VBA, use DoCmd.RunSQL "Update ...... WHERE field=" & Me!Field & ";"

[Micron]

I recommend avoiding vba for constructing queries when a simple actual query can reference your form fields for the names of the fields you want to update or provide criteria for an update. The only caveat is that the form has to be open in form view, but that's not a problem unless you try to test the query but forgot to open the form first. You can limit your vba to running the query on some form or control event. Just a thought...

[matt704]

I can't figure this out. I have Update tbl Set tbl.present =No Where present=" Me!Present & ";"

I know im missing something basic here...

[Micron]

What's not helping is that you're naming your controls the same as your fields.
And your table name is "tbl"? Where are you doing this? You can't put ME! in a query and a textbox control cannot have a sql statement as a control source.
Is the form not bound to an updatable query or a table? Deselecting the check box would automatically alter the value in the table. You might be making this more complicated than it needs to be.

[andy49]

Try set [present]=no etc?


Sent from my iPhone using Tapatalk

[matt704]

Going back to the original question ...

I have a table tblItems that I've created a form from. The control source and names are the same. What should I change the names to?

I can easily deselect the check box on the current form record and that record will be deselected in my table but the previous records with the same item and location are still marked as present in the table. What I want to happen is, if I deselect present on the form, I want to also deselect present from all previous records In the table.

[matt704]

I should have added " with the same item and location" to the end of that last sentence.

[Micron]

Wizards name controls the same as the fields. That's ok at first, but I always rename them using a naming convention, kind of like this
https://access-programmers.co.uk/for...d.php?t=225837
however, that is likely not your issue here - it can be an issue when Access cannot decipher between a field and a control when the name is used in certain circumstances. It sometimes clouds the posted issue as it can create ambiguity.

Upon reviewing this thread, I see I forgot your original intent, which was correctly answered in post #2. However, you haven't provided enough of the code or answered questions posed in post #7 (the name of your table, where you're trying to use the expression).
Post the entire piece of relevant code,
-where you have placed it, and
-ensure we can decipher the name of the table and the field(s) you want to update
-and the names of the controls on the form that will supply the criteria (and their data type if it isn't obvious to us)
-and the name of the form. Depending on where you're using this, the form name may be irrelevant but include it anyway.

[matt704]

table name- tblAllCompiledLocations

I would like to use the expression on the On Click Event of a checkbox. The default for the checkbox is yes.

Field names are EventDate, Item, Present, and Location. There are more fields in the form, but these fields are all that are needed to refer to previous records (i.e., knowing the item and location would allow you to determine if the item was present in previous dates at the location).

Control names are: txtEventDate (text box with General Date datatype), txtItem (text box with General Number datatype), chbxPresent (check box), and cmbLocation (combo box with Short Text datatype).

Form Name: frmDataUpdate

The current code associated with the checkbox is below. It is used to automatically assign a date to the record specifying when the "Item" was removed from the "location". Users first select a "location" from the combo box, an "item" number is then populated into the "item" field and then if the item was no longer at the "location", they would deselect the "present" check box which would then run the code below to automatically assign a date(RemoveDate) to another field in the table(tblAllCompiledLocations).

Private Sub chbxPresent__Click()
If [Present].Value = True Then
[RemoveDate].Value = ""

ElseIf [Present].Value = False Then
[RemoveDate].Value = Date
End If
End Sub

Let me know if you need more information.

[matt704]

I forgot to mention, there is also a date field associated with the date of the new record. That field is what I would need to reference in previous records to deselect all dates that had the particular item present at the particular location. That date field is VisitDate.

[Micron]

On click is the wrong event for this. Since you're changing data in a control and you want to affect records based on the new value, this must be done in the AfterUpdate event. In your OP, Location looks like a number, but you're saying it's short text, so I went that way. I don't understand whay you mean by 'deselect all dates'. You cannot 'deselect' in a table so I don't get the significance of your last post regarding the date field. Your OP stated that you wanted to update the present field to No wherever item A is no longer in location 1, so I coded for that, but based on the contents of your form controls and not simply A or 1. Put a break point on the DoCmd.RunSql line after pasting in the code and saving and have the form open with a record that you can deselect the checkbox for. I have written this to output the sql into the immediate window so you can grab it, dump it into a new query in sql view (you may have to add the ending semi colon in that view; it is not needed in vba) and choose datasheet view. If the query doesn't balk it should show you how many records are going to be updated (but not the actual update values). If it does balk, hopefully it will highlight the offending portion. If it's a data type mismatch, then we're probably not on the same page regarding data types. BACKUP YOUR TABLE(S) FIRST.
This is 'air' code and obviously not tested since I don't have a copy of your db.

Private Sub chbxPresent_AfterUpdate()
Dim sql as String

sql = "Update tblAllCompiledLocations SET Present = No WHERE Location = '" & Me.cmbLocation & "' AND  Item = " & Me.txtItem
If Me.chbxPresent = 0 Then
Debug.Print sql 'disable this line when everthing is OK
DoCmd.RunSql sql
End If

End Sub

[matt704]

Thanks a lot Micron. This is really close but there are some issues. Seems to be in this part of the query (" & Me.txtItem). I'm honestly do not know the difference between '", "', ", and ' but maybe this has something to do with it? I can get the sql query to appear in datasheet view IF I change the " before & Me.txtItem to "", but that isn't really what I want (the sql code below is what access converts your query to after I make the change from " to ""). Any suggestions?

UPDATE tblAllCompiledLocations SET tblAllCompiledLocations.Present = No
WHERE (((tblAllCompiledLocations.Location)='" & Me.cmbLocation & "') AND ((tblAllCompiledLocations.Item)="& [Me].[txtItem]"));