Access - Show Only Recent records on Form

[arcaidius]

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

 

[Micron]

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]

Sounds logical, How do I do a IF blank filter? Sorry but I'm a Caveman...

 

[arcaidius]

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]

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]

How about this?

Table 1 Employees

EmplyIID

First

Last

Primary Key (Autonumber)

Name

Name

Table 2 Stamps

Stamp No

In Stock

Status

Primary Key (Autonumber)

Physical status (Broken, Missing, Here)

Issued to employee or not

Table 3 Stamp History

ID

Stamp No

Issue Date

Return Date

EmplyID

Primary key (not related)

Related to Table 2 Stamp No

Date

Date

Related to Table 1 EmplyID

Table 4 Employee History

ID

Employee ID

Hire Date

Termination Date

Primary key (not related)

Related to Table 1 EmplyID

Date

Date

 

[Micron]

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]

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.