Prevent Table Update (1 Viewer)

Drand

Registered User.
Local time
Today, 18:57
Joined
Jun 8, 2019
Messages
65
Hi All
I am fairly new to access and coding. I have an issue with a form as follows:

The form is unbound as it update two different table when the "Add Record" button is clicked.

Within the form are three check boxes, "Active", "GST Applicable" and "MinStockSelect"

I have code to ensure that the other fields are not null which is triggered by the Add Record button.

My issue is that the checkbox field will create a new record before the other fields are completed when the Add Record is selected. As the checkboxes already contain a value I do not know how to prevent this.

My code is:
Code:
Private Sub Add_Record_Click()

If IsNull(Me.DateAdded) Then
    MsgBox "Please enter the date stock added!", vbCritical, "Missing Date"
    Me.DateAdded.SetFocus
Exit Sub
End If

If IsNull(Me.Supplier) Then
    MsgBox "Please select the supplier!", vbCritical, "Missing Supplier"
    Me.Supplier.SetFocus
Exit Sub
End If

If IsNull(Me.Grp) Then
    MsgBox "Please select the group for this stock item!", vbCritical, "Missing Group"
    Me.Grp.SetFocus
Exit Sub
End If

If IsNull(Me.SubGrp) Then
    MsgBox "Please select the sub group for this stock item!", vbCritical, "Missing Sub Group"
    Me.SubGrp.SetFocus
Exit Sub
End If

If IsNull(Me.StockItem) Then
    MsgBox "Please enter the stock description for this stock item!", vbCritical, "Missing Stock Description"
    Me.StockItem.SetFocus
Exit Sub
End If

If IsNull(Me.Price) Then
    MsgBox "Please enter the price for this stock item!", vbCritical, "Missing Price"
    Me.Price.SetFocus
Exit Sub
End If

If IsNull(Me.Quantity) Then
    MsgBox "Please enter the quantity for this stock item!", vbCritical, "Missing Quantity"
    Me.Quantity.SetFocus
Exit Sub
End If

If IsNull(Me.SalePrice) Then
    MsgBox "Please enter the selling price for this stock item!", vbCritical, "Missing Sale Price"
    Me.SalePrice.SetFocus
Exit Sub
End If

If Me.MinStockSelect = True And Me.MinStock <= 0 Then
    MsgBox "Please enter the minimum stock value!", vbCritical, "Missing Stock Value"
    Me.MinStock.SetFocus
Exit Sub
End If

If Me.MinStockSelect = True And Me.StockCount <= 0 Then
    MsgBox "Please enter the opening stock count", vbCritical, "Missing Stock Count"
    Me.StockCount.SetFocus
Exit Sub
End If

 
Dim tblStock As DAO.Recordset

Set tblStock = CurrentDb.OpenRecordset("SELECT * FROM [tblStock]")
    tblStock.AddNew
    tblStock![DateAdded] = Me.DateAdded.Value
    tblStock![StockItem] = Me.StockItem.Value
    tblStock![GrpId] = Me.GrpId.Value
    tblStock![Grp] = Me.Grp.Value
    tblStock![SuGrpId] = Me.SubGrpId.Value
    tblStock![SubGrp] = Me.SubGrp.Value
    tblStock![Supplier] = Me.Supplier.Value
    tblStock![SupplierId] = Me.SupplierId.Value
    tblStock![Price] = Me.Price.Value
    tblStock![Quantity] = Me.Quantity.Value
    tblStock![UnitCost] = Me.UnitCost.Value
    tblStock![SalePrice] = Me.SalePrice.Value
    tblStock![Margin] = Me.Margin.Value
    tblStock![InventoryItem] = Me.MinStockSelect
    tblStock![GSTApplicable] = Me.GSTApplicable.Value
    tblStock![Active] = Me.Active.Value
tblStock.Update






If Me.MinStockSelect = True Then
    Dim tblInvMins As DAO.Recordset
    Dim LastestDate As TempVars
     TempVars!LatestDate = DLookup("StockNumber", "tblStock", "LatestDate = #" _
     & DMax("LatestDate", "tblStock") & "#")

Set tblInvMins = CurrentDb.OpenRecordset("SELECT * FROM [tblInvMins]")
    tblInvMins.AddNew
    tblInvMins![StockItem] = Me.StockItem
    tblInvMins![StockNumber] = TempVars!LatestDate
    tblInvMins![MinStock] = Me.MinStock
    tblInvMins![OpeningStock] = Me.StockCount
    tblInvMins![Supplier] = Me.Supplier
    tblInvMins![SupplierId] = Me.SupplierId
tblInvMins.Update
End If

TempVars.RemoveAll


End Sub

I need to prevent the checkbox fields from creating a record until the entire form is completed.

Would appreciate any assistance on this
Thanks
 

June7

AWF VIP
Local time
Yesterday, 22:57
Joined
Mar 9, 2014
Messages
3,362
Is DefaultValue property set in table?
 

Drand

Registered User.
Local time
Today, 18:57
Joined
Jun 8, 2019
Messages
65
Hi June7
Yes it is
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 02:57
Joined
May 21, 2018
Messages
3,631
If all fields are required, then set that at the table level.
 

June7

AWF VIP
Local time
Yesterday, 22:57
Joined
Mar 9, 2014
Messages
3,362
Then I suggest you try not having DefaultValue set in table, just set it in control on form.
 

Drand

Registered User.
Local time
Today, 18:57
Joined
Jun 8, 2019
Messages
65
Thank you for the responses. Why will this prevent issue? Sorry I do not understand.
 

June7

AWF VIP
Local time
Yesterday, 22:57
Joined
Mar 9, 2014
Messages
3,362
DefaultValue set in table is only thing I can see that would cause this issue. So change the property in table design. What exactly is not clear about this advice?
 

moke123

AWF VIP
Local time
Today, 02:57
Joined
Jan 11, 2013
Messages
1,911
Are you saying that your validation code is not working and a record gets created even if everything is empty?

As an aside and from a user perspective, If you have particularly bad users who miss a lot of fields, it can get tedious to respond to numerous message boxes in succession. There are several ways to do validation and alert users to what they missed. Some are more complicated than others so in keeping it simple for an example, I just modified your code a little and broke it out to a seperate function. This makes it a little easier to test and makes your code a little easier to read.

Code:
Private Function IsMyFormValid() As Boolean

    Dim flg As Boolean
    
    flg = True

    If IsNull(Me.DateAdded) Then
        Me.DateAdded.BorderColor = vbRed
        flg = False
    Else
        Me.DateAdded.BorderColor = vbBlack
    End If
    

    If IsNull(Me.Supplier) Then
        Me.Supplier.BorderColor = vbRed
        flg = False
    Else
        Me.Supplier.BorderColor = vbBlack
    End If
    

    If IsNull(Me.Grp) Then
        Me.Grp.BorderColor = vbRed
        flg = False
    Else
        Me.Grp.BorderColor = vbBlack
    End If
    

    If IsNull(Me.SubGrp) Then
        Me.SubGrp.BorderColor = vbRed
        flg = False
    Else
        Me.SubGrp.BorderColor = vbBlack
    End If
    

    If IsNull(Me.StockItem) Then
        Me.StockItem.BorderColor = vbRed
        flg = False
    Else
        Me.StockItem.BorderColor = vbBlack
    End If
    

    If IsNull(Me.Price) Then
        Me.Price.BorderColor = vbRed
        flg = False
    Else
        Me.Price.BorderColor = vbBlack
    End If
    

    If IsNull(Me.Quantity) Then
        Me.Quantity.BorderColor = vbRed
        flg = False
    Else
        Me.Quantity.BorderColor = vbBlack
    End If
    

    If IsNull(Me.SalePrice) Then
        Me.SalePrice.BorderColor = vbRed
        flg = False
    Else
        Me.SalePrice.BorderColor = vbBlack
    End If
    

    If Me.MinStockSelect = True And Me.MinStock <= 0 Then
        Me.MinStock.BorderColor = vbRed
        flg = False
    Else
        Me.MinStock.BorderColor = vbBlack
    End If
    

    If Me.MinStockSelect = True And Me.StockCount <= 0 Then
        Me.StockCount.BorderColor = vbRed
        flg = False
    Else
        Me.StockCount.BorderColor = vbBlack
    End If
    

    If flg = False Then
    
        MsgBox "Please complete the required fields indicated in red"
        
    End If

    IsMyFormValid = flg

End Function

This will go through the whole form and if anything is missing it will set the bordercolor to red and set the value of flg to false. At the end of the procedure if flg is false it will throw 1 message box stating fill in the fields with the red borders. Only one interuption and they can readily see what they need to do.

To use this in your add record procedure you would need one line of code.

Code:
Private Sub Add_Record_Click()

If IsMyFormValid = False Then Exit Sub  ' If form fails validation then exit sub

    Dim db As DAO.Database
    Dim tblStock As DAO.Recordset
    Dim tblInvMins As DAO.Recordset
    Dim strSql As String
    Dim strSql2 As String
    Dim VarLatestDate As String    ' not sure of datatype so you may need to change it

    strSql = "Select * from tblStock where YourPrimaryKeyName = 0"  ' no need to actually return records
    strSql2 = "SELECT * FROM [tblInvMins] where YourPrimaryKeyName = 0"

    Set db = CurrentDb()

    Set tblStock = db.OpenRecordset(strSql)

    tblStock.AddNew

    tblStock![DateAdded] = Me.DateAdded.Value
    tblStock![StockItem] = Me.StockItem.Value
    tblStock![GrpId] = Me.GrpId.Value
    tblStock![Grp] = Me.Grp.Value
    tblStock![SuGrpId] = Me.SubGrpId.Value
    tblStock![SubGrp] = Me.SubGrp.Value
    tblStock![Supplier] = Me.Supplier.Value
    tblStock![SupplierId] = Me.SupplierId.Value
    tblStock![Price] = Me.Price.Value
    tblStock![Quantity] = Me.Quantity.Value
    tblStock![UnitCost] = Me.UnitCost.Value
    tblStock![SalePrice] = Me.SalePrice.Value
    tblStock![Margin] = Me.Margin.Value
    tblStock![InventoryItem] = Me.MinStockSelect
    tblStock![GSTApplicable] = Me.GSTApplicable.Value
    tblStock![Active] = Me.Active.Value

    tblStock.Update


    If Me.MinStockSelect = True Then

        VarLatestDate = DLookup("StockNumber", "tblStock", "LatestDate = #" & DMax("LatestDate", "tblStock") & "#")

        Set tblInvMins = db.OpenRecordset(strSql2)

        tblInvMins.AddNew

        tblInvMins![StockItem] = Me.StockItem
        tblInvMins![StockNumber] = VarLatestDate
        tblInvMins![MinStock] = Me.MinStock
        tblInvMins![OpeningStock] = Me.StockCount
        tblInvMins![Supplier] = Me.Supplier
        tblInvMins![SupplierId] = Me.SupplierId

        tblInvMins.Update

    End If


MyExit:
    tblStock.Close
    tblInvMins.Close
    Set tblStock = Nothing
    Set tblInvMins = Nothing
    Set db = Nothing
End Sub

A couple other things to consider:

Your only adding records so there is no need to open a recordset of every record in the whole table. Note the strsql includes a where clause "where YourPrimaryKeyName = 0". (assuming you have no primary key with the value of 0 ) Be sure to change the name if you try this code.

You dont need a tempvar. you can just dim a variable and set that to the value you want

You dont need .value as that is the default.

Hope you find this helpful.
Thank you for attending my TedTalk.:D
 
Last edited:

moke123

AWF VIP
Local time
Today, 02:57
Joined
Jan 11, 2013
Messages
1,911
if you put the IsMyFormValid code above into your form it will give you a visible clue to what controls are failing validation.

Under a button put-
Code:
 msgbox IsMyFormValid.
Any empty controls that dont turn red are failing validation, as are any controls that have values that turn red.
 

Drand

Registered User.
Local time
Today, 18:57
Joined
Jun 8, 2019
Messages
65
Thank you so much for your response. As I said I am a bit of an amateur at this and I guess it shows.

The users are not that bad but my code was designed to check if every field was completed. I have implemented IsMyFormValid and it works really well. Much tidier than what I did!

Back to the original problem however which I may not have explained very well.

"Are you saying that your validation code is not working and a record gets created even if everything is empty?"

No, this does not occur. The record is only created in the table if a checkbox has been changed and the add record is triggered.

For example, if the user changes the GST Applicable check box and nothing else on the form the add record will add a new record to the table which only contains the GST Applicable status, nothing else. The same applies with the other checkboxes.

I hope that is clearer.
 

moke123

AWF VIP
Local time
Today, 02:57
Joined
Jan 11, 2013
Messages
1,911
is Add_Record a command button?

Are you running code in any event of the check boxes?
 

Drand

Registered User.
Local time
Today, 18:57
Joined
Jun 8, 2019
Messages
65
Yes Add_Record is a command button and two of the checkboxes run code.

Code:
Private Sub GSTApplicable_Click()
If Me!GSTApplicable = 0 Then
Me!GST = 0
Else
Me!GST = [Price] * 10 / 100
End If
End Sub

And

[CODE]
Private Sub MinStockSelect_Click()
If Me.MinStockSelect = True Then
Me.Label97.Visible = True
Me.Label100.Visible = True
Me.MinStock.Visible = True
Me.StockCount.Visible = True
Me.StockCount.Value = 0
Me.MinStock.Value = 0
Else
Me.Label97.Visible = False
Me.Label100.Visible = False
Me.MinStock.Visible = False
Me.StockCount.Visible = False
Me.StockCount.Value = 0
Me.MinStock.Value = 0
End If
End Sub
[/CODE]
 

moke123

AWF VIP
Local time
Today, 02:57
Joined
Jan 11, 2013
Messages
1,911
Lets recap.

Nothing at all happens if you dont click the add_record button.

The validation code works. You have tested with all the fields filled in, and one by one tested each control empty by itself and the validation code caught it.

The form is unbound so there are no form events firing any new record code.

No bound subforms.

You tried the other suggestions.

For example, if the user changes the GST Applicable check box and nothing else on the form the add record will add a new record to the table which only contains the GST Applicable status, nothing else. The same applies with the other checkboxes.

This makes me think your validation doesn't work. Its skipping right past it and creating the record.

It may be helpful to upload a stripped down copy of your db with just the relevant items.
 

Drand

Registered User.
Local time
Today, 18:57
Joined
Jun 8, 2019
Messages
65
That is correct. The issue is that there is no validation for the checkboxes so the add_record runs straight to the code to add that field without any checks, hence creating a record with just that data.

The form is definitely not bound and there are no sub forms.
 

June7

AWF VIP
Local time
Yesterday, 22:57
Joined
Mar 9, 2014
Messages
3,362
Even if there is no validation on checkboxes, moke's code should prevent record creation if IsMyFormValid is properly used.
 

Drand

Registered User.
Local time
Today, 18:57
Joined
Jun 8, 2019
Messages
65
How do I upload the stripped down version?
 

Drand

Registered User.
Local time
Today, 18:57
Joined
Jun 8, 2019
Messages
65
I hope this works. The exit button for the form will not but close does.
Sorry but I do not have WinZip.
 

Attachments

  • Test.accdb
    664 KB · Views: 10

Drand

Registered User.
Local time
Today, 18:57
Joined
Jun 8, 2019
Messages
65
I hadn't even got that far to test that button yet! I noticed the problem before testing that.

If you just do this, open the form, change the value of GST_Applicable, click on Add_Record and do nothing else it creates a record in the table with just that field added. That is what I am trying to prevent until all fields are completed.
 

Users who are viewing this thread

Top Bottom