DLookup in Access 2003

[blazerklk]

Why won’t this work? There is possibly a better way to do this. I’m a beginner.


Private Sub LandDescription_Click()
On Error GoTo Err_LandDescription_Click

Dim TmpTractFull As String
Dim TmpCounty As String
Dim TmpState As String
Dim TmpSurveyTownshipName As String
Dim TmpAbstractTownship As String

TmpTractFull = Left(Forms!OGLease![OGLTract Subform]![TractFull], 10)
‘Parse Left 10 out of TX102-0635-0140 which is TractFull assign to
TmpTractFull As String
TmpSurveyTownshipName = DLookup("[Survey/TownshipName]", "Survey",
"[Abstract/TownshipFull] = " & "TmpTractFull")
‘Abstract/TownshipFull is TX102-0635 which is index field get
Survey/TownshipName
TmpAbstractTownship = DLookup("[Abstract/Township]", "Survey",
"[Abstract/TownshipFull] = " & "TmpTractFull")
‘Abstract/TownshipFull is TX102-0635 which is index field get
Abstract/Township “0635â€
TmpCounty = DLookup("[County]", "County", "[CountyFull] = " &
Left(TmpTractFull, 5)
‘ Get the County Name
Rem TmpState = DLookup("[State]", "County", "[CountyFull] = " &
Left(TmpTractFull), 5)
‘Get the State
LandLease = LandLease & Forms!OGLease![OGLTract Subform]![Acres] & " acres,
more or less, part of the " & [TmpSurveyTownshipName] & ", Abstract " &
[TmpAbstractTownship] & "in " & [TmpCounty] & " County, " & [TmpState] & "and
described in " & " dated " & " " & " and recorded in Volume
" & " Page " & " Records of " & [TmpCounty] & " County, " &
[TmpState] & "."
‘Click button to help fill the description in LandLease Memo field.
Exit_LandDescription_Click:
Exit Sub
Err_LandDescription_Click:
MsgBox Err.Description
Resume Exit_LandDescription_Click

End Sub

 

[John W. Vinson]

Why won’t this work? There is possibly a better way to do this. I’m a beginner.

Well, I see quite a few problems... but you don't say a) in what way it's not
working or b) what you WANT it to do. Comments inline.

Private Sub LandDescription_Click()
On Error GoTo Err_LandDescription_Click

Dim TmpTractFull As String
Dim TmpCounty As String
Dim TmpState As String
Dim TmpSurveyTownshipName As String
Dim TmpAbstractTownship As String

TmpTractFull = Left(Forms!OGLease![OGLTract Subform]![TractFull], 10)
‘Parse Left 10 out of TX102-0635-0140 which is TractFull assign to
TmpTractFull As String

Storing multiple subfields as you're doing here in one field is Bad Design. If
TX is Texas, and 102 is a county, and so on - they should be separate fields.
You can easily concatenate them for display.

TmpSurveyTownshipName = DLookup("[Survey/TownshipName]", "Survey",
"[Abstract/TownshipFull] = " & "TmpTractFull")

The third argument to DLookUp should be a text string evaluating to a legal
SQL WHERE clause: in this case

[Abstract/TownshipFull] = 'TX102-0635'

Note the quotemark delimiters. You are instead creating

[Abstract/TownshipFull] = TmpTractFull

This fails in two ways - it would be searching for the *name of the variable*,
rather than its value; except that it won't work at all because you don't have
the delimiters. The correct syntax would be

"[Abstract/TownshipFull] = '" & TmpTractFull & "'")

For clarity I'll space that out so you can see where the quotes sit:

"[Abstract/TownshipFull] = ' " & TmpTractFull & " ' ")

‘Abstract/TownshipFull is TX102-0635 which is index field get
Survey/TownshipName
TmpAbstractTownship = DLookup("[Abstract/Township]", "Survey",
"[Abstract/TownshipFull] = " & "TmpTractFull")
ditto...

‘Abstract/TownshipFull is TX102-0635 which is index field get
Abstract/Township “0635”

Eh? Just substring it: Right([Abstract/TownshipFull], 4)

TmpCounty = DLookup("[County]", "County", "[CountyFull] = " &
Left(TmpTractFull, 5)
‘ Get the County Name
Rem TmpState = DLookup("[State]", "County", "[CountyFull] = " &
Left(TmpTractFull), 5)

Much easier if you have multiple fields for the township - state, county, etc.
Again, it's easier to put them together than to split them apart!

You can also JOIN a townships table with state, county, township names rather
than using DLookUp.

‘Get the State
LandLease = LandLease & Forms!OGLease![OGLTract Subform]![Acres] & " acres,
more or less, part of the " & [TmpSurveyTownshipName] & ", Abstract " &
[TmpAbstractTownship] & "in " & [TmpCounty] & " County, " & [TmpState] & "and
described in " & " dated " & " " & " and recorded in Volume
" & " Page " & " Records of " & [TmpCounty] & " County, " &
[TmpState] & "."
‘Click button to help fill the description in LandLease Memo field.

Probably a Bad Idea - you can create a Report with this information, rather
than storing redundant data in a memo field.

Exit_LandDescription_Click:
Exit Sub
Err_LandDescription_Click:
MsgBox Err.Description
Resume Exit_LandDescription_Click

End Sub

John W. Vinson [MVP]

 

[blazerklk]

Thanks John
I setup a query to get these answers and it works fine now. It was a bad
idea, I've waste 3 days working on it. Again thanks for your help.

Why won’t this work? There is possibly a better way to do this. I’m a beginner.

Well, I see quite a few problems... but you don't say a) in what way it's not
working or b) what you WANT it to do. Comments inline.

Private Sub LandDescription_Click()
On Error GoTo Err_LandDescription_Click

Dim TmpTractFull As String
Dim TmpCounty As String
Dim TmpState As String
Dim TmpSurveyTownshipName As String
Dim TmpAbstractTownship As String

TmpTractFull = Left(Forms!OGLease![OGLTract Subform]![TractFull], 10)
‘Parse Left 10 out of TX102-0635-0140 which is TractFull assign to
TmpTractFull As String

Storing multiple subfields as you're doing here in one field is Bad Design. If
TX is Texas, and 102 is a county, and so on - they should be separate fields.
You can easily concatenate them for display.

TmpSurveyTownshipName = DLookup("[Survey/TownshipName]", "Survey",
"[Abstract/TownshipFull] = " & "TmpTractFull")

The third argument to DLookUp should be a text string evaluating to a legal
SQL WHERE clause: in this case

[Abstract/TownshipFull] = 'TX102-0635'

Note the quotemark delimiters. You are instead creating

[Abstract/TownshipFull] = TmpTractFull

This fails in two ways - it would be searching for the *name of the variable*,
rather than its value; except that it won't work at all because you don't have
the delimiters. The correct syntax would be

"[Abstract/TownshipFull] = '" & TmpTractFull & "'")

For clarity I'll space that out so you can see where the quotes sit:

"[Abstract/TownshipFull] = ' " & TmpTractFull & " ' ")

‘Abstract/TownshipFull is TX102-0635 which is index field get
Survey/TownshipName
TmpAbstractTownship = DLookup("[Abstract/Township]", "Survey",
"[Abstract/TownshipFull] = " & "TmpTractFull")
ditto...

‘Abstract/TownshipFull is TX102-0635 which is index field get
Abstract/Township “0635â€

Eh? Just substring it: Right([Abstract/TownshipFull], 4)

TmpCounty = DLookup("[County]", "County", "[CountyFull] = " &
Left(TmpTractFull, 5)
‘ Get the County Name
Rem TmpState = DLookup("[State]", "County", "[CountyFull] = " &
Left(TmpTractFull), 5)

Much easier if you have multiple fields for the township - state, county, etc.
Again, it's easier to put them together than to split them apart!

You can also JOIN a townships table with state, county, township names rather
than using DLookUp.

‘Get the State
LandLease = LandLease & Forms!OGLease![OGLTract Subform]![Acres] & " acres,
more or less, part of the " & [TmpSurveyTownshipName] & ", Abstract " &
[TmpAbstractTownship] & "in " & [TmpCounty] & " County, " & [TmpState] & "and
described in " & " dated " & " " & " and recorded in Volume
" & " Page " & " Records of " & [TmpCounty] & " County, " &
[TmpState] & "."
‘Click button to help fill the description in LandLease Memo field.

Probably a Bad Idea - you can create a Report with this information, rather
than storing redundant data in a memo field.

Exit_LandDescription_Click:
Exit Sub
Err_LandDescription_Click:
MsgBox Err.Description
Resume Exit_LandDescription_Click

End Sub

John W. Vinson [MVP]