Page 1 of 3 123 LastLast
Results 1 to 15 of 42
  1. #1
    big24fan is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Oct 2016
    Posts
    21

    Newbie Here... Help with building form to update database records.

    Hello. I have an Access database that I need to build a form in that users can use to update data in a selected table. Here is the criteria I need to meet...

    We have a number of tables in the database, each one specific to a certain size label we print here... so our 2x1 labels have a table, our 3x2 labels have a table, and so on. Each one of these tables contains all of the information specific to the different item numbers in it. So in our 2x1 table, we have a column for a item #, and then the columns related to that item number, like Quantity, Description, Location, etc.

    We are looking for a single form that a user can use to update fields by selecting the table name and entering an item number first. This would bring up all of the editable fields specific to that item in the form. They make the changes in the form to the item, then use a button to update the field.



    I am very VERY new to Access and I need help with doing this. Any help would be greatly appreciated.

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Typically you wouldn't have separate tables for products like that, you'd have them in a single table with a field to distinguish them (in your case, the size). With that design you request is simple, you'd filter the form to the desired size. The concept is called normalization, and is vital to good database design:

    Fundamentals of Relational Database Design -- r937.com

    What you ask is possible; it would involve programmatically changing the record source of the form. I'd revisit the design first though.





    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    big24fan is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Oct 2016
    Posts
    21
    Thank You very much for your response. We have taken your advice and have created a single database with all of the items in it. The primary key is now a combo key that is comprised of the Label Template ID and the Item ID. This is so that each item can be used on each template ID if need be.

    So now, what would be the easiest form to create so that items can be found using the TemplateID and ItemID, then all the details of this item updated using the form?

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Try the combo box wizard on a form bound to that table, choosing the third option, "Find a record...". This may also be helpful:

    Baldy-Cascading Combos
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    big24fan is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Oct 2016
    Posts
    21
    Awesome! That worked for selecting the template ID and Item ID's.

    I have attached a screenshot of the form now. I have all of the other fields that I would liek to have populated based on what is selected in the item and template ID fields. Then from there, I would like to be able to update the record from within the form, or add a new record, or delete the selected record. Do you know what I would need to do with this now?

    Click image for larger version. 

Name:	Capture2.PNG 
Views:	34 
Size:	29.6 KB 
ID:	26138

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Did you use the wizard on the second combo, so the form goes to the selected record? I'd do that, and have all of your textboxes be bound to the fields in the table. That handles your edit. You should be able to use the button wizard to add 2 buttons that:

    1) go to a new record
    2) delete the displayed record
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    big24fan is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Oct 2016
    Posts
    21
    OK, I tried all day yesterday to get this too work and I got somethings to work, but something was weird. Nothing was updating in the database until I clicked the pencil icon on the left of the form. It would change to a play button, and when I opened the table to see if anything had changed, the only thing changed was the Template number. That would get the table messed up in a hurry. So essentially, I have ran the form wizard and let it build the form for me. Now if I edit or change any data in any of these fields, it updates the table like it is supposed to. Here are the things I would like to do from here...
    1. Make the TemplateID field a selection list so that a user can use that instead of having to select each record separately using the arrows at the bottom of the form.
    2. Make the ItemID field a selection list based on the selection in the TemplateID field.
    3. Have all of the other fields working as they are now, so that when the TemplateID and ItemID field display any combination of data, the applicable fields are shown and editable.
    4. I would like to use an Add, Delete, Update, Clear, and Close button on the form as well.

    Is all of this possible?
    Click image for larger version. 

Name:	Capture2.PNG 
Views:	32 
Size:	17.4 KB 
ID:	26156

  8. #8
    big24fan is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Oct 2016
    Posts
    21

    Attached file

    I have attached a file that I think if I can get this working I will be ok.

    When using the comboboxes to select records, you will see that using the ID works, but if I select the phone number that is the common phone number for all three people, it goes back to the 1st record instead of using the record selected in the ID field. The ID combobox and the ID txt field should always be the same.
    Attached Files Attached Files

  9. #9
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    It's because there's no unique field in the table. You should be using the ID rather than the phone, but your ID number repeats, so it goes to the first one. If you add a primary key field and use that in the macro, it should work.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  10. #10
    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, you can also use both fields in your where condition:

    ="[ID] = " & Str(Nz([Screen].[ActiveControl].[column](1),0)) & " AND [PHONE] = " & Str(Nz([Screen].[ActiveControl],0))
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  11. #11
    big24fan is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Oct 2016
    Posts
    21
    OK. Thank you. My primary key in this case is the combination of ID and Phone. How would I do that using a combination key?

  12. #12
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Did you see post 10?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  13. #13
    big24fan is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Oct 2016
    Posts
    21
    Yes, thank you. That worked. I think I am at a good place now. I will keep working on it and let you know if I come across any other issues.

  14. #14
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    No problemo.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  15. #15
    big24fan is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Oct 2016
    Posts
    21
    I'm now trying to put these changes into our live database and the embeded macro looks a little different. When I try to add the 2nd criteria, I get type mismatch with an error 2950.

    Here is the code that Access generated automatically when creating the combobox...

    ="[ItemID] = " & "'" & [Screen].[ActiveControl] & "'"

    Here is the code I am came up with to try to make it check 2 criteria...

    ="[ItemID] = " & "'" & [Screen].[ActiveControl].[column](1) & "'" AND "[TemplateID] = " & "'" & [Screen].[ActiveControl] & "'"

    Can you help me with the code for this?

Page 1 of 3 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