Record not save into table after clicking on "Save" button (1 Viewer)

L

luzz

Registered User.
Local time
Today, 09:50
Joined
Aug 23, 2017
Messages
346
Hello everyone, I am facing problem whereby my record is not saved even after i click onto the "Save" button on my access form. May i know what causes this issue?

Below is my code:
Code: 
   If Me.txtGLGPO.Tag & "" = "" Then
        'this is for insert new
        'add data to table
         CurrentDb.Execute "INSERT INTO ModifyMainTable(GLGPO,Mill,[Solid/Printing],Reference,FabricDelivery,GarmentDelDate,Fabrication,Width,FinishedGoods,GSMPerSqYd,Colour,LabDipCode,GrossWeight,NettWeight,Lbs,Loss,Yds,Remarks,POType,ComboName,GroundColour,GarmentSketch,BuyerRequirement)" & _
            " VALUES(" & Me.txtGLGPO & ",'" & Me.cboMill & "','" & Me.cboSP & "','" & Me.cboRef & "','" & Me.txtFabricDelivery & "','" & Me.txtGarmentDelDate & "','" & Me.txtFabrication & "','" & Me.txtWidth & "','" & Me.txtFinishedGood & "','" & _
             Me.txtGSMsq & "','" & Me.txtColour & "','" & Me.txtLabDipCode & "','" & Me.txtGrossweight & "','" & _
             Me.txtNettweight & "','" & Me.txtLbs & "','" & Me.txtLoss & "','" & _
             Me.txtYds & "','" & Me.txtRemarks & "','" & Me.cboPoType & "','" & _
             Me.txtGroundColour & "','" & Me.txtComboName & "','" & Me.txtGarmentSketch & "','" & Me.txtBuyerRequirement & "')"
    Else
        'otherwise ( Tag of txtGLGPO store the GLGPO to be modified)
        CurrentDb.Execute "UPDATE ModifyMainTable " & _
                " SET GLGPO = " & Me.txtGLGPO & _
                ", Mill = '" & Me.cboMill & "'" & _
                ", [Solid/Printing] = '" & Me.cboSP & "'" & _
                ", Reference = '" & Me.cboRef & "'" & _
                ", FabricDelivery = '" & Me.txtFabricDelivery & "'" & _
                ", Fabrication = '" & Me.txtFabrication & "'" & _
                ", Width = '" & Me.txtWidth & "'" & _
                ", FinishedGoods = '" & Me.txtFinishedGood & "'" & _
                ", GSMPerSqYd = '" & Me.txtGSMsq & "'" & _
                ", Colour = '" & Me.txtColour & "'" & _
                ", LabDipCode = '" & Me.txtLabDipCode & "'" & _
                ", GrossWeight = '" & Me.txtGrossweight & "'" & _
                ", NettWeight = '" & Me.txtNettweight & "'" & _
                ", LBS = '" & Me.txtLbs & "'" & _
                ", Loss = '" & Me.txtLoss & "'" & _
                ", Yds = '" & Me.txtYds & "'" & _
                ", Remarks = '" & Me.txtRemarks & "'" & _
                ", POType = '" & Me.cboPoType & "'" & _
                ", ComboName = '" & Me.txtComboName & "'" & _
                ", GroundColour = '" & Me.txtGroundColour & "'" & _
                ", GarmentSketch = '" & Me.txtGarmentSketch & "'" & _
                ", SampleRequirement = '" & Me.txtBuyerRequirement & "'" & _
                " WHERE GLGPO = " & Me.txtGLGPO
                
    End If
 
Minty

Minty

AWF VIP
Local time
Today, 17:50
Joined
Jul 26, 2013
Messages
10,371
You appear to be trying to set GLGPO to the same value as the criteria, which makes no sense. Take GLGPO out of your UPDATE statement.
 
L

luzz

Registered User.
Local time
Today, 09:50
Joined
Aug 23, 2017
Messages
346
Minty said:
You appear to be trying to set GLGPO to the same value as the criteria, which makes no sense. Take GLGPO out of your UPDATE statement.
Click to expand...

The record is still unable to save into my table after i have took out GLGPO in my update statement
 
J

JHB

Have been here a while
Local time
Today, 18:50
Joined
Jun 17, 2012
Messages
7,732
If it not update do you get some error message, if not do you've any error handling running, is yes comment it out until you've found the problem, (You're only showing a part of code).
 
L

luzz

Registered User.
Local time
Today, 09:50
Joined
Aug 23, 2017
Messages
346
JHB said:
If it not update do you get some error message, if not do you've any error handling running, is yes comment it out until you've found the problem, (You're only showing a part of code).
Click to expand...

There is no error message tho. After i click update, the record will be cleared from my form.
 
Minty

Minty

AWF VIP
Local time
Today, 17:50
Joined
Jul 26, 2013
Messages
10,371
Create the query string into a string variable and then debug.print it.
Copy and paste that into the query editor. See what happens if you run it from the query window.
 
L

luzz

Registered User.
Local time
Today, 09:50
Joined
Aug 23, 2017
Messages
346
Minty said:
Create the query string into a string variable and then debug.print it.
Copy and paste that into the query editor. See what happens if you run it from the query window.
Click to expand...

I have solve it already. However, now i am facing issue whereby it show error message "Invalid use of Null" when i click on my edit button.
Code: 
Private Sub cmdModify_Click()
 'check whether there is exists data in list
    If Not (Me.FormMxdSub.Form.Recordset.EOF And Me.FormMxdSub.Form.Recordset.BOF) Then
        'get data to text box control
        With Me.FormMxdSub.Form.Recordset

            Me.txtGLGPO = .Fields("GLGPO")
            Me.cboMill = .Fields("Mill")
            Me.txtFabricDelivery = .Fields("FabricDelivery")
            Me.txtGarmentDelDate = .Fields("GarmentDelDate")
            Me.txtFabrication = .Fields("Fabrication")
            Me.txtWidth = .Fields("Width")
            Me.txtFinishedGood = .Fields("FinishedGoods")
            Me.txtGSMsq = .Fields("GSMPerSqYd")
            Me.txtColour = .Fields("Colour")
            Me.txtLabDipCode = .Fields("LabDipCode")
            Me.txtGrossweight = .Fields("GrossWeight")
            Me.txtNettweight = .Fields("NettWeight")
            Me.txtLbs = .Fields("Lbs")
            Me.txtLoss = .Fields("Loss")
            Me.txtYds = .Fields("Yds")
            Me.txtRemarks = .Fields("Remarks")
            Me.cboPoType = .Fields("POType")
            Me.txtComboName = .Fields("ComboName")
            Me.txtGroundColour = .Fields("GroundColour")
            Me.txtGarmentSketch = .Fields("GarmentSketch")
            Me.txtBuyerRequirement = .Fields("SampleRequirement")
            Me.txtGarmentSketch2 = .Fields("GarmentSketch2")
            Me.txtModifyGarment3 = .Fields("GarmentSketch3")
            Me.txtModifyGarment4 = .Fields("GarmentSketch4")
            Me.txtModifyGarment5 = .Fields("GarmentSketch5")
            Me.txtBuyer = .Fields("Buyer")
            Me.txtMRName = .Fields("MRName")
            Me.txtMXDPO = .Fields("MXDPO")
            Me.Image83.Picture = Me.txtGarmentSketch
            Me.ImageModify2.Picture = Me.txtGarmentSketch2 (Error show here) 
            Me.ImageModify3.Picture = Me.txtModifyGarment3
            Me.ImageModify4.Picture = Me.txtModifyGarment4
            Me.ImageModify5.Picture = Me.txtModifyGarment5
            'store GLGPO in Tag of txtGLGPO in case GLGPO is modified
            Me.txtGLGPO.Tag = .Fields("GLGPO")
            
            'change caption of button add to update
            Me.cmdModifyAdd.Caption = "Update"
            
            'disable button edit
            Me.cmdModify.Enabled = False
            

        End With
    End If
End Sub


I am not sure if this message pop up because i do have any filepath attached for this PO.
 
L

luzz

Registered User.
Local time
Today, 09:50
Joined
Aug 23, 2017
Messages
346
luzz said:
I have solve it already. However, now i am facing issue whereby it show error message "Invalid use of Null" when i click on my edit button.
Code: 
Private Sub cmdModify_Click()
 'check whether there is exists data in list
    If Not (Me.FormMxdSub.Form.Recordset.EOF And Me.FormMxdSub.Form.Recordset.BOF) Then
        'get data to text box control
        With Me.FormMxdSub.Form.Recordset

            Me.txtGLGPO = .Fields("GLGPO")
            Me.cboMill = .Fields("Mill")
            Me.txtFabricDelivery = .Fields("FabricDelivery")
            Me.txtGarmentDelDate = .Fields("GarmentDelDate")
            Me.txtFabrication = .Fields("Fabrication")
            Me.txtWidth = .Fields("Width")
            Me.txtFinishedGood = .Fields("FinishedGoods")
            Me.txtGSMsq = .Fields("GSMPerSqYd")
            Me.txtColour = .Fields("Colour")
            Me.txtLabDipCode = .Fields("LabDipCode")
            Me.txtGrossweight = .Fields("GrossWeight")
            Me.txtNettweight = .Fields("NettWeight")
            Me.txtLbs = .Fields("Lbs")
            Me.txtLoss = .Fields("Loss")
            Me.txtYds = .Fields("Yds")
            Me.txtRemarks = .Fields("Remarks")
            Me.cboPoType = .Fields("POType")
            Me.txtComboName = .Fields("ComboName")
            Me.txtGroundColour = .Fields("GroundColour")
            Me.txtGarmentSketch = .Fields("GarmentSketch")
            Me.txtBuyerRequirement = .Fields("SampleRequirement")
            Me.txtGarmentSketch2 = .Fields("GarmentSketch2")
            Me.txtModifyGarment3 = .Fields("GarmentSketch3")
            Me.txtModifyGarment4 = .Fields("GarmentSketch4")
            Me.txtModifyGarment5 = .Fields("GarmentSketch5")
            Me.txtBuyer = .Fields("Buyer")
            Me.txtMRName = .Fields("MRName")
            Me.txtMXDPO = .Fields("MXDPO")
            Me.Image83.Picture = Me.txtGarmentSketch
            Me.ImageModify2.Picture = Me.txtGarmentSketch2 (Error show here) 
            Me.ImageModify3.Picture = Me.txtModifyGarment3
            Me.ImageModify4.Picture = Me.txtModifyGarment4
            Me.ImageModify5.Picture = Me.txtModifyGarment5
            'store GLGPO in Tag of txtGLGPO in case GLGPO is modified
            Me.txtGLGPO.Tag = .Fields("GLGPO")
            
            'change caption of button add to update
            Me.cmdModifyAdd.Caption = "Update"
            
            'disable button edit
            Me.cmdModify.Enabled = False
            

        End With
    End If
End Sub


I am not sure if this message pop up because i do have any filepath attached for this PO.
Click to expand...

I managed to solved the error message by using nz(textbox).
Thank you.
 
P

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:50
Joined
Feb 19, 2002
Messages
43,270
Glad you got it worked out.

Access is a RAD tool and the best RAD feature is bound forms. If you don't use bound forms, you end up having to write a lot of code to do what Access would have done for you without any code at all and you're still left with the baggage of having an Access app.

If you created the unbound form because you didn't understand the Access Form's Event model, we can help you with that. Proper use of the Form level events will give you all the control you need to ensure that only valid records are saved.

If you created the unbound form because you need to use Access to update data in a database over the internet, then there are less code intensive methods where you bind ADO recordsets to the form and that reduces a lot of the code.
 
L

luzz

Registered User.
Local time
Today, 09:50
Joined
Aug 23, 2017
Messages
346
Pat Hartman said:
Glad you got it worked out.

Access is a RAD tool and the best RAD feature is bound forms. If you don't use bound forms, you end up having to write a lot of code to do what Access would have done for you without any code at all and you're still left with the baggage of having an Access app.

If you created the unbound form because you didn't understand the Access Form's Event model, we can help you with that. Proper use of the Form level events will give you all the control you need to ensure that only valid records are saved.

If you created the unbound form because you need to use Access to update data in a database over the internet, then there are less code intensive methods where you bind ADO recordsets to the form and that reduces a lot of the code.
Click to expand...

WOW! Thanks for sharing!
 
P

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:50
Joined
Feb 19, 2002
Messages
43,270
You're welcome. Does that mean you want to learn about form events so you can save yourself a lot of work in the future?
 
You must log in or register to reply here.

Similar threads

I
save timestamp upon click of save button
Replies
2
Views
824
The_Doc_Man
The_Doc_Man
L
How to edit current record in access and save the edited record as a new record
Replies
2
Views
765
Minty
Minty
L
Type Mismatch
Replies
5
Views
708
pbaldy
pbaldy

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom