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.

 

[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...