Overwrite existing records or create new ones 2

[CapnOats]

After my last plea for help, hopefully ill get another helpful comment.

I have a table called work which contains the fields "FM Reference", "Designer" and "Hours Worked". What I am needing is a query that will overwrite an existing record if the FM Ref and Designer equal that supplied and overwrite it with the new Hours Worked. If no record exists create a new one with the supplied data.

Pseudo-code:

if record exists with FMRef = newFMRef and Designer = newDesigner then
Hours Worked = newHours Worked
else
create new record with newFMRef, newDesigner, newHours Worked


hopefully you can understand this and be of some assistance

Thanks very much,
Mike Coats

 

[lespaul]

write a query that searches:

SELECT * FROM TABLE WHERE FM_REFERENCE = _________ AND DESIGNER = _________________

check the recordset, if it's empty add a record
INSERT INTO TABLE .....
if there's a record, modify it.

Should get you started!

Leslie

 

[KenReay]

Hi

Does your table have a unique key?

If yes, I would do this with two SQL strings

first an update, this will update the record if found, and fail if not found

second an insert, this will fail if record exists and insert one if it does not

by using db.execute or suppressing error messges and using DoCmd.RunSQL user will not see error message(s)

If you table does not have a unique key, how can you recognise if this is a 'new' record?

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[CapnOats]

how do i check the record set?

the way i was TRYING to do it before was make a variable equal to the DoCmd openquery or runsql, but it doesnt seem to return anything - ideally i would have hoped that DoCmd would return true if it did it or false if it failed but it doesnt seem to?

how would i go about doing it your way?
is there an easy way to return a "count" of records to a vba variable as ive not really gotten into traversing through tables yet.

Hopefully this clarifies some things.

Regards,
Mike

 

[CapnOats]

For Ken:
No the table does not have a unique key as more than one designer can work on each job (the fm ref). what im trying to implement is something like a double unique key. basically, the designer can call up the form, and update their hours for that job - i dont want duplicates but can use a unique key because there will be more than one of each value in all fields.

 

[KenReay]

Hi

Dim Db as DAO.Database
strSQL as String

set Db = CurrentDb
strSQL = "UPDATE SET Hours = " & NewHoursWorked & "WHERE FM_REFERENCE = _________ AND DESIGNER = _________________"

db.execute strSQL
strSQL = "INSERT ([FM_REFRENCE],DESIGNER,HOURS) VALUES (...etc...) FM_REFERENCE = _________ AND DESIGNER = _________________"
db.execute strSQL




Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[CapnOats]

Thanks Ken.

Yeah, using some bastardised version of your code i managed to get it to do what i was needing.

Your help is very much appreciated, if you ever get as far north as Inverness, I owe you a pint.


Regards,
Mike

 

[CapnOats]

well...

it almost does -

they both do what they're supposed to do, but i still have no way of stopping the insert and i cant tell if the update completed or failed - remember there are no unique key so it can fail automatically.

i have one idea though, i could create a unique key in the table that is basically a concat'd string created from the fmref and designer name - then the insert would definitely fail if one already existed - its a bit of a hack and a kludge i know, but it may just work.

ill post a comment on here whether it works or not, just so everyone else can see it if they need a complete solution to "borrow" from.


Regards,
Mike

 

[lespaul]

You can make it a composite PK. In the table designer, highlight both fields and press the Key button. You now have a composite PK where the combination of the two fields has to be distinct. Of course this assumes that you can modify the table design and that all the existing records conform to the new PK.

HTH

leslie

 

[CapnOats]

aha! a far more elegant solution than my kludged hash.

once again the tek-tips community saves the day.

Looks like I owe you a pint too.

Regards,
Mike

 

[lespaul]

Next time I'm over the pond, I'll be sure to collect on that!



Leslie