DoCmd.RunSQL "UPDATE -asking for Parameter Value (1 Viewer)

[Keyes71]

Hello,

I am running the below code, but getting an error.

DoCmd.RunSQL "UPDATE [tbl_Running_Open_Transaction] SET [tbl_Running_Open_Transaction].[Matched] = 1 WHERE [tbl_Running_Open_Transaction].[ID] = [tbl_Matches].[TBL_ID] ;"

I have a list of IDs in the tbl_Matches Table that are in column TLB_ID. I am trying to have the check box ( [tbl_Running_Open_Transaction].[Matched] ) checked if the AutoNumber ID is in the list in tbl_Matches.

It is giving me an Enter Parameter Value Error asking for tbl_Matches.TBL_ID

Any clue as to why it is not recognizing this field?

Thanks,

 

[The_Doc_Man]

Invariably, when you get an "Enter Parameter Value" box from out of the blue, the thing that is named cannot be found. Looking at your SQL, the problem is that tbl_Matches doesn't appear in the list of tables (i.e. between UPDATE and SET) being named as data sources. Your WHERE clause cannot name anything that didn't appear in the table list (unless there is a sub-query involved.)

 

[Keyes71]

Ah - That makes sense Thanks The_Doc_Man!

 

[theDBguy]

Hi. Welcome to AWF!

You could try joining the two tables. For example:

DoCmd.RunSQL "UPDATE [tbl_Running_Open_Transaction] INNER JOIN [tbl_Matches] ON [tbl_Running_Open_Trasaction].[ID]=tbl_Matches].[TBL_ID] SET [tbl_Running_Open_Transaction].[Matched] = 1"

(untested - make sure you have a backup before trying it)

 

[CJ_London]

not sure why you need the matched field - you can find out as and when required with a simple query. Danger is that having 'matched' a record if the tbl_id record is deleted or changed, it will still appear to be matched.

If you do want the a field to show 'matched' then better that the [tbl_Matches] table has a PK field as well and you store that in your tbl_Running_Open_Transaction table. That way you can maintain referential integrity - and you can still use a checkbox to display true or false since false is 0 and any other number will be interpreted as true.

Assuming you are using an access back end and not sql server or other rdbms checkboxes have the value of 0 or -1. Although your update query will update to 'true' the underlying value will actually be -1. May matter to you, may not

 

[The_Doc_Man]

There is another issue in using the JOIN. In order for it to work properly, you STILL need a way to select the correct record from tbl_Matches in order to pull the ID. Unless you have a table of matches by ID and you want ALL of the IDs in tbl_Matches to drive the setting of the Match flag to 1. Then you have a solution.