Solved

MS Access - Lookup Record by Result from Query

Posted on 2009-06-28
18
746 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
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 …

751 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