Syntax error

[Guest]

The following sub returns syntax error in the last line. Please, advise the
correct syntax.

Private Sub TextBox_AfterUpdate()
Dim branchID As Integer
Dim branchName As String
branchID = DLookup("[BranchID]", "Ledger",
"[VersionID]=[Forms]![frmMyForm]![VersionID]")
branchName = DLookup("[BranchName]", "Branches", "[BranchID]=" & branchID)
End Sub

Thank you in advance!

 

[Tom Lake]

The following sub returns syntax error in the last line. Please, advise
the
correct syntax.

Private Sub TextBox_AfterUpdate()
Dim branchID As Integer
Dim branchName As String
branchID = DLookup("[BranchID]", "Ledger",
"[VersionID]=[Forms]![frmMyForm]![VersionID]")
branchName = DLookup("[BranchName]", "Branches", "[BranchID]=" &
branchID)
End Sub

Thank you in advance!

If BranchID is Null, you'll get that error.

Tom Lake

 

[James Franklin]

The following sub returns syntax error in the last line. Please, advise the
correct syntax.

Private Sub TextBox_AfterUpdate()
Dim branchID As Integer
Dim branchName As String
branchID = DLookup("[BranchID]", "Ledger",
"[VersionID]=[Forms]![frmMyForm]![VersionID]")
branchName = DLookup("[BranchName]", "Branches", "[BranchID]=" & branchID)
End Sub

Thank you in advance!

Try:
branchName = DLookup("[BranchName]", "Branches", "[BranchID]='" & branchID & "'")

I suspect branchID is not an Integer after the assignment and therefore you need to quote the value in the second DLookup.

It is not a good idea to use the same names (even if you change the case).

 

[Guest]

Thank you for the tips!
I've tried the syntax suggested by James.
Now I get run-time error ‘3464’

The first line returns branchID=1 ( not Null).

The following sub returns syntax error in the last line. Please, advise the
correct syntax.

Private Sub TextBox_AfterUpdate()
Dim branchID As Integer
Dim branchName As String
branchID = DLookup("[BranchID]", "Ledger",
"[VersionID]=[Forms]![frmMyForm]![VersionID]")
branchName = DLookup("[BranchName]", "Branches", "[BranchID]=" & branchID)
End Sub

Thank you in advance!

Try:
branchName = DLookup("[BranchName]", "Branches", "[BranchID]='" & branchID & "'")

I suspect branchID is not an Integer after the assignment and therefore you need to quote the value in the second DLookup.

It is not a good idea to use the same names (even if you change the case).

 

[Guest]

Hi Mike,

The first line is not necessarily related to the line that fails, because it
is looking up the BranchID in a table or query named Ledger. The second line
is attempting to look up BranchID in a table or query named Branches. So, if
there is a record in Ledger with BranchID=1, this doesn't automatically mean
that there will be a record in Branches with the same ID number.

Try the following:

branchName = Nz(DLookup("[BranchName]", "Branches", "[BranchID]=" &
branchID),"No Match Found")



Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

Thank you for the tips!
I've tried the syntax suggested by James.
Now I get run-time error ‘3464’

The first line returns branchID=1 ( not Null).