Results 1 to 12 of 12
  1. #1
    programmingnewby is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jul 2019
    Posts
    9

    'ADD' Button not available until a blank social is entered, causing an error.

    Quick summary; this database was created to be able to store drivers information. All fields and buttons were working, until i went ahead and added a new field manually for phone number.
    Now random errors occur without displaying an error message.

    1. The 'ADD' driver button should be available whenever the social field is blank, but now does not show up until I purposely cause an error by leaving it blank and pressing update. Then the 'ADD" appears as it should.

    2. Random entries now refuse to edit, when selecting an existing data, the update button works on some, but not others.

    this is the best way i can explain my issue.

    Option Compare Database


    Code:
    Private Sub cmdAdd_Click()
        'when we click on button Add there are two options
        '1. for insert
        '2. for update
        If Me.txtSocial.Tag & "" = "" Then
            ' this is for insert new
            'add data to table
        CurrentDb.Execute "INSERT INTO tblDrivers(tbLast, tbFirst, tbDOB, tbCDL, tbCDLEXP, tbCDLST, tbMC, tbMCEXP, tbCOM, tbHire, tbSocial, tbdrug, tbPhone) " & _
            " VALUES('" & Me.txtLast & "', '" & Me.txtFirst & "', '" & Me.txtDOB & "', '" & Me.txtCDL & "', '" & Me.txtCDLEXP & "', '" & Me.txtCDLST & "', '" & _
            Me.txtMC & "', '" & Me.txtMCEXP & "', '" & Me.txtCMP & "', '" & Me.txtHIRE & "', '" & Me.txtSocial & "', '" & Me.txtDrug & "', '" & Me.txtPhone & "')"
                    Else
                'otherwise (Tag of txtSocial store to the Social of Drivers to be notified)
                CurrentDb.Execute "UPDATE tblDrivers " & _
                    " SET tbSocial=" & Me.txtSocial & _
                    ", tbfirst='" & Me.txtFirst & "'" & _
                    ", tblast='" & Me.txtLast & "'" & _
                    ", tbDOB='" & Me.txtDOB & "'" & _
                    ", tbCDL='" & Me.txtCDL & "'" & _
                    ", tbCDLEXP='" & Me.txtCDLEXP & "'" & _
                    ", tbMC='" & Me.txtMC & "'" & _
                    ", tbMCEXP='" & Me.txtMCEXP & "'" & _
                    ", tbCOM='" & Me.txtCMP & "'" & _
                    ", tbHire='" & Me.txtHIRE & "'" & _
                    ", tbDrug='" & Me.txtDrug & "'" & _
                    ", tbPhone='" & Me.txtPhone & "'" & _
                    " WHERE tbSocial=" & Me.txtSocial.Tag
                End If
                'clear form
        cmdClear_Click
        'refresh data in list on form
        frmDriverSub.Form.Requery
    End Sub
    
    
    Private Sub cmdClear_Click()
        Me.txtLast = ""
        Me.txtFirst = ""
        Me.txtDOB = ""
        Me.txtCDL = ""
        Me.txtCDLEXP = ""
        Me.txtCDLST = ""
        Me.txtMC = ""
        Me.txtMCEXP = ""
        Me.txtCMP = ""
        Me.txtHIRE = ""
        Me.txtSocial = ""
        Me.txtDrug = ""
        Me.txtPhone = ""
    
        'focus on ID text Box
        Me.txtSocial.SetFocus
            'set button edit to enable
            Me.cmdEdit.Enabled = True
            'change caption of button add to Add
            Me.cmdAdd.Caption = "Add"
            'clear tag on txtSocial for reset new
            Me.txtSocial.Tag = ""
    
    End Sub
    
    
    Private Sub cmdClose_Click()
        DoCmd.Close
    End Sub
    
    
    Private Sub cmdEdit_Click()
    
    
        'check whether data exists in list
        If Not (Me.frmDriverSub.Form.Recordset.EOF And Me.frmDriverSub.Form.Recordset.BOF) Then
            'get data to text box control
            With Me.frmDriverSub.Form.Recordset
            Me.txtSocial = .Fields("tbSocial")
            Me.txtLast = .Fields("tbLast")
            Me.txtFirst = .Fields("tbFirst")
            Me.txtDOB = .Fields("tbDOB")
            Me.txtCDL = .Fields("tbCDL")
            Me.txtCDLEXP = .Fields("tbCDLEXP")
            Me.txtCDLST = .Fields("tbCDLST")
            Me.txtMC = .Fields("tbMC")
            Me.txtMCEXP = .Fields("tbMCEXP")
            Me.txtCMP = .Fields("tbCOM")
            Me.txtHIRE = .Fields("tbHIRE")
            Me.txtSocial = .Fields("tbSOCIAL")
            Me.txtDrug = .Fields("tbDRUG")
            Me.txtPhone = .Fields("tbPHONE")
             'store social of Driver in Tag of txtSocial in case Social is modified
            Me.txtSocial.Tag = .Fields("tbSocial")
            'change caption of button add to Update
                Me.cmdAdd.Caption = "Update"
                'disable buton edit
                Me.cmdEdit.Enabled = False
                'refresh data in list on form
                frmDriverSub.Form.Requery
    
    
                End With
                End If
    
    
    End Sub


  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Suggest you review the Form BeforeUpdate as mentioned by Micron in the related post. It seems to me you would not want the ADD button to appear until all required fields had proper values.

    Also that you include Option Explicit as second line at top of every module.

    You could provide a copy of your database with some sample data and instructions on how to reproduce an error that is giving you concern. Best to provide a copy of the database in zip format.

  3. #3
    programmingnewby is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jul 2019
    Posts
    9
    Now i believe i broke the rest of it by manually adding the phone option. This is a little different from my earlier post, this time the 'ADD' button has simply stopped showing up when the form is first opened. I can try adding a sample but parts of the database are filled with private information.

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Don't post anything private/confidential. Either drop the tables with such data if that is possible/practical or anonymize the data for this particular copy. It seems only a few records would be needed for readers to review/test the specific irritant. Still, instructions as to what is needed; what is observed and what is wrong/unintended are important.
    Good luck.

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    If only there was a way to obscure private data...

    still not using code tags, I see.

  6. #6
    programmingnewby is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jul 2019
    Posts
    9
    Apologies, I am very new to the forum.

    I created a info-free version but does not let me upload the ZIP, i may be doing something wrong. I have tried using the code tags, let me know if that's what you mean.

    CORRECTED LINK*** LAST LINK MISSING PHONE FIELD: https://drive.google.com/open?id=1vp...E_0C6Q4Te3XW_l

  7. #7
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I have tried using the code tags, let me know if that's what you mean.
    Yes! Perfect. Thanks for the modification.
    There is a 2 Mb (I think) attachment size limit. How big is the db after you compact/repaired and zipped your copy.
    FYI - Some won't download from such sites; some are at work and can't. If it's Google or OneDrive, I'm OK with it but don't like downloading from file sharing sites.

    Wait - it's empty and still nearly 12 Mb??
    Will have to look then see if I have any questions...

    EDIT - You might want to remove your name from the db properties?
    and either provide the main form and remove the reference to the application icon. Neither are available and do cause slight compacting issue.
    update - you are storing objects in your db - ill advised for other reasons, but as for the db size, they remain even when you remove their tables.

  8. #8
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    The 'ADD' driver button should be available whenever the social field is blank, but now does not show up until I purposely cause an error by leaving it blank and pressing update
    There must be code or a macro that you haven't provided (or it's the mssing startup form) that causes it. Once I resized everything to fit my monitor, the buttons are there when form is opened.

    Maybe the most important question is, why all the code instead of just binding the form to the table? If that's not necessary, might as well start over and forget why that code doesn't work as expected rather than spending time sifting through it.

  9. #9
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    OK, long reply that I've been composing while playing. Some of it may have been rendered pointless due to the changes but I'm going to leave it as is, plus let you know that I am out of free time in this one. It's been about 2 hours and I'm basically nowhere. I think that when you take this all in you'll understand.
    **************
    OPTION EXPLICIT - not there, I think already mentioned (Orange?) so best not to forget.

    Outdated:
    I found no data input controls with tag property values so why this next line?
    If Me.txtSocial.Tag & "" = "" Then


    The logic seems a bit flawed for determining whether or not "this is new" unless a user cannot ever delete an existing SSN (if that's what that field is for). If they can, then the operation at hand is not a "new" but is an edit that will be treated as a "new"? HOWEVER given the fact that there is no Tag property value that I could find, at least, that block of code will never run.

    If it's supposed to be If Me.txtSocial & "" = "" Then

    INSERT INTO tblDrivers(tbLast,....VALUES('" & Me.txtLast & "', '" & Me.txtFirst <<all those fields are empty so you're not only attempting to insert "" everywhere (including numeric & date fields) but it fails silently because you have CurrentDb.Execute "UPDATE tblDrivers ..." instead of
    CurrentDb.Execute "UPDATE tblDrivers ..." ,dbFailOnError

    I have spent over an hour on this (maybe half of it making forms so that I could see and navigate) and I'm only on the 4th line of code. While doing so, I see you've altered the link db and THAT one has a numeric tag value which STILL means that block of code will never run. Plus now the Add button per your original post is gone. I could try to figure out which one replaces it but...

    In the edit event, you're setting all the form controls to be what's already there? I've never seen code that refers to a form's recordset that way - always has been the recordset clone. Kinda surprised it works, if it does.

    Hope someone else will chime in, but you might have to fix a few things first
    - import the tables, forms etc into a new db and see if that dumps the image objects that I can see
    - upload the db here
    - say why you can't just bind the form (because it seems that there is a lot of unnecessary code that has issues)

    Good luck with your project.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  10. #10
    programmingnewby is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jul 2019
    Posts
    9
    https://drive.google.com/open?id=1y6...cVEF7zpHMhHDnL

    Name Removed***

    The problem was when trying to ADD a driver, the button ADD was not appearing. Up until i went ahead and cause an error by trying to 'EDIT / UPDATE' with no social.

    And yes as you said above, there should be no additions allowed without a social security number.

    Will go ahead and look through all recommendations, thank you for your time!

  11. #11
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    COPY FOR FORUM davegri-v01.zip

    You can save a lot of this hassle if you use a bound form. A split form can look just like your setup. See attached.
    Copied everything to new empty DB to get the size down from 10MB to less than 1MB.

    Edit: Forgot to mention that for your original form, I got the update and insert queries working OK, mostly by employing correct delimiters. You need to add data validation, because the queries will fail for blank or inconsistent data entry.
    The SSNO cannot be a long integer, because it can't hold numbers that big. Changed it to short text.
    I find you presentation of the buttons very confusing to the users. How are they to know to click "Clear Form" to add a new driver?
    Last edited by davegri; 07-16-2019 at 10:22 AM.

  12. #12
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Do you have a description of the steps involved in your process?
    Do you know which fields must be populated and the range of acceptable values for them? It's a good starting point to get meaningful error and info messages to make form interaction/interface user friendly.

    You can record and/or display all Capital letters via code.
    Hopefully those who will use the forms and system understand the names you have given to controls on forms.

    I agree that bound forms seems a more straight forward approach.

    Good luck with your project.

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

Similar Threads

  1. combobox causing update or cancelupdate error
    By AccessThomas in forum Access
    Replies: 3
    Last Post: 05-17-2016, 12:50 PM
  2. StrConv causing a read only error?
    By snipe in forum Forms
    Replies: 3
    Last Post: 08-12-2014, 11:02 AM
  3. Replies: 5
    Last Post: 04-29-2014, 06:42 PM
  4. Replies: 11
    Last Post: 11-13-2011, 06:57 PM
  5. Blank column causing errors on import
    By brickballer in forum Import/Export Data
    Replies: 7
    Last Post: 05-20-2011, 12:54 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