Page 3 of 3 FirstFirst 123
Results 31 to 42 of 42
  1. #31
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    The implication is that there is no textbox on the form with that name. Check them all, because I've seen it highlight the wrong one within a string like that.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  2. #32
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Just a suggestion:

    This is how I would create the VBA. This makes it a lot easier to debug the SQL statements.
    Code:
    Private Sub cmdAdd_Click()
        Dim sSQL As String
    
        If Me.txtTemplateID.Tag And Me.txtItemID.Tag & "" = "" Then
            sSQL = "INSERT INTO LABELS (TemplateID, ItemID, DescLine1, DescLine2, LotSNSym, Qty, OriginStmnt, Separator, Company, Note1, SterileSym) "
            sSQL = sSQL & " VALUES('" & Me.txtTemplateID & "','" & Me.txtItemID & "','" & Me.txtDescLine1 & "','" & Me.txtDescLine2 & "','" & Me.txtLotSNSym & "','" & Me.txtQty & "','" & Me.txtOriginStmnt & "','" & Me.txtSeparator & "','" & Me.txtCompany & "','" & Me.txtNote1 & "','" & Me.txtSterileSym & "');"
            
            Debug.Print sSQL   '<<--comment out after debugging
            CurrentDb.Execute sSQL, dbFailOnError
        Else
            sSQL = "UPDATE LABELS"
            sSQL = sSQL & " SET DescLine1='" & Me.txtDescLine1 & "'"
            sSQL = sSQL & ", DescLine2='" & Me.txtDescLine2 & "'"
            sSQL = sSQL & ", 4LAddress='" & Me.txt4LAddress & "'"
            sSQL = sSQL & ", LotSNSym='" & Me.txtLotSNSym & "'"
            sSQL = sSQL & ", Qty='" & Me.txtQty & "'"
            sSQL = sSQL & ", OriginStmnt='" & Me.txtOriginStmnt & "'"
            sSQL = sSQL & ", Separator='" & Me.txtSeparator & "'"
            sSQL = sSQL & ", Company='" & Me.txtCompany & "'"
            sSQL = sSQL & ", Note1='" & Me.txtNote1 & "'"
            sSQL = sSQL & ", SterileSym='" & Me.txtSterileSym & "'"
            sSQL = sSQL & " WHERE TemplateID='" & Me.txtTemplate.Tag & "' And ItemID='" & Me.txtItemID.Tag & "';"
            
            Debug.Print sSQL     '<<--comment out after debugging
            CurrentDb.Execute sSQL, dbFailOnError
        End If
    
        Call cmdClear_Click
    
        Me.frmLABELSsub.Form.Requery
    
    End Sub

  3. #33
    big24fan is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Oct 2016
    Posts
    21
    I really appreciate all of your help!

    I've got it to a point now where when I click the Add button, if certain required fields are empty, it will pop up a message. So far the conditions that will pop a message are below:

    1. If Any of the 3 required fields are blank in the Labels form, TemplateID, ItemID, and Company.
    2. If a company name is entered in the form that doesn't already have a record in the COMPANIES table.

    The other pop up message I would like if someone tries to enter a TemplateID and ItemID number that are already in the Labels table. Right now, if you do this, it just cycles through with no messages and nothing changes in the table.

    It took me forever to figure out how to get the company name field to be verified, so I think I need so help on this one. I have attached the latest version of my DB for you to try out.
    Attached Files Attached Files

  4. #34
    big24fan is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Oct 2016
    Posts
    21
    OK, so I have been trying everything I can to get this last validation to work. See the attached screenshot of my form. Basically, you will notice that TemplateID and ItemID both say Test in the subform. They also both say test in the main form. If I click Add like that, nothing happens. I would like it to give me a message like shown in the code below. I have tried for hours to get this to work, but it wont. Here is the last script I tried.

    Code:
    If DCount("TemplateID", "LABELS", "(TemplateID = Forms!frmLABELS!txtTemplateID) & [ItemID] = Forms!frmLABELS!txtItemID") > 1 Then
    MsgBox "This Template ID and Item ID combination is already in the LABELS table. Please click Edit to edit that label information."
    So I'm trying to get it to give me a message to let me know that that TemplateID and ItemID combination is already in the table and cannot be added.
    Attached Thumbnails Attached Thumbnails Capture.PNG  

  5. #35
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    You had missing single quotes, missing double quotes, missing ampersands and too many parentheses,.
    If you have have AT LEAST 1 record in the table, but shouldn't have MORE THAN 1, then I think the comparison should be >0 or >= 1.

    Try
    Code:
        If DCount("TemplateID", "LABELS", "TemplateID = '" & Forms!frmLABELS!txtTemplateID & "' AND [ItemID] = '" & Forms!frmLABELS!txtItemID & "'") >= 1 Then
            MsgBox "This Template ID and Item ID combination is already in the LABELS table. Please click Edit to edit that label information."
        End If
    Last edited by ssanfu; 11-01-2016 at 09:40 AM. Reason: corrected error

  6. #36
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Plus you need AND or OR between the fields in the criteria.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #37
    big24fan is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Oct 2016
    Posts
    21
    Thank You. I got the form working like i wanted, then somehow it stopped working.

    If you click the Add button the first time the form opens it works. Every time after that it does not. I was so proud of myself because I got the last validation check I asked you about working on my own, then none of the all of the sudden validation rules are being skipped like crazy when the add button is hit. Do you see anything obvious? What would cause that?
    Attached Files Attached Files

  8. #38
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    @Paul
    Good catch. Thanks. I was going to add the "And", then it slipped my mind. Too early, not enough caffeine.
    {Corrected the code)

  9. #39
    big24fan is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Oct 2016
    Posts
    21
    OK so I found the issue. It was because of the cmdClear_Click I had in the cmdAdd Sub. Do I have that typed right?

  10. #40
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I would point out that your validation tests for Null but your clear code sets controls to a zero length string (""). My test is usually:

    If Len(Me.SomeControl & vbNullString) = 0 Then
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  11. #41
    big24fan is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Oct 2016
    Posts
    21
    Hello Again!

    I have another question... Surprise, Surprise right?

    So on the Delete button, if you try to delete a record that cant be deleted due to relationships and such, how could I add a message box that says that is why it isnt deleting the record instead of it just not doing it.

    An example would be to try to delete the Company listed in the companies table in the attached database. You cant delete it because it is tied to the label in the labels table, but I would like to have a message that says that instead of it just not working.

    Can you help?
    Attached Files Attached Files

  12. #42
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Already addressed:

    Quote Originally Posted by pbaldy View Post
    Oh, and you aren't getting an error because you haven't requested one. The execute method can fail silently, which sometimes you want. You can use dbFailOnError, like:

    Code:
            strSQL = "UPDATE COMPANIES " & _ 
                     "SET 2LAddress='" & Me.txt2LAddress & "'" & _
                     ", 3LAddress='" & Me.txt3LAddress & "'" & _
                     ", 4LAddress='" & Me.txt4LAddress & "'" & _
                     ", Logo='" & Me.txtLogo & "'" & _
                     ", URL='" & Me.txtURL & "'" & _
                   " WHERE CompanyName='" & Me.txtCompanyName.Tag & "'"
            Debug.Print strSQL
    
    
            CurrentDb.Execute strSQL, dbFailOnError
    Add that and error handling:

    http://www.baldyweb.com/ErrorTrap.htm
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Page 3 of 3 FirstFirst 123
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 17
    Last Post: 12-14-2015, 10:23 PM
  2. Replies: 2
    Last Post: 11-12-2015, 02:36 AM
  3. Newbie Import. Update Field of Existing Records from Excel File
    By gedwards913 in forum Import/Export Data
    Replies: 8
    Last Post: 03-12-2015, 07:53 PM
  4. Replies: 6
    Last Post: 01-07-2015, 01:59 PM
  5. Replies: 5
    Last Post: 11-24-2010, 11:46 PM

Tags for this Thread

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