cant create forms based on tables!

shez

Registered User.
Local time
Today, 03:50
Joined
Jan 27, 2009
Messages
28
Hi

I have no idea what i am doing wrong but i cant seem to create forms based on multi-tables! If i create them using wizards, the forms can be seen and they have the "dummy record i created" displayed. however i cant seem to edit it or create a new record through the form!

In desperation I am attaching my database so that you can have a look at the relationship or suggest something!
 

Attachments

A form can only have a single data source, so what you will need to do is base your form on a query, that gathers together all the data you wish to show on your form.
 
Additionally I would add a primary key, in the form of an Auto Number to each of the tables.
 
I have tried a query too and when i run the query it gives me all the records (currently 1) in the table! when i convert that query into a form, same thing happens i.e. i can view the current record but cant add/edit the data :s

form properties are appropriately set
 
Which is the form that is currently giving you grief?


i am sorry for being so silly! I should have mentioned the form. it is the ADD PARTICIPANT form.

I have created a similar form to add participant but it uses subform. This seems to allow me to change data in the dummy record and also allow me to add data however there is a problem because it states that "A related record required in personalDetails table"
 
Last edited:
Your data structure seems to be unnecessarily complicated. You have several 1to1 links but you are using a different identifier for each of them from the PersonalIdentifier table. I think this is what is giving you the problems. If they are truly 1to1 then you should use the same id to link them.

When I ran your DB it was trying to create a new record but because of the complex links it couldn't get these to work properly. so it couldn't build valid records.
 
You have fields in the query on which the form is based that don't appear on the form but are related in your relationship diagram with referential integrity enforced.
 
thanks for answering! one last question and if it doesnt work then I will rechange the relationship.

I was trying and rebuild query from scratch. I added different tables individually to see where the problem lies and i found out that somehow it lies in the uni_link/university table! The problem is that these tables are created exactly the same as college_link/college table and when i added college tables, the query was working (ie i could add records!) ??

Rabbie: What do you mean by a true 1:1 relationship? do you mean that I should use something like:

table1(tbl1Id,....)
table2(tbl1Id,.....)

because i was told that for a 1:1 relationship you should use:

table1(tbl1Id,table2Id....)
table2(table2Id,....)

because each ID is representative of each table so if i just put table1 ID in table2 ID then it doesnt make sense e.g. for my tables I cant have a refNo of a student in a uniLink table? or am i thinking it wrong?
 
Rabbie: What do you mean by a true 1:1 relationship? do you mean that I should use something like:

table1(tbl1Id,....)
table2(tbl1Id,.....)

because i was told that for a 1:1 relationship you should use:

table1(tbl1Id,table2Id....)
table2(table2Id,....)

because each ID is representative of each table so if i just put table1 ID in table2 ID then it doesnt make sense e.g. for my tables I cant have a refNo of a student in a uniLink table? or am i thinking it wrong?
Are both sides of your 1to1 links alwatys there and is it guaranteed that there will never be more than record from each table linked this way.

My understanding of 1to1 is you use the same ID for both tables. If it is a true 1to1 relationship then this will always work. You would need to review the design if in fact it it is not a true 1To1 but is just a special case of a 1toMany with only one child record.

I understand the the theoretical need for 1to1 relationships especially when you need to restrict access to confidential information but in many years of using Access I have never needed to use a 1to1 relationship. Are you sure that you are using them correctly. It seems to me that all your issues are coming from the plethora of 1to1 you have floating around.

As Neil correctly says You were also missing fields from your query that were needed for RI reasons.
 
Hi

Thank you RABBIE & NEILG and everyone who has helped me.

I took into account what you have said rabbie & after thinking about it, you were right about the unnecessary field i was creating. I modified my design & now it works (so far!)
 
Glad to have been of help. Good luck. remeber to post again if you still have problems.
 

Users who are viewing this thread

Back
Top Bottom