Unableto Add a new record

[Jen0dorf]

Hi

on the homeward run I think!!!

Created three tables and one linking table, created two queries linked to a form to display the results and have now setup the code to display details.

Next step to create a form to add a new record to the database.

Initialy I used the form wizard, dragged the fields in and then viewed the form to see if I could add a record.

However the new record button at the bottom is greyed out.

I checked the Form properties and allow editions, deletions and edits are all set to yes.

the source for the form is:

SELECT [tblGraves].[Plot], [tblGraves].[GraveNo], [tblGraves].[GraveID], [tblDeceased].[Forenames], [tblDeceased].[Surname], [tblDeceased].[DayOfDeath], [tblDeceased].[MonthOfDeath], [tblDeceased].[YearOfDeath], [tblDeceased].[DayOfBurial], [tblDeceased].[MonthOfBurial], [tblDeceased].[YearOfBurial], [tblDeceased].[Age], [tblDeceased].[pp], [tblDeceased].[Notes], [tblDeceased].[Inscription] AS tblDeceased_Inscription, [tblDeceased].[DeceasedID], [tblMemorialGraveJoin].[fk_GraveID], [tblMemorialGraveJoin].[fk_MemorialID], [tblMemorialGraveJoin].[MemorialGraveID], [tblMemorial].[Inscription] AS tblMemorial_Inscription, [tblMemorial].[LInks] FROM (tblGraves INNER JOIN (tblMemorial INNER JOIN tblMemorialGraveJoin ON [tblMemorial].[MemorialID] =[tblMemorialGraveJoin].[fk_MemorialID]) ON [tblGraves].[GraveID] =[tblMemorialGraveJoin].[fk_GraveID]) INNER JOIN tblDeceased ON [tblGraves].[GraveID] =[tblDeceased].[fk_GraveID];

and it is a dynaset record set type.



So I thought ( dangerous I know) use a query, I created the query setup the joins etc etc.

I double clicked on the query and all my records were listed, I then selected the new record and the bottom but that is greyed out as well.

Yet again I am at a loss

any help appreciated as ususal

thanks

Ian

[ItsMe]

If you create a query using the SQL in the form's recordsource, can you create new records while in datasheet view of the new query object?

When you create a JOIN within a query, you will typically create a non-updatable query.

[Jen0dorf]

Hi

thanks for the reply

If I open the query in datasheet view I cannot enter new records, it appears that the issue is that I'm trying to add a new record that is spread across three tables.

Back to the drawing board

thanks

Ian

[ItsMe]

You might want to try Left Joins on indexed columns to maintain the 'updatable' status of your query.

[Jen0dorf]

HI

having spent hours on this I'm beginning to give up.

I have a database spread over three tables plus a joining table. I created a query to display all 39201 records in datasheet view.

If I double click on the query and do into datasheet view I cannot select the new record button at the bottom.

So thinking again I created a new table called tblNewRecord containing all the fields from the other three fields -

I then tried to create an append query to append the records to the three tables but whilst making the query I have to select the table to update.

So I created three queries one for each table

The next stage is to some how get all three queries to run and when successful delete the record in tblNewRecord

Is this possible? and is there a better way, it seems to me to be very clumsy

thanks

Ian

[Homegrownandy]

What do you want to do? just have all the information you have to view in a big list?

Querys are not for adding data mate. you need forms ( I know you said in your first post you use forms but you also said query in the last one).

make a new copy of your database. delete the data in it. post it here. Ill happily have a look. I don't mind fixing small problems and explaining how/why.

Its both hard to explain and understand problems so detailed on here. I have the same problem in most of my posts. haha.

[ItsMe]

...Querys are not for adding data mate. you need forms ( I know you said in your first post you use forms but you also said query in the last one)....

I suggested to Ian that he make a query. Often times, forms are bound to queries. I usually make sure I have a working query before I concern myself with building a form.

[ItsMe]

...
So I created three queries one for each table...

If you add some data to your database, will it always be necessary to create a record in each of your 4 tables? Would there ever be a reason to add a record to just one table?

If you have a joining table that is managing a many to many relationship, it will be easiest to use some VBA to append records and maintain referential integrity. However, not every change to your database should require an append to this 'joining table'. Perhaps it would be best to isolate different actions that require update and append actions.

Is there an action that a user might take that would require an append to only two tables?

[Jen0dorf]

Hi

I've attached a copy of the database with 10 records in it.

When a new record is added entries will always be made in tblGraves and tblDeceased, the linking table should automatically inhabit ( I hope) and tblMemorial will have an entry if the grave has a memorial.

Just to clarify:

Any single grave can have multiple deceased and multiple memorials.

As far as I'm aware the two searches work perfectly ( one for year of death and one for Surname) as does the onclick to open the frmDetail.

As I said at the start it's just adding a new record that is fighting me

thanks

Ian

[ItsMe]

...As I said at the start it's just adding a new record that is fighting me...

I took a look at your DB. I do not see any forms that would be used to create new records. Before I looked at your DB I imagined more than one requirement for creating records and or updating records. In other words, some business rules would require appending a record to a single table while other business rules would require appending records to multiple tables (more than one table).

After looking at your DB, my viewpoint remains the same. You need to distinguish different actions that a User might need to take. I can imagine certain circumstances where a user may have the need to create records in each of the tables, all within a short period of time. However, these actions need to be segregated within your application. Consider each act of creating a new record as an act that is independent from others. At the end of the day, there may be 5 different acts or reasons to create a new record.

You need to start by isolating the business rules as separate actions. Ask yourself different questions. I do not know what this database is for or about. Assuming this is a business, I would be prepared for someone walking through the front door and asking for a plot but not a memorial. Or maybe someone desires a memorial but not a plot. Maybe someone needs to create an account and has information for a deceased but has not decided if they want a memorial or a plot or both.

You may need a form for each possible act that will create new data or update existing data. You may be able to combine two or may acts into a single form (known as CRUD operations). If there is a single act that will append multiple tables (like tblGraves and tblDeceased) you should be able to create an updatable query that retrieves data from both tables.

[Jen0dorf]

Hi

apologies for any confusion about what the database does.

I'm creating it for members of my Family Tree society and it's a list of Graves, occupants and memorials. It's a labour of love as it is for all members who answer questions from around the world about the local family history.

The only person entering records will be the admin - myself at the moment. The end user- members will have no means or wish to do so!

The database will be compiled into a runtime -and split if necessary-and sent to members who will be interested in specific graves. The grave will be located by one of two reference facts: either Surname or year of death results will be filtered after that.

So really only two those twp searches are necessary.

As the the tables and forms to create a new record I deleted them from the uploaded version so as to simplify the issue.

Hope this clarifies what Im trying to do. No doubt to you experts it's simple but to me .....

thanks

Ian

[Jen0dorf]

Hi

thanks for the input, the database is not for business and is probably simpler than you can imagine;-) Probably my explanation complicates things- sorry

I will look up CRUD operations as that sounds a good possibility

thanks again

Ian

[ItsMe]

CRUD = Create Read Update and Delete

Even if you were surveying an existing cemetery, you would segregate the different actions of appending records. How else would you know what value to use in Foreign Key fields? Yes, there are features that are intrinsic to Access that can cascade delete, etc. However, I am not aware of an intrinsic feature to maintain referential integrity of a many to many relationship.

[Jen0dorf]

Hi

thanks for the advice

Ian

[Homegrownandy]

Morning Ian, I'm in work and was stuck on the m56 last night so I've not had/got a lot of time to look into this. I don't think you need a join table at all.
an example of structure:

deceased - all info about the dead guy.
memorial (more than one for a dead person) - info specific to that single memorial... ALSO this is the important bit.. a foreign key relating to the deceased table.


so that one record in deceased.. has multiple records elsewhere that use its FK to combine all the data.

hope this makes sense. ask away.