Update Query is asking for Parameters

[NBVC]

[Solved] Update Query is asking for Parameters

I've got an Update Query I am trying to run.

So far I've got an SQL for it something like this:

UPDATE ElectricalParts INNER JOIN [SYSADM_PART - Sandbox] ON ElectricalParts.ID = [SYSADM_PART - Sandbox].ID 
SET [SYSADM_PART - Sandbox].DESCRIPTION = [ElectricalParts].[Description], 
[SYSADM_PART - Sandbox].STOCK_UM = [ElectricalParts].[STOCK_UM],

...(more of similar code)

[SYSADM_PART - Sandbox].IS_KIT = [ElectricalParts].[IS_KIT];

but when I run it, it pops up input boxes for each field asking for a parameter... I just want it to update each field according to the ID matching in both tables.

How can I fix this please... I also tried to remove square brackets and still no success.

Thanks in advance for any help.

Note: this is crossposted here, with no solutions offered yet:

http://www.excelforum.com/access-tables-and-databases/667507-running-update-query-using-a-table.html

 

[Guus2005]

Your query asks for parameters eventough you didn't put them in there by design? Write down the parameters the query requests, e.g. "[ElectricalParts].[IS_KIT]".
This means that table, query or alias "[ElectricalParts]" doesn't have a field with the name "IS_KIT". So there's your problem.

HTH

 

[Atomic Shrimp]

Is 'SYSADM_PART - Sandbox' definitely the right name for one of the tables?

I was going to suggest that maybe the dash in the name was tripping it up, but I just made a test table and query here and it works fine.

 

[NBVC]

Thanks to both of you.

Guus2005: Yes, it asks for parameter inputs even though I have not intentionally designed it that way. All of the parameters it asks for are in both tables...so they do exist.

Mike Gurman: 'SYSADM_PART - Sandbox' is the name of one of the 2 tables. I am trying to update that table (which is linked to our main database) with a table called 'ElectricalParts' where the ID's in both tables match.... All fields are the same titles in both tables.

Any other possibilities... I even tried taking away the square brackets from the field names in the ElectricalParts table fields in my SQL, but it did not work.

 

[boblarson]

Thanks to both of you.

Guus2005: Yes, it asks for parameter inputs even though I have not intentionally designed it that way. All of the parameters it asks for are in both tables...so they do exist.

Mike Gurman: 'SYSADM_PART - Sandbox' is the name of one of the 2 tables. I am trying to update that table (which is linked to our main database) with a table called 'ElectricalParts' where the ID's in both tables match.... All fields are the same titles in both tables.

Any other possibilities... I even tried taking away the square brackets from the field names in the ElectricalParts table fields in my SQL, but it did not work.

When it asks you for those, then something isn't spelled exactly the same (in 99.9% of the cases). Check again (or post the db so we can check)

 

[NBVC]

When I do an append query using the exact same tables, no errors, no parameter requests...and it appends the records no problem... so spelling should not be the issue here... I believe.

My tables are all linked to either my main DB or Excel Spreadsheets... will posting the Access file still help?

 

[Guus2005]

Open the query in design mode.
Rightclick in the QBE and choose Query Parameters.
Apparently there is an entry describing a query parameter.
Delete the entry and if every field is accounted for, the query will run normally without question.

Enjoy!

 

[NBVC]

I did that and the popup list shows no parameters, but yet when I run the query I get the attached.

The first field in both tables is "Description"....

If I click Ok, it asks for STOCK_UM and so on.

 

[Guus2005]

Like Mike Gurman said, 'SYSADM_PART - Sandbox' is a very strange name for a table. Eventough you have isolated the name using [], execution of the query results in the subtraction of Sandbox from SYSADM_PART. Try renaming this table and leave out the minus sign and spaces in the name. You also shouldn't use these characters in field names.

HTH

 

[NBVC]

Well, what-do-you-know. That seems to be it!

I changed the table name to 'SYSADM_PART_Sandbox' and it works now with no glitches.

I should've tried that before but Mike Gurman said he tried an example with the dash and it worked for him..so I didn't bother to try that... but I probably should have.

Thanks to all for your help.