Access - Show Only Recent records on Form

arcaidius

Board Regular
Joined
Dec 4, 2018
Messages
79
Good morning, If you could please help, I'm pretty Good with Excel but I'm a dumbass with access. How to set up the tables and relationships is easy, it makes sense. The macros and functions and codes and all that is completely alien compared to excel though.

I am using a form to update and or add records to a several different tables. Trying to make the form so that when an employee is selected, it only shows the Tool they currently have, and if it is returned or issued, I can update table 3 with the dates, and table 2 with the status. Thought I had it with a query grouping by MAX on the issue date and the EmplyID = the EmplyID on the form, but when I hit the next record button it changed to the next instance of a different tool number issued to the same employee. I only want to see tools currently issued and be able issue new tool or return current ones.

Any help is appreciated.

Relationships Below:

Table 1 Employees (List of all Employees and information) Autonumber primary Key: EmplyID
Table 2 Tools (List of all Tools and whether it is available or issued) Autonumber primary Key: Tool No
Table 3 Tool History (Records of when tool was loaned to which employee) Secondary keys: Tool No and EmplyID
Table 4 Employee History (Hire & Quit Dates) secondary key: EmplyID
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
2,281
Office Version
  1. 365
Platform
  1. Windows
Difficult to give an exact answer without seeing the rest of the table fields but it seems like your query needs to include a status field to show only the tool that has not been returned. If not status, then something else that restricts the records to what you want when the form loads.
 

arcaidius

Board Regular
Joined
Dec 4, 2018
Messages
79
I tried IsNull([return Date]) and IsEmpty([return Date]) in the return Date field in the query and on the subform it only shows empty (new) records.
 

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
2,281
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Again, still don't know what the other fields look like.
Perhaps if using Status, include it in your WHERE clause, as in WHERE Status = "On Loan" AND EmplyID = 2 AND ...

IsEmpty is only for object variables. IsNull is for VBA. In queries, use [Return Date] Is Null
 

arcaidius

Board Regular
Joined
Dec 4, 2018
Messages
79
How about this?

Table 1 Employees
EmplyIIDFirstLast
Primary Key (Autonumber)NameName

Table 2 Stamps
Stamp NoIn StockStatus
Primary Key (Autonumber)Physical status (Broken, Missing, Here)Issued to employee or not

Table 3 Stamp History
IDStamp NoIssue DateReturn DateEmplyID
Primary key (not related)Related to Table 2 Stamp NoDateDateRelated to Table 1 EmplyID

Table 4 Employee History
IDEmployee IDHire DateTermination Date
Primary key (not related)Related to Table 1 EmplyIDDateDate
 

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
2,281
Office Version
  1. 365
Platform
  1. Windows
Hate to ask but could you also post the sql of the query you're currently using (the one that gives you too many records)?
 

arcaidius

Board Regular
Joined
Dec 4, 2018
Messages
79
Micron,

Sorry for the delay getting back to you, I decided to just leave the data on the form as a sub form and we can just edit each record in the sub form.
 
Solution

Forum statistics

Threads
1,147,482
Messages
5,741,407
Members
423,657
Latest member
Medrok2021

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top