how to get specific value from table field

[basilyos]

in my vba code i write this
If DLookup("Full_Name", "tbl_Personal_Information", "Full_Name = Forms!frm_New_Person!F_N") > 0 Then
'--------------------------
MSG = MsgBox("this client is existed and his id is {" & [P_ID] & "} ", vbOKOnly + vbInformation + vbMsgBoxRtlReading, "client database")
Else
strsql101 = "insert into tbl_Personal_Information (Name_English, Father_English, Family_English, Mother_English, P_ID, NName, Father, Family, Mother, Birthdate, Nationality, Record_Number, Record_Place, Address, Mobile1, Mobile2, Phone1, Phone2, Ets_Name) Values ('" & Me.Name_English & "','" & Me.Father_English & "','" & Me.Family_English & "','" & Me.Mother_English & "','" & Me.PID & "','" & Me.NName & "','" & Me.Father & "','" & Me.Family & "','" & Me.Mother & "','" & Me.Birthdate & "','" & Me.Nationality & "','" & Me.Record_Number & "','" & Me.Record_Place & "','" & Me.Address & "','" & Me.Mobile_1 & "', '" & Me.Mobile_2 & "','" & Me.Phone_1 & "', '" & Me.Phone_2 & "','" & Me.Ets_Name & "')"
DoCmd.SetWarnings False
DoCmd.RunSQL strsql101
'--------------------------
MSG = MsgBox("information added", vbOKOnly + vbInformation + vbMsgBoxRtlReading, "client database")



my problem is the id in the msgbox give me the last id from tbl_personal_information

but what i want is the id of the client that i try to add

 

[pr2-eugin]

Hello basilyos, Welcome to AWF

Why is this Form not bound to the table? Use DCount instead of DLookup. You need to concatenate the Form value to the Domain function.

 

[Mihail]

Dim ExistentID as Long
  ExistentID =NZ( DLookup("P_ID", "tbl_Personal_Information", "Full_Name = Forms!frm_New_Person!F_N"),0)
  If ExistentID  > 0 Then
'--------------------------
  MSG = MsgBox("this client is existed and his id is {" & ExistentID   &  "} ", vbOKOnly + vbInformation + vbMsgBoxRtlReading, "client database")
'--------------------------
 End If

 

[basilyos]

pr2-eugin thank you for your reply

but i f i want to use the dlookup and i don't want to bound the form with the table is there a solution for my problem

 

[basilyos]

Mihail thank you bro
it works, am so happy

 

[Mihail]

Glad to help you.