I'm working on a renter/receipt database. There are two tables, one with renter space numbers, and another with receipt numbers. Any single renter has many receipts. What I want to do is select a particular space number in the form, and have that form go to the last receipt record for that renter.
I created a query which correctly returns the last receipt number; however I can not find a way to select the record for that receipt. I tried turning my SQL query into a Dlookup, but I keep running into errors, usually it says it can't find the field referred to in my expression.
I think the problem is that I don't know how to lookup the table value correctly.
(SELECT TOP 1 [Payment Receipts].[Receipt Number] FROM [Payment Receipts] WHERE [Renter Space Assignments].[Space Number] = Forms![Rental Property Management]![Payment Receipts_Space Number] ORDER BY [Payment Receipts].[Receipt Number] DESC)
Private Sub Payment_Receipts_Space_Number_AfterUpdate()
Dim RecVar As Variant
Dim MatchVar As Variant
Dim TableVar As Variant
MatchVar = Me![Payment Receipts_Space Number]
TableVar = [Renter Space Assignments].[Space Number]
RecVar = DLookup("[Receipt Number]", "Payment Receipts", "'" & TableVar & " = " & "'" & MatchVar & "'")
DoCmd.FindRecord (RecVar), acAnywhere, False, acSearchAll, False, acCurrent, True