Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 759
  • Last Modified:

MS Access - Lookup Record by Result from Query

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
judas2158
Asked:
judas2158
  • 8
  • 7
  • 3
1 Solution
 
thenelsonCommented:
To jump to the last record in the form but still be able to scroll through all the other records:

Me.RecordSet.MoveLast
0
 
Jeffrey CoachmanCommented:
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
 
thenelsonCommented:
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Jeffrey CoachmanCommented:
LOL!

;-)

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

Jeff
0
 
judas2158Author Commented:
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
 
Jeffrey CoachmanCommented:
I'll leave this to thenelson, but try something like this:
Me.RecordSet.FindFirst "[Receipt Number] = " & RecVar & " AND Renter=" & RenterID
0
 
thenelsonCommented:
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
 
judas2158Author Commented:
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
 
thenelsonCommented:
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
 
judas2158Author Commented:
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
 
thenelsonCommented:
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
 
judas2158Author Commented:
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
 
thenelsonCommented:
So You want the last receipt number.  How do you determine which one is the last one?
0
 
judas2158Author Commented:
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
 
thenelsonCommented:
So you would want something like:
Me.Recordset.FindLast "renter = " & RenterTextBoxName & "And [Renter Space Assignments_Space Number] = '" & [Payment Receipts_Space Number] & "'"
0
 
judas2158Author Commented:
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
 
thenelsonCommented:
Did you get it to work?
0
 
judas2158Author Commented:
Not the way I wanted it to.  :)
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 8
  • 7
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now