Page 2 of 3 FirstFirst 123 LastLast
Results 16 to 30 of 42
  1. #16
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Common mistake; delete the quotes on both sides of AND.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  2. #17
    big24fan is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Oct 2016
    Posts
    21
    Ok, so I have gotten the comboboxes to work like I need them to... mostly. I have attached a picture and the database I am working on. Any insight on completing the remaining functionality would be great and VERY Appreciated.
    Click image for larger version. 

Name:	Untitled.png 
Views:	18 
Size:	33.8 KB 
ID:	26187
    Attached Files Attached Files

  3. #18
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    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.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  4. #19
    big24fan is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Oct 2016
    Posts
    21
    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.

  5. #20
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Can you attach the db here?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #21
    big24fan is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Oct 2016
    Posts
    21
    Here is the updated db.
    Attached Files Attached Files

  7. #22
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    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.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  8. #23
    big24fan is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Oct 2016
    Posts
    21
    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.
    Attached Files Attached Files

  9. #24
    big24fan is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Oct 2016
    Posts
    21
    Hello, did you see my latest post?

  10. #25
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    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
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  11. #26
    big24fan is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Oct 2016
    Posts
    21
    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?

  12. #27
    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 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.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  13. #28
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    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
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  14. #29
    big24fan is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Oct 2016
    Posts
    21
    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?

  15. #30
    big24fan is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Oct 2016
    Posts
    21
    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

Page 2 of 3 FirstFirst 123 LastLast
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