Need help coding a function to define relationship

[Bob Howard]

Hi everyone....

I have an application deployed with front-end / back-end, and have periodic
need to replace front-end with a new version. Sometimes, this new version
requires some structure changes to the back-end (add a field to an existing
table, add a table, define a relationship, etc.).

So I have a little mdb (I call it the "upgrade" processor) that I've built
that is deployed along with the new front-end version. The user executes
the upgrade processor as part of the process of installing the replacement
front-end. When executed, it makes the necessary changes to the back-end
for the new front-end version.

Within the upgrade processor, I would like to be able to clean up the code a
bit. In the next series of updates, I have need to add some tables and
define three relationships between these new tables, plus a fourth
relationship between one of the new tables and one of the existing tables in
the back-end.

Rather than having the series of code four separate times in the upgrade
processor, I wanted to create a Function to define the relationship, and
execute it four times. Here's the Function:

==========================================
Private Function CreateRelationship( _
relationshipName As Variant, _
fromTable As Variant, _
fromField As Variant, _
toTable As Variant, _
toField As Variant, _
relationshipAttribute As Long)

' In a one-to-many relationship:
' from = the "one" side
' to = the "many" side

' Attribute:
' 0 (default): ref-Int w/o casc updt or del
' dbRelationDeleteCascade: Ref-Int with casc del
' dbRelationUpdateCascade: Ref-Int with casc updt
' dbRelationDontEnforce: No Ref-Int

Dim daoDatabase As DAO.Database
Set daoDatabase = OpenDatabase(Command)
Dim newRel As Relation
Set newRel = daoDatabase.CreateRelation( _
relationshipName, _
fromTable, _
toTable, _
relationshipAttribute)
newRel.Fields.Append newRel.CreateField(fromField)
' ***** THE FOLLOWING LINE FAILS *****
newRel.Fields![fromField].ForeignName = toField
daoDatabase.Relations.Append newRel

End Function
==========================================

The code to execute this Function is as follows:

CreateRelationship _
"Ministries_to_Activities", _
"Attendance Ministries Table", _
"Record Number", _
"Attendance Activities Table", _
"Record Ministry", _
0

The line indicated fails to properly pick up the fromField value (Record
Number). When I hard-code this value into that line in the Function, it
works fine and the relationship is created.

The error I'm receiving is #3265 (Item not found in this collection). The
line prior (which also uses the fromField variable) adds the Item to the
collection --- so what I'm is that "fromField" is not properly being
substituted into the failing line.

Is it possible that the double quotes are being substituted as well?

Does anyone have any input on this?

Thanks in advance !!!!!

Bob (@Martureo.Org)

 

[Bas Cost Budde]

newRel.Fields.Append newRel.CreateField(fromField)

I'd do

dim fd as field
set fd=newrel.createfield(fromfield)
fd.foreignname=tofield
newrel.fields.append fd

i.e. assign ForeignField first, then append to collection.

' ***** THE FOLLOWING LINE FAILS *****
newRel.Fields![fromField].ForeignName = toField

anyway, this should read
newRel.Fields(fromField).ForeignName

 

[Bob Howard]

thanks --- it now works!

newRel.Fields.Append newRel.CreateField(fromField)

I'd do

dim fd as field
set fd=newrel.createfield(fromfield)
fd.foreignname=tofield
newrel.fields.append fd

i.e. assign ForeignField first, then append to collection.

' ***** THE FOLLOWING LINE FAILS *****
newRel.Fields![fromField].ForeignName = toField

anyway, this should read
newRel.Fields(fromField).ForeignName