update

[nishant.dhruve]

I m trying to create a form in such a way that when i try to update area of operation by clicking update button. it should enter data realted to check box into report below and it does reflect.


Problem is that when i hit update it erase the previews data and i want to keep this previous along with the new data what i m entering
Here is my code what i have done so far.

Private Sub Command97_Click()
Dim strAreaOfOper  As String
If chkcgmp.Value = "-1" Then
    strAreaOfOper = lblchkcgmp.Caption
End If
If chkSAP.Value = "-1" Then
    If strAreaOfOper = "" Then
        strAreaOfOper = lblSAP.Caption
    Else
        strAreaOfOper = strAreaOfOper & ", " & lblSAP.Caption
    End If
    End If
    
If chkgp.Value = "-1" Then
    If strAreaOfOper = "" Then
        strAreaOfOper = lblgp.Caption
    Else
        strAreaOfOper = strAreaOfOper & ", " & lblgp.Caption
    End If
End If

If chkgqa.Value = "-1" Then
    If strAreaOfOper = "" Then
        strAreaOfOper = lblgqa.Caption
    Else
        strAreaOfOper = strAreaOfOper & ", " & lblgqa.Caption
    End If
    End If
    
If chkgran.Value = "-1" Then
    If strAreaOfOper = "" Then
        strAreaOfOper = lblgran.Caption
    Else
        strAreaOfOper = strAreaOfOper & ", " & lblgran.Caption
    End If
    End If
If chkcomp.Value = "-1" Then
    If strAreaOfOper = "" Then
        strAreaOfOper = lblcomp.Caption
    Else
        strAreaOfOper = strAreaOfOper & ", " & lblcomp.Caption
    End If
    End If
If chkcoat.Value = "-1" Then
    If strAreaOfOper = "" Then
        strAreaOfOper = lblcaot.Caption
    Else
        strAreaOfOper = strAreaOfOper & ", " & lblcaot.Caption
    End If
    End If
If chkencap.Value = "-1" Then
    If strAreaOfOper = "" Then
        strAreaOfOper = lblencap.Caption
    Else
        strAreaOfOper = strAreaOfOper & ", " & lblencap.Caption
    End If
    End If
If chksoft.Value = "-1" Then
    If strAreaOfOper = "" Then
        strAreaOfOper = lblsoft.Caption
    Else
        strAreaOfOper = strAreaOfOper & ", " & lblsoft.Caption
    End If
    End If
If chkrd.Value = "-1" Then
    If strAreaOfOper = "" Then
        strAreaOfOper = lblrd.Caption
    Else
        strAreaOfOper = strAreaOfOper & ", " & lblrd.Caption
    End If
    End If
If chkit.Value = "-1" Then
    If strAreaOfOper = "" Then
        strAreaOfOper = lblit.Caption
    Else
        strAreaOfOper = strAreaOfOper & ", " & lblit.Caption
    End If
    End If
If chkit.Value = "-1" Then
    If strAreaOfOper = "" Then
        strAreaOfOper = lblsafety.Caption
    Else
        strAreaOfOper = strAreaOfOper & ", " & lblsafety.Caption
    End If
    End If
    
    
    
 CurrentDb.Execute " UPDATE EmployeeI " _
 & " SET [AreaOfOper]  = '" & strAreaOfOper & "' " _
 & "WHERE [EMployee_ID] = '" & Me.metxtemp & "'"
 



End Sub

[Bulzie]

CurrentDb.Execute "INSERT INTO EmployeeI " _
& " SET [AreaOfOper] = '" & strAreaOfOper & "' " _
& "WHERE [EMployee_ID] = '" & Me.metxtemp & "'"

[nishant.dhruve]

Hi,
I tried that before but this is not working.
any other way??

[orange]

I'm not sure what you overall goal is, but you are working with a non normalized structure which could cause you more pain than you need in future.

Consider tables
Employee
AreasOfOperation
EmployeeAreasOfOps

Employee----->EmployeeAreasOfOps<-----AreasOfOperation

I'd consider a form with Employee info and a listbox with the various areas of Operation. Rowsource of Listbox would be AreasOfOperation.

Select all AreasOfOps that apply, then a button to take selections and create records for each selection in EmployeeAreasOfOps

[Bulzie]

Syntax needs to be right for Insert statement. Look it up but will be something like:

CurrentDb.Execute "INSERT INTO Employee1 ([AreaOfOper]) VALUES ('" & Me.strAreaOfOper & "') Where EMployee_ID = '" & Me.metxtemp & "'"

[ssanfu]

This is about your code.

You have code like:

<snip>
    If chkcgmp.Value = "-1" Then
        strAreaOfOper = lblchkcgmp.Caption
    End If
    If chkSAP.Value = "-1" Then
        If strAreaOfOper = "" Then
            strAreaOfOper = lblSAP.Caption
        Else
            strAreaOfOper = strAreaOfOper & ", " & lblSAP.Caption
        End If
    End If
<snip>

"chkcgmp" and "chkSAP" (two of many) are check box controls bound to a Yes/No type field. This is a number (a zero or a minus 1). The only values that the field can be is zero (0) or minus 1 (-1).

Microsoft has declared two constants: FALSE is equal to zero. TRUE is equal to -1.
Mathematically, FALSE = 0 and TRUE = Not FALSE. This means that TRUE can be any number other than zero!

Test it out. Add a text box control to a form and bind it to a number type field (Integer, Long Integer, Single or Double). Now add a check box control to a form and bind it to the same field as the text box.
Set the value for the text box to zero; the check box control will show FALSE.
Set the value for the text box to 100; the check box control will show TRUE.



You have a text string being compared to a number!!
If "chkcgmp" value is equal to TRUE (-1) and the test is

If chkcgmp = "-1" then

the result will ALWAYS return FALSE because a number is NEVER equal to a string!
It is the same as asking "Is -1 equal to "Microsoft"? Obviously the answer is and will forever be NO.

The point being, you should use the defined constants TRUE or FALSE instead of 0, -1, "0" or "-1".

<snip>
    If chkcgmp.Value = TRUE Then
        strAreaOfOper = lblchkcgmp.Caption
    End If
    If chkSAP.Value = TRUE Then
        If strAreaOfOper = "" Then
            strAreaOfOper = lblSAP.Caption
        Else
            strAreaOfOper = strAreaOfOper & ", " & lblSAP.Caption
        End If
    End If
<snip>

".Value" is not needed because "Value" is the default property. Doesn't hurt to use it, but it is a waste of time and typing.


Also, using the "Me." keyword makes the code clearer: Me.chkcgmp indicates a control on the form. "chkcgmp" can be a control or a variable defined in the code.


Last rant:
Instead of this code

<snip>
CurrentDb.Execute " UPDATE EmployeeI " _
 & " SET [AreaOfOper]  = '" & strAreaOfOper & "' " _
 & "WHERE [EMployee_ID] = '" & Me.metxtemp & "'"

I use

<snip>
    sSQL = "UPDATE EmployeeI SET [AreaOfOper]  = '" & strAreaOfOper & "'"
    sSQL = sSQL & " WHERE [EMployee_ID] = '" & Me.metxtemp & "';"
    Debug.Print sSQL
    
    CurrentDb.Execute sSQL, dbFailOnError

because I can check if the SQL syntax is correct and I can see what the variable values are.

[nishant.dhruve]

Thanks steve
your code really worked for me in one way but its not working when still update part has is not what i want. so when i m trying to add new checkbox name after clearing all field it does the same thing like.
for example my table already has chkgmp related data into it and i want add new one to that column but keeping the chkgmp value in it.
how can i do this??






Also still i have other question if u can solve that for me please

so here is my question
i want to open a report related to this check box.
so when i click next button it should open that report with record filtered as per the check box.
so for example if chkgmp = true then
it should open a report with all records filtered to gmp .
or if two check box are selected then it should open a report with both that check box filter related data

[ssanfu]

You posted a pic of your form; is the form in "Single Form" or "Continuous Form" view?

your code really worked for me in one way but its not working when still update part has is not what i want. so when i m trying to add new checkbox name after clearing all field it does the same thing like.
for example my table already has chkgmp related data into it and i want add new one to that column but keeping the chkgmp value in it.

Of course the current value in the control/field "AreaOfOperation" is being replaced. That is how your code is written.
You NEVER set the variable "strAreaOfOper" to the CURRENT value of "AreaOfOperation" before you start checking to check box controls!!

Here is the code revised: (UNTESTED)
***Blue lines are code I added***

Private Sub Command97_Click()   '<<-- poor name for the button :(
    Dim strAreaOfOper As String
    Dim sSQL As String

    'get "Area Of Operation" current value
    strAreaOfOper = Trim(Me.AreaOfOper)  

    If Me.chkcgmp = True Then
        If strAreaOfOper = "" Then
            strAreaOfOper = Me.lblchkcgmp.Caption
        Else
            strAreaOfOper = strAreaOfOper & ", " & Me.lblchkcgmp.Caption
        End If
    End If

    If Me.chkSAP = True Then
        If strAreaOfOper = "" Then
            strAreaOfOper = Me.lblSAP.Caption
        Else
            strAreaOfOper = strAreaOfOper & ", " & Me.lblSAP.Caption
        End If
    End If

    If Me.chkgp = True Then
        If strAreaOfOper = "" Then
            strAreaOfOper = Me.lblgp.Caption
        Else
            strAreaOfOper = strAreaOfOper & ", " & Me.lblgp.Caption
        End If
    End If

    If Me.chkgqa = True Then
        If strAreaOfOper = "" Then
            strAreaOfOper = Me.lblgqa.Caption
        Else
            strAreaOfOper = strAreaOfOper & ", " & Me.lblgqa.Caption
        End If
    End If

    If Me.chkgran = True Then
        If strAreaOfOper = "" Then
            strAreaOfOper = Me.lblgran.Caption
        Else
            strAreaOfOper = strAreaOfOper & ", " & Me.lblgran.Caption
        End If
    End If

    If Me.chkcomp = True Then
        If strAreaOfOper = "" Then
            strAreaOfOper = Me.lblcomp.Caption
        Else
            strAreaOfOper = strAreaOfOper & ", " & Me.lblcomp.Caption
        End If
    End If

    If Me.chkcoat = True Then
        If strAreaOfOper = "" Then
            strAreaOfOper = Me.lblcaot.Caption
        Else
            strAreaOfOper = strAreaOfOper & ", " & Me.lblcaot.Caption
        End If
    End If

    If Me.chkencap = True Then
        If strAreaOfOper = "" Then
            strAreaOfOper = Me.lblencap.Caption
        Else
            strAreaOfOper = strAreaOfOper & ", " & Me.lblencap.Caption
        End If
    End If

    If Me.chksoft = True Then
        If strAreaOfOper = "" Then
            strAreaOfOper = Me.lblsoft.Caption
        Else
            strAreaOfOper = strAreaOfOper & ", " & Me.lblsoft.Caption
        End If
    End If

    If Me.chkrd = True Then
        If strAreaOfOper = "" Then
            strAreaOfOper = Me.lblrd.Caption
        Else
            strAreaOfOper = strAreaOfOper & ", " & Me.lblrd.Caption
        End If
    End If

    If Me.chkit = True Then
        If strAreaOfOper = "" Then
            strAreaOfOper = Me.lblit.Caption
        Else
            strAreaOfOper = strAreaOfOper & ", " & Me.lblit.Caption
        End If
    End If

    If Me.chkit = True Then
        If strAreaOfOper = "" Then
            strAreaOfOper = Me.lblsafety.Caption
        Else
            strAreaOfOper = strAreaOfOper & ", " & Me.lblsafety.Caption
        End If
    End If


    sSQL = " UPDATE EmployeeI SET [AreaOfOper]  = '" & strAreaOfOper & "'"
    sSQL = sSQL & " WHERE [EMployee_ID] = '" & Me.metxtemp & "';"
    Debug.Print sSQL

    CurrentDb.Execute sSQL, dbFailOnError


End Sub

Note the use of the "Me." keyword.....

Also still i have other question if u can solve that for me please

so here is my question
i want to open a report related to this check box.
so when i click next button it should open that report with record filtered as per the check box

And for one selected "Employee_ID" or all employees?
Is the report created?
Do you have any code for the button?
What is the report record source? (the SQL please)

so for example if chkgmp = true then
it should open a report with all records filtered to gmp .

What data type is the field "gmp"?



BTW, I would have the caption for the button "Print", not "Next"...... less confusing.
How does one know that clicking "Next" opens a report???

[nishant.dhruve]

And for one selected "Employee_ID" or all employees?
Is the report created?
Do you have any code for the button?
What is the report record source? (the SQL please)

your answer to all this questions

Employee is not main concern while opening the report as this report is not bound to employee.
yes, its already created. i have kept attachment for you

this is the code what i have created so far checking all chkbox

Dim ctl As Control

For Each ctl In Me.Controls
    If ctl.ControlType = acCheckBox Then
        If ctl.Value <> True Then
            ctl.Value = True
        End If
    End If
Next ctl

but after this code is executed i want to open a report which is there in above image name of report is rptsopprint in new page with all those filter applied according to active check box



following is sql for report

SELECT SOP_Data.ID, SOP_Data.[SOP #], SOP_Data.[REV #], SOP_Data.Title, SOP_Data.[Area of SOP] FROM SOP_Data;

[ssanfu]

From Post #7:

i want to open a report related to this check box.
so when i click next button it should open that report with record filtered as per the check box.
so for example if chkgmp = true then
it should open a report with all records filtered to gmp .
or if two check box are selected then it should open a report with both that check box filter related data

What is "gmp"?
What field do you want to filter on??
If "chkgmp" is TRUE and "chkSAP" is TRUE, what field(s) are being use to filter on?
What does the data in the field(s) look like? Example please...

Employee is not main concern while opening the report as this report is not bound to employee.

Having the print button on the "ADD EMPLOYEE" form implies (to me) that a report concerning one or more employees will be printed.

but after this code is executed i want to open a report which is there in above image name of report is rptsopprint in new page with all those filter applied according to active check box

So you check all of the check boxes using the code

Dim ctl As Control

For Each ctl In Me.Controls
    If ctl.ControlType = acCheckBox Then
        If ctl.Value <> True Then
            ctl.Value = True
        End If
    End If
Next ctl

, but what I still don't understand which field to filter on or what the filter string will look like.


--------------------------------------------

SELECT SOP_Data.ID, SOP_Data.[SOP #], SOP_Data.[REV #], SOP_Data.Title, SOP_Data.[Area of SOP] FROM SOP_Data;

FYI, object names (ie field names) should only be letters or numbers. NO spaces, punctuation or special characters.
Alternatives would be
[Area of SOP] would be [AreaOfSOP] or [Area_of_SOP]
[SOP #] would be [SOPNum] or [SOP_Num]. The hash mark (#) is a Date/Time delimiter..

[nishant.dhruve]

[Quote = What is "gmp"?
What field do you want to filter on??
If "chkgmp" is TRUE and "chkSAP" is TRUE, what field(s) are being use to filter on?
What does the data in the field(s) look like? Example please...[/quote]

ok first of all forget GMP is just a record name in the field of areaofsop,there are different record such as granulation, coating ,compression etc.
if "chkgmp" is TRUE and "chkSAP" is TRUE, caption name of this check button which is GMP and SAP respectively are use to filter the field areaofsop.
Data in the field look like the image of report shown previously in this thread with blue mark around to that field.

-----------------------------------------------------------
[QUOTE = Having the print button on the "ADD EMPLOYEE" form implies (to me) that a report concerning one or more employees will be printed.

[\QUOTE]

here is the image what this save and update button do. Just trying to clear out your confusion

so when u enter a employee name id and all txt field and after that one has to click all that check box related to employee where he is going to work and when the save button is pressed it will store all this data in the table/report below.
So now when print button is clicked it does have any relation with the employee it is totally out of this page.
But this is the requirement of the company that what ever checkbox are been true it should open a whole new page of report where sopdata are being store and it should just open records which with the caption name of this chk box that are being filter in areaofsop.
for example if "chkcgmp" is TRUE where caption name of this chk button is CGMP which are same as the record in the field in of areaofsop.
so now when it is true it should open that report with those filter in areaofsop field having those caption name.

So if u can see this is not at all realted to employee now.

-------------------------------------------------------
yes i have used that code to check which chkbox are true.