Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2012
    Posts
    28

    Unanswered: Update form without SQL

    Hi. I have a form called Populate_Progression which uses a temporary table.

    This is a datasheet with "Chapter_No", "Initial_Mapping_Completed" etc. When the user hit Apply it pushes the data to another table.

    When the user populates the "Initial_Mapping_Completed" field for a particular "Chapter_No", and hit Apply, an SQL query updates all rows with that chapter number.

    But they may populate "Initial_Mapping_Completed" in the main form underneath with a whole bunch of values, so they will see more rows for "Chapter_No". Attached is a screengrab. Chapter 19 has two different values, but the user can't change the value in this form because I can't figure out how to without using SQL, and even SQL updates all fields for that chapter no, not that row, as there are no unique ids in the temp table. At the moment they can only update Chapters 20 and 21, which have one value.

    I've spent so long on this and almost want to give up. The SQL works fine for the chapters with one value. For the other scenario where a chapter_no has more than one value I tried

    Code:
    Forms!BM_Product.MapProgression_Subform.Form!INITIAL_MAPPING_COMPLETE = Me.Populate_Progression_Subform.Form.Init_Mapping_Complete
            
            Forms!BM_Product.MapProgression_Subform.Requery
    but the only updates the row that was selected in the main form.

    Sorry if that was confusing. It is difficult to explain.

    Here is the code for that event.

    Code:
    If Me.Populate_Progression_Subform.Form.Init_Mapping_Complete.Enabled = True Then
        Dim StrSQL_Init As String
        
        CountValue_Init = DCount("INITIAL_MAPPING_COMPLETE", "Temp_Progression_Populate", "Chapter_No = '" & Me![Populate Progression Subform].Form!Chapter_No & "'")
         
        If CountValue_Init = 1 Then
         
            StrSQL_Init = "Update dbo_BM_Map inner Join Temp_Progression_Populate on dbo_BM_Map.Product_ID = Temp_Progression_Populate.Product_ID " _
                & "Set dbo_BM_Map.Initial_Mapping_Complete = Temp_Progression_Populate.Initial_Mapping_Complete " _
                & "Where dbo_BM_Map.Chapter_No = Temp_Progression_Populate.Chapter_No " _
                & "And Temp_Progression_Populate.Chapter_No in (Select Temp_Progression_Populate.Chapter_No " _
                & "from Temp_Progression_Populate " _
                & "Group by Temp_Progression_Populate.Chapter_No " _
                & "Having Count(Temp_Progression_Populate.Initial_Mapping_Complete) = 1)"
    
            DoCmd****nSQL (StrSQL_Init)
        
        End If
        
        If CountValue_Init > 1 Then
          
            Forms!BM_Product.MapProgression_Subform.Form!INITIAL_MAPPING_COMPLETE = Me.Populate_Progression_Subform.Form.Init_Mapping_Complete
            
            Forms!BM_Product.MapProgression_Subform.Requery
              
        End If
               
    End If

    Thanks heaps
    Attached Thumbnails Attached Thumbnails 2012-10-15_161425.jpg  

  2. #2
    Join Date
    Mar 2012
    Posts
    28
    I can see my explanation isn't clear. The question out of all of that is -

    can you update rows in a table without using SQL? I tried it using simple VBA, but the field that is selected is only updated.

    The reason why I don't want to use SQL is because the update statement will update all fields for that chapter, not the ones I want. I know an ID field would help, but this is an update table and I don't think it would allow it.

    Thanks

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    being pedantic you cannot update any tables in an Access DB talking toJET tables without using SQL,. even if you don't issue SQL commands directly behind the scenes you can bet whatever you like that the Access runtime is using SQL.

    it sounds to me like your table design isn't properly designed, every table in a relational DB ought to have a primary key (something that uniquely identifies that row. there are exceptions but they are relatively few and far between and are usually bastardisations of 'proper' relational design.

    think on it.you cannot update the data manually unless you have a means of identifying the row uniquely. whether you do that through a proimary key, a n autgenerated key or just a series of terms in your where clause. in order to update a row you need to be able to identify a row
    its all down to the where clause

    looking at your snapshot image would suggest your design isn't normalised. on top of that there is no obvious natural key as primary key, so it would probably be best to crearte a surrogate key using an autonumber column.
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Mar 2012
    Posts
    28
    Thanks for the reply Healdem.

    The table in the snapshot is an update table. I thought about adding a foreign key but I wouldn't want that to be updated and there would be constraints anyway.

    Is it possible to have an update table but not all fields are updatable?

    Thanks
    Josh

  5. #5
    Join Date
    Mar 2012
    Posts
    28
    Actually if I added a foreign key there will be multiple rows for chapter_no's and the update won't work.

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    foreign key, where's that coem form

    you say your problem is that you cannot use SQL to update your table, which is wrong
    'all' you need to do is to find a mechanism that uniquely identifies the row to be updated. either use a primary key (somethign tht makes each row unique or sepcify as many rows as required

    ferinstance
    UPDATE MyTable SET .....blah di blah
    WHERE Product_id = 409 AND CHAPTER_NO=20 AND Chap_Name = "Chapter 19" AND Intiial... = "JG"

    however you need to be aware that string matching can be a pain in the ass as capitalisation and spelling can be problems (its one of the reasons string/text columns are often frowned on when choosing those as possible primary keys).

    or assign an autonumber ID column.

    in either event you can use SQL to update the specific row. the limitation is your design NOT SQL
    I'd rather be riding on the Tiger 800 or the Norton

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
  •