In case you would be interested in my 'solution' i have tried to explane
below how it works for me:-
I can select a person, have a list of all available certificates shown
below, and create the link record when required.
I first have a query selecting the certificates selected (or previous
selected, therefore a record in the tblPersonCerts excists) for the fPers
selected on the form.
SELECT tblPersonCerts.fLinkPersID, tblPersonCerts.fLinkCertID,
tblPersonCerts.fLinkYN
FROM tblPersonCerts
WHERE (((tblPersonCerts.fLinkPersID)=[Forms]![frmCertReq]![cboPerson]))
WITH OWNERACCESS OPTION;
Then the form is based on the following query, where I link the tblCert with
the above query based on the selection of the person in cboPerson or a not
jet excisting entry in the tblPersonCerts (but excisting certificate which
could be required for the person):
SELECT tblCert.fCertName, qryFirstLink.fLinkYN, qryFirstLink.fLinkPersID,
tblCert.fCertGroup
FROM qryFirstLink RIGHT JOIN tblCert ON qryFirstLink.fLinkCertID =
tblCert.fCertID
WHERE (((qryFirstLink.fLinkPersID)=[Forms]![frmCertReq]![cboPerson])) OR
(((qryFirstLink.fLinkYN) Is Null))
ORDER BY tblCert.fCertGroup
WITH OWNERACCESS OPTION;
On the form I put the option of 'Allow additions' to NO the avoid new
records for non excisting certificates. Last I put the following code in the
form event 'Form_BeforeUpdate' in order to ad the person ID to the record
befor if saved.
If Me.cboPerson <> "" Then
Me.fLinkPersID = Me.cboPerson
Else
MsgBox "No person selected, thus no information saved.", vbCritical,
"Vsn Operational DataBase"
DoCmd.CancelEvent
End If
Hope this makes sense to you. If you want to see it working in the DB, let
me know where i can send it.
I am quit happy I finaly managed to create this, which should give
unrestricted possibilities for any future, jet unknown certificate adds.
Thx for your much appriciated help with it, sorry if my english has been a
bit 'mickey mouse' with my question or explanation.
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.
"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.
"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
