Results 1 to 10 of 10
  1. #1
    Join Date
    Mar 2016
    Posts
    5

    Unanswered: Need to update all fields in the existing record

    I have an access 2010 database and the form designed for data entry has a lot of controls (txt-boxes and combo-boxes). This is because this form is a essentially a re-design of a form in excel. There were more than 300 fields so due to the limitation of 256 columns in access I had to split them into 2 tables, tbl1 and tbl2. Most of the important fields are from tbl1 and a lot of unbound controls that do some cost calculations are all mapped to tbl2. These costs are visible to the user to make some informed decisions.
    So basically when I save the record in the form (via a Save button), the click event correctly saves the data in tbl1 and the values from the unbound controls are saved into tbl2 through an insert statement Docmd.Run SQL Insert INTO ....blah, blah.
    So far so good. Once a record is saved this form also allows the user to go to a previously saved record and make some changes and re-save the record.When the user clicks the save button the record in tbl1 is correctly updated but unfortunately the insert statement now adds a new record in tbl2 for all of the unbound controls. I know this is what the insert statement is meant to do so mybad.
    My question is: How do I make the Docmd.Run SQL Insert INTO...statement to first run a check to see if a record already exists (say for any field for example the ID field) and if it finds an existing record then instead of inserting a new record it notifies the user and then updates the old record in tbl2. Conversely if it doesn't find one then it goes ahead and inserts the new record in tbl2.
    OR may be you experts out there have a more ingenious way to resolve this issue. I am quite new to database development access and this is my very first attempt to make my boss happy. I have already spent many sleepless nights over this issue and don't want to give up with trying every avenue. Please help me if you can. Will appreciate a lot.
    Thanks,
    Subrato.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,440
    Provided Answers: 14
    If there is a primary key in Tbl2 (or a foreign key with a unique constraint) you can easily test for the existence of a row in Tbl2 with a determined value in the PK or FK:
    Code:
    If DCount("*", "Tbl2", "PK = " & SomePrimaryKeyValue) > 0 Then
        ' A row with such a value already exists --> Must update.
    Else
        ' A row with such a value does not  exists --> Must Insert.
    End If
    More generally, the design of the database is suspicious. If you're obliged to use 2 tables to store a single row, it's highly probable that the database is not normalized, which is often the case when it consists in a transcription of an Excel sheet. See (among many others): http://www.tonymarston.net/php-mysql...se-design.html
    Last edited by Sinndho; 03-30-16 at 05:55.
    Have a nice day!

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    What i have done in the past is to have a form with various subfirms on a tabbed dialog. Each sub form has say 50 odd question, so for 250 questions youd need 5 tabbed dialg sub forms. But you could gave 100 per tab. But dont crowd the form.
    If its a customer surevey then use a combo box for the each multiple chouce question, user inputs a number for each mc question.

    Bear in mind around 250 in a query, table is when Access starts to choke. Break you data inti chunks tgat are more manageabke, all linked togethere with the survey pk
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Mar 2016
    Posts
    5

    Need to update all fields

    Quote Originally Posted by Sinndho View Post
    If there is a primary key in Tbl2 (or a foreign key with a unique constraint) you can easily test for the existence of a row in Tbl2 with a determined value in the PK or FK:
    Code:
    If DCount("*", "Tbl2", "PK = " & SomePrimaryKeyValue) > 0 Then
        ' A row with such a value already exists --> Must update.
    Else
        ' A row with such a value does not  exists --> Must Insert.
    End If
    More generally, the design of the database is suspicious. If you're obliged to use 2 tables to store a single row, it's highly probable that the database is not normalized, which is often the case when it consists in a transcription of an Excel sheet. See (among many others): http://www.tonymarston.net/php-mysql...se-design.html

    Firstly, thanks a lot Sinndho for responding to my request.
    I have ensured to follow all the rules of normalisation and can guarantee there is no redundant data. As I mentioned that there were too many controls so I had to split the data. Moreover tbl2 has a PK of its own and the PK of tbl1 is the FK in tbl2.
    Some of the fields from tbl1 are mapped to unbound controls that do some calculations based on other tables. These values are then saved in tbl2 (when I click the save button) and map to the record in tbl1 via the PK.
    I adopted this method because the user form allows a combination of certain combo boxes (fields from tbl1) then looks up some costs from other tables and returns the value in unbound text boxes. These then get saved onto tbl2.
    If my method is wrong could you suggest how do I get the fields from tbl2 directly look up costs from other tables (base on some DLookup formulas) and save them onto tbl2. I did not have any luck with calculated fields as the expression builder doesn’t allow me to put formulas like Dlookup, Iif etc…
    Anyway, your solution is great but my only question is what should the Update SQL statement be in case the DCount function returns a value > 0.
    Also looked at the Tonymarston link and realised there is a lot to learn.
    Looking forward to your advise.

  5. #5
    Join Date
    Mar 2016
    Posts
    5

    Need to update all fields

    Quote Originally Posted by healdem View Post
    What i have done in the past is to have a form with various subfirms on a tabbed dialog. Each sub form has say 50 odd question, so for 250 questions youd need 5 tabbed dialg sub forms. But you could gave 100 per tab. But dont crowd the form.
    If its a customer surevey then use a combo box for the each multiple chouce question, user inputs a number for each mc question.

    Bear in mind around 250 in a query, table is when Access starts to choke. Break you data inti chunks tgat are more manageabke, all linked togethere with the survey pk

    Hi Healdem,
    I tried using subforms initially but ran into trouble. This was because when I changed a value on a subform for any particular record, the change automatically reflected for all of the records. I need each record to be unique and the user can make any selection on the subforms which should be specific to that record.
    Can you suggest how this can be done as that would be a great solution?
    Looking forward to your advice.

  6. #6
    Join Date
    Mar 2016
    Posts
    5

    Need to update all fields

    Quote Originally Posted by Sinndho View Post
    If there is a primary key in Tbl2 (or a foreign key with a unique constraint) you can easily test for the existence of a row in Tbl2 with a determined value in the PK or FK:
    Code:
    If DCount("*", "Tbl2", "PK = " & SomePrimaryKeyValue) > 0 Then
        ' A row with such a value already exists --> Must update.
    Else
        ' A row with such a value does not  exists --> Must Insert.
    End If
    More generally, the design of the database is suspicious. If you're obliged to use 2 tables to store a single row, it's highly probable that the database is not normalized, which is often the case when it consists in a transcription of an Excel sheet. See (among many others): http://www.tonymarston.net/php-mysql...se-design.html
    Firstly, thanks a lot Sinndho for responding to my request.
    I have ensured to follow all the rules of normalisation and can guarantee there is no redundant data. As I mentioned that there were too many controls so I had to split the data. Moreover tbl2 has a PK of its own and the PK of tbl1 is the FK in tbl2.
    Some of the fields from tbl1 are mapped to unbound controls that do some calculations based on other tables. These values are then saved in tbl2 (when I click the save button) and map to the record in tbl1 via the PK.
    I adopted this method because the user form allows a combination of certain combo boxes (fields from tbl1) then looks up some costs from other tables and returns the value in unbound text boxes. These then get saved onto tbl2.
    If my method is wrong could you suggest how do I get the fields from tbl2 directly look up costs from other tables (base on some DLookup formulas) and save them onto tbl2. I did not have any luck with calculated fields as the expression builder doesn’t allow me to put formulas like Dlookup, Iif etc…
    Anyway, your solution is great but my only question is what should the Update SQL statement be in case the DCount function returns a value > 0.
    Also looked at the Tonymarston link and realised there is a lot to learn.
    Looking forward to your advice

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so your problem is the code you have in the second form. the one that actually writes the new data as opposed to updating. thats how you have designed it.
    as a first guess you could test to see if the PK is set (assuming that the pk is passed from the master form).
    if its set then update the data
    if its not set then write new data. if its set and invlaid then decide what to do....

    quite seriously I don't understand why you need to have a mixed bound and unbound design. you are making it more complicated than it need be.

    there's good reasons for both approaches, but raely if ever have I found to use both iun the same application.

    Ive used unbound forms with server back ends
    I tend to use bound controls in a JET back end, but not always.

    as said before the survey app, done a long long time ago used tabbed dialogs each with a a subform all tied into a master form. each tab had a hotkey. the speed (and reliability) at which skilled data typists can reach on doing data capture is fearsome (and awe inspiring)
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Moreover tbl2 has a PK of its own and the PK of tbl1 is the FK in tbl2.
    why?
    that is the essential nub of your problem
    if there is a one to one mapping between a row in t1 and t2 then use the same PK. storing the pk of t1 in f2 is redundant. the PK of t1 should be the same as the pk of t2

    a kludgy workaround woulb be to declare a unique constrain on ther FK column in t2.... but why. if its unique its unique so its showing itself as a candidate for being the PK in t2

    one of the banes of people developing oin Access is that all to often then go the easy route of accepting Access suggestion of an autonumber column as a PK. there's good reasons to do that, but not each and every time. its about the design. if you other candidates for a PK are too long, too complex or prone to change then thats a good reason to choose autonumber. just as autonumber is never a good choice if you need to have sequential numbers for things like invoices, GRN's, Delviery Notes and so on.
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by Subrato View Post
    Hi Healdem,
    I tried using subforms initially but ran into trouble. This was because when I changed a value on a subform for any particular record, the change automatically reflected for all of the records. I need each record to be unique and the user can make any selection on the subforms which should be specific to that record.
    did it
    why do you think that happened
    what steps did you take to investigate that

    could it be that becuase you are using unbound controls you forgot to set some controls, or your code updated all rows.

    dealing with unbound controls requirs a good understanding of the Access event model, understanding when events fire, and lots and lots of testign to make certain your code does what you think it does, when you think it should. I'd rarely suggest that anyone attempts at desiging unbound control application untill they have really got to grips with bound forms & reports
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    Mar 2016
    Posts
    5

    Need to update all fields

    [QUOTE=healdem;6642168]so your problem is the code you have in the second form. the one that actually writes the new data as opposed to updating. thats how you have designed it.
    as a first guess you could test to see if the PK is set (assuming that the pk is passed from the master form).
    if its set then update the data
    if its not set then write new data. if its set and invlaid then decide what to do....


    Thanks a lot for your reply Healdem.
    Firstly, I do not have 2 forms. There is only a tabbed form called "frmNewSalesQuote" that has about 6 tabs. This form has a Save button in the header which actually writes the new data into tables tbl1 and tbl2. The form allows a combination of certain combo boxes (fields from tbl1) then looks up some costs from other tables and returns the value in unbound text boxes (that has dlookup formulas and returns a cost that is visible to the user). The second table only stores all of the costs that are passed from the unbound controls in the form. This form also prints out a report (which is the initial quote) that the user sends to the customer.

    Before the quote is finalized, the user might get a call from the customer and then need to open a specific record (which is the quote) and change some combo box selections and then re-save the record. This happens when the customer decides to go with some cheaper options and a new quote is provided to the customer. Note: the quote number remains the same.

    My issue is not mainly about not being able to use sub-forms. The various tabs in the form now has been able to accommodate all of the data and my only problem is that the values from the unbound controls that are saved onto tbl2 through the insert statement do not get overwritten when the user makes changes to an existing quote as I am using an Insert SQL statement instead of checking for an existing record and then updating it if it exists. Note: All of the fields from tbl1 are correctly updated when changes are made to the record (since they are bound controls) but unfortunately the unbound controls (costs for these fields) do not get updated when the record is changed and instead a new record is entered in tbl2 when the save button is clicked.

    Since you also mentioned that the PK of tbl1 should be the same as the PK of tbl2, I did this without any real benefit compared to what I originally had. What I essentially need is that when the save button is clicked the code checks to see if the record already exists in tbl2 and takes the following actions
    UPDATE --> If record already exists
    INSERT -->If record does not exist.
    My sincere apologies if I am not making myself clear when explaining the issue. I am very new to database development and arduously going through my learning curve.
    Looking forward to your advice.

Tags for this Thread

Posting Permissions

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