none
One for for Add and Update for SQL Server linked table RRS feed

  • Question

  • Hi,

    I need to display 1 record at a time of SQL Server table in Access Form. I should allow that record to be updated and also allow add new record into the table. I am able to do this if my form's record Source is set to 'Table'. If I make it as qurery builder and query the table by using where clause, form allows neither updates nor additions. I understand it becomes select query.

    I need to filter my data from table before coming into the form.

    How can I achieve table filter, allow update of record and add new record to the table

    Thank You

    Monday, May 8, 2017 7:57 PM

Answers

  • Hi,

    Just curious... What happens if you used the form's Filter property rather than modifying its Record Source with a WHERE clause?

    • Marked as answer by Spunny Wednesday, May 10, 2017 2:37 AM
    Monday, May 8, 2017 8:09 PM
  • Hi Spunny,

    if filter property not work then I don't think there is any other way available to load specific data that allows you to insert and update.

    I will try to search further let you know about that if I find any useful thing.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Spunny Wednesday, May 10, 2017 2:37 AM
    Tuesday, May 9, 2017 7:30 AM
    Moderator
  • One simple way is to NOT include the where clause in the query.

    Simply set the forms filter property (which is a SQL where clause without the word “where”) to the desired record to display. At that point, you can thus still use the forms “navigation” buttons to add a record. And this is quite nice since then you don’t have to create or modify the forms query or “sql” part.

    From an “ease” of use interface, I often suggest building some kind of “search” form in which the user can type in a bit of the company name, and then “display” the results. I used a continues items form, and built something like this:

    So like google or some such, you type in a few chars of the company and then display a list of search results. You can see in above the “glasses” icon can be clicked on allowing the user to then edit one record by simply clicking on a button.

    The code behind the “edit” button to launch your “edit details” form is easy:

    Docmd.OpenForm "frmCust",,,"id = " & me!id

    The above is a nice interface since it encourages the user to always search first – if they don’t see the company in question then they click on the add button. It also great for seeing accidental duplicates etc. The add button simply opens the form in add record mode.

    Regards,
    Albert D. Kallal
    Edmonton, Alberta Canada

    • Marked as answer by Spunny Wednesday, May 10, 2017 2:37 AM
    Tuesday, May 9, 2017 9:36 PM

All replies

  • Hi,

    Just curious... What happens if you used the form's Filter property rather than modifying its Record Source with a WHERE clause?

    • Marked as answer by Spunny Wednesday, May 10, 2017 2:37 AM
    Monday, May 8, 2017 8:09 PM
  • Thank you for response. I tried that Filter property of a form. Nothing happens. It shows all database records. OR I may not be setting the property properly.
    Tuesday, May 9, 2017 1:34 AM
  • Hi Spunny,

    if filter property not work then I don't think there is any other way available to load specific data that allows you to insert and update.

    I will try to search further let you know about that if I find any useful thing.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Spunny Wednesday, May 10, 2017 2:37 AM
    Tuesday, May 9, 2017 7:30 AM
    Moderator
  • One simple way is to NOT include the where clause in the query.

    Simply set the forms filter property (which is a SQL where clause without the word “where”) to the desired record to display. At that point, you can thus still use the forms “navigation” buttons to add a record. And this is quite nice since then you don’t have to create or modify the forms query or “sql” part.

    From an “ease” of use interface, I often suggest building some kind of “search” form in which the user can type in a bit of the company name, and then “display” the results. I used a continues items form, and built something like this:

    So like google or some such, you type in a few chars of the company and then display a list of search results. You can see in above the “glasses” icon can be clicked on allowing the user to then edit one record by simply clicking on a button.

    The code behind the “edit” button to launch your “edit details” form is easy:

    Docmd.OpenForm "frmCust",,,"id = " & me!id

    The above is a nice interface since it encourages the user to always search first – if they don’t see the company in question then they click on the add button. It also great for seeing accidental duplicates etc. The add button simply opens the form in add record mode.

    Regards,
    Albert D. Kallal
    Edmonton, Alberta Canada

    • Marked as answer by Spunny Wednesday, May 10, 2017 2:37 AM
    Tuesday, May 9, 2017 9:36 PM
  • Thank you all for suggestions. Setting Filter property in property sheet didn't work for some reason. Not sure what I am doing wrong. So, I added code in Form OnLoad event and set filter property. This worked for me. After running, I see that Filter property is filled out at design time also.
    Wednesday, May 10, 2017 2:37 AM
  • Hi,

    Congratulations! Glad to hear you got it sorted out. Good luck with your project.

    Wednesday, May 10, 2017 2:40 PM