Changing info in multiple locations from form

[jamiesuperman420]

Hello!! I'm building an inefficient database and need your help.

There's a field called "Code" that is important to several forms/tables. Because I'm great at setting this up, if I change it in one spot it doesn't automatically update all the others.

What I'm trying to do is make a form where there's an OldCode field and a NewCode field. You select the old code from a list, type in the new code you want, then hit a button to change 'em.

It needs to change the codes in multiple tables.

I tried using Nz(DLookup, etc, etc) and a few other things, but I just can't figure it out. Do you have any suggestions??

Thank you in advance!!

 

[pbaldy]

Update queries would be one option, probably the most efficient. I suspect you'd need one for each table.

 

[jamiesuperman420]

Thank you so much for your constant help

I can't quite get it working. Due to the potential for error, I'd like it to be on a form where they can select the [OldCode] from a list, and type in the [NewCode], then hit that button.

I can get it to work but it always says, "Replaced 0 rows" and nothing changes.

Field: Code
Table: Central
Update To: [Forms]![zzzUpdateTest]![NewCode]
Criteria: [Forms]![zzzUpdateTest]![OldCode]

Any idea what I could do to make it happen??

Also, I wanted to let you know I figured out how to put the security on without that blasted wizard... Theoretically all on my own, too!! hehe

 

[pbaldy]

What is the SQL of the query? Make sure in testing that you move focus off both controls. Otherwise, the value property will not have been updated and the query won't see the value.

 

[jamiesuperman420]

Good point about the focus, I double checked that and still no avail. Here's the SQL:

UPDATE Central SET Central.Code = [Forms]![zzzUpdateTest]![NewCode]
WHERE (((Central.Code)=[Forms]![zzzUpdateTest]![OldCode]));

When I run it without the form open, it asks for OldCode, but doesn't ask for NewCode. Not sure if that helps any - but you help me!! (and I'm really grateful)

 

[pbaldy]

The form would have to be open; what happens then? Can you post a sample db?

 

[jamiesuperman420]

Paul - here's the DB stripped down to the current issue. Thank you very much for your help - I'm sure it's something simple and I just can't see it.

 

[pbaldy]

Your combo has 2 fields in the rowsource, and the autonumber is the bound column. That's the column the query is trying to use, so no records will match. I would change it to only return the code field, unless you need the autonumber for something else.

 

[jamiesuperman420]

Ah!! Of course - works great now... Great eye and thank you for the help again!!