DLookup IF Statement

TallMan

Registered User.
Local time
Today, 05:22
Joined
Dec 5, 2008
Messages
239
Hello,

I am trying to complete an IF statement after using a Dlookup Function.
Basically I have a form that an associate will enter an account number in the Account_Search text box. Then they will click a button "Search Account" button that will check an existing table "Client_PIP_Main_TBL" IF the first Dlookup function does not find the account then I need a query to be run which would insert this into the table. I have the query ready and the code listed below. My issue is I do not know how to tell it "when null execute 2nd Dlookup statement. Any ideas?

Code:
Private Sub Search_Account_Click()
Dim db As DAO.Database
Set db = CurrentDb
Dim Account As Integer
Dim Branch As String
Dim FA_ID As String
Dim Client_Short_Name As String
Dim AMS_Begin_Date As Date
Dim Program As String
Dim Original_OBJ_Description As String
Dim Orig_Obj As String
 
If Me.Account.Value = DLookup("Account_Number", "Client_PIP_Main_TBL", "Account_Number='" & Me.Account_Search & "'") Is Null Then
'Me.Branch.Value = DLookup("Branch", "Client_PIP_Main_TBL", "Account_Number='" & Me.Account_Search & "'")
'Me.FA_ID.Value = DLookup("FA_ID", "Client_PIP_Main_TBL", "Account_Number='" & Me.Account_Search & "'")
'Me.Client_Short_Name.Value = DLookup("Client_Short_Name", "Client_PIP_Main_TBL", "Account_Number='" & Me.Account_Search & "'")
'Me.AMS_Begin_Date.Value = DLookup("AMS_Begin_Date", "Client_PIP_Main_TBL", "Account_Number='" & Me.Account_Search & "'")
'Me.Program.Value = DLookup("Program", "Client_PIP_Main_TBL", "Account_Number='" & Me.Account_Search & "'")
'Me.Orig_Obj.Value = DLookup("Orig_Obj", "Client_PIP_Main_TBL", "Account_Number='" & Me.Account_Search & "'")
'Me.Original_OBJ_Description.Value = DLookup("Original_Obj_Description", "Client_PIP_Main_TBL", "Account_Number='" & Me.Account_Search & "'")
 
Me.Account.Value = DLookup("Account_Number", "New_Acct_LKU_QRY", "Account_Number='" & Me.Account_Search & "'")
Me.Branch.Value = DLookup("Branch", "New_Acct_LKU_QRY", "Account_Number='" & Me.Account_Search & "'")
Me.FA_ID.Value = DLookup("FA_ID", "New_Acct_LKU_QRY", "Account_Number='" & Me.Account_Search & "'")
Me.Client_Short_Name.Value = DLookup("Client_Short_Name", "New_Acct_LKU_QRY", "Account_Number='" & Me.Account_Search & "'")
Me.AMS_Begin_Date.Value = DLookup("AMS_Begin_Date", "New_Acct_LKU_QRY", "Account_Number='" & Me.Account_Search & "'")
Me.Program.Value = DLookup("Program", "New_Acct_LKU_QRY", "Account_Number='" & Me.Account_Search & "'")
Me.Orig_Obj.Value = DLookup("Orig_Obj", "New_Acct_LKU_QRY", "Account_Number='" & Me.Account_Search & "'")
Me.Original_OBJ_Description.Value = DLookup("Original_Obj_Description", "New_Acct_LKU_QRY", "Account_Number='" & Me.Account_Search & "'")
sqlinsert = "INSERT INTO Client_PIP_Main_TBL ([Branch],[FA_ID],[Client_Short_Name],[AMS_Begin_Date],[Program],[Original_OBJ_Description],[Orig_Obj]) VALUES ('" & br_id & "','" & FA_ID & "','" & short_name & "','" & begin_dt & "','" & acct_pgm & "','" & Style_Name & "','" & money_mgr_dicsipline_code & "');"
db.Execute (sqlinsert)
Me.Refresh
End If
End Sub
 
I'm a bit confused why you are using a dlookup for every field. What you are looking to do can be accomplished with a combo box. There is a NotInList event for a combo box. You can use that event to add the new account number.

dlookups are slower than a query and should be used sparingly. As the numbers of records grow, the time it will take for Access to execute all those dlookups will increase.

edit:
btw, when testing for Null, it would be:

Code:
If isnull(DLookup("Account_Number", "Client_PIP_Main_TBL", "Account_Number='" & Me.Account_Search & "'")) Then
 

Users who are viewing this thread

Back
Top Bottom