Option/Checkbox Issue (1 Viewer)

Sazed

New member
Local time
Today, 15:27
Joined
Jul 19, 2020
Messages
18
Issue: Uncheck previously checked record automatically as when the latest record is Checked.

Scenario:
The Table has a Column (Field) namely "Serial_Number" (text data type) while another field name is "Stock" (Checkbox type). The Serial_Number" field is not unique data type and can be duplicate as we issue the same serial number and get return to/from users. The Serial numbers are of different instruments that we issue and receive to/from the users. Once we issue any instrument we type the serial number of the instrument then user name and then press the "Stock" field (Checkbox). Pressing the Stock field is to mean that the current user/warehouse is having this stock. So, the previously checked Stock field should be unchecked. Because the instrument can only be with one user/warehouse at a time.

Requirement: Since the same Serial Number is inputted many times as we issue it to user and the user returns to us some other time or handover to other user as it requires. So, the checkbox "Stock" should only be checked as being with the latest user/warehouse and the old checked position should be automatically unchecked when the latest issue case happens and the Stock field is checked.

Request: Please help it to resolve to automatically uncheck the previously checked Stock field as when the latest record is checked.

Illustration:
1595157760714.png
 

Gasman

Enthusiastic Amateur
Local time
Today, 12:27
Joined
Sep 21, 2011
Messages
14,051
Use an Update query to set the Stock field to False for that Serial_No and ID < current record ID ?
 

Sazed

New member
Local time
Today, 15:27
Joined
Jul 19, 2020
Messages
18
Use an Update query to set the Stock field to False for that Serial_No and ID < current record ID ?
1. There are several Serial Numbers and each serial number has more then one record. The objective is if the Checkbox of current record is Checked then if there is any previously checked record of the same serial number then that will be unchecked.
2. what would be the coding please?

Thank you...
 

isladogs

MVP / VIP
Local time
Today, 12:27
Joined
Jan 14, 2017
Messages
18,186
@Sazed
I have now deleted the other three duplicate threads you started including the one to the code repository which is a moderated area.
As already explained by @Gasman, creating duplicate threads is not helpful and will not help your chances of getting answers.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 12:27
Joined
Feb 19, 2013
Messages
16,555
you shouldn't need to store the check value, just calculate when required

use a calculation in your stock control controlsource something like

=[Transaction_Date]=dMax("Transaction_Date","myTable","[Serial_No] ='" & me.serial_No & "'")

which will return a boolean true or false

Or you can put it in your query
 

Sazed

New member
Local time
Today, 15:27
Joined
Jul 19, 2020
Messages
18
you shouldn't need to store the check value, just calculate when required

use a calculation in your stock control controlsource something like

=[Transaction_Date]=dMax("Transaction_Date","myTable","[Serial_No] ='" & me.serial_No & "'")

which will return a boolean true or false

Or you can put it in your query
The Max and also the Last function tried already. They do not return the correct result always. So I decided to add the 'Stock' field with data type Checkbox to manually check it for identifying the user who has the stock as received the stock lastly. What I want is like this -for the same Serial Number whenever any record (stock field) will be checked manually then with this action all the previously checked record (Stock field) of the same Serial number should be unchecked automatically.
I hope you have got the point.
Appreciate your help.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 12:27
Joined
Feb 19, 2013
Messages
16,555
T
hey do not return the correct result always
DLast won't work, DMax should if it doesn't then there is something in your data you are not saying. One possibility is you are showing date, but not time and your example all have separate dates - if you have two issues on the same day, you need to include a time element
 

June7

AWF VIP
Local time
Today, 04:27
Joined
Mar 9, 2014
Messages
5,425
How could Max() or DMax() not return latest date for a given serial number? Would item have multiple records for same date?

Not sure I would have IssuedTo and ReceivedFrom in same record. I would probably have IssueDate and ReturnDate. If item has a Record where ReturnDate is Null, indicates item is in use. Might look at MS Lending Library database template.
 

Sazed

New member
Local time
Today, 15:27
Joined
Jul 19, 2020
Messages
18
T
DLast won't work, DMax should if it doesn't then there is something in your data you are not saying. One possibility is you are showing date, but not time and your example all have separate dates - if you have two issues on the same day, you need to include a time element
I understand what you are saying. But can we get the solution as I want?
 

June7

AWF VIP
Local time
Today, 04:27
Joined
Mar 9, 2014
Messages
5,425
A solution was proposed in post #2. Did you try it? In VBA like:

CurrentDb.Execute "UPDATE table SET Stock = False WHERE Serial_No='" & Me.tbxSN & "' AND ID<" & Me.tbxID
 

Sazed

New member
Local time
Today, 15:27
Joined
Jul 19, 2020
Messages
18
Please give me the
Use an Update query to set the Stock field to False for that Serial_No and ID < current record ID ?
Please help me with coding for this update query.
 

Sazed

New member
Local time
Today, 15:27
Joined
Jul 19, 2020
Messages
18
I will try this one and let you know tomorrow.
Thank you.
 

Sazed

New member
Local time
Today, 15:27
Joined
Jul 19, 2020
Messages
18
A solution was proposed in post #2. Did you try it? In VBA like:

CurrentDb.Execute "UPDATE table SET Stock = False WHERE Serial_No='" & Me.tbxSN & "' AND ID<" & Me.tbxID
I will this one and let you know tomorrow.
Thank you.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 12:27
Joined
Feb 19, 2013
Messages
16,555
a potential problem with that code is if records are not entered in the right order

I understand what you are saying. But can we get the solution as I want?
You are trying to apply excel type methodology to your problem, not a database one, so for me, it is not a way I would want to go. Good luck with your project
 

Sazed

New member
Local time
Today, 15:27
Joined
Jul 19, 2020
Messages
18
a potential problem with that code is if records are not entered in the right order
Which field record you mean if not entered correctly? The Stock field is a Checkbox which has only choice to click. Please give me an example of what you said "if the record is not entered in the right order".
 

CJ_London

Super Moderator
Staff member
Local time
Today, 12:27
Joined
Feb 19, 2013
Messages
16,555
your ID's are an autonumber which ascends so if by mistake you entered details for your 4th record in your example before your third record, then entered the third record - it will be the third record which is checked, not the 4th record. So to correct it, you would have to edit both records to show the data in the 'correct' order. Like I said, not the database way of doing things.
 

June7

AWF VIP
Local time
Today, 04:27
Joined
Mar 9, 2014
Messages
5,425
Do you get a stack of transaction records to enter? What if you enter today's data before yesterday's? Then the latest ID will be for yesterday's records. As we have been saying, saving this calculated value is bad design.

If an item can have only one record for any date then instead of ID<Me.tbxID, maybe: [Transaction_Date]<Me.tbxDate. However, I see you show international date format. Review allenbrowne.com/ser-36.html
 

Sazed

New member
Local time
Today, 15:27
Joined
Jul 19, 2020
Messages
18
your ID's are an autonumber which ascends so if by mistake you entered details for your 4th record in your example before your third record, then entered the third record - it will be the third record which is checked, not the 4th record. So to correct it, you would have to edit both records to show the data in the 'correct' order. Like I said, not the database way of doing things.
You are right. I already have experienced this problem in other project. So to avoid this problem please direct me to the right database way.
 

Sazed

New member
Local time
Today, 15:27
Joined
Jul 19, 2020
Messages
18
Do you get a stack of transaction records to enter? What if you enter today's data before yesterday's? Then the latest ID will be for yesterday's records. As we have been saying, saving this calculated value is bad design.

If an item can have only one record for any date then instead of ID<Me.tbxID, maybe: [Transaction_Date]<Me.tbxDate. However, I see you show international date format. Review allenbrowne.com/ser-36.html
To avoid this problem I added the Checkbox Stock field to manually check it. But I want to uncheck the other records within the same Serial number Automatically by coding.
 

Users who are viewing this thread

Top Bottom