Table wont update from query

[EDOx16]

Hello all,



I am new to this forum and was not sure if this is the correct place for my problem. It falls in a lot of areas.



Background: I have a form "frmNewInfo for the purpose of creating or updating a record. The form values are loaded into a table to create a unique transaction key(so there is a record of each transaction), and that table is used for a query, and the information pulled from that query is used to update the live table.

The issue is this, when I try and update a table from the query it gives me an error "failed to add new record, too few parameters, expected 1". The problem I am having I think is in the criteria section of the query, the script will not pull the information from the query for some reason.

I have the criteria set to [forms]![frmNewInfo]![frmKey]. When I run the query everything comes up it pulls the unique key and fills the column data according to the unique key on the active form. This used to work and now i get the too few parameters error when I run the script as of 2 weeks ago. I tested by manually entering the criteria to the current form key, and when running the query all gives all the same information, but when the criteria is hard coded to the active form number the update script will work. I only get the scripting error when the criteria isn't hard coded.

So the query works on its own, the script works with the criteria hard coded, but the script will not work with the query using forms.xxx.xxx as the criteria

Any help or ideas are appreciated.

[ranman256]

you didnt post the query sql.
could it be that

[forms]![frmNewInfo]![frmKey] is numeric, and your query has it as string?

pls post the sql.

[EDOx16]

Thank you for the response.
Here is the sql from the query. Maybe because the update script is dimensioned as a string?

SELECT DISTINCTROW tblNewInfo.*
FROM tblNewInfo
WHERE (((tblNewInfo.RequestKey)=[forms].[frmNewInfo].[requestkey]));

[Micron]

Forms![frmNewInfo].[requestkey] not [forms].

Anything with brackets around it is interpreted as a field name. In your case, the brackets aren't actually necessary but Access will usually add them even so.

[EDOx16]

Thank you for the response.
The brackets are there because access forces them in, if i type forms! it automatically updates with brackets. Taking them out of the sql directly made no difference. I still get the too few parameters expected error.

[Micron]

Except you wrote forms. and now you wrote forms! and I believe it makes a difference. Since you posted this in 'programming' I suspect this isn't a query sql; rather it is in code. That could explain your issue. So is this sql being executed from code?

EDIT - if it is code, can you post it (please use code tags - # on posting toolbar)

[EDOx16]

The original was
SELECT DISTINCTROW tblNewInfo.*
FROM tblNewInfo
WHERE (((tblNewInfo.RequestKey)=[FORMS]![frmNewInfo]![RequestKey]));

I rewrote it from with . just to see if it made a difference. They both do the exact same thing and show the exact same info on the query. I posted it in programming because when I run the query it gives all the info. But when I run the script to update, it will not pull the info from the query results if I use forms!xxx!xxx as the criteria, only if i manually enter the current form key as the criteria. It will return something like below if I run the query. But if I set the criteria using forms!xxx!xxx the script wont run, but If I set the criteria to 5 as per the request key the update script no longer throws an error.

requestkey	original name	first	second
5	test5	test1	tests2
(new)

[davegri]

I've seen this squirrelly behavior before. Try this: Set a tempvar = the form's RequestKey control value, then use the tempvar as the query criteria.

[Gicu]

You showed us the SQL for the query but you haven't showed the "update script".
Cheers,

[EDOx16]

I've seen this squirrelly behavior before. Try this: Set a tempvar = the form's RequestKey control value, then use the tempvar as the query criteria.

Thanks for the response and squirrelly is right on. This worked one week, then the next week it was throwing a too few parameters error. Let me look into how to use tempvar

[Micron]

You showed us the SQL for the query but you haven't showed the "update script".
Cheers,

Still don't even know if there is one??
Let's assume yes. Easiest way I've found to deal with it is to make a variable = Forms!whatever (or Me.whatever) then pass the variable rather than the form control reference. It is also much simpler than creating parameters either in code or as a query property, which seems to be the usual suggestion.

[EDOx16]

You showed us the SQL for the query but you haven't showed the "update script".
Cheers,

Here is an example of the update script. The overall script is longer and more complicate. I just pulled some parts of it. It will no longer update any table from any query that uses forms!.

Dim StrNew As String
Set wsCurrent = DBEngine.Workspaces(0)
Set db = CurrentDb

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

[EDOx16]

Still don't even know if there is one??
Let's assume yes. Easiest way I've found to deal with it is to make a variable = Forms!whatever (or Me.whatever) then pass the variable rather than the form control reference. It is also much simpler than creating parameters either in code or as a query property, which seems to be the usual suggestion.

I did not write the script. The person who wrote it retired, and the people in charge decided whoever they dropped in his spot didn't need training. I am still not sure why he used a query as opposed to directly updating from the form values. I rewrote a whole section to update directly from the form, then realized it would take too long so I went back to trying to fix this query issue.

If i set a tempvar can I do it in the sql code or is it in the declarations for the whole thing?

[davegri]

If i set a tempvar can I do it in the sql code or is it in the declarations for the whole thing?

I don't know the structure of your DB or from where in the code the sql string is being constructed. If it's behind the form, set the tempvar there before building the query string. In any case, tempvars are global.

[Micron]

temp var wasn't my suggestion so I can't answer that question. I suggested using a variable. Maybe the rest of the code would make sense of why it seems to set 2 variables for what looks like the same current database but I was kind of surprised at that. so

Dim lngKey As Long

lngKey = Me.RequestKey

...
"WHERE (((qryNewInfo.RequestKey)= " & lngKey & "));"