Create New Record

[scarface_jr]

Hi all, hope everyone has had a good Christmas.

I'm just trying a few basic things to get a feel for access/database programming. I'm trying to use a form to input data into the tables in my database. At the moment all tables are blank so the form displays no controls. I tried adding 'DoCMD.GoToRecord , , acNewRecord' to the load event but that returns the error '2105 Can't go to the specified record'. I have set my form's data source to a query since I am taking information from multiple tables which I am guessing is causing the problem. The Query uses an inner join to match the corresponding Shop ID#'s.

SELECT tblShop.*, tblProperties.* FROM tblShop INNER JOIN tblProperties ON tblShop.[Unit #] = tblProperties.[Unit#];

 

[Poppa Smurf]

You can use the form data source to add new SHOPS or new PROPERTIES but you can not use a form data source to add both.

 

[plog]

A form should be based on one table. Where you have a one-to-many relationship in your table structure, you can use sub-forms. However, that still means one form is based on one table.

 

[Pat Hartman]

Although each form should update data in a single table, the RecordSource query might contain multiple tables that provide lookup information.

If the form opens blank, that means your form is set to not allow additions or the query doesn't support additions. Check the form properties and make sure that AllowAdditions is set to Yes. Then open the query and try to add a record directly in the query.

If the query doesn't allow additions, go back to the table definitions and make sure that the primary keys and relationships are correctly defined.

However, I agree with Poppa that you should be using a main form and a subform in this situation.

 

[scarface_jr]

ok thanks.

I've somehow got it to work without using a subform. I think I changed the recordset type to dynaset. I will do another form using a subform and see how I get on. Since this exercises for me is about learning and has no 'real world' value I'm going to experiment with a few things.

When I'm inputting data into my tables using the forms is there a way to link fields without using a subform? I ask because say you only wanted a text box for one field in another table. Say you had three tables.

table1: [UnitID], [Area], [Owner], [Location]
table2: [UnitID], [ELEC_Services], [FuseRating], [FireAlarmType]
table3: [UnitID], [LeaseExpires], [Lease Acquired]

say you wanted all fields from tables 1&2 and only [LeaseExpires] from table 3. Obviously you don't want to input the UnitID again and creating a subform for a single field seems wrong. What is the correct practice?

 

[JHB]

... and creating a subform for a single field seems wrong...

Not it my eyes, (if the table relation is one to one I would put it in the "main" table)!

 

[scarface_jr]

I suppose that makes sense having all the info in one table.

Hypothetically though, would it be possible to link the two unitID's without a subform?

 

[JHB]

Hypothetically though, would it be possible to link the two unitID's without a subform?

The answer would no. You need to have a control there bind the two unitID's together like Master/child fields.
You can do some code which could synchronise the data using the two unitID's value, but it will only work if the main form is a single form and the relation is one to one.
What do you have against subforms?

 

[scarface_jr]

Adding a subform for a single textbox/button/dropdown seems more complicated than adding a textboxt. My background is in console applications so to me it seems simple to create a variable called unitID and then be able to assign it the value to multiple fields/variables. If I had full control and it was a 1-2-1 relationship I would aggregate the tables. However, from what little experience I have you may not have control over the design of the initials tables and you have to work with/around what you are given if that makes sense.

 

[David R]

You can consider the subform to be a complex 'control', especially if it only contains one field. Subforms give you flexibility and leverage the strengths of a relational database - i.e. connecting one-to-many in this situation.

Why might you not have control over your tables?? Don't let other people dictate your data structures if you don't have to - they should only see the final presentation!

 

[scarface_jr]

I work for a reasonably sized organisation (4000+ employees) where we have a data centre that stores our data tables which is populated by our in house case logging application. I do not have control over these tables. Sure I could create my own, but it is quite easy to exceed a million rows in a single query.

What I have done to solve my problem is to add some code to the on-save event of the form that takes the same value from [table1.UnitID] and copies that value to [table3.UnitID].

 

[David R]

Yeah, having to rely on tables from other applications is a pain...