Results 1 to 9 of 9
  1. #1
    matey56 is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Jul 2020
    Posts
    136

    Error 2105 You can

    Click image for larger version. 

Name:	Capture.JPG 
Views:	15 
Size:	8.9 KB 
ID:	49637


    Hi all,
    First, sorry about the title. I accidently posted the question while I was typing the title.

    So, I have a form with a dropdown box that when you select a product it populates a subform with that product information. For some reason I'm getting this error when I try to add a record via the Add record button that I coded. Here is the code it's telling me to debug. The weird thing is I use this same code for all my forms that are built almost identical and it works fine.

    Private Sub cmd_add_record_Click()
    Me.AllowAdditions = True
    DoCmd.GoToRecord , , acNewRec
    End Sub

    What am I doing wrong? Is it the query I have for the subform? It connects two tables via the product name field.

  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,399
    it is likely your query is not updateable. Usual rule is one form, one table, you appear to have two. You can sometime get round the problem by going into the subform properties and changing the recordset type to 'dynaset - inconsistent updates'. Whether it works or not depends on the query and what fields you are looking to insert/update

  3. #3
    matey56 is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Jul 2020
    Posts
    136
    Thanks CJ. Yes, my query is not updateable. How can I fixt that? It is true the subform qry has 2 tables. I joined them with the product name field. I need several fields from the second table in my subform. What other options do I have?

    Also, just to test I removed the second table from my subform query but I still get the error when I try to add a record.

  4. #4
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Can you show us the query in design view? You should not join on product name (I assume is a short text data type), but on a numeric ProductID. If the fields you need in the subform come from the tblProduct you can simply make the ProductID a combo and include those fields in its row source. To display them in the subform use textboxes that reference the columns of the combo (they are 0-based) so [cboProductID].Column(2) would be the third column.

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  5. #5
    Join Date
    Apr 2017
    Posts
    1,673
    Is product name a primary key for products table? When yes, then consider a situation, where after couple of year you have to change the name for some product! You have to run update queries for every changed name through all tables which contain product name as foreign key field.

    You must have the products table with (preferably autonumeric) ProductID field, plus fields ProductName, and other fields you need there. In any tables, which must have the link to products table, you will have ProductID as foreign key - and nothing more.

    Now about your form. Why all this hullabaloo with populating the subform? There are really 3 options:
    1. On your main form you have a combo which is linked to ProductID in source table. The RowSource of the combo is a query from your products table (like SELECT ProductID, ProductName FORM tblProducts ORDER BY 2), with BoundColumn set to 1 and COlumnWdths set to "0,2,5"). User sees/selects product name and combo's value is set to ProductID. You add a subform with a single type form based on your products table as source, and in subform's properties (NB! When you drag the form with product info into main form to create a subform, Access automatically names the created subform with same name as the form it is based on. This may be confusing, so my advice is to rename the subform. E.g. when product info form has the name like fProductInfo, rename the subform as sfProductInfo!) you set both LinkMasterFields and LinkChildFields properties to ProductID. It's all!;
    2. You don't use the subform at all. Add unbound controls to your main form, with formulas to read the info from products table matching with ProductID read from combobox control using DLookup function;
    3. Like 2., but in combobox control, in it's RowSource query, you have additional fields you want to display, with their widths set to 0. Add unbound controls to your main form, which read the info from appropriate hidden fields of your combobox.

    Edit! Let's assume you have 2 forms fForm1 and fForm2, and you drag fForm2 into fForm1 as subform. Then really you get 3 objects on different levels - every one of them with it's own set of propertis:
    fForm1 as main form;
    sfForm2 as a control in main form;
    fForm2 as Source of sfForm2.

  6. #6
    matey56 is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Jul 2020
    Posts
    136
    Okay my head is spinning a bit.

    Here is my subform query in design view. The Product_Name fields are not the PK. the PK is Last_Modified with is a an auto date/time field.

    Click image for larger version. 

Name:	Capture.JPG 
Views:	14 
Size:	103.6 KB 
ID:	49638

  7. #7
    Join Date
    Apr 2017
    Posts
    1,673
    Quote Originally Posted by matey56 View Post
    Here is my subform query in design view. The Product_Name fields are not the PK. the PK is Last_Modified with is a an auto date/time field.
    It looks like you have serious DB design problems here! Whatever it is what you have, it is not a normally designed relational database!

  8. #8
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,399
    the PK is Last_Modified with is a an auto date/time field.
    not a good field to use as a PK. Dates are stored as doubles and doubles are imprecise.

    I note your tables have a dbo_ prefix - so are your tables in sql server?

  9. #9
    matey56 is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Jul 2020
    Posts
    136
    Quote Originally Posted by CJ_London View Post
    not a good field to use as a PK. Dates are stored as doubles and doubles are imprecise.

    I note your tables have a dbo_ prefix - so are your tables in sql server?
    Yes they are in SQL server.

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

Similar Threads

  1. Proactive 2105 error for bof/eof?
    By twgonder in forum Programming
    Replies: 5
    Last Post: 10-22-2022, 06:25 AM
  2. Replies: 5
    Last Post: 08-26-2021, 05:06 PM
  3. Run-time error 2105
    By flantrains in forum Programming
    Replies: 3
    Last Post: 12-09-2019, 12:53 AM
  4. Error 2105 all of a sudden
    By TaliaKlein in forum Forms
    Replies: 7
    Last Post: 11-09-2015, 10:47 AM
  5. Replies: 5
    Last Post: 08-22-2012, 04:27 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