Referential Integrity

[SJW123]

I have a master table, tblProject, and set a one to many Relationship with
tblsource, and specified Referential Integrity, but I am
having trouble adding new values to the tblsource. I am trying to
populate the field via a cbo and I should be able to enter new data into the
table via a cbo, which then copies to the master. The cbo is bound to the
field 'source' which appears in tblsource and tblproject. when I try to enter
new data into the cbo, I am receiving the correct message asking whether I
wish to add new data, but when click yes, an error msge appears - stating no
record can be written on the many side if there is no corresponding Record on
the one side. I am confused.
I have similar cbo operating on other databases doing exactly what is
required, ie.added new data via a cbo and the many sided sourceID is copied
to the corresponding field in master, but I cannot replicate the process.
hopefully this makes sense and many thanks for advice
Steven

 

[Steve Schapel]

Steven,

Is the 'Source' field the basis of the relationship between the two
tables? If so, you will not be able to enter a value into the Source
field in the tblsource table unless a record already exists in the
tblProject table with this same value in the source field there... this
is the meaning of referential integrity. Have I understood your
situation correctly?

 

[SJW123]

Steve

No, the basis for the relationship is ProjectID.
My code for the cbo on entering new data at 'notinlist' is :
Private Sub Combo29_NotInList(NewData As String, Response As Integer)

Dim db As Database
Set db = CurrentDb

'Ask the user if they want to add to the list
If MsgBox("Do you want to add this entity to the list?", vbYesNo +
vbQuestion, "Add new value?") = vbYes Then

'The user clicked Yes - add the new value
db.Execute "INSERT INTO tblSource (Source) VALUES (""" & NewData & """)",
dbFailOnError

'Tell Access you've added the new value
Response = acDataErrAdded

Else

'The user clicked No - discard the new value
Me.Combo29.Undo
'Tell Access you've discarded the new value
Response = acDataErrContinue

End If

db.Close
Set db = Nothing

End Sub

As mentioned, this code works OK. It's saving the new data to the field that
causes the problem with the master entry.
thanks
Steven

 

[Steve Schapel]

Steven,

Do you mean that there is a ProjectID field in the tblSource table? So
what is the mechanism for entering the ProjectID into the tblSource
table? And which tblProject record is it supposed to be related to?
Sorry, I can't seem to get me head around what you're doing here. I can
understand a combobox whose Row Source is based on the tblSource table,
and this is used for entering source data into the tblProject table.
But I can't get how there is a one-to-many relationship between
tblProject and tblSource based on a ProjectID. And, assuming this is
correct that there should be a ProjectID in tblSource to relate it to
tblProject, obviosly you still have to make sure the ProjectID in any
new tblSource record will obey Referential Integrity, i.e. will be the
ProjectID value of an existing tblProject record.

 

[SJW123]

Steve
Apologies for my unclear explanation. ProjectID is an autonumber primary key
in tblProject. ProjectID is a number in tblSource. Linked by referential
integrity. Your explanation of what I am trying to achieve is correct.
thanks

 

[Steve Schapel]

Steven,

Regarding "Your explanation of what I am trying to achieve is correct",
to be honest I still can't understand what you are trying to achieve.
You are trying to enter a Source into the tblProject table, based on the
data in the tblSource table, and at the same time, you are trying to
enter a ProjectID into the tblSource table, based on the data in the
tblProjects table. Sorry, this doesn't make sense to me. I asked some
questions before, which you didn't respond to: What is the mechanism for
entering the ProjectID into the tblSource table? And which tblProject
record is it supposed to be related to? Maybe you could help by
explaining some more details about the data in these tables and what it
is all about, maybe with some examples?

 

[SJW123]

I am a beginner in Access and trying to adapt what is happening in another
database we have, to a new database tracking project opportunities. this new
database incl. details such as title, description, source of funds, type of
project. I have designed a form based on tblProject, where all information is
entered into tblProject, including the source of funding (through the
problematic cbo).

There are many possibilities for funding and we are trying to use a cbo for
easier input of info (eg. World Bank, Asian Dev Bank, Governments etc) and
populate the main table. The primary key in the main table - tblProject is
ProjectID and an autonumber. This is the field linking to ProjectID in
tblsource (where it is a number). I had always assumed (wrongly?) that the
autonumber generated within tblProject for ProjectID when entering new
project details, would automatically transfer to the ProjectID field in
tblsource. I have been unable to enter any new data through the cbo.
Therefore, I do not know the mechanism for entering the ProjectID value into
tblsource.

So, yes, I am trying to enter a value for source of funding into tblProject
based on information entered into the tblsource via a cbo. For the transfer
of the value for ProjectID in tblProject into the ProjectID field in
tblsource, I don't know how this is done.
tblProject :
ProjectID - autonumber
Title - text
Source - text
Description - Memo
type of project - text

tblsource :
sourceID - autonumber
ProjectID - number
source - text

I hope this explanation makes sense and thanks for your patience.

 

[Steve Schapel]

Steven,

Thank you for your further information. Ding!! I think I can now see
what you want. Any given Project can have more than one source of
funding, right? So therefore, it is not appropriate to have the Source
field in the tblProjects table at all. You need 3 tables, like this...

tblProject :
ProjectID - autonumber
Title - text
Description - Memo
type of project - text

tblSource :
source - text

tblFunding :
FundingID
ProjectID - number
source - text

The tblSource table will be your "master list" of funding sources, and
it is there that you want to add new items as they arise. And the
tblFunding will be the basis of a continuous view subform on the
Projects form, where you can enter all the funding sources for the
current project.

Am I right? If so, this article may be of interest...
http://accesstips.datamanagementsolutions.biz/many.htm

 

[SJW123]

Steve
We are almost there, and I was remiss in leaving out one fact. For each
project there is only one funding source (I currently have a one-to-many
relationship between tblProject and tblsource). We could eventually have
hundreds of projects on this database. and a drop down menu on a cbo would
make things easy.
I can enter new funders into the cbo when this is necessary, but hte problem
remains populating the main tblProject.
thanks

 

[Steve Schapel]

Steven,

Well, we have now gone around in a complete circle. One funding source
per project was what I had originally assumed, but then I decided I must
be wrong about that, seeing as we weren't getting anywhere. Well, if
there's only one funding source, why do you think you need a ProjectID
in the tblSource table? And what does a one-to-many relationship
between tblProject and tblSource mean, if there is not more than one
source per project? Just delete the ProjectID field. This is all you
need...

tblProject :
ProjectID - autonumber
Title - text
Source - text
Description - Memo
type of project - text

tblSource :
Source - text

 

[SJW123]

Many thanks for your persistence Steve.
It now works.
I had always thought all tables had to be linked through referential
relationships, thus locating ProjectID in tblstatus.
I am continually learning, yet seems everytime advance, need to go back and
learn some basic point.
thanks again.

 

[SJW123]

Sorry Steve
One more query.
I have set-up a table based on tblProject. I have included source as one of
the fields, but am only getting a blank in this field on the report, although
the correct information is appearing in the source field on tblProject. I
tried basing this report on a query between tblsource and tblProject but then
everything is blank (I suppose because there is no relationship between these
two tables).
thanks for any advice.

 

[Steve Schapel]

Steven,

No, relationships do not have to be defined between tables. Only where
applicable... which in practice is most tables, where the data is
related, you will want to define relationships. But you see, in your
example, there is a relationship, it's just not what you were defining
it as. There is a one-to-many relationship between tblSource and
tblProjects, based on the the Source field. I.e. for every record in
the tblSource table, there can be more than one related record in the
tblProjects table. Right? Now, to say this is a relationship is true
in a relatively trivial sense, because tblSource is really just a lookup
table, not a table that contains core operational data.

 

[Steve Schapel]

Steven,

Sorry, I can't think why this is happening. If your Source field in the
tblProjects table contains the correct data, and you make a report based
on this table which includes the Source field, then the correct data
should be shown on the report. Did you follow my earlier suggestion,
and just have the one Source field in the tblSource table? You haven't
got the Source field in the tblProjects table set up in the table design
as a Lookup Field, have you?