VBA/CurrentDb.Execute + SQL problem

[shurgs]

Hi there. Trying to add a new record, while filling some of the fields in with information from the current record in another table:

Set db = CurrentDb
Set rs = db.OpenRecordset("referral")
rs.AddNew
    rs("agencyid") = 1
    rs("progid") = db.Execute("SELECT pid FROM progress WHERE pid = IDENT_CURRENT('progress')")
    rs.Update
rs.Close

Creates the new record with agencyid equalling 1, but can't get the other part to work? Any ideas?
Thanks for readin'
-Shurgs

 

[pbaldy]

The closest equivalent to what you have would be:

rs("progid") = DLookup("pid", "progress", "pid = " & IDENT_CURRENT('progress'))

I assume IDENT_CURRENT is a function of yours? Given that you're looking up the same value you have from that (pid), why not just use that? It doesn't make sense to look it up again.

 

[shurgs]

Hi, thanks for the reply.

I have a button that adds a new record in the "progress" table and a tickbox that states whether or not that new record is a referral or not. If the checkbox is ticked I need a new record in the "referral" table that's related to the progress record by "progid" in both. *EDIT* Actually, it's pid in progress is related to progid in referral.

There must be a way of doing it, but maybe I'm going about it completely the wrong way? Alas, I'm the most tech savvy person in this charity (which I promise you isn't saying much ) and thus have been assigned the task of making this database.

 

[pbaldy]

I'm not disagreeing with what you're trying to do, just how. Your proposed SQL (which can't be used that way) basically says "look up the pid equal to this pid". You already have the pid, so why not just:

rs("progid") = IDENT_CURRENT('progress')

That's of course assuming that IDENT_CURRENT is a function that returns the value you want.

 

[shurgs]

Turns out I found some duff information about IDENT_CURRENT on the Internet. Turns out what I was looking for was:

rs("progid") = DLookup("max(pid)", "progress")

I officially feel silly. Thanks for your help, though.
shurgs out
*Csshkk*