1,105,633 Community Members

Option1 is not being save to data base

Member Avatar
kimangel
Junior Poster in Training
73 posts since Jan 2013
Reputation Points: 13 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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
Member Avatar
AndreRet
Industrious Poster
4,492 posts since Jan 2008
Reputation Points: 362 [?]
Q&As Helped to Solve: 499 [?]
Skill Endorsements: 24 [?]
 
0
 

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

Member Avatar
kimangel
Junior Poster in Training
73 posts since Jan 2013
Reputation Points: 13 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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.

Member Avatar
AndreRet
Industrious Poster
4,492 posts since Jan 2008
Reputation Points: 362 [?]
Q&As Helped to Solve: 499 [?]
Skill Endorsements: 24 [?]
 
0
 

Only a pleasure.

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

Member Avatar
kimangel
Junior Poster in Training
73 posts since Jan 2013
Reputation Points: 13 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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.

Member Avatar
AndreRet
Industrious Poster
4,492 posts since Jan 2008
Reputation Points: 362 [?]
Q&As Helped to Solve: 499 [?]
Skill Endorsements: 24 [?]
 
0
 

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. :)

Member Avatar
kimangel
Junior Poster in Training
73 posts since Jan 2013
Reputation Points: 13 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

thank you sir. ill wait your reply.

Member Avatar
AndreRet
Industrious Poster
4,492 posts since Jan 2008
Reputation Points: 362 [?]
Q&As Helped to Solve: 499 [?]
Skill Endorsements: 24 [?]
 
0
 

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
Member Avatar
kimangel
Junior Poster in Training
73 posts since Jan 2013
Reputation Points: 13 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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.

Member Avatar
AndreRet
Industrious Poster
4,492 posts since Jan 2008
Reputation Points: 362 [?]
Q&As Helped to Solve: 499 [?]
Skill Endorsements: 24 [?]
 
0
 

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?

Member Avatar
rishif2
Posting Whiz in Training
284 posts since Dec 2012
Reputation Points: 57 [?]
Q&As Helped to Solve: 58 [?]
Skill Endorsements: 4 [?]
 
0
 

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 . . .

Member Avatar
AndreRet
Industrious Poster
4,492 posts since Jan 2008
Reputation Points: 362 [?]
Q&As Helped to Solve: 499 [?]
Skill Endorsements: 24 [?]
 
0
 

@rishif

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

Member Avatar
rishif2
Posting Whiz in Training
284 posts since Dec 2012
Reputation Points: 57 [?]
Q&As Helped to Solve: 58 [?]
Skill Endorsements: 4 [?]
 
0
 

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.

Member Avatar
kimangel
Junior Poster in Training
73 posts since Jan 2013
Reputation Points: 13 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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?

Member Avatar
rishif2
Posting Whiz in Training
284 posts since Dec 2012
Reputation Points: 57 [?]
Q&As Helped to Solve: 58 [?]
Skill Endorsements: 4 [?]
 
0
 

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 . . .

Member Avatar
kimangel
Junior Poster in Training
73 posts since Jan 2013
Reputation Points: 13 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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.

Member Avatar
rishif2
Posting Whiz in Training
284 posts since Dec 2012
Reputation Points: 57 [?]
Q&As Helped to Solve: 58 [?]
Skill Endorsements: 4 [?]
 
0
 

please make this more clear

Member Avatar
kimangel
Junior Poster in Training
73 posts since Jan 2013
Reputation Points: 13 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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.

Member Avatar
rishif2
Posting Whiz in Training
284 posts since Dec 2012
Reputation Points: 57 [?]
Q&As Helped to Solve: 58 [?]
Skill Endorsements: 4 [?]
 
0
 

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 . . .

Member Avatar
kimangel
Junior Poster in Training
73 posts since Jan 2013
Reputation Points: 13 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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.

Question Answered as of 1 Year Ago by AndreRet and rishif2
You
This question has already been solved: Start a new discussion instead
Post:
Start New Discussion
Tags Related to this Article