Results 1 to 11 of 11
  1. #1
    SpudXIII is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2016
    Posts
    4

    If statement to prevent null fields doesn't seem to work anymore

    This code worked for me before and when I moved on to work on another form and came back it wasn't working anymore. The goal is very simple: I want to prevent users from trying to enter blank fields that are required and I want it to prompt them as well as put the cursor on the missing field. For some reason I'm getting "Run-time error '3058': Index or primary key cannot contain a Null value" and when I debug it, it says it's erroring at rs.Update. I don't understand why it gets that far with null fields and my if statements. For the record, I've also used "= Null" rather than the double quotations with the same result. Any help would be greatly appreciated. I should also note that I'm just picking up Access/VBA for the first time since college, which was about 12 years ago.Needless to say, I'm a bit rusty, so please go easy on me. This is also my first time actually posting in a forum for help, so please forgive me if my etiquette is a bit off. Here's my code:

    Private Sub btnSubmit_Click()


    Dim rs As Recordset


    Set rs = CurrentDb.OpenRecordset("tblPulling")


    If Me.txtOrderNumber = "" Then
    MsgBox ("Please enter an order number.")
    Me.txtOrderNumber.SetFocus
    Exit Sub
    ElseIf Me.txtTagNumber = "" Then
    MsgBox ("Please enter a tag number.")
    Me.txtTagNumber.SetFocus
    Exit Sub
    ElseIf Me.txtOrderedBy = "" Then
    MsgBox ("Please enter a customer name.")


    Me.txtOrderedBy.SetFocus
    Exit Sub
    ElseIf Me.txtSalePrice = "" Then
    MsgBox ("Please enter a sale price.")
    Me.txtSalePrice.SetFocus
    Exit Sub
    Else
    rs.AddNew
    rs!OrderNumber = Me.txtOrderNumber
    rs!ProductNumber = [Forms]![frmEntry]![sfmOrderInv].[Form]![ProductNumber]
    rs!EnteredBy = Me.txtUser
    rs!OrderedBy = Me.txtOrderedBy
    rs!SalePrice = Me.txtSalePrice
    rs!Location = [Forms]![frmEntry]![sfmOrderInv].[Form]![Location]
    rs!Cost = Me.txtCost
    rs!TagNumber = Me.txtTagNumber
    rs!ID = [Forms]![frmEntry]![sfmOrderInv].[Form]![ID]
    rs.Update


    Me.txtOrderNumber = ""
    Me.txtOrderedBy = ""
    Me.txtSalePrice = ""
    Me.txtTagNumber = ""
    End If
    End Sub

  2. #2
    vicsaccess's Avatar
    vicsaccess is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Apr 2015
    Posts
    451
    hey welcome, a little research says that that error is referring to your primary key that you are trying to save. Is rs!id your primary or foreign key? I would start with commenting out that line with the ID and see if the error remains.

  3. #3
    CJ_London is offline VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    unless you have preseeded the fields with zero length string, this

    If Me.txtOrderNumber = "" Then

    will not capture nulls

    also you appear to be mixing datatypes - ordernumber and salesprice, implies a number which cannot be a zero length string

  4. #4
    SpudXIII is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2016
    Posts
    4
    Thanks so much for the advice and suggestions, guys. To vicsaccess: Yes, ID is my primary key in that table. I know that's why I'm getting the error, but I'm trying to catch the Nulls before it gets that far with my "if" statements. I want it to prompt the user before it tries to add the record if anything is left blank. To Ajax: I went ahead and replaced the = "" with = Null, but I'm still getting the same result. Also, Me.txtOrdernumber is an unbound text box in my form always to be entered by the user and I've got nothing for the default value. So I'm a little confused about the datatypes comment. Please let me know if there's anything I can provide that will help shed some light on my situation. Again, thanks so much for the responses.

  5. #5
    SpudXIII is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2016
    Posts
    4
    Click image for larger version. 

Name:	accessscreengrab.PNG 
Views:	17 
Size:	139.8 KB 
ID:	24051
    Here's a picture of the form. The code is for the Submit button.

  6. #6
    vicsaccess's Avatar
    vicsaccess is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Apr 2015
    Posts
    451
    is the ID and autonumber? if so that is your error. as for checking empty or null(they are differant) work on that first and get it 100% before you try adding the code to add the record, comment all of it out. as for the empty or null, check out Pbaldy's page, specificly validate data before saving.
    http://www.baldyweb.com/

  7. #7
    SpudXIII is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2016
    Posts
    4
    THANK YOU SO MUCH, VICSACCESS!! You're a lifesaver! Pbaldy's method works great and I learned about a new function today. It's a glorious day.
    Click image for larger version. 

Name:	tmyn.png 
Views:	15 
Size:	248.3 KB 
ID:	24052

  8. #8
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Aside from the few tips you've received and no doubt are working on, I'd like to add this. Null and "" are not the same thing, so if you trap for one and not the other, you need to be aware of what the data types are when coding - or else cover all bases each time. One way to enforce data entry is by setting it at the control property sheet level, including message text. This has the advantage of being simpler and does not require you to direct focus. However, it has limitations.
    Another way to do this is write If Is Null(name) or name="" Then each time. However, not only can this be repetitive, but sometimes the number of fields you want to validate requires too many Else If segments. I think you're approaching that level. I now use a function that accepts the form control name and evaluates if it is Null or empty string and returns Boolean (True/False) to the Save Record command button code. If required data is missing, form level code constructs a message box with a message containing the caption of each control label listed, so there's only one prompt for all missing values. The function is in a standard module so that I can call it from any form. The only requirements that come to mind is that each control to be validated needs a tag property value (such as "Reqd") and the control labels must be attached or else you have to get the control name (txtOrderNum might look weird to the user). To avoid raising an error when checking a control that does not have the tag property (only the command button AFAIK at this time), you either direct error trapping to Resume Next for that code segment or direct flow to bypass command buttons. Probaby sounds more complicated than it is.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  9. #9
    vicsaccess's Avatar
    vicsaccess is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Apr 2015
    Posts
    451
    Micron, you have me intrieged. do you know of any articles, sample databases or other information on this function and its use with TAG's? just as Spud, i have several data validation subs that get longer than i like. any information would be helpful.

  10. #10
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    For information on the tag property and its uses, I recommend just searching and read what intrigues you. As for code samples, review what I use (in one form or another). Note that some people do not advocate looping through a form with many controls on it as they seem to fear a performance hit. I've used this on forms with appx. 100 controls and found that the time it takes to execute is virtually un-noticeable.

    In a standard module:
    Code:
    Function HasNoData(vCheckVal As Variant) As Boolean
        HasNoData = False
        If IsNull(vCheckVal) Or vCheckVal = "" Then HasNoData = True
    End Function
    If all you want to do is terminate when validating a few controls, in command button code:
    Code:
    If HasNoData(Me!cmbMonth) Or HasNoData(Me!cmbYear) Or HasNoData(Me!txtJobID) Then
        MsgBox "Need to supply Month, Year and JobID values to continue."
        Exit Sub
    End If
    If you want to validate a bunch AND present a list in a message box, then in command button code:
    Code:
    Dim ctl as Control
    Dim svList as String, svList2 as String
    
    On Error GoTo errHandler
    svList = "Please enter a value for: " & vbCrLf
    svlist2 = ""
    For Each ctl In Me.Controls
        Select Case ctl.ControlType
        Case acTextBox, acComboBox 'add to Case as required
            If ctl.Tag = "Reqd" Then
                If HasNoData(ctl) Then svList2 = svList2 & ctl.Controls.Item(0).Caption & vbCrLf
            End If
        End Select
    nxtCtl:
    Next
    
    If svList2 <> "" Then
        MsgBox svList & svList2, vbOKOnly, "Missing Information"
        Exit Sub
    End If
    
    errHandler:
    If Err.Number = 2467 Then
        svList2 = svList2 & ctl.Name & vbCrLf
        Resume nxtCtl
    End If
    - where labels are not attached to controls, the error handler gets the control name, which may be cryptic
    - the Select Case block allows restriction of control types rather than handling an error where a control in the loop has no tag property

    Probably not written perfectly, but it works.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  11. #11
    vicsaccess's Avatar
    vicsaccess is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Apr 2015
    Posts
    451
    thanks Micron, i'll add that to the library

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 04-14-2014, 12:37 PM
  2. Why Doesn't Stuff Work Anymore?
    By HowardOfOcal in forum Programming
    Replies: 4
    Last Post: 02-03-2012, 05:57 PM
  3. Calling for parameter that doesn't exist anymore
    By rankhornjp in forum Programming
    Replies: 3
    Last Post: 12-22-2011, 02:14 PM
  4. Replies: 7
    Last Post: 10-25-2011, 08:32 PM
  5. Replies: 2
    Last Post: 01-03-2011, 05:17 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums