Results 1 to 3 of 3
  1. #1
    vmbarlow is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2021
    Posts
    4

    Problems adding a new record to a table using forms

    GOAL: Use an Access form to add new records to an Access table



    APPROACH: Access Form (based on a query) created by form wizard. 2 text boxes (member name & payment type), converted to combo boxes, populated by SQL statements in row source. I also changed column count and column width for member name.

    PROBLEMS:

    1. In the member name combo box, I do get a list of members (last name concatenated with comma-space first name) but in the combo box, I can highlight but NOT SELECT any names from the list.
    2. The date of payment field DOES display the calendar icon to the right of the field but when I select a date from the popup calendar, that date DOES NOT populate back to the textbox. Again, not selectable.
    3. How do I physically force the new record to be added to the table? PK: Member ID + payment date (the member id is in the SQL combo box select statement but hidden, column width 0 – does the member id value update automatically?)

    Click image for larger version. 

Name:	payment form - design view.JPG 
Views:	19 
Size:	110.6 KB 
ID:	46664Click image for larger version. 

Name:	member name properties.JPG 
Views:	18 
Size:	53.6 KB 
ID:	46665

  2. #2
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Your form should be bound to an updatable query (or less desired IMO, a table). If it's not, then that's the first fix. Looks like it is because you no doubt used the form wizard and accepted that Access named your controls with the field names they're bound to. However, you test adding records to a query first when using that as the form recordsource. If you cannot add/edit then you can't use said query as the form recordsource and would have to address that.

    You're showing Expr1 in the name control, what is that? If it's an expression in the control's controlsource, you cannot bind such calculated form controls to table/query fields.
    does the member id value update automatically?)
    Can only do so if the control is bound to a field and the form to a table/query. This happens if any of the following take place (after editing any form fields)
    - you close the form
    - you navigate to or select another record
    - you Save (from the ribbon or Ctrl+S)
    - you do so via code

    At the moment, no clear idea why you can't pick a date and show it in the textbox. You've set its locked property to true?

    Suggest you do not use special characters (save for underscore) or spaces in any object names. I think you'd find much of the following very informative and helpful as you're making typical fundamental mistakes in design ...

    Normalization Parts I, II, III, IV, and V
    http://rogersaccessblog.blogspot.com...on-part-i.html
    and/or
    http://holowczak.com/database-normalization/

    Entity-Relationship Diagramming: Part I, II, III and IV
    http://rogersaccessblog.blogspot.com...ng-part-i.html

    How do I Create an Application in Microsoft Access?
    http://rogersaccessblog.blogspot.com...cation-in.html

    Important for success:
    Naming conventions - http://access.mvps.org/access/general/gen0012.htm
    https://www.access-programmers.co.uk...d.php?t=225837

    What not to use in names
    - http://allenbrowne.com/AppIssueBadWord.html

    About Auto Numbers
    - http://access.mvps.org/access/general/gen0025.htm

    The evils of lookup fields - http://access.mvps.org/access/lookupfields.htm
    Table and PK design tips - http://www.fmsinc.com/free/newtips/primarykey.asp
    About calculated table fields - http://allenbrowne.com/casu-14.html
    About Multi Value Fields - http://www.mendipdatasystems.co.uk/m...lds/4594468763
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    What are the form properties, like AllowEdits, AllowAdditions?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 04-21-2014, 06:52 AM
  2. Problems With Adding Data Back To Table
    By jimbob90 in forum Access
    Replies: 10
    Last Post: 09-30-2012, 05:28 PM
  3. Replies: 4
    Last Post: 08-14-2012, 07:14 AM
  4. Replies: 6
    Last Post: 07-02-2012, 02:53 PM
  5. Replies: 18
    Last Post: 11-09-2010, 02:39 PM

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