0

There is a main form bound to the main table, public_OUT_Detalle_PMC. And there is a form bound to a smaller table, public_ficha_negocio, which is a selection of fields in the bigger table. This second form is used as a subform, and I want to save the edits made to the subform onto the main table.

I have tried to use a simple SQL Update query, but for some unkown reason I can't make it work and it is driving me crazy.

    Dim db As DAO.Database
    Dim qdfFN As DAO.QueryDef
    Dim rsFN As DAO.Recordset
    Set db = CurrentDb
    Set qdfFN = db.CreateQueryDef("")
    qdfFN.sql = "select * from [public_ficha_negocio] where [Codigo_RQM_Necesidad]='" & Me.Codigo_filtro.value & "';"
    qdfFN.ReturnsRecords = True
    Set rsFN = qdfFN.OpenRecordset
    Dim qdf As DAO.QueryDef
    Dim sSQL As String
    sSQL = "UPDATE [public_OUT_Detalle_PMC] SET " & _
              {a lot of fields are updated, I cut them out of the paste for simplicity}
              "WHERE [public_OUT_Detalle_PMC].[Codigo_RQM_Necesidad] = '" & rsFN!Codigo_RQM_Necesidad & "');"
    Set qdf = db.CreateQueryDef("", sSQL)
    qdf.Execute dbFailOnError
    qdf.Close
    qdfFN.Close
    rsFN.Close
    Set qdfFN = Nothing
    Set rsFN = Nothing
    Set qdf = Nothing

The query qdfFN is used to retrieve the record of the smaller table which data I want to "passthrough" to the bigger table. The query qdf is the one that, either doesn't work or I am not understanding how to use these vba objects.

I have used Debug.print to check the values of rsFN and they are correct, I mean, the values I want to pass onto the bigger table are there! This is cracking my head

0

Since I am a VBA and Access newbie I didn't know you have to single quote 'text' values, not quote numeric values and quote with hashes date values when doing an insert into in vba.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.