Solved

MS Access - Lookup Record by Result from Query

Posted on 2009-06-28
18
732 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
 
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
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…

747 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now