showing the id of a new record when click on save

[basilyos]

hello

i want to get the id of a new record in a msgbox

this is my code

Dim MSG As Integer
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
'--------------------------
MSG1 = MsgBox("this person is registered under this ID {" & [ExistentID] & "}, do you want to check his file", vbYesNo + vbQuestion + vbMsgBoxRtlReading, "customer database")
If MSG1 = vbYes Then

If DCount("*", "qry_Personal_Data", "[P_ID] = " & [ExistentID]) > 0 Then
DoCmd.OpenReport "rpt_personal_information_01", acViewReport, , "[P_ID] = " & [ExistentID]
Else
DoCmd.OpenReport "rpt_personal_information_02", acViewReport, , "[P_ID] = " & [ExistentID]
End If
End If
Else
strsql101 = "insert into tbl_Personal_Information (Plate_Number_1, Plate_Number_2, chk_lebanese, Name_English, Father_English, Family_English, Mother_English, NName, Father, Family, Mother, Birthdate, Nationality, Record_Number, Record_Place, Address, Mobile1, Mobile2, Phone1, Phone2, Ets_Name) Values ('" & Me.Plate_Number_1 & "','" & Me.Plate_Number_2 & "','" & Me.chknationality & "','" & Me.Name_English & "','" & Me.Father_English & "','" & Me.Family_English & "','" & Me.Mother_English & "','" & 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
'--------------------------
MSG2 = MsgBox("the information is saved under the ID {" & [P_ID] & "}, do you want to add a new record?", vbYesNo + vbQuestion + vbMsgBoxRtlReading, "customer database")
If MSG2 = vbYes Then
DoCmd.RunSQL "DELETE * FROM tbl_Names_Lebanon"
DoCmd.Close acForm, "frm_New_Person"
DoCmd.OpenForm "frm_New_Person"
DoCmd.SetWarnings False
ElseIf MSG2 = vbNo Then
DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE * FROM tbl_Names_Lebanon"
End If
End If
End If

my problem is with MSG2 when i click save it will save the info but it will not show the message MSG2
if i remove this {" & [P_ID] & "} from the MSG2 it will open
if it's existed she don't open and if i press again it will show the MSG1

so any help??

 

[Minty]

You aren't setting P_ID anywhere as a variable. Use option explicit and declare all your variables

 

[smig]

If you use autonumber DMax() will give you latest id

 

[basilyos]

smig is this what i should write


Dim lastID As Long

lastID = DMax("P_ID", "tbl_Personal_Information")

MSG2 = MsgBox("the information is saved under the ID lastID, do you want to add a new record?", vbYesNo + vbQuestion + vbMsgBoxRtlReading, "customer database")

 

[Minty]

Nearly try

Dim lastID As Long

lastID = DMax("P_ID", "tbl_Personal_Information")

MSG2 = MsgBox("the information is saved under the ID " & lastID & ", do you want to add a new record?", vbYesNo + vbQuestion + vbMsgBoxRtlReading, "customer database")

 

[basilyos]

Minty thank you so much it works

 

[pr2-eugin]

Crying "it does not work", makes very little sense... I looked over your code, needs a lot of change to begin with..

Dim ExistentID As Long
ExistentID = Nz(DLookup("P_ID", "tbl_Personal_Information", "Full_Name = [COLOR=Red][B]'" &[/B][/COLOR] Forms!frm_New_Person!F_N [COLOR=Red][B]& "'"[/B][/COLOR]), 0)

If ExistentID > 0 Then
[COLOR=Green]'--------------------------[/COLOR]
If MsgBox("This person is registered under this ID {" & [ExistentID] & "}, do you want to check his file", _
            vbYesNo + vbQuestion + vbMsgBoxRtlReading, "Customer Database") = vbYes Then
    If DCount("*", "qry_Personal_Data", "[P_ID] = " & [ExistentID]) > 0 Then
        DoCmd.OpenReport "rpt_personal_information_01", acViewReport, , "[P_ID] = " & ExistentID
    Else
        DoCmd.OpenReport "rpt_personal_information_02", acViewReport, , "[P_ID] = " & ExistentID
    End If
[COLOR=Red][B]Else[/B][/COLOR]
    strsql101 = "INSERT INTO tbl_Personal_Information (Plate_Number_1, Plate_Number_2, chk_lebanese, Name_English, " & _
                "Father_English, Family_English, Mother_English, NName, Father, Family, Mother, Birthdate, Nationality, " & _
                "Record_Number, Record_Place, Address, Mobile1, Mobile2, Phone1, Phone2, Ets_Name) " & _
                "VALUES ('" & Me.Plate_Number_1 & "', '" & Me.Plate_Number_2 & "', '" & Me.chknationality & "', '" & _
                Me.Name_English & "', '" & Me.Father_English & "', '" & Me.Family_English & "', '" & Me.Mother_English & "', '" & _
                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 & "')"
    [COLOR=Red][B]CurrentDb.Execute[/B][/COLOR] strsql101
    If MsgBox("The information is saved under the ID {" & [P_ID] & "}, do you want to add a new record?", _
                vbYesNo + vbQuestion + vbMsgBoxRtlReading, "Customer Database") = vbYes Then
        [COLOR=Red][B]CurrentDb.Execute[/B][/COLOR] "DELETE * FROM tbl_Names_Lebanon"
        DoCmd.Close acForm, "frm_New_Person"
        DoCmd.OpenForm "frm_New_Person"
    Else
        [COLOR=Red][B]CurrentDb.Execute[/B][/COLOR] "DELETE * FROM tbl_Names_Lebanon"
    End If
End If

Is all your fields in your table Text - including Birthdate?

 

[smig]

I did not read all your long code.
Why do you care what id will it be saved to ?

 

[basilyos]

Pr2 eugin yes they are

And thanks for correcting my codes

I'm proud of my self coz i'm a newbie ans no one learn me just this forum so thank you guys for everything


Smig i want this id coz it will be a unique id for a specific person
So i want to writr down this id on papers coming from the judge refer to this person
So when i want to close this case i make a search for this person by his id

So it's easy for me to write it down and use it again