Re: Link table?

Tech-Archive recommends: Speed Up your PC by fixing your registry

I'm afraid that I just don't understand what you want to do
with the form. I thought you wanted to see all the certs
for a person. Now it seems like you want to see all certs,
but I don't see how that can be useful so I can't suggest
the "right" way to it.

If you want to be able to add to the list of certs for a
person (checked or not) then that's different from what I
thought you wanted. In this case, just set the form's
record source to the simple query:

SELECT fLinkID, fLinkPersID, fLinkCertID, fLinkYN
FROM tblPersonCerts
WHERE tblPersonCerts.fLinkPersID =
Forms!yourform.cboPerson

Bind the fLinkCertID field to a combo box and set the combo
box's RowSource query to:

SELECT fCertID, fCertName
FROM tblCert
ORDER BY fCertName

This way, the combo box has the list of available certs and
you can assign on to a person by selecting it from the combo
box. If you display the form in continuous view, you can
see the list of certs assigned to the person and check or
uncheck the ones you want the person to get.

You can use the form's BeforeInsert procedure to set the
fLinkPersID field as you had before.

If this is not what you are trying to do, I will need a more
detailed description of **what** you are trying to
accomplish.
--
Marsh
MVP [MS Access]


"Vsn" <vsn at hotmail> wrote:
Thx for your effort, however your suggestion does not give the result I do
look for.

On the form, the certificates do not appear if not asigned or has been
assigned once to person (no record exist jet), so if a new certificate will
be added to the tblCert if will not appear of the form as an option.

I have slightly changed the form query to:

SELECT tblCert.fCertName, tblPersonCerts.fLinkYN, tblPersonCerts.fLinkPersID
FROM tblCert LEFT JOIN tblPersonCerts ON tblCert.fCertID =
tblPersonCerts.fLinkCertID
WHERE (((tblPersonCerts.fLinkPersID)=[Forms]![frmCertReq]![cboPerson])) OR
(((tblPersonCerts.fLinkPersID) Is Null));

and added

Private Sub Form_BeforeUpdate(Cancel As Integer)
Me!fLinkPersID = Me.cboPerson
End Sub

But this will now show all the available certificates but as well the
assigned certificates to other persons.

I have been strugling with this and can get around.


"Marshall Barton" wrote
"Vsn" <vsn at hotmail> wrote:
I have a problem I can't get my fingers behind hope you can help and I am
clear enough with the description below.

A table with personnel (tblPers)
fPersID Auto number
fPersName Text(20)

Further a table with certificates (tblCert), with plenty of certificates,
which are not necessary applicable to every one in the table personnel.
fCertID Auto number
fCertName Text(2)

Now I would like to link them with a linking table (tussen tabel in Dutch)
fLinkID Auto number
fLinkPersID Integer
fLinkCertID Integer
fLinkYN Boolean

Now I would like to create a query where I can select one person from
tblPers and have al available certificates there to determine via the Yes
/
No field if the person needs to have a typical certificate. This query I
will use than in a form and select one person using the filter, launching
from another form.


Add a combo box to the form's header section. set its
properties:
Name cboPerson
RowSource SELECT fPersID, fPersName
FROM tblPers
ORDER BY fPersName
ColumnCount 2
BoundColumn 1
ColumnWidths 0;

Add this line to the combo box's AfterUpdate event:
Me.Requery

Then set the form's RecordSource to:
SELECT fCertName, fLinkYN
FROM tblCert INNER JOIN tblPersonCerts
ON tblCert.fCertID = tblPersonCerts.fLinkCertID
WHERE tblPersonCerts.fLinkPersID =
Forms!yourform.cboPerson
.

Relevant Pages

  • Re: Link table?
    ... have a list of all available certificates shown ... I first have a query selecting the certificates selected (or previous ... therefore a record in the tblPersonCerts excists) for the fPers ... FROM qryFirstLink RIGHT JOIN tblCert ON qryFirstLink.fLinkCertID = ...
    (microsoft.public.access.queries)
  • Re: Link table?
    ... have a list of all available certificates shown ... I first have a query selecting the certificates selected (or previous ... therefore a record in the tblPersonCerts excists) for the fPers ... FROM qryFirstLink RIGHT JOIN tblCert ON qryFirstLink.fLinkCertID = ...
    (microsoft.public.access.queries)
  • Re: Link table?
    ... have a list of all available certificates shown ... therefore a record in the tblPersonCerts excists) for the fPers ... Then the form is based on the following query, where I link the tblCert ...
    (microsoft.public.access.queries)
  • Re: Link table?
    ... record source to the simple query: ... the certificates do not appear if not asigned or has been ... be added to the tblCert if will not appear of the form as an option. ... FROM tblCert LEFT JOIN tblPersonCerts ON tblCert.fCertID = ...
    (microsoft.public.access.queries)
  • Re: How to fix broken security in Windows 2000?
    ... mvp) post all this stuff? ... >> involved in importing security certificates. ... > and Microsoft code signing are not proof that Microsoft is writing ... > past two days you have said that certs are missing, ...
    (microsoft.public.win2000.windows_update)