Use Variable for DLookup Form name

  • Thread starter Thread starter Ryan Tisserand
  • Start date Start date
R

Ryan Tisserand

Private Sub CPUAccountNumber_BeforeUpdate(Cancel As Integer)
Dim stDocName As String
Dim stLinkCriteria As String
Dim stDocFilter As String
stDocName = ([CurrentDatabase] & "" & "TMHP")
stFilter = ([CurrentDatabase] & "" & "Filters")
If IsNull(DLookup("[TRFAM#]", "C54FILES_TRANS", "[TRFAM#]=" &
[Forms]![C54Filters]![CPUAccountNumber]))

Instead of
(DLookup("[TRFAM#]", "C54FILES_TRANS", "[TRFAM#]=" &
[Forms]![C54Filters]![CPUAccountNumber]))

I would like
(DLookup("[TRFAM#]", "C54FILES_TRANS", "[TRFAM#]=" &
[Forms]!stFilter![CPUAccountNumber]))

Is this possible?
 
I don't understand these lines:
stDocName = ([CurrentDatabase] & "" & "TMHP")
stFilter = ([CurrentDatabase] & "" & "Filters")

What is [CurrentDatabase] ?

Can you explain your objective? It looks like maybe you are trying to
dynamically identify a form from which to get the value.
 
Assuming I'm understanding the question correctly, try

DLookup("[TRFAM#]", "C54FILES_TRANS", "[TRFAM#]=" &
Forms(stFilter)![CPUAccountNumber])

or even

DLookup("[TRFAM#]", "C54FILES_TRANS", "[TRFAM#]=" &
Forms(stFilter).Controls("CPUAccountNumber"))
 
Thank you very much, you solved my problem. Here is the complete code that
funcions just as I want it to.
Private Sub CPUAccountNumber_BeforeUpdate(Cancel As Integer)
Dim stDocName As String
Dim stLinkCriteria As String
Dim stFilter As String
Dim Trans As String
stDocName = ([CurrentDatabase] & "" & "TMHP")
stFilter = ([CurrentDatabase] & "" & "Filters")
Trans = ([CurrentDatabase] & "" & "Files_Trans")
If IsNull(DLookup("[TRFAM#]", (Trans), "[TRFAM#]=" &
Forms(stFilter)![CPUAccountNumber])) Then
MsgBox "You typed an invalid account number, please try again.", vbCritical,
"Invalid Account Number"
DoCmd.Close
DoCmd.OpenForm stFilter
Exit Sub
Else
If Not IsNull(Me.CPUAccountNumber) Then
DoCmd.OpenForm stDocName, , , stLinkCriteria
Else
MsgBox "An account number is required.", vbCritical
End If
End If
End Sub

Douglas J. Steele said:
Assuming I'm understanding the question correctly, try

DLookup("[TRFAM#]", "C54FILES_TRANS", "[TRFAM#]=" &
Forms(stFilter)![CPUAccountNumber])

or even

DLookup("[TRFAM#]", "C54FILES_TRANS", "[TRFAM#]=" &
Forms(stFilter).Controls("CPUAccountNumber"))

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Ryan Tisserand said:
Private Sub CPUAccountNumber_BeforeUpdate(Cancel As Integer)
Dim stDocName As String
Dim stLinkCriteria As String
Dim stDocFilter As String
stDocName = ([CurrentDatabase] & "" & "TMHP")
stFilter = ([CurrentDatabase] & "" & "Filters")
If IsNull(DLookup("[TRFAM#]", "C54FILES_TRANS", "[TRFAM#]=" &
[Forms]![C54Filters]![CPUAccountNumber]))

Instead of
(DLookup("[TRFAM#]", "C54FILES_TRANS", "[TRFAM#]=" &
[Forms]![C54Filters]![CPUAccountNumber]))

I would like
(DLookup("[TRFAM#]", "C54FILES_TRANS", "[TRFAM#]=" &
[Forms]!stFilter![CPUAccountNumber]))

Is this possible?
Click to expand...
Click to expand...
 
Back
Top