Syntax error

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

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!
 
Mike said:
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!
Click to expand...

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

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





James Franklin said:
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!
Click to expand...
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).
Click to expand...
 
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).
 
Back
Top