How to search for record in Access before update? 1

[adtimg]

I'd like to use code to search for an existing record in field called "Partnum" and then use things like update delete or add new. I'd really like to use that field as the primary key, but it's not a necessity.
So far I'm only able to get dbs.EXECUTE "INSERT INTO....."
to work for me.
I have a search function that populates text boxes on my forms that works fine, but I just can't seem to figure out how to do the above as a before update procedure.
Can anyone point me to some help?
The code below is what I'm using to commit changes and I'd prefer not to create new records if I can update them instead.
TIA
Tim G
If Y = "Mori Seiki SL-35" Then
dbs.Execute "INSERT INTO MoriSeikiHub (Partnum,BilletL,BodyL,OAL,BilletD,FlangeD,BodyD,BoreD,Hole,ODcut,ODfeed,IDcut,IDfeed,Speed,Machine,Material,Volume,Timeof) Values " _
& "(('" & txtPN & "'), ('" & Text1 & "'),('" & Text2 & "'), ('" & Text3 & "'), ('" & Text4 & "'), ('" & Text5 & "'), " _
& "('" & Text6 & "'), ('" & Text7 & "'), ('" & Text8 & "'), ('" & ComboODcut & "'), ('" & ComboODfeed & "'), ('" & ComboIDcut & "'), ('" & ComboIDfeed & "'), " _
& "('" & Combospeed & "'), ('" & lblmachine & "'), ('" & lblmaterial & "'), ('" & Text9 & "'), ('" & Text10 & "')) "
ElseIf etc. below this.

 

[programmerbrian]

Ever think of using recordsets?
Dim adoRecordset As New Recordset

adoRecordset.Open "Select Partnum,BilletL,BodyL,OAL,BilletD,FlangeD,BodyD,BoreD,Hole,
etc. from MoriSeikiHub WHERE Partnum = 12345", databasename, adOpenStatic, adLockOptimistic

adoRecordset.Fields(BilletL) = Text1
adoRecordset.Fields(BodyL) = Text2

adoRecordset.Update

adoRecordset.Close


This would let you just update the existing records instead of creating new ones. If partnum is not unique, you need to make sure your only returning one record for update so when you do your select look for your part number and the primary id of the record.

adoRecordset.Delete
will delete your record.

adoRecordset.Addnew
will create a new one in your table.

You might have to play with your code to get this working but I've done this in the past and it works ok.

I hope this helps?

 

[adtimg]

programmerbrian
Thanks for the info.
I am using recordsets for my search command, but am more familiar with "insert into" for adding new records.

This should allow me to keep each partnum unique as other machines use separate tables and can have the same value in that field.
I'll try your suggestion and see how many errors it takes me to get it right
An Access guru at work will do any other crap with the data since I'm just trying to create and update records.
Timg

 

[adtimg]

Well................I got it to work
lots of errors and a bunch of cigarettes later!
The code ended up not being pretty and I had to use an on error statement to let me get by no match contitions
I had to use this

rs!BilletL = Text1

instead of

adoRecordset.Fields(BilletL) = Text1

and a different call for the recordset in order to get it done

Dim ws As Workspace, db As Database, rs As Recordset, Q$
Set ws = CreateWorkspace("alvin", "Admin", "", dbUseJet)

Set db = ws.OpenDatabase("H:\Mfg\Shared\Machinetime.mdb&quot
Q$ = "SELECT Partnum,BilletL,BodyL,OAL,BilletD,FlangeD,BodyD,BoreD,Hole, " _
& "ODcut,ODfeed,IDcut,IDfeed,Speed,Machine,Material,Volume,Timeof FROM MoriSeikiHub WHERE Partnum LIKE '" & txtPN & "'"
Set rs = db.OpenRecordset(Q$)
In any case..........Thanks a bunch for the clues!

 

[BobRodes]

You have created a solid DAO solution.

Something to remember is if you have references to both DAO and ADO in your project, and dim something as Recordset, it will be whichever recordset is further up the reference list. (Ever wonder what that "priority" button is for?!?) If you want to distinguish, you can dim something as ADODB.Recordset and so on. If you think you're working with an ADO recordset and you give it commands for a DAO recordset, or vice versa, your program will quite reasonably barf.

Bob

 

[AndyWatt]

> "... your program will quite reasonably barf."

Heh heh, that tickled me. I have wondered about those priority buttons. Now I know.



Andy
"Logic is invincible because in order to combat logic it is necessary to use logic." -- Pierre Boutroux
"Why does my program keep showing error messages every time something goes wrong?"