Option1 is not being save to data base

[kimangel]

Hi Expert, my Option1.value is not save to databse while Option2.value is saving. Pls help me check my code. Thanks

Private Sub cmdSave_Click()

Set rs = New ADODB.Recordset
rs.Open "select*from Employees", cn, adOpenKeyset, adLockOptimistic

On Error GoTo err

rs.AddNew
rs!Employees_IdNo = txtSearch1.Text
rs!Lastname = txtLastname1.Text
rs!Firstname = txtFirstname1.Text
rs!Middle_Initial = txtMiddle_Initial1.Text
rs!Address = txtAddress1.Text
rs!Birthdate = DTPickerBirthdate1
rs!Date_Hired = DTPickerDate_Hired1
rs!Position = cboPosition.Text
rs!Rate = lblRate.Caption
rs!Civil_Status = cboCivil_Status1.Text
rs!Contact_No = txtContact_No1.Text
rs!Tin_No = txtTin_No1.Text
rs!SSS_No = txtSSS_No1.Text
rs!Philhealth_No = txtPhilhealth_No1.Text
rs!Pagibig_No = txtPagibig_No1.Text
rs!Remarks = txtRemarks1.Text

If Option1.Value = True Then
Option1.Caption = "Female"
rs!Gender = Option1.Caption

ElseIf Option2.Value = True Then
Option2.Caption = "Male"
rs!Gender = Option2.Caption


rs.Update
rs.Close
MsgBox "Saved.", vbInformation, "Employee"
Set rs = Nothing
Form03.clear
End If
Exit Sub

err:
    MsgBox "The ID number is already exist. Enter new number.", vbExclamation, "Employee"

End Sub

[AndreRet]

a Few things... First the question, you do not have code within the option1 part of your if statement to update the record. You do have it in the option2 part though. Change the code to -

If Option1.Value = True Then
Option1.Caption = "Female"
''rs!Gender = Option1.Caption You do not need to use the caption property. You KNOW it will be Female, just use Female...
rs!Gender = "Female" 
ElseIf Option2.Value = True Then
Option2.Caption = "Male"
''rs!Gender = Option2.Caption Same here, just use "Male"...
''NOW close the end if and THEN update the record...
End If

rs.Update
rs.Close
MsgBox "Saved.", vbInformation, "Employee"
Set rs = Nothing
Form03.clear
''Remove this part of End If...End If

The second part, I suggest you read up some more on error trapping. You have used a msgbox that states that the user already exists WITHOUT checking if he exists to trap an error. That would be misleading and you will not know what is wrong should an error occur... Change to the following...

Set rs = New ADODB.Recordset
rs.Open "SELECT * FROM Employees WHERE Employees_IdNo = '" & txtSearch1.Text & "'", cn, adOpenKeyset, adLockOptimistic
On Error GoTo err ''Use this to do proper error trapping...

''Use the following to check if user exists... Yopu already have an employee number. If it returns a record, then use message box to say the user exists, else, it does NOT exist, add the new user...

If rs.BOF = True Or rs.EOF = True Then''No user exist...

    ''Add new user...
    rs.AddNew
    rs!Employees_IdNo = txtSearch1.Text
    rs!Lastname = txtLastname1.Text
    rs!Firstname = txtFirstname1.Text
    rs!Middle_Initial = txtMiddle_Initial1.Text
    rs!Address = txtAddress1.Text
    rs!Birthdate = DTPickerBirthdate1
    rs!Date_Hired = DTPickerDate_Hired1
    rs!Position = cboPosition.Text
    rs!Rate = lblRate.Caption
    rs!Civil_Status = cboCivil_Status1.Text
    rs!Contact_No = txtContact_No1.Text
    rs!Tin_No = txtTin_No1.Text
    rs!SSS_No = txtSSS_No1.Text
    rs!Philhealth_No = txtPhilhealth_No1.Text
    rs!Pagibig_No = txtPagibig_No1.Text
    rs!Remarks = txtRemarks1.Text

    ''Remember to add the option button part back in here!!!!
    If Option1.Value = True Then
        Option1.Caption = "Female"
        ''rs!Gender = Option1.Caption You do not need to use the caption property. You KNOW it will be Female, just use Female...
        rs!Gender = "Female" 
    ElseIf Option2.Value = True Then
        Option2.Caption = "Male"
        ''rs!Gender = Option2.Caption Same here, just use "Male"...
        ''NOW close the end if and THEN update the record...
    End If

rs.Update
rs.Close
MsgBox "Saved.", vbInformation, "Employee"
Form03.Clear
    ''user exists, do nothing...
        Else
    MsgBox "a User with Employee Number - " & txtSearch1.Text & " already exist. Please add a new employee or exit page", vbOkOnly + vbInformation

    rs.Close
    Form03.Clear
End Sub

''Exit sub to do error trapping...
Exit Sub

err:
MsgBox "an Error occured in your search. Please try again.", vbExclamation, "Employee"

End Sub

[kimangel]

Thank you Andre for correcting my error trapping, I do appreciate that and I welcome any suggestion.

Sir, i'm following your code above, but an error occur, if I enter new number (not exist yet in database) "A User with Employee Number - " & txtSearch1.Text & " already exist." Please help me recheck. Thanks.

[AndreRet]

Only a pleasure.

What error do you get? on which line of code?

[kimangel]

sir the error is when I attempt to save the form both existing and non existing employee number will prompt a message A User with Employee Number - " & txtSearch1.Text & " already exist.

Sir andre, could you please help me on my two previous post, click event condition and field value present in two tables. Your help is needed badly. My defense is scheduled this saturday. Thank very much sir.

[AndreRet]

I am almost off to home soon. I will sit with this as soon as I get home and settled in. Stay on line - you will see me coming online in about 1 1/2 hours...

I will assit then. :)

[kimangel]

thank you sir. ill wait your reply.

[AndreRet]

Ok, use the code as I have it here... You should not get an error... If a messagebox pops up saying - "a User with Employee Number - ABC1234 (ONLY A SAMPLE!!!) already exist. Please add a new employee or exit page", it means the user exists. do nothing or exit the form/page, go back to main menu or let the user search again. My 2 cents, why use search to add a new employee? You should have a form on its own for managing an employee (add, edit, delete). Any rtae, use the following code...

On Error GoTo err ''Use this to do proper error trapping...

If txtSearch1.Text = vbNullString Then
    MsgBox "Please add a valid employee number"

    txtSearch1.SetFocus

    Exit Sub
        Else
    Set rs = New ADODB.Recordset
    rs.Open "SELECT * FROM Employees WHERE Employees_IdNo = '" & txtSearch1.Text & "'", cn, adOpenKeyset, adLockOptimistic

    If rs.BOF = True Or rs.EOF = True Then''No user exist...

        ''Add new user...
        rs.AddNew
        rs!Employees_IdNo = txtSearch1.Text
        rs!Lastname = txtLastname1.Text
        rs!Firstname = txtFirstname1.Text
        rs!Middle_Initial = txtMiddle_Initial1.Text
        rs!Address = txtAddress1.Text
        rs!Birthdate = DTPickerBirthdate1
        rs!Date_Hired = DTPickerDate_Hired1
        rs!Position = cboPosition.Text
        rs!Rate = lblRate.Caption
        rs!Civil_Status = cboCivil_Status1.Text
        rs!Contact_No = txtContact_No1.Text
        rs!Tin_No = txtTin_No1.Text
        rs!SSS_No = txtSSS_No1.Text
        rs!Philhealth_No = txtPhilhealth_No1.Text
        rs!Pagibig_No = txtPagibig_No1.Text
        rs!Remarks = txtRemarks1.Text

        If Option1.Value = True Then
            Option1.Caption = "Female"
            rs!Gender = "Female" 
        ElseIf Option2.Value = True Then
            Option2.Caption = "Male"
            rs!Gender = "Male"
        End If

    rs.Update

    MsgBox "Saved.", vbInformation, "Employee"
    rs.Close

    Unload Me

    Form01.Show ''ONLY if you want to show another form...
            Else
    MsgBox "a User with Employee Number - " & txtSearch1.Text & " already exist. Please add a new employee or exit page", vbOkOnly + vbInformation

    rs.Close

    Unload Me
    Form01.Show
End Sub

''Exit sub to do error trapping...
Exit Sub

err:
MsgBox "an Error occured in your search. Please try again.", vbExclamation, "Employee"

txtSearch1.SetFocus

Exit Sub

[kimangel]

Hi Sir, i'm following your code top to bottom but an error occur. Any number that I type (new) will message a User with Employee Number - " anynumber " already exist!. I tried revising your code but theres still the same error occurs. Please help me check sir.

[AndreRet]

Kim, what error occur? what is the message you get? is it an error or is it the message box "a User with Employee Number.... exist"???

Also, you have typed "anynumber" in txtSearch1 ' s textbox, why. Are you not suppose to enter a PROPER employee number?

[rishif2]

try this . . .

Private Sub cmdSave_Click()
On Error GoTo err
Set rs = New ADODB.Recordset
rs.Open "select*from Employees", cn, adOpenKeyset, adLockOptimistic
rs.AddNew
rs!Employees_IdNo = txtSearch1.Text
rs!Lastname = txtLastname1.Text
rs!Firstname = txtFirstname1.Text
rs!Middle_Initial = txtMiddle_Initial1.Text
rs!Address = txtAddress1.Text
rs!Birthdate = DTPickerBirthdate1
rs!Date_Hired = DTPickerDate_Hired1
rs!Position = cboPosition.Text
rs!Rate = lblRate.Caption
rs!Civil_Status = cboCivil_Status1.Text
rs!Contact_No = txtContact_No1.Text
rs!Tin_No = txtTin_No1.Text
rs!SSS_No = txtSSS_No1.Text
rs!Philhealth_No = txtPhilhealth_No1.Text
rs!Pagibig_No = txtPagibig_No1.Text
rs!Remarks = txtRemarks1.Text
If Option1.Value = True Then
Option1.Caption = "Female"
rs!Gender = "Female" 
ElseIf Option2.Value = True Then
Option2.Caption = "Male"
rs!Gender = "Male"
End If
rs.Update
cn.Close
Exit Sub
err:
MsgBox "Employee id already exist"
Set rs = Nothing
If cn.State = 1 Then cn.Close
End Sub

hope this helps you . . .

[AndreRet]

@rishif

Your code will be incomplete because the op wants to check if a record exist before adding the new record. :)

[rishif2]

its simple , just put primary key constraint on the related field(id field). And when we try to insert record with the number which already exist then it will throw an error and there is already defined code that can handle error.

[kimangel]

thank you rishift2 and andre it works but I did some revision on the bottom.

rs.Update
rs.Close
Set rs = Nothing
MsgBox "Saved!", vbOKOnly + vbInformation, "Employee"

Exit Sub

err:
    MsgBox "Employee ID already exist.", vbOKOnly + vbInformation, "Employee"

Another thing sir, the code above when click the save button, it saves even when there is with empty fields. The teacher requires when there are empty fields it wont be saved. How will I do that sir?

[rishif2]

use the following for on LostFocus event for required field

Private Sub Text1_LostFocus()
If Trim(Text1.Text) = "" Then
MsgBox "Field Cannot Be Left Blank"
Text1.SetFocus
Exit Sub
End If
End Sub

or you can you the same code on the click event of the command button

If Trim(Text1.Text) = "" Then
MsgBox "Field Cannot Be Left Blank"
Text1.SetFocus
Exit Sub
End If

hope this helps you . . .

[kimangel]

Sir, I already have this in all textboxes, what I mean is when i skip the other textbox; let say I have type on the field Lastname and skipping other textboxes, and click the save button it saves. leaving some fields empty.

[rishif2]

please make this more clear

[kimangel]

What I mean Sir is that I already set up lostfocus for every textboxes. But when I jump the mouse and point to save button skipping some textboxes empty then it will save. The teacher requires me that it should not be saved if some textboxes is empty.

[rishif2]

try this . . .

        Private Sub cmdSave_Click()
        If Trim(Text1.Text) = "" Or Trim(Text2.Text) = "" Or Trim(Text3.Text) = "" Or (Option1.Value = False And Option2.Value = False) Then
        MsgBox "cannot save to data base"
        Exit Sub
        End If
        On Error GoTo err
        Set rs = New ADODB.Recordset
        rs.Open "select*from Employees", cn, adOpenKeyset, adLockOptimistic
        rs.AddNew
        rs!Employees_IdNo = txtSearch1.Text
        rs!Lastname = txtLastname1.Text
        rs!Firstname = txtFirstname1.Text
        rs!Middle_Initial = txtMiddle_Initial1.Text
        rs!Address = txtAddress1.Text
        rs!Birthdate = DTPickerBirthdate1
        rs!Date_Hired = DTPickerDate_Hired1
        rs!Position = cboPosition.Text
        rs!Rate = lblRate.Caption
        rs!Civil_Status = cboCivil_Status1.Text
        rs!Contact_No = txtContact_No1.Text
        rs!Tin_No = txtTin_No1.Text
        rs!SSS_No = txtSSS_No1.Text
        rs!Philhealth_No = txtPhilhealth_No1.Text
        rs!Pagibig_No = txtPagibig_No1.Text
        rs!Remarks = txtRemarks1.Text
        If Option1.Value = True Then
        Option1.Caption = "Female"
        rs!Gender = "Female" 
        ElseIf Option2.Value = True Then
        Option2.Caption = "Male"
        rs!Gender = "Male"
        End If
        rs.Update
        cn.Close
        Exit Sub
        err:
        MsgBox "Employee id already exist"
        Set rs = Nothing
        If cn.State = 1 Then cn.Close
        End Sub

replace the text1 , text2 , text3 with their name what you defined
hope this helps you . . .

[kimangel]

thank very much for this rishift2 it works. My form1 is now complete with error trapping.
by the way Sir can I ask your assistance also on my previous post, title - click event condition. I really bothered on that update form for my time out. Thanks.