Please help: mark True/False if value of the same record has been mentioned in other table (1 Viewer)

[martin_vn]

* I have 02 tables: [tblLotSys] and [tblLocationList]

[tblLotSys] has 03 fields: [LotSerialNo] ; [ReceiptDate] ; [Location]
[tblLocationList] has 02 fields: [Location] ; [VacancyStatus]

the [tblLotSys]![Location] looks up value from the [tblLocationList]![Location]

* Here is the actual data:

[tblLotSys]
[LotSerialNo] ; [ReceiptDate] ; [Location]
08C23-001 ; 23/08/2023 ; M-1-1-1
08C23-002 ; 20/07/2023 ; M-1-1-2
08C23-003 ; 15/09/2023 ; M-1-1-4

[tblLocationList]
[Location] ; [VacancyStatus]
M-1-1-1 ; False
M-1-1-2 ; False
M-1-1-3 True
M-1-1-4 ; False

* I need to update each record of the field [tblLocationList]![VacancyStatus] to be "False" for each time the value from the field [tblLocationList]![Location] of that same record has been picked/used by a [tblLotSys]![Location] record.

* For example, if I change the Location of LotSerial 08C23-003 from M-1-1-4 to M-1-1-3 ; how could I automatically update the VacancyStatus of M-1-1-4 back to True, while M-1-1-3 to False?

^^ I have tried setting the field [tblLocationList]![VacancyStatus] to be a calculated field, then I tried to use the dlookup/dcount function inside the Expression Builder to count if there is any value in the [tblLotSys]![Location] matching with [tblLocationList]![Location]. It has not been going anywhere. I don't know how to build the syntax. Could anyone please help?

 

[theDBguy]

Hi. Welcome to AWF!

First of all, I would recommend adding a primary key to all your table.

Next, I would say you probably don't need the vacancy status column, because you can always determine the status from the main table using a query.

 

[RogerCooper]

It looks that you are trying to track inventory in some fashion. Lots are being received, moved and I assume shipped. You need to have a table of inventory transactions. Once you are tracking that information, you can determine everything else with a query.

 

[martin_vn]

I'm trying to create a drop down box like this, so that I can assign 1 (unique) location for each lot.
But I would like the location value to disappear from the drop down box once it has been assigned for any Lot. As you can see in the photo, the location "M-1-1-10" is still on the drop down list although it has been assigned for the previous Lot.

My idea is to automatically update the vacancy status of the location inside its own table, then from the Look up field setting of the Lot table, I could query only those locations that are still vacant.

But since I cannot find a way to automatically update the vacancy status each time I have chosen that location for a Lot, I cannot do it.

Could you guys please explain in more detail?

 

[martin_vn]

UPDATING: I have successfully created a query to automatically update the VacancyStatus of each Location:

But still I cannot exclude the Location from the drop down box when it has been assigned to a previous Lot.
Anyone can show me where am I wrong?

 

[arnelgp]

you can do that using a Continuous Form, but a little trickier.

 

[XPS35]

As said before, there is no need for a vacancy field. You can determine whether a location is already in use from the recorded data. The field is therefore redundant and you run the risk of inconsistent information arising.
To make sure only vacant locations are shown in the combobox, set the rowsource of the combobox to something like:

SELECT Location.Location
FROM Location LEFT JOIN Lot ON Location.Location = Lot.Location
WHERE Lot.Location Is Null;

After selecting a location, you need to refresh the combobox to make sure the next time it will show the right values. So make an after update event on the combobox like:

Private Sub Location_AfterUpdate()
    Me.Refresh
    Me.Location.Requery
End Sub

I have attached a highly simplified example.

 

[martin_vn]

As said before, there is no need for a vacancy field. You can determine whether a location is already in use from the recorded data. The field is therefore redundant and you run the risk of inconsistent information arising.
To make sure only vacant locations are shown in the combobox, set the rowsource of the combobox to something like:

SELECT Location.Location
FROM Location LEFT JOIN Lot ON Location.Location = Lot.Location
WHERE Lot.Location Is Null;

After selecting a location, you need to refresh the combobox to make sure the next time it will show the right values. So make an after update event on the combobox like:

Private Sub Location_AfterUpdate()
    Me.Refresh
    Me.Location.Requery
End Sub

I have attached a highly simplified example.

Thank you so much, I'll try your instruction.
Could I ask where could I learn about these code? Is there any course that you would recommend?
I've learned through the basics of Access on Udemy, like how to create table, query, form, report, and just an introduction to VBAs. But I know I need more in-depth knowledge. Where could I learn about it?

 

[Pat Hartman]

Access is a relational database. Any course that teaches you how to design a relational database applies to Access. NOT the SQL mind you but the concepts apply to all RDBMS - Oracle, DB2, Sybase, SQL Server, Jet/ACE, etc.

 

[martin_vn]

Access is a relational database. Any course that teaches you how to design a relational database applies to Access. NOT the SQL mind you but the concepts apply to all RDBMS - Oracle, DB2, Sybase, SQL Server, Jet/ACE, etc.

okay, I'll try that. thank you!!!