DLookup in Access 2003

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
Why won’t this work? There is possibly a better way to do this. I’m a beginner.
Click to expand...

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
Click to expand...

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")
Click to expand...

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”
Click to expand...

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)
Click to expand...

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.
Click to expand...

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
Click to expand...

John W. Vinson [MVP]
 
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.

John W. Vinson said:
Why won’t this work? There is possibly a better way to do this. I’m a beginner.
Click to expand...

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
Click to expand...

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")
Click to expand...

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â€
Click to expand...

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)
Click to expand...

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.
Click to expand...

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
Click to expand...

John W. Vinson [MVP]
Click to expand...
 
Back
Top