Page 1 of 2 12 LastLast
Results 1 to 15 of 25
  1. #1
    PinkLady50 is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2017
    Posts
    171

    Not all fields are writing to db from form

    I have a basic form that is capturing the following fields.


    COD, Manager, Staff, Contract_Number, Fiscal-Year, Quarter, Date_Opened, Dated_Closed, Category, Problem, Description

    I noticed that 3 fields; StaffID, ProblemID and CategoryID are not writing to the Problem_Record tbl so I added them to the bottom of the form and changed Visible to No.



    I have added them into the code below but it is still not writing them. I have attached a copy of my form.
    StaffID, ProblemID and CategoryID are each part of separate tables and are linked to the Problem_Record tbl.

    How do I get it to lookup the corresponding ProblemID from the Problem tlb and add that to the Problem_Record tbl?

    Private Sub cmdAdd_Click()
    'add data to table
    CurrentDb.Execute "INSERT INTO problem_record (cod, manager, staff, staffID, problemID, categoryID, contract_number, fiscal_year, quarter, date_opened, problem, category, description)" & _
    " VALUES(" & Me.COD & "','" & Me.Manager & "','" & Me.Staff & "','" & Me.StaffID & "','" & Me.ProblemID & "','" & Me.CategoryID & "',' " & Me.Contract_Number & "','" & Me.Fiscal_Year & "','" & Me.Quarter & "','" & Me.Problem & "','" & Me.Category & "','" & Me.Date_Opened & "','" & Me.Description & "'"")"
    Attached Thumbnails Attached Thumbnails Input Form.PNG  

  2. #2
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    StaffID should be bound to the Staff combobox, you don't carry both on your table.

    ProblemID should be bound to your Problem combobox, same reason.

    Same for CategoryID.

  3. #3
    PinkLady50 is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2017
    Posts
    171
    Many Thanks!

  4. #4
    PinkLady50 is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2017
    Posts
    171
    I changed the Category Row Source query to include the CategoryID. I also deleted CategoryID from the Input_Form.

    SELECT problem_record.Category FROM category INNER JOIN problem_record ON category.CategoryID = problem_record.CategoryID;

    It's still not working. Should I be doing this some place else?

  5. #5
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Where is that SQL? Is that the combobox?

  6. #6
    PinkLady50 is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2017
    Posts
    171
    It is the SQL for the Category combobox.
    I also put Category_categoryID to the form (Visible No) - Control Source Category_categoryID
    Columns 2
    2";0"
    SELECT problem_record.Category, problem_record.CategoryID FROM problem_record ORDER BY Category;

    Private Sub Category_AfterUpdate()
    Me!CategoryID = Me!Category.Column(1)
    End Sub


    It is now picking up the CategoryID and writing it to the record but my Category list is not drawing from the table. It is drawing from the records.

  7. #7
    PinkLady50 is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2017
    Posts
    171
    So now the ID is working but the list in the combobx is not grabbing the right list.

  8. #8
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    The SQL for the comboboxes must each have two columns - ID first and value (staff/problem/category) second. The ID will have a column width of zero. When you select a value the first column will be the one stored on the table, which is the control source.. Remove the three fields you had hidden at the bottom.

  9. #9
    PinkLady50 is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2017
    Posts
    171
    It's putting a 6 in Category and CategoryID instead of the text for Category. So close!

  10. #10
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Do you have both fields on the table? Where is it getting Category from?

  11. #11
    PinkLady50 is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2017
    Posts
    171
    Category tbl
    CategoryID
    Category

    Problem_Record has other fields but also includes
    CategoryID
    Category

  12. #12
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Like I said, only the ID should be stored on the table, that is one of the three main database normalization rules - non-repetition.

  13. #13
    PinkLady50 is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2017
    Posts
    171
    SELECT category.CategoryID, category.Category FROM category ORDER BY Category;

    This is the Category Row Source that was working before I tried to add the ID field.

  14. #14
    PinkLady50 is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2017
    Posts
    171
    so Problem_Record should only have the CategoryID and to tie to the Category table and I should delete the Category field from Problem_Record?

  15. #15
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    That SQL is correct. The control source should be CategoryID, the columns should be 2 with the first one zero width. This way the CategoryID will be stored on the table correctly but the Category won't. If you really want it you will have to add an AfterUpdate routine to add it to the table (strongly recommend against it, however!).

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 08-04-2015, 01:44 PM
  2. Writing Formula in query on existing fields
    By Esmatullaharifi in forum Queries
    Replies: 1
    Last Post: 04-14-2015, 07:29 AM
  3. Replies: 4
    Last Post: 12-09-2014, 02:01 AM
  4. Replies: 23
    Last Post: 11-06-2014, 02:27 PM
  5. Replies: 16
    Last Post: 12-08-2012, 07:44 AM

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