Use Variable for DLookup Form name

[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?

 

[Klatuu]

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.

 

[Douglas J. Steele]

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"))

 

[Ryan Tisserand]

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

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!)

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?