Append

[naturalsn]

Hi

I was hoping someone could possibly assist.

I have Two Tables

TblIntrvCandidates & TblEmpInfo.

In the "TbleIntrvCandidates" we have hundred of records of candidates being interviewed and using it for sending document etc. Once a candidates interview was not successfull or experience was not enough, the record status would be set to Inactive and that is the end untill next time they apply for something.

However once the cadidate is being made an offer i would like to move all the fields for that record we have to our "TblEmpInfo" Table.

All the Reocrds in the "TblIntrvCandidates" are in the "TblEmpInfo" Table. (But not vice versa)

(I know it is best to make use of status changes in on table and run queries, but we do not want to keep records of just interviewed candidates with permanent employees.

So i would like to create an after update statement, that when the users status is changed to "offer" made and append to the "empinfo" table swill run and a delete within the interview table on the current record.

I would like to make use of an append query, or a select * to not having to redefine the record names because i am sure that i will have to add records within the next few month "I hope that makes sense. "

I was hoping to use something such as

If Me.Status = "Offer" Then
iAnswer = MsgBox("Are you sure you would like to move the record for " & _
Me.FirstName & " " & Me.Surname & " " & _
 "to the Employee table?", _
vbCrLf & vbYesNoCancel)

DoCmd.RunSQL ("INSERT INTO tblEmpinfo SELECT * FROM TblIntrvCandidates;")


DoCmd.RunSQL "DELETE * FROM TblIntrvCandidates WHERE (id='" & Me.ID & "')"

but it does not work.

or if i try

DoCmd.SetWarnings false
DoCmd.OpenQuery "QryAppendOfferDetails"
DoCmd.OpenQuery "QryDelIntvDetails"

how do i define that it will be with the current record that i am working with on my form.. And wich would be the best to use.(if i can get it to work)

Any help / adivse would be greatly appreciated

Kind Regards
Sn

 

[lameid]

You need criteria in your query to reference the correct employee.

I am going to assume your form's name is frmExample and the unique identifier for the person is PersonId (autonumber field in TblIntrvCandidates and primary key).

DoCmd.RunSQL ("INSERT INTO tblEmpinfo SELECT * FROM TblIntrvCandidates " & _
"Where Person_id = Forms!frmExample!Person_id;")

That of course an example in code of adding it. You can do the same in the query object.

As for which method, I usually execute saved query objects unless I have to programmatically build some part of the SQL. The two advantages to this method are that stored queries can be compiled ahead of time (design them, save them, run them, save them again) and the query designer is easier to use than raw SQL (my opinion). Alternately putting them in code is advantageous because you can see everything it is doing in one pane and it is more portable (if you have to import the form for some reason you do not have to worry about supporting queries referenced in code).

Both methods have their merits. If there is a lot of data or complicated query involved, compiling the query for performance may be an issue but I doubt it is for you. Also portability is rarely a concern so that leaves whether you like the ease of use of the QBE (query designer) or seeing everything in one pane (argueably a detriment by programming purist standards as you want to break things up into separte procedures where reasonable).

 

[lespaul]

All the Reocrds in the "TblIntrvCandidates" are in the "TblEmpInfo" Table. (But not vice versa)

I think here you mean that all the fields in TblIntrvCandidates are in TblEmpInfo and that there are fields in TblEmpInfo that are not in TblIntrvCandidates. If that is the case then you need to qualify what fields in TblEmpInfo get the info from TblIntrvCandidates and I don't think you can use the * in the SELECT for that either:

INSERT INTO tblEmpInfo (EmpID, FirstName, LastName) (SELECT EmpID, FirstName, LASTNAME FROM TblIntrvCandiates WHERE Person_id = Forms!frmExample!Person_id)

Leslie

Have you met Hardy Heron?

 

[lameid]

I read fields vs records in the statement.

Also as long as all the Fields in TblIntrvCandidates are in TblEmpInfo, the SQL would be correct except for the missing where clause I added.

Also, I missed the fact that the delete statement has a where clause. The same where clause should work with the insert statement. Although you would have to use the Fully Qualified syntax for the field on the form if you want to use query objects.

 

[lespaul]

It's unclear, that's why I posted. If the record is deleted from the Candidate table after it's moved to the Employee table then none of the records currently in the Candidate table are in the Employee table, right? There are two completely separate lists of names no Employees are Candidates and no Candidates are Employees. That's why I thought maybe the OP meant fields instead of records.

Leslie

Have you met Hardy Heron?

 

[lameid]

Leslie,

I agree it is misworded (unclear), however if you change "records" to "fields" in that statement then the SQL is correct.

If there are fields in TblIntrvCandiates (source / select) that are not in tblEmpInfo (Destination / Insert Into) then you have to specify fields in both the select and insert into but not vice versa as you suggested. I suspect other SQL languages are less forgiving but JET likes it. JET maches field names when the asterisk is used where I expect others may use the ordinal field positions to perform the insert (same way fields have to match up for a union query in JET).

I get bitten by SQL nuances in SQL server every once in a while because I think it should work the way JET does.

 

[lespaul]

Ok, all the SQL I've ever worked with didn't like the * unless the structure was identical...if there are any differences every field has to be listed.

Thanks for the info, I'll be sure to remember that for future postings!
Leslie

 

[naturalsn]

Thank you very much for both your help.

Got is working

thanks