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.
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.
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
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.
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.
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.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."
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
Plus you need AND or OR between the fields in the criteria.
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?
@Paul
Good catch. Thanks. I was going to add the "And", then it slipped my mind. Too early, not enough caffeine.
{Corrected the code)
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?
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
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?
Already addressed:
Add that and error handling: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
http://www.baldyweb.com/ErrorTrap.htm