If data exists then run query

[BulbFresh]

Hi,

I am using Access 2007.

I have a simple table with 2 fields [Case] and [Target]
My form has a list box populated with Case Types, a text box for the user to input the Target number for the selected Case and a button that i need to run queries to update the table on click.

The table could start blank and i don't want any duplicates, so the 2 queries i think i need to run are:
1. To check if a certain Case exists and if so update the Target with what the user has input.
2 If the Case doesn't already have a target to update, the new Case and Target need to be inserted.

I'm running them from VB as:

DoCmd.RunSQL "UPDATE CaseTarget SET [Target] = ('" & Forms!MainMenu!CTargetText & "') WHERE [Case] = ('" & Forms!MainMenu!CallList & "');"

DoCmd.RunSQL "INSERT INTO CaseTarget (Case, Target) VALUES (('" & Forms!MainMenu!CallList & "'), ('" & Forms!MainMenu!CTargetText & "'));"

But how do i say if there is no Case to Update, then Insert?

Thanks

Chris

[FishVal]

Hi, Chris.

You may just set a unique index on field [Case] and run these two queries sequentially. If you sandwich the code between

DoCmd.SetWarnings False
.......
DoCmd.SetWarnings True
 

then you will not receive message " ... record(s) was/were not added due to index violation" - the second command will just fail silently if a value you try to write to [Case] field is a duplicate.

Regards,
Fish.

[BulbFresh]

Hi, Chris.

You may just set a unique index on field [Case] and run these two queries sequentially. If you sandwich the code between

DoCmd.SetWarnings False
.......
DoCmd.SetWarnings True
 

then you will not receive message " ... record(s) was/were not added due to index violation" - the second command will just fail silently if a value you try to write to [Case] field is a duplicate.

Regards,
Fish.

Thats worked great, thank you!
I have another related problem now but i'll make a new post!

Chris

[NeoPa]

This may be off-base, but why not let Access handle this automatically for you in a form created by the Form-Wizard?