Solved

MS Access - Lookup Record by Result from Query

Posted on 2009-06-28
18
737 Views
Last Modified: 2013-11-28
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 & "'")
Me![Receipt Number].SetFocus
DoCmd.FindRecord (RecVar), acAnywhere, False, acSearchAll, False, acCurrent, True
 
End Sub

Open in new window

0
Comment
Question by:judas2158
  • 8
  • 7
  • 3
18 Comments
 
LVL 39

Expert Comment

by:thenelson
ID: 24733130
To jump to the last record in the form but still be able to scroll through all the other records:

Me.RecordSet.MoveLast
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24736164
You can use the generic MS code to find the record on the form as well.

So I am guessing that your code would need to look something like this:

    ' Find the record that matches the control.
    Dim rs As Object
    Set rs = Me.Recordset.Clone
    rs.FindFirst "[Receipt Number] = " & RecVar
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark

In any event, I am sure you get the idea.

JeffCoachman

0
 
LVL 39

Expert Comment

by:thenelson
ID: 24736297
Or to use Jeff's idea in one line, use the form's recordset directly instead of cloning the recordset:

Me.RecordSet.FindFirst "[Receipt Number] = " & RecVar
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24736673
LOL!

;-)

Yeah, I just copied that straight from the Wizard generic code.

Jeff
0
 
LVL 1

Author Comment

by:judas2158
ID: 24738302
I think there's a misunderstanding here.  The receipt records available in the form are for all the renters, not just the selected renter.  I need to find the last receipt record for the selected renter out of all the receipt records.  For example, the receipt numbers may be from 1 to 1600, but the last receipt written to renter #2 was 1512; so I need to select 1512, because 1600 is actually for a different renter.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24738463
I'll leave this to thenelson, but try something like this:
Me.RecordSet.FindFirst "[Receipt Number] = " & RecVar & " AND Renter=" & RenterID
0
 
LVL 39

Expert Comment

by:thenelson
ID: 24739745
Me.RecordSet.FindLast "Renter=" & RenterID
Of course change "Renter=" & RenterID to the name of your renter control and field.

This will find the last matching record in the recordset (the form's record source). If you want the last matching record chronologically, place a sort on the date field.   If you want the last matching record based on record id, place a sort on the record id field.
0
 
LVL 1

Author Comment

by:judas2158
ID: 24741995
Well, that was interesting.  I tried it.  It seems to be going to the last record where the statement is true, which happens to be the last receipt for the last renter.  There are 18 renters and 1532 receipts, and so it returns receipt 1532 for renter 18, which is the correct receipt for the renter.  However, if I pick renter #1, it jumps to renter 18.
Me.Recordset.FindLast "[Renter Space Assignments_Space Number] = [Payment Receipts_Space Number]"

Open in new window

0
 
LVL 39

Expert Comment

by:thenelson
ID: 24742517
I assume [Payment Receipts_Space Number] is a control in your form. If so then the line should be:
Me.Recordset.FindLast "[Renter Space Assignments_Space Number] = " & [Payment Receipts_Space Number]

BTW: It's best to not use spaces or underscores and other special characters in your naming of objects. Instead of:
[Payment Receipts_Space Number]
Use:
PaymentReceiptsSpaceNumber
then the brackets would not the needed.

An extreme example of the problems you will have using special characters in your names:
I named a textbox
!@#$%^&*()_+= -{}:;"'<,>?/|~
and had Access create an event procedure.  Access converted the name of the textbox to:
Ctl_____________________________
so if the form had the name:
~!@#$%^&*()_+= -{}:;"'<,>?/|~
referencing the textbox would be:
Forms!l_____________________________!Ctl_____________________________
A useful tip for someone who doesn't want someone else (probably even themselves) from reading their code.
And spaces will sometimes cause problems in VBA references even after years of trouble free operation.

References:
http://www.xoc.net/standards/rvbanc.asp
http://www.dhdurso.org/articles/ms-access-naming.html
http://www.acc-technology.com/namconv.htm
http://www.databasedev.co.uk/naming_conv.html
http://en.wikipedia.org/wiki/Leszynski_naming_convention
0
 
LVL 1

Author Comment

by:judas2158
ID: 24751342
When I do that exactly, I get a "run time error 3464: data type mismatch in criteria expression".  Any idea?
Me.Recordset.FindLast "[Renter Space Assignments_Space Number] = " & [Payment Receipts_Space Number]

Open in new window

0
 
LVL 39

Accepted Solution

by:
thenelson earned 165 total points
ID: 24752485
What type of data is [Payment Receipts_Space Number]? If text the the line should be:
Me.Recordset.FindLast "[Renter Space Assignments_Space Number] = '" & [Payment Receipts_Space Number] & "'"
0
 
LVL 1

Author Comment

by:judas2158
ID: 24786388
Getting closer.  It works going in one direction.  That is, when I select the next renter, if the receipt number is higher than the one I just looked at, then it works.  When I try to go in the other direction, it just stays at the same number.
0
 
LVL 39

Expert Comment

by:thenelson
ID: 24788848
So You want the last receipt number.  How do you determine which one is the last one?
0
 
LVL 1

Author Comment

by:judas2158
ID: 24789017
The last receipt is the highest number for any given renter.  For example,

Renter #1 has receipt numbers 30, 32, and 34.
Renter #2 has receipt numbers 31, 33, and 35.

When I select renter #1, I should get receipt 34, and when I select renter #2, I should get receipt 35 [which it does thus far].  When I select renter #1 again, the receipt should go back to 34 [which it's not doing].  :)
0
 
LVL 39

Expert Comment

by:thenelson
ID: 24799737
So you would want something like:
Me.Recordset.FindLast "renter = " & RenterTextBoxName & "And [Renter Space Assignments_Space Number] = '" & [Payment Receipts_Space Number] & "'"
0
 
LVL 1

Author Comment

by:judas2158
ID: 24799845
Something like that.  I think the problem is the form's data source is an SQL query based on two tables, and the tables only have one piece of relationtional information in common, which is the space number.  This recordset.findlast method seems to almost work; but according to what I've read, it can't really work with two conditions, only one.

Eh, perhaps it does work and I'm just implementing it wrong.  I'll pick the closest answer.  :)
0
 
LVL 39

Expert Comment

by:thenelson
ID: 24815596
Did you get it to work?
0
 
LVL 1

Author Comment

by:judas2158
ID: 24816185
Not the way I wanted it to.  :)
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Documents and settings folder 30 76
Count with a subquery showing details 10 42
Calculation in Access 5 25
IIF help, YN field 7 21
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

786 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question