Creating or Updating a Table from a Query (1 Viewer)

music_al · Sep 20, 2018

I have a database that tracks a candidate through a company’s recruitment process.
When the Candidate’s status is changed to ‘Contract Accepted’ I want them to appear on a ‘Master Staff List’. Once they are on that list there is a lot more data that will be added to the staff member’s record.
I’m unsure the best way to do this;
· a make table query doesn’t seem to do it as each time the query is being run it is creating the table from scratch and I would lose any other fields added to that table.
· an update query requires for the record to already be there; doesn’t it ??
· append query is creating duplicate records of staff members that have already been added to the Master Staff List
Any help would be greatly appreciated.

arnelgp · Sep 20, 2018

if you are using firm, you add code to uts before update event:

private sub form_beforeupdate(cancel as integer)
if nz(dcount("*", "masterlist", "candidateID=" & Me.candidateId), 0) =0 then
' does not exist add
' run Insert Query here
end if
end sub

isladogs · Sep 20, 2018

When the Contract Accepted button is clicked use an unmatched append query. This will append a new record but only if it doesn't already exist.
In other words if a primary key field is null.

If it does already exist, you then use an UPDATE query to modify data.
A procedure can be written that selects the correct query.

Alternatively there is a clever query that combines both of these into one. I call it an UPEND query but it's also known as an UPSERT query by others.

HTH

music_al · Sep 20, 2018

Hi,

There is a chance that someone who is already on the MasterStaffList (an existing member of staff) table will be a candidate for a new role and will be the successful candidate and therefore, their record needs to be updated with the data of the new role.

I just need to figure out how to add a record when I want it to add a record and to update a record when I want it to update.

mike60smart · Sep 20, 2018

Hi

How are you storing the details of Candidates who have not yet had their Contract accepted?

If it is in its own specific Table then on the Status being changed to "Contract Accepted" then an Insert into your Master List table would be easy to implement.

isladogs · Sep 20, 2018

See if this helps
The attached database contains:
a) 2 tables - tblOld with 12 records & tblNew with 6 incomplete records
b) Query qryAppendNew - adds new records from tblOld to tblNew but doesn't update existing records
c) Query qryUpdate - updates records in tblNew but doesn't add any new records
d) Query qryUpEnd - combines both in one query - adds new records AND updates existing records

To use the same idea in your own application (but with modified queries), do something like this:

Code:

Private Sub cmdContractAccepted()

Me.Status="Contract Accepted"

If DCount("*","MasterStaffList","ID =" & Me.ID)>0 Then 
    'record exists - update it
    DoCmd.OpenQuery "qryUpdate"
Else
    'no record - append new
    DoCmd.OpenQuery "qryAppendNew"
End If

End Sub

Or if you feel brave, do it in one query

Code:

Private Sub cmdContractAccepted()

    Me.Status="Contract Accepted"
    'append / update record as appropriate
    DoCmd.OpenQuery "qryUpEnd"

End Sub

HTH

music_al · Sep 21, 2018

Good to see another ex Corps member on here Mike60Smart.

Ex 4 Div, Herford, Germany myself

mike60smart · Sep 21, 2018

Hi

13 Sigs , 226 Sigs, 14 Sigs and Berlin mine

Creating or Updating a Table from a Query (1 Viewer)

music_al

Registered User.

arnelgp

..forever waiting... waiting for jellybean!

isladogs

MVP / VIP

music_al

Registered User.

mike60smart

Registered User.

isladogs

MVP / VIP

Attachments

music_al

Registered User.

mike60smart

Registered User.

Similar threads

Users who are viewing this thread