SubForm Pass Value to check if Record Exists then Create New Record

[adwyer]

Hi everyone.
Forgive me this is my first post, I am new to MS Access and just exploring VB and macros.
I have an existing external database that I can connect to but can’t modify (read only). I need to add some additional fields to enhance the functionality of this database and the reporting.
Therefore I have created a new table [ProspectAddtionalFieldsT] with a text field [ID] which needs to link the primary key from the existing external database to the table containing the additional fields I need for my reporting.
I have successfully created a search form [ProspectSmartSearch] that displays filtered records in a subform [ProspectSmartSearch subform], now I need to add the capability to CLICK on the “ProspectID” from within the subform to open a new form which passes the Primary Key from the external database (ProspectID) to the new table [ProspectAddftionalFieldsT] and enables me to add and maintain the additional data fields I require.
Issue
I need to check if the “ProspectID” already exists within my new table [ProspectAddftionalFieldsT], If it doesn’t I need to create the new record by passing the “ProspectID” to the new table form the existing subform. Alternatively if it already exists go to the form linked to the primary key.
I have done a lot of research and realise this can be done, but have got myself completely confused with so many similar but slightly different alternatives on addressing this very issue.
I hope this is clear, I lack to correct terminology to communicate exactly what I am attempting to achieve.
Thank you in advance.
Ant

 

[PGAC]

Hi adwyer,

Why not link the external table into you local database and create an append query to copy all external records into the local table? You could just copy the Primary key field. You can then run the append query before you start any work on your local database.

For this to work, the primary key in the external table must be imitated in your local table. Using a text field as a primary key is not good practice. Use an autonumber/long integer.

By definition primary keys are unique so the append query will not duplicate but you might get warnings about records that failed to copy (which will be records that already exist in your local table).