Adding new record in VBA to main table and secondary related table

[Moss100]

Hello,

I have been 'programming' in Access for around 10 years - and although I have pretty much always acheived the end goal, I have restricted myself to using macros. I undertand that these are not the best way and am trying to learn code.

I would like help to acheive the following.

I have two tables:

tblProperty
tblOccupier

tblProperty has the following fields:

Property_ID (autonumber - primary key)
Property_Name
Property_Postcode

tblOccupier has the following fields:

Occupier_ID (autonumber - primary key)
Property_REF (this will contain the Property_ID number to link records)
Occupier_FirstName
Occupier_Surname

I have an unbound form - FORM1 - with the following textboxes

txtProperty_ID
txtProperty_Name
txtProperty_Postcode
txtOccupier_ID
txtProperty_REF
txtOccupier_FirstName
txtOccupier_Surname

I presume that the two id fields on the form are a bit irrelevant at this stage???

Once the text boxes are filled in I would like to have two buttons:


one to cancel, close the form and not add the record

and

one to add the record to each table and ensure that the correct id numder is entered into the occupier table.

I would be very garteful if someone could take the time to show me the code that I would need to acheive this and also if possible to explain things that I should be aware of, naming conventions, error checksing etc.

Thank you for any help

Regards

Mark

 

[magster06]

Mark,

Access will create an inner join for you to accomplish what you want by add fields to your form using the "Add Existing Fields" button from the Access toolbar ribbon in design view.

As far as coding for the "Save" button, just use the standard macro and then convert the macro to vba using the "Convert Forms Macros to Visual Basic" button and then you can tweak the code as needed.

Unless I am missing something, this will be all you need at the moment. Also, you can do a search for almost any coding that you can adjust to your needs.