Common mistake; delete the quotes on both sides of AND.
Common mistake; delete the quotes on both sides of AND.
1) I'd have a second set of combos that are bound to the table. The ones you have are unbound, which is correct for searching. You want a second set to be able to add/edit a record.
2) I'd probably have the form bound to the table, not a query, and use this to populate the data from the companies table:
BaldyWeb - Autofill
typically you don't users editing the data in the company table all the time. Your situation may be different.
3) I'd just use the command button wizard to create the Add and Delete buttons. You should already be able to edit the data of an existing record.
I am trying to follow the info at the link you mentioned above, but when I get to this part...
=ComboName.Column(2)
I enter the combo name, then hit ., then type C and all of the selections pop up but Column isnt a selection. Even if I type Column in and then put the number in parenthesis, it shows me the label for the field and not the data I need.
Can you attach the db here?
Here is the updated db.
Sorry, I meant I would use a combo to select company, instead of the existing textbox. You can't use the search combos since they're normally empty.
Alright, so I gave up on the other one I was working on. I couldn't get it to work. I have decided to just use a form for each table and do it the way I saw in a youtube video. The only problem I see with this one is that the edit and update buttons appear to work, but none of the table data is changing. Can you help me with this to see why it isnt actually updating like it should be? It looks like all of the other buttons are working ok. The delete button wont work if there is a record in the labels table that is using a company name, but other than that it seems to work fine. Thanks for all of your help.
Hello, did you see my latest post?
Sorry, sometimes the work for which I get paid intrudes.
The main form is bound to the companies table, which is appropriate, but none of the textboxes are bound to their respective fields. The add button treats the form as unbound, which is why it works. Normally you either want the form bound to the table and let Access do most of the work, or have it unbound and do the work yourself. More here:
http://www.baldyweb.com/BoundUnbound.htm
Thank You and I completely understand about the paid work coming first.
I like the way the unbound form is working, except I am finding that if I select a company that has any labels related to it, the edit button is not updating the table like it should. If it is a new company with no labels, it works fine. IT has to be something in the programming of the form right?
The problem is you've included the company name field in the update SQL. Referential integrity won't let you update that field when there are related records (even if you aren't changing the value). Presuming you wouldn't want to change the name, drop it from the SQL. If you may want to update the name, you can cascade updates in the relationship.
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
Awesome! Thank You! I made that change now. I only see one more thing happening that has me puzzled. Whenever I open the form, and I edit the first record, it works fine the first time. But if I try to change that record back, I get an error message. The only way to change that record back to how it was is to close and reopen the form. Even then, I only have the initial edit to make to that first record before it starts erroring out again. This ONLY happens on the first record of the table. If you select the 2nd record and edit it, you can make updates to it continuously without any errors. Any ideas?
OK, I am working on the bigger table now that uses 2 key fields and I am getting the following error:
Compile Error: Method or data member not found.
This is the code giving me the error (Red Text is highlighted on error):
Code:Private Sub cmdAdd_Click() If Me.txtTemplateID.Tag And Me.txtItemID.Tag & "" = "" Then CurrentDb.Execute "INSERT INTO LABELS (TemplateID, ItemID, DescLine1, DescLine2, LotSNSym, Qty, OriginStmnt, Separator, Company, Note1, SterileSym) " & " VALUES('" & Me.txtTemplateID & "','" & Me.txtItemID & "','" & Me.txtDescLine1 & "','" & Me.txtDescLine2 & "','" & Me.txtLotSNSym & "','" & Me.txtQty & "','" & Me.txtOriginStmnt & "','" & Me.txtSeparator & "','" & Me.txtCompany & "','" & Me.txtNote1 & "','" & Me.txtSterileSym & "')" Else CurrentDb.Execute "UPDATE LABELS " & _ " SET DescLine1='" & Me.txtDescLine1 & "'" & _ ", DescLine2='" & Me.txtDescLine2 & "'" & _ ", 4LAddress='" & Me.txt4LAddress & "'" & _ ", LotSNSym='" & Me.txtLotSNSym & "'" & _ ", Qty='" & Me.txtQty & "'" & _ ", OriginStmnt='" & Me.txtOriginStmnt & "'" & _ ", Separator='" & Me.txtSeparator & "'" & _ ", Company='" & Me.txtCompany & "'" & _ ", Note1='" & Me.txtNote1 & "'" & _ ", SterileSym='" & Me.txtSterileSym & "'" & _ " WHERE TemplateID='" & Me.txtTemplate.Tag & "' And ItemID='" & Me.txtItemID.Tag & "'" End If cmdClear_Click Me.frmLABELSsub.Form.Requery End Sub