Add or Update Records 2

[JoeMicro]

Hi Guys,

Does anybody know the best way, to loop thru records (in an unbound access Sub form)to check whether it’s already recorded or not.

If its already recorded Then update the changes, if not then it should Add new?

Thanks
Joe

 

[PHV]

You may consider the DLookUp function.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[JoeMicro]

Yes but if I m to use RecordsetClone to loop thru all the records it takes a very long time.

How do I lookup each individual record?

Thanks
Joe

 

[PHV]

You talked about an unbound form and thus I don't understand how you would use RecordsetClone ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[JoeMicro]

I have a subform on a main form (the main and the sub have no Bound fields)

The subform is linked to a table which is always being emptied after each receipt (i m just using it for a control) all the records are being Added to another table (which i m using as the Database).

Now, if i want to edit a receipt, i would like the code to compare the recordID on the subform with the RecordID in the database to check whether its a new or old record.

So i was thinking of using recordsetClone so i could do,

Do while J < then recordsetColne.Recordcount -1
'do the Operation
j=j+1
loop

But this procedure takes a very long time.


Thank you so much for replying
Joe

 

[PHV]

What about the FindFirst method ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[JoeMicro]

Could you please elaborate on this method?

I have never used it before.

Does it use a loop?, how does it work?


Thanks a million
Joe

 

[PHV]

In the debug window (Ctrl-G) type findfirst and press the F1 key.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[JoeMicro]

Hi PHV,

Ok I have got this Code

Private Sub Command8_Enter()
Dim rs As DAO.Recordset
Dim Str, SQL, updtSQL As String

SQL = "Select * From Table1"
Set rs = CurrentDb.OpenRecordset(SQL)

Str = "[ID]= " & Me.Table2_subform![ID]

rs.FindFirst (Str)
If rs.NoMatch Then
    rs.AddNew
    rs("ID") = Me.Table2_subform![ID]
    rs("1") = Me.Table2_subform![a]
    rs.Update
Else
    rs.Edit
    rs("ID") = Me.Table2_subform![ID]
    rs("1") = Me.Table2_subform![a]
    rs.Update
End If
rs.Close
Set rs = Nothing
End Sub

But now it only works for each record I manually select, how do I tell the code to do all the records at once.


Thanks
JOe

 

[PWise]

this code will records into table1 if that id dose not exist

currentdb.execute "insert into table1(id,[1])Select id,a From table2 left join table1 on table1.id=table2.id where table1.id is null

 

[JoeMicro]

Thanks pwise for you reply,

But what is it different then the code I have?

I m afraid nobody understands my question

Let me try to clarify.
My question is not how to either Add or update (separately)

My question is, whets the fastest way to loop thru records in a subform so I could check whether each record is in the database, and perform some code accordingly?

I gauss my subject line is confusing everybody, but I don’t know how to change it

Thank you all for trying to help
Joe

 

[PWise]

try this

dim SubformRs as String
SubformRs = me.subformcontrolname.form.recordsource
currentdb.execute "insert into table1(id,[1])" & SubformRs  & " left join table1 on table1.id=table2.id where table1.id is null

 

[JoeMicro]

Thanks pwise


but isn't it actually the same code you posted above?

currentdb.execute "insert into table1(id,[1])Select id,a From table2 left join table1 on table1.id=table2.id where table1.id is null

BTW i needed to correct it a drop

Insert Into Table1([1],id) Select Table2.a, Table2.ID From Table2 Left Join Table1 on Table1.ID = Table2.ID Where Table1.ID is null

and now it actually adds new records to the database, and ignores the exiting records.

now my question is how do i loop through all the subform records so it should look at each one individually so it could check whether the record is old or new.

Thanks again for trying to help
Joe

p.s. i m probebly getting on everybodys nerves here just let me know.

 

[JoeMicro]

Thank you so much pwise.

you are right i m just stupid and cant understand your code.

but i finely got it. i put together your query with an update query and it does the job no looping needed. Wonderful

here is my code

SQL = "Insert Into Table1([1],id) Select Table2.a, Table2.ID From Table2 Left Join Table1 on Table1.ID = Table2.ID Where Table1.ID is null"

updtSQL = "UPDATE Table1 INNER JOIN Table2 ON Table1.id = Table2.ID SET Table1.id = Table2.id, Table1.[1] = Table2.a"

CurrentDb.Execute (SQL)
CurrentDb.Execute (updtSQL)

Works great

Thank you again

Joe

 

[PHV]

Only one query is needed, provided ID is the primary key in both tables:

CurrenDb.Execute "UPDATE Table1 AS O RIGHT JOIN Table2 AS N ON O.id = N.ID SET O.id = N.ID, O.[1] = N.a"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[JoeMicro]

Wow, that does work.

have you got a moment to explain how you did that?

Thanks PVH,

Joe

 

[JoeMicro]

Oh I see now, because you did a right join.

i m learning tons from posting here.

Thanks
Joe