none
Updating a recordset using Form Variables

    Question

  • The table I am trying to update is called tbl_KPIMain. I have a form the has txtMonth and txtYear that is used to set the criteria to insert into the table. I then have  another text box called txtRidgefieldMonthlyStoreroomRevenue that I need to update that column. Below is my code and I think it is a concatenation issue so I removed all the quotes as not to confuse anyone here. I have more fields that I need to update but just keep it simple I am using just one example below.

    Month is a text format

    Year is a text format

    RidgefieldMonthlyStoreroomRevenue is a number format.

    Private Sub cmdAddRecord_Click()

            Dim rs As Recordset
           
            
            Set rs = CurrentDb.OpenRecordset("SELECT * FROM [tbl_KPIMain] where [Month] = me.txtMonth.value and [Year] = me.txtYear.value")
                
                rs.Edit
                
                rs![RidgefieldMonthlyStoreroomRevenue] = Me.txtRidgefieldMonthlyStoreroomRevenue.Value

                
                rs.Update
                rs.Close
                Set rs = Nothing
                    
                MsgBox "Record Updated in database"
                
                DoCmd.Close , , acSaveNo
                
    End Sub

    Any help would be greatly appreciated. I am not sure if I am using the right code or not.

    Mike

    Wednesday, August 28, 2013 11:34 AM

Answers

  • Try this:

    Dim rs As DAO.Recordset
    Dim strSQL As String

    strSQL = "SELECT * FROM tbl_KPIMain" & _
                 " WHERE Month = '" & Me!txtMonth & "' And Year = '" & Me!txtYear & "'"

    Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)

    BTW You should not use reserved words like "Month" and "Year" as field names. (see the list at http://www.allenbrowne.com/AppIssueBadWord.html).

    cu
    Karl
    *********
    Access-FAQ (German/Italian): http://www.donkarl.com

    Wednesday, August 28, 2013 1:12 PM

All replies

  • If I made this confusing please let me know and I will reword.
    Wednesday, August 28, 2013 12:07 PM
  • Try this:

    Dim rs As DAO.Recordset
    Dim strSQL As String

    strSQL = "SELECT * FROM tbl_KPIMain" & _
                 " WHERE Month = '" & Me!txtMonth & "' And Year = '" & Me!txtYear & "'"

    Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)

    BTW You should not use reserved words like "Month" and "Year" as field names. (see the list at http://www.allenbrowne.com/AppIssueBadWord.html).

    cu
    Karl
    *********
    Access-FAQ (German/Italian): http://www.donkarl.com

    Wednesday, August 28, 2013 1:12 PM
  • It is generally easier to trouble shoot database tasks using record sets rather than visual code. 

    If you are open to this approach: create a query using the design query feature that results in the correct records of your criteria from your form.  Name that query CriteriaFind

    This you can test easily standalone.  (Note that if your form is unbound that the criteria fields may need to be defined explicitly in your query.)

    Once CriteriaFind works - then create an Update Query again using the design query feature that uses CriteriaFind linked correctly the appropriate table; and give that a name i.e. WriteRecords

    This is also easily testable standalone.

    Once those work - then implement with visual: docmd.openquery "WriteRecords" at the appropriate event.

    Hope this helps.

     
    Wednesday, August 28, 2013 1:18 PM
  • The code worked perfectly. I really appreciate your time and efforts in helping me.

    Best Regards

    Mike

    Wednesday, August 28, 2013 1:27 PM
  • Thanks very much for your explanation. I tend to make thinks harder than needed sometimes but I am trying to hone my VBA skills but am having a terrible time with understanding concatenation.

    Thanks and I will try yours on the next one.

    Mike

     
    Wednesday, August 28, 2013 1:40 PM
  • Another solution may be this


     CurrentDb.execute "UPDATE  [tbl_KPIMain] SET  RidgefieldMonthlyStoreroomRevenue] = " & Me.txtRidgefieldMonthlyStoreroomRevenue.Value  & " where Month = '" = me.txtMonth.value & "' and Year ='"  &  me.txtYear.value & "' "

                

    try and tell us

    deneg_nhj

    Wednesday, August 28, 2013 7:24 PM