Adding updatable table to a linked table.

[satori_1]

Im having a mare of a job with something im trying to do..

I have a linked table that links directly into a SQL database and
pulls the status of a users account that’s recorded but the audit team
when they do reviews.


So I get rows of data that tell me the

System, Access Lever, Login,Status and Review ID for each user

SO

Active Directory , Domain Admin , login, Valid, REV00001

And so on…

Now there are people that are marked as NoneValid and they need to be
looked into..

Currently I link to this table and then use my own local table to
provide a comments box that I can update..

SO…
I created a table that contains all the possible review ID's (REV00001
- REV99999999)

I then created a query that looks up the linked table and does a
"Join" with my local comments table so that I get the results

Active Directory , Domain Admin , login, Valid, REV00001-----REV00001
(joined to local table), Comments

This means that people can update the comments table as it’s a local
table and the comments remain joined to the linked table for looking
at in the future…..

That all worked fine until…..

Part of the linked table changed and now there can be 2 logins with
the same reference number… so when I link the comments to it the
comments for one user also show on another if they have the same
reference…

The only way I know to add comments to the table is to link them via a
common key.. That being the rev number at the moment…
Because the users and systems and access levels are always changing
there is no way I can make a local copy and link that to the
comments..

Do you know of anyway to kind of make access take the login access
level and system.. Dump them into a table and to add the comments box
to it? Somebody told me it was a composit key but I cant work out how
the heck to get it working….

 

[Mark A. Sam]

If you have two fields that make a unique value that will work. You would
add the same fields to the comments table. When you add a comment you many
have to assign the values through code on the comments form. If the
comments is a subform, you try could assigning the LinkMasterFields and
LinkChildFields property as [field1];[field2], but I don't know if this will
work if the composit fields are not a primary key. You need to test it.

If this doesn't make sense, respond back.

God Bless,

Mark A. Sam




Im having a mare of a job with something im trying to do..

I have a linked table that links directly into a SQL database and
pulls the status of a users account that’s recorded but the audit team
when they do reviews.


So I get rows of data that tell me the

System, Access Lever, Login,Status and Review ID for each user

SO

Active Directory , Domain Admin , login, Valid, REV00001

And so on…

Now there are people that are marked as NoneValid and they need to be
looked into..

Currently I link to this table and then use my own local table to
provide a comments box that I can update..

SO…
I created a table that contains all the possible review ID's (REV00001
- REV99999999)

I then created a query that looks up the linked table and does a
"Join" with my local comments table so that I get the results

Active Directory , Domain Admin , login, Valid, REV00001-----REV00001
(joined to local table), Comments

This means that people can update the comments table as it’s a local
table and the comments remain joined to the linked table for looking
at in the future…..

That all worked fine until…..

Part of the linked table changed and now there can be 2 logins with
the same reference number… so when I link the comments to it the
comments for one user also show on another if they have the same
reference…

The only way I know to add comments to the table is to link them via a
common key.. That being the rev number at the moment…
Because the users and systems and access levels are always changing
there is no way I can make a local copy and link that to the
comments..

Do you know of anyway to kind of make access take the login access
level and system.. Dump them into a table and to add the comments box
to it? Somebody told me it was a composit key but I cant work out how
the heck to get it working….

 

[satori_1]

If you have two fields that make a unique value that will work.  You would
add the same fields to the comments table.  When you add a comment you many
have to assign the values through code on the comments form.  If the
comments is a subform, you try could assigning the LinkMasterFields and
LinkChildFields property as [field1];[field2], but I don't know if this will
work if the composit fields are not a primary key.  You need to test it..

If this doesn't make sense, respond back.

God Bless,

Mark A. Sam



Im having a mare of a job with something im trying to do..

I have a linked table that links directly into a SQL database and
pulls the status of a users account that’s recorded but the audit team
when they do reviews.

So I get rows of data that tell me the

System, Access Lever, Login,Status and Review ID for each user

SO

Active Directory , Domain Admin , login, Valid, REV00001

And so on…

Now there are people that are marked as NoneValid and they need to be
looked into..

Currently I link to this table and then use my own local table to
provide a comments box that I can update..

SO…
I created a table that contains all the possible review ID's (REV00001
- REV99999999)

I then created a query that looks up the linked table and does a
"Join" with my local comments table so that I get the results

Active Directory , Domain Admin , login, Valid, REV00001-----REV00001
(joined to local table), Comments

This means that people can update the comments table as it’s a local
table and the comments remain joined to the linked table for looking
at in the future…..

That all worked fine until…..

Part of the linked table changed and now there can be 2 logins with
the same reference number… so when I link the comments to it the
comments for one user also show on another if they have the same
reference…

The only way I know to add comments to the table is to link them via a
common key.. That being the rev number at the moment…
Because the users and systems and access levels are always changing
there is no way I can make a local copy and link that to the
comments..

Do you know of anyway to kind of make access take the login access
level and system.. Dump them into a table and to add the comments box
to it? Somebody told me it was a composit key but I cant work out how
the heck to get it working….

It is my understanding from what you have said that I just create a
table with 2 of the fields with the same name as my linked table plus
my comments section...

Then i run a queary to display both the linked table and the local one
and that should link them together?

I have tried this and i get no results at all..

If i enter something in one of the comments boxes then i get whatever
i enter against every record in the linked table :-(

 

[Mark A. Sam]

It doesn't work likely becuase there are no values in one or both of the
linking fields. They need to be populated some way. Open the Comments
table and see if any field values are missing.

If you have two fields that make a unique value that will work. You would
add the same fields to the comments table. When you add a comment you many
have to assign the values through code on the comments form. If the
comments is a subform, you try could assigning the LinkMasterFields and
LinkChildFields property as [field1];[field2], but I don't know if this
will
work if the composit fields are not a primary key. You need to test it.

If this doesn't make sense, respond back.

God Bless,

Mark A. Sam



Im having a mare of a job with something im trying to do..

I have a linked table that links directly into a SQL database and
pulls the status of a users account that’s recorded but the audit team
when they do reviews.

So I get rows of data that tell me the

System, Access Lever, Login,Status and Review ID for each user

SO

Active Directory , Domain Admin , login, Valid, REV00001

And so on…

Now there are people that are marked as NoneValid and they need to be
looked into..

Currently I link to this table and then use my own local table to
provide a comments box that I can update..

SO…
I created a table that contains all the possible review ID's (REV00001
- REV99999999)

I then created a query that looks up the linked table and does a
"Join" with my local comments table so that I get the results

Active Directory , Domain Admin , login, Valid, REV00001-----REV00001
(joined to local table), Comments

This means that people can update the comments table as it’s a local
table and the comments remain joined to the linked table for looking
at in the future…..

That all worked fine until…..

Part of the linked table changed and now there can be 2 logins with
the same reference number… so when I link the comments to it the
comments for one user also show on another if they have the same
reference…

The only way I know to add comments to the table is to link them via a
common key.. That being the rev number at the moment…
Because the users and systems and access levels are always changing
there is no way I can make a local copy and link that to the
comments..

Do you know of anyway to kind of make access take the login access
level and system.. Dump them into a table and to add the comments box
to it? Somebody told me it was a composit key but I cant work out how
the heck to get it working….

It is my understanding from what you have said that I just create a
table with 2 of the fields with the same name as my linked table plus
my comments section...

Then i run a queary to display both the linked table and the local one
and that should link them together?

I have tried this and i get no results at all..

If i enter something in one of the comments boxes then i get whatever
i enter against every record in the linked table :-(

 

[satori_1]

It doesn't work likely becuase there are no values in one or both of the
linking fields.  They need to be populated some way.  Open the Comments
table and see if any field values are missing.

If you have two fields that make a unique value that will work. You would
add the same fields to the comments table. When you add a comment you many
have to assign the values through code on the comments form. If the
comments is a subform, you try could assigning the LinkMasterFields and
LinkChildFields property as [field1];[field2], but I don't know if this
will
work if the composit fields are not a primary key. You need to test it.

If this doesn't make sense, respond back.

God Bless,

Mark A. Sam

Im having a mare of a job with something im trying to do..

I have a linked table that links directly into a SQL database and
pulls the status of a users account that’s recorded but the audit team
when they do reviews.

So I get rows of data that tell me the

System, Access Lever, Login,Status and Review ID for each user

Active Directory , Domain Admin , login, Valid, REV00001

And so on…

Now there are people that are marked as NoneValid and they need to be
looked into..

Currently I link to this table and then use my own local table to
provide a comments box that I can update..

SO…
I created a table that contains all the possible review ID's (REV00001
- REV99999999)

I then created a query that looks up the linked table and does a
"Join" with my local comments table so that I get the results

Active Directory , Domain Admin , login, Valid, REV00001-----REV00001
(joined to local table), Comments

This means that people can update the comments table as it’s a local
table and the comments remain joined to the linked table for looking
at in the future…..

That all worked fine until…..

Part of the linked table changed and now there can be 2 logins with
the same reference number… so when I link the comments to it the
comments for one user also show on another if they have the same
reference…

The only way I know to add comments to the table is to link them via a
common key.. That being the rev number at the moment…
Because the users and systems and access levels are always changing
there is no way I can make a local copy and link that to the
comments..

Do you know of anyway to kind of make access take the login access
level and system.. Dump them into a table and to add the comments box
to it? Somebody told me it was a composit key but I cant work out how
the heck to get it working….

It is my understanding from what you have said that I just create a
table with 2 of the fields with the same name as my linked table plus
my comments section...

Then i run a queary to display both the linked table and the local one
and that should link them together?

I have tried this and i get no results at all..

If i enter something in one of the comments boxes then i get whatever
i enter against every record in the linked table :-(- Hide quoted text -

- Show quoted text -

Ok... ermmm...

I created a local table that contains Login, system and comments...

I then made a qeary that contains all data from my linked table + all
data from the table above...

Then when i run it I get no lines of data..

If i enter comething in the comments table under the comments columb..

When I wun teh quesry again i get the same data from the comments
against every record in the linked table...

The linked table is never blank... the local table is because i need
it to auto fill in whatever login and system are valid for the
comments im putting in.. somehow it needs to work out im putting
comments in for that login and system and then fill on those fields in
the table by itself..

Im not sure im explaining it very well :-(

 

[Mark A. Sam]

The linked table is never blank... the local table is because i need
it to auto fill in whatever login and system are valid for the
comments im putting in.. somehow it needs to work out im putting
comments in for that login and system and then fill on those fields in
the table by itself..

Im not sure im explaining it very well :-(

You are explaining well, but I don't think you know the techniques for
filling in the needed field values. The easiest way it so add a subform for
the comments. Then fill in the LinkMasterFields and LinkChildFields
properties for the subform control using this syntax:

LinkMasterField property = [field1];[field2]
LinkChildFields property = [field1];[field2]

where [field1] and [field2] are the two fields you are using as your
composite.

If you do it this way, Access will automatically populate the values for
you. Generally there is only one field to populate, but if you have
multiple fields, that is the format to use. Access will look for [field1]
and [field] two in both the MainForm and SubForm recordsources.

If you are using a Popup form, the method is different and I dont have the
time to explain it now, but if you look at a June 30 post from SJ entitled
"Comments form help" I described how to do this.

God Bless,

Mark A. Sam

 

[satori_1]

Thankyou so much for your help!!!

I have got it and got it all working now
YAY

THANKS AGAIN!!