How do I add records using a form ? (1 Viewer)

[liamfitz]

Sounds basic I know ( and maybe should be obvious ), but how do I enter new data/records into my DB ? I spent a while ensuring the query my form is based on allows additions ( as shown by the last navigation button 'Add New' being active eventually). However, it just locks and accepts no new fields/records. Neither will the Query accept new data, when I run that 'standalone' . Only by going into the table ( tblClients ) directly, which is the main table in the form's Recordsource Query, can I add records. Any pointers much appreciated.

 

[CraigDolphin]

Hard to comment without seeing the SQL of your query....and even then, my ability to cold read SQL is shaky Thankfully there are much cleverer chaps and chap-esses here who are better than me at that.

However, if I were to take a guess, you've either got a totals query (using group by, sum etc) and/or you've got a query that uses more than one table to get its fields from.

A form can only input data into one table or updateable query at a time. Usually, if you wish to input data into two or more related tables, you use forms and linked subform(s) to input the data into each source object and using the master/child key field to link the data.

But for more specific advice, you're going to either want to post an example database, or the sql of your query along with a description of the underlying table structure if at all possible.

Otherwise this becomes an exercise of guessing at riddles in the dark

 

[liamfitz]

You're right. I've got a query that uses data from more than one table. If I could change some property or setting on the form ( that would allow me at least, to type text ) I could use events related to each individual text box/combo box, and VBA, to update that way. I find it difficult to believe, the designers @ Microsoft haven't considered the real possibility, that a main form, may need fields from more than one table displayed, from a fully 'normalised' table/relationship structure, and at least provide for the contingency of adding to/editing those fields/records programatically ? I can send a copy of DB if you think you can help, ( the SQL statement in question is quite long, so I would send a shorter version which should illustrate the theory or principle - difficulty in fact - just as well ) Thanks.

 

[CraigDolphin]

But M$ does permit you to add data to related tables, you just need to use forms/subforms. Because the pk of the main form gets copies to any data in the related tables, it actually makes life a lot easier than programmatically adding data. That said, there's nothing to stop you using vba and dynamic SQL to insert, delete, update data to related tables from a main form bound to a non-updateable recordset if you like too. It's just more work if you do it that way. But there are times I've taken that approach to achieve a particular UI. But that's pretty rare.

An example database showing just the tables, your forms, and the source query would be helpful for demonstrating the problem you're trying to tackle. That said, I'm heading home oin a few minutes so will likely not get to look at it til tomorrow.