Add new record from form and enter data into two tables

[Moss100]

Hello - firstly thank you all all who have helped me in the past - much appreciated.

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

 

[Moss100]

Any ody about -

I guess what I am trying to do is add the record to both tables setting the records many tables refernce field with the main tables primary key.

Thanks Mark

 

[MajP]

This would be a little involved for someone who has never coded. The first time is easy, you insert the parent, do a dlookup to find the new autoid (max), then insert the child. However, if a property already exists you would have to determine that, find its ID, and only insert the child record. The design seems awkward to me, unless you can code I would recommend bound forms. What is wrong with a typical form subform parent/child? You would really have to convince me you need an unbound form, because 99/100 you can do the same thing bound but way easier.

To do this unbound you create an insert query. Numbers are not delimited, text fields require single quotes, dates require #.

INSERT INTO table_name (column1,column2,column3,...)
VALUES (value1,value2,value3,...)

So your code is something like this

dim someText as string
dim someNumber as long
dim someDate as date
dim strSql as string

someText = me.someControl
someNumber = me.someOtherControl
someDate = format(me.someOtherControl2,"MM/DD/YYYY")

strSql = "INSERT INTO table_name (textfieldName,numberfieldName,datefieldName)"
strSql = strSql & " VALUES ('" & someText & "', " & someNumber & ", #" & someDate & "#)"
debug.print strSql ' verify it
currentdb.execute strSql

'Now insert the child

 

[MajP]

Sorry, that would be a bad example. You would likely not need to format function for the date if the form is unbounded.

 

[Andrzejek]

If you could have a single quote in the [tt]textfieldName[/tt] field, like a last name (O'Brien) or something user can type (description, location, etc.), don't forget:

[tt]...VALUES ('" & [blue]Replace([/blue]someText[blue], "'", "''")[/blue] & "', "[/tt]

Have fun.

---- Andy

 

[Moss100]

Thanks - I would only use this method for adding a new record, so there would be no need to look to see if it already exists.

I have seen and had working code for adding a new record to one table, but please could you show me how I look up the id for the record I am adding so I can set the id for the second table record

Thanks mark

 

[MajP]

Normally if you are adding a child record to a parent record, you would first select the parent from a pulldown or a main form. You would need to describe how your parent form worked. In my mind I would have a combobox with property name and a hidden bound field with property ID. Then you select a property name and the value of the combo is the property ID.

 

[Moss100]

I would like to have an unbound form with unbound text boxes.

The values would then be sent to the tables only when the user clicks a save button.

At present I am using a bound form. Depending on where the bound form is opened from, a date record is inserted. So if a user opens the form from my main menu the date is set to today, but if it is opened from a different form the date is set to a weeks time.

As the date is set when the form is opened, this automatically creates a record. If the user then decides not to continue and quits the form, a record is inserted into the database without anything other than a date. I figured using an unbound form and code would stop these unfinished records ending up in the table.

Hope that makes sense. Thanks mark.

 

[MajP]

If the user then decides not to continue and quits the form, a record is inserted into the database without anything other than a date.

It is simple to avoid that situation, but choosing to go with unbound forms is not the simple solution. Normally this is done on the form's before update event. Here you can check your values and issue a cancel command if they do not meet your validation. So in sense the record never updates.

 

[Moss100]

Thanks for this - what would I put in the before update event?

Thanks mark.

 

[PHV]

what would I put in the before update event
All your validation rules ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Moss100]

When I run the undo command it does not undo the record.

I have tried me.undo

I have tried just creating a new button with the wizard and choosing the undo function (this gets a message when clicked saying undo is not available)

The form is set for data entry and has on open event setting a date.

Why would undo not work in this situation?


Thanks mark.

 

[Moss100]

Ps.

How do I go the following?

"The first time is easy, you insert the parent, do a dlookup to find the new autoid (max), then insert"

Would this always get the ID of the record or could two users adding at the same time end up with the wrong ids set?

Thanks mark

 

[dhookom]

At present I am using a bound form. Depending on where the bound form is opened from, a date record is inserted. So if a user opens the form from my main menu the date is set to today, but if it is opened from a different form the date is set to a weeks time.

As the date is set when the form is opened, this automatically creates a record. If the user then decides not to continue and quits the form, a record is inserted into the database without anything other than a date.

I would set the default value for the bound date field. If no other value is updated by the user then there is no record insert.

If you are worried about "two users adding at the same time" then add a field to the table to distinguish either the user or computer.

Duane
Hook'D on Access
MS Access MVP