VBA update a table wirh a certificate from an unrelated table

[Oblio]

I have two tables:

t_MCRs:
MCRs_ID
MCRs_DE_ID ( Short text ) this is the certificate field to update

t_Certificates:
Certificates_ ID
MCRs_DE_ID short text, this is where i need to get tge certificate from. It is a pre-populated table of thousands of certificate numbers)
Assigned? Boolean

What would you reccomend as the best approach to apply these certificates to the MCR table ?

Thank you for your advice !

Bill

[ranman256]

Is [Assigned] field needed?

if t_MCRs.MCRs_DE_ID is filled in, then its assigned. This is determined by a query.

[Oblio]

I was thinking that I would need to update the status to assigned=yes, due to my inexperience on how to proceed.

I just do not know how to assign a t_certificates!MCRs_DE_ID to the t_MCRs!MCRs_DE_ID field...as it is not related on anything really....qe are basically just stamping it in. I have actually been pasting them in in datasheet mode...not ideal.

[OBP]

The Text from the certificate table should NOT be transferred to the MCR table.
The MCR table field should be Number type Long and store the CertificateID, which is presumably an Autonumber field.
If the process of transferring the Certificate is as random as you suggest then I would suggest that you use next available certificate and use a Combo to select it or VBA code to add it to the table.

[Oblio]

Would you please be able to advise me how to do this with VBA. I do truly understand your reccomendations as that would be the proper way, HOWEVER, we must do it as a batch after the fact....I have been counting the records in t_MCRs, then copying that many certificate numbers and pasting them into the t_MCRs!MCRs_DE_ID field and noting the ones used in the certificates table. Can you please help me with the VBA to do this please???

Thank you sincerely,

Bill

[OBP]

Do you still have my email address?

[Oblio]

Not where I am now.... could you please resend to me ?

[OBP]

Are you still using your fseso email account?

[Oblio]

Are you still using your fseso email account?

Yes I am...