Table wont update from query

[Gicu]

Is qryNewInfo the query you showed in the previous posts that already has the forms.[.... parameter? Because if it is in this example you're are duplicating it. If you reference a query (qryNewInfo) that has a reference to a form control in VBA you will get the too few parameters error. So you would need to remove it from the query (or eliminate the query all together and reference the source table directly) and build your insert SQL in VBA like you have it or save the insert SQL as an append query and use

Docmd.Setwarnings False
Docmd.OpenQuery "qryAppendNewToOld"
Docmd.SetWarnings True

Alternatively (and generally recommended ) is using CurrentDb.Execute "qryAppendNewToOld",dbFailOnError but that will involve some extra lines of code to force the evaluation of parameters:

Dim qdf as DAO.QueryDef,prm as Parameter
Set qdf=CurrentDb.QueryDefs("qryAppendNewToOld")
For each prm in qdf.Parameters
    prm.Value=Eval(prm.Name)
Next prm


qdf.Execute dbFailOnError

EDIT:Here is what I meant by eliminating the query and using the table:

strNEW = "INSERT INTO tblOLD " & _
"SELECT DISTINCTROW tblNewInfo.OldName, tblNewInfo.First, " & _
"tblNewInfo.Second FROM tblNewInfo " & _
"WHERE tblNewInfo.RequestKey= " & Me.RequestKey & ";"

And maybe show us an example with Forms. instead of Me......
Cheers,

[EDOx16]

Is qryNewInfo the query you showed in the previous posts that already has the forms.[.... parameter? Because if it is in this example you're are duplicating it. If you reference a query (qryNewInfo) that has a reference to a form control in VBA you will get the too few parameters error. So you would need to remove it from the query (or eliminate the query all together and reference the source table directly) and build your insert SQL in VBA like you have it or save the insert SQL as an append query and use

Docmd.Setwarnings False
Docmd.OpenQuery "qryAppendNewToOld"
Docmd.SetWarnings True

Alternatively (and generally recommended ) is using CurrentDb.Execute "qryAppendNewToOld",dbFailOnError but that will involve some extra lines of code to force the evaluation of parameters:

Dim qdf as DAO.QueryDef,prm as Parameter
Set qdf=CurrentDb.QueryDefs("qryAppendNewToOld")
For each prm in qdf.Parameters
    prm.Value=Eval(prm.Name)
Next prm


qdf.Execute dbFailOnError

EDIT:Here is what I meant by eliminating the query and using the table:

strNEW = "INSERT INTO tblOLD " & _
"SELECT DISTINCTROW tblNewInfo.OldName, tblNewInfo.First, " & _
"tblNewInfo.Second FROM tblNewInfo " & _
"WHERE tblNewInfo.RequestKey= " & Me.RequestKey & ";"

And maybe show us an example with Forms. instead of Me......
Cheers,

Yes it is the same query, I guess it is referencing it twice as you say. Here is the code that executes the update. So If I really cannot reference the form in the query I may have to figure out something else. The weird part is this code worked the week before the guy retired, then stopped working the week after, and nothing was changed.

Private Sub cmdNew()
On Error GoTo Err_Rollback
    wsCurrent.BeginTrans
    MsgBox ("Begin Trans")
    
    db.Execute strNEW, dbFailOnError
    MsgBox ("record added to local tblOLD")

    wsCurrent.CommitTrans
    
On Error GoTo Err_cmdNew
    Set db = Nothing
    wsCurrent.Close

Exit_cmdNew:
    Exit Sub

Err_cmdNew:
    Set db = Nothing
    MsgBox ("Error in cmdNew")
    MsgBox Err.Description
    Resume Exit_cmdNew
    
Err_Rollback:
    wsCurrent.Rollback
    Set db = Nothing
    wsCurrent.Close
    MsgBox ("Database transaction failed to add the new record")
    MsgBox Err.Description
    Resume Exit_cmdNew
    
End Sub

I started something similar to your idea where i would just remove the query and pull directly from the form data. But the whole script is pretty big and I would have to make the changes in maybe 1000 lines so I was trying to figure out if I could just make the old script work. This is a cut down version of the script. It actually involves 20 entry rows.

    strNEW = "INSERT INTO tblOLD (OldName, First, Second) " & _
    "VALUES ('" & Me.OldName & "', '" & Me.First & "', " & _
    "'" & Me.Second & "');"

[Gicu]

You don't need the query if you are doing it in VBA, just try what I suggested in my previous post:

strNEW = "INSERT INTO tblOLD " & _
"SELECT DISTINCTROW tblNewInfo.OldName, tblNewInfo.First, " & _
"tblNewInfo.Second FROM tblNewInfo " & _
"WHERE tblNewInfo.RequestKey= " & Me.RequestKey & ";"

CurrentDb.Execute strNew,dbFailOnError

Cheers,

[EDOx16]

You don't need the query if you are doing it in VBA, just try what I suggested in my previous post:

strNEW = "INSERT INTO tblOLD " & _
"SELECT DISTINCTROW tblNewInfo.OldName, tblNewInfo.First, " & _
"tblNewInfo.Second FROM tblNewInfo " & _
"WHERE tblNewInfo.RequestKey= " & Me.RequestKey & ";"

CurrentDb.Execute strNew,dbFailOnError

Cheers,

roger that, That looks like rewriting it may be the way I have to go. Maybe I will try some search and replace to make it go faster.

I will try one more time to see if I can use some offset variable to call the criteria, setting it as a variable gave the same issue, trying to see if I can get a tempvar to work. But as was previously stated using a form reference in the query criteria is causing errors throughout the script. Looks like it never should have worked being scripted that way but it used to.

Thanks everyone for the help and suggestions

[Micron]

Somehow your sql morphed from what you showed in post 7 to something that uses a select from a second query?

[EDOx16]

Somehow your sql morphed from what you showed in post 7 to something that uses a select from a second query?

nothing morphed, but according to Gicu apparently in VBA the criteria of the query cant reference the form control, which is the request key, without returning an error. This makes considering, as I said previously, the error occurred whenever I tried to reference the form directly in the criteria and it would not run during the script.

[Micron]

post 3
SELECT DISTINCTROW tblNewInfo.*
FROM tblNewInfo
WHERE (((tblNewInfo.RequestKey)=[FORMS]![frmNewInfo]![RequestKey]));

post 7
strNEW = "INSERT INTO tblOLD " & _
"SELECT DISTINCTROW qryNewInfo.OldName, qryNewInfo.First, " & _
"qryNewInfo.Second FROM qryNewInfo " & _
"WHERE (((qryNewInfo.RequestKey)= " & Me.RequestKey & "));"

Those are the same?

apparently in VBA the criteria of the query cant reference the form control,

I doubt he said what you're claiming because I know that to be untrue and I suspect, so does he. Your issue is likely partly due to calling an actual query as part of code generated sql and expecting that query to resolve the form control reference. That is not the same thing as constructing the entire sql in code. One can define the parameters in a query, or construct all the sql in code, or use variables, or define query def objects. I have used all these approaches to deal with this issue and they all worked.

[EDOx16]

post 3 is sql for the query, post 7 is the update script.

If you reference a query (qryNewInfo) that has a reference to a form control in VBA you will get the too few parameters error.

That is what was said in post 16, and as I stated previously the query works fine on its own, it just wont work during the update script when setting the criteria using forms!xxx!xxx to reference the key in the form.

[Gicu]

If you reference a query (qryNewInfo) that has a reference to a form control in VBA you will get the too few parameters error. So you would need to remove it from the query (or eliminate the query all together and reference the source table directly)

Because your query already limits the records returned from tblNewInfo by making a reference to [FORMS]![frmNewInfo]![RequestKey] you will get the "too few parameters" error when attempting to call it in VBA. So I suggested you either open the query in design view and remove the criteria (because it will be applied in the VBA code anyway as per post #7) or even better just ignore the query (that does nothing else than the SQL in strNEW) and reference the table directly as per my last code paragraph.

By leaving the criteria in the query AND the in code you are applying twice for no reason, so regardless if you make it work with the tempvar you should only do it in one place.

I hope this clears things a bit.
Cheers,
Vlad

[EDOx16]

Because your query already limits the records returned from tblNewInfo by making a reference to [FORMS]![frmNewInfo]![RequestKey] you will get the "too few parameters" error when attempting to call it in VBA. So I suggested you either open the query in design view and remove the criteria (because it will be applied in the VBA code anyway as per post #7) or even better just ignore the query (that does nothing else than the SQL in strNEW) and reference the table directly as per my last code paragraph.

By leaving the criteria in the query AND the in code you are applying twice for no reason, so regardless if you make it work with the tempvar you should only do it in one place.

I hope this clears things a bit.
Cheers,
Vlad

Yes thanks I understood it. I was responding to the other guy trying to help. And I was wondering if that is what was happening when it stopped working which is why I started rewriting it to load directly from the form. This post was more to see if I could get it to work the way it was already written. I am still not sure why the query was used in the first place. I haven't had time to rewrite it but thanks again for your help and info, along with everyone else's.

[EDOx16]

Thanks to everyone that helped. I ended up just rewriting the script as per Gicu's suggestion. It is working so far in all my tests.

[Gicu]

Great news, thanks for letting us know!
Good luck with your project!
Cheers,