Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2003
    Posts
    24

    Post Unanswered: Subforms with queries won't allow me to add a new record with MSSQL backend

    Hi everyone,

    I've upsized my access database to MSSQL and now I have some problems in my project.

    In the subforms where I had a dropdown list with a query as a row source before (with the Access backend), I could had a new record in the subform.
    Now that we moved to a MSSql backend, I can't do that anymore. And I've checked that AllowAdd is set to Yes in the properties of the subform.
    I've then noticed that if I just put the name of the table upon which the query is based, it is then possible to add a new record...
    The trouble is that I had some queries based on several table using join or some queris with some order by clauses and apparently and I have no idea how I could solve this.

    I guess (hope) I'm not the only one to encounter this problem. So if anyone of you did, I would really appreciate any advice.

    Thanks in advance,

    Victor

  2. #2
    Join Date
    Feb 2003
    Location
    Auckland, NZ
    Posts
    150
    You may want to check that all the tables in your query has a primary key.
    JJ Kennedy
    Double J IT Solutions
    www.doublejit.co.nz

    VB 6, VB.NET, ASP.NET, MS SQL Server, MySQL, MS Access

  3. #3
    Join Date
    Apr 2003
    Posts
    17

    Re: Subforms with queries won't allow me to add a new record with MSSQL backend

    Originally posted by thorgnole
    Hi everyone,

    I've upsized my access database to MSSQL and now I have some problems in my project.

    In the subforms where I had a dropdown list with a query as a row source before (with the Access backend), I could had a new record in the subform.
    Now that we moved to a MSSql backend, I can't do that anymore. And I've checked that AllowAdd is set to Yes in the properties of the subform.
    I've then noticed that if I just put the name of the table upon which the query is based, it is then possible to add a new record...
    The trouble is that I had some queries based on several table using join or some queris with some order by clauses and apparently and I have no idea how I could solve this.

    I guess (hope) I'm not the only one to encounter this problem. So if anyone of you did, I would really appreciate any advice.

    Thanks in advance,

    Victor
    Hi buddy
    I am also facing the same problem(refer my post: JPainuthara 8-May- 2003). Somebody should report this to Microsfot to correct this bug in next version. I had a work arround with all the the 5 joined tables used in subform query where rebuilt as 5 different subforms and had to compromise with the layout. Thats a nightmare i know. Let me knwo that did u set the unique table property of subform? if u didn't. try that
    all the best

  4. #4
    Join Date
    Feb 2003
    Posts
    24

    Thumbs up

    Thx Machado! But I usually tend not to let any table without any primary key (which is,as far as I'm concerned, a basic rule in database design). That's one of the first thing I've checked though assuming that I could have forgotten this table. But no, there was a primary key.

    So, as JPainuthara suggested, I guess that it's a Microsoft issue...

    A workaround we found is to set the where and order by clauses in VB code (event procedure) on Load event. This is quite convenient in our case. But I guess it doesn't help in JPainuthara's case as you may have some information from the five tables in your select clause. I'll let you know if I find an easier workaround than doing five subforms, which, as you told, must be an awful nightmare... I didn't check every parts of the software with the MSSQL backend yet so I may encounter the same problems as you...

    Thanks to both of you!! It's reconforting to get some support when you think that you're desperatly alone to struggle with a problem and that you probably missed something.

  5. #5
    Join Date
    Feb 2003
    Posts
    24
    Apparently it works fine if you select every fields of every tables used in your query:

    If you have a query with Tbale1 and table2, your query in your subform should look like this:

    SELECT Table1.*, Table2.* FROM Table1 INNER(or LEFT, RIGHT) JOIN Table2 ...

    Hope it helps some of you,

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •