Looking up a field.

[Guest]

I want to be able to enter in data in a field and have another field
automatically pull in the value based on what is entered in the first field
(i.e. I have a list of Standard Operating Procedures stored in a database. I
have an SOP number and a title. I want to be able to reference other SOPs
that are mentioned in the SOPs. Say SOP1 references SOP2 and 3, I want to be
able to enter into one box the SOP Number - SOP2 and have the title field be
pulled in automatically). Can this be done and if so how is the best way to
tackle it?

 

[Graham Mandeno]

Hi Roger

You first need to add a new table, called a "junction table" to your
database structure. A junction table (JT) is the way to implement a
many-to-many relationship, which is what you have here: One SOP can refer to
many other SOPs, and can also be referenced by many other SOPs.

The JT needs two fields: ReferenceFrom and ReferenceTo. These should both
be the same data type as, and be related to the primary key of your SOP
table. The records describing the references in your example would look
like this:

From To
----- -----
SOP1 SOP2
SOP1 SOP3

To display the references on a form (or report) you use a subform (or
subreport).

Your subform should have the JT as its recordsource, and should be linked to
the main form by SOPNumber (LinkMasterFields) and ReferenceFrom
(LinkChildFields). On the subform, in a row, place a combo box, a textbox,
and a command button. Set the DefaultView to "Continuous forms".

Set the following properties for the combo box:
Name: cboReferenceTo
ControlSource: ReferenceTo
RowSource: Select SOPNumber, SOPName from SOPTable;
Columns: 2
BoundColumn: 1
ColumnWidths: same as width of the combo box
ListWidth: same as the width of the combo AND the textbox combined

Set the following properties for the textbox:
Name: txtSOPName
ControlSource: =cboReferenceTo.Column(1)
Enabled: No
Locked: Yes

Set the following properties for the command button:
Name: cmdView
Caption: View reference
OnClick: [Event Procedure]

Click on the "..." next to the OnClick cell and add the following code to
your form module:

Private Sub cmdView_Click()
Dim frmP as form
Set frmP = Me.parent
With frmP.RecordsetClone
.FindFirst "SOPNumber='" & cboReferenceTo & "'"
' if SopNumber is a numeric field, use this instead:
' .FindFirst "SOPNumber=" & cboReferenceTo
if not .NoMatch then frmP.Bookmark = .Bookmark
End With
End Sub

[You might need to change some of this to reflect your actual fieldnames
etc]

Now, you should see in the subform a list of the refences. To add a new
one, select it from the combo box in the blank record at the end of the
list. To delete a reference, delete the record from the subform. To switch
the main form to view a refence, click on the command button.

 

[Guest]

Thanks for the help. It works great.

I have one little problem. The 'SOP name' field is a hyperlink field linked
to the actual PDF copy of the SOP.

'Set the following properties for the textbox:
'> Name: txtSOPName
'> ControlSource: =cboReferenceTo.Column(1)
'> Enabled: No
'> Locked: Yes

This text box is refering to the 'SOP name' column of the combo box. I dont
necessarily need it to be hyperlinked in the subform but when it pulls the
name it displays it as 'TITLE OF THE SOP#location of the hyperlink#'.

My question is, can I make it a hyperlink or just get the hyperlink
reference dropped?

Make sense???

Hi Roger

You first need to add a new table, called a "junction table" to your
database structure. A junction table (JT) is the way to implement a
many-to-many relationship, which is what you have here: One SOP can refer to
many other SOPs, and can also be referenced by many other SOPs.

The JT needs two fields: ReferenceFrom and ReferenceTo. These should both
be the same data type as, and be related to the primary key of your SOP
table. The records describing the references in your example would look
like this:

From To
----- -----
SOP1 SOP2
SOP1 SOP3

To display the references on a form (or report) you use a subform (or
subreport).

Your subform should have the JT as its recordsource, and should be linked to
the main form by SOPNumber (LinkMasterFields) and ReferenceFrom
(LinkChildFields). On the subform, in a row, place a combo box, a textbox,
and a command button. Set the DefaultView to "Continuous forms".

Set the following properties for the combo box:
Name: cboReferenceTo
ControlSource: ReferenceTo
RowSource: Select SOPNumber, SOPName from SOPTable;
Columns: 2
BoundColumn: 1
ColumnWidths: same as width of the combo box
ListWidth: same as the width of the combo AND the textbox combined

Set the following properties for the textbox:
Name: txtSOPName
ControlSource: =cboReferenceTo.Column(1)
Enabled: No
Locked: Yes

Set the following properties for the command button:
Name: cmdView
Caption: View reference
OnClick: [Event Procedure]

Click on the "..." next to the OnClick cell and add the following code to
your form module:

Private Sub cmdView_Click()
Dim frmP as form
Set frmP = Me.parent
With frmP.RecordsetClone
.FindFirst "SOPNumber='" & cboReferenceTo & "'"
' if SopNumber is a numeric field, use this instead:
' .FindFirst "SOPNumber=" & cboReferenceTo
if not .NoMatch then frmP.Bookmark = .Bookmark
End With
End Sub

[You might need to change some of this to reflect your actual fieldnames
etc]

Now, you should see in the subform a list of the refences. To add a new
one, select it from the combo box in the blank record at the end of the
list. To delete a reference, delete the record from the subform. To switch
the main form to view a refence, click on the command button.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

I want to be able to enter in data in a field and have another field
automatically pull in the value based on what is entered in the first
field
(i.e. I have a list of Standard Operating Procedures stored in a database.
I
have an SOP number and a title. I want to be able to reference other SOPs
that are mentioned in the SOPs. Say SOP1 references SOP2 and 3, I want to
be
able to enter into one box the SOP Number - SOP2 and have the title field
be
pulled in automatically). Can this be done and if so how is the best way
to
tackle it?

 

[Graham Mandeno]

Hi Roger

Try setting the controlsource of the textbox to:
=HyperlinkPart(cboReferenceTo.Column(1), 0)
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Thanks for the help. It works great.

I have one little problem. The 'SOP name' field is a hyperlink field
linked
to the actual PDF copy of the SOP.

'Set the following properties for the textbox:
'> Name: txtSOPName
'> ControlSource: =cboReferenceTo.Column(1)
'> Enabled: No
'> Locked: Yes

This text box is refering to the 'SOP name' column of the combo box. I
dont
necessarily need it to be hyperlinked in the subform but when it pulls the
name it displays it as 'TITLE OF THE SOP#location of the hyperlink#'.

My question is, can I make it a hyperlink or just get the hyperlink
reference dropped?

Make sense???

Hi Roger

You first need to add a new table, called a "junction table" to your
database structure. A junction table (JT) is the way to implement a
many-to-many relationship, which is what you have here: One SOP can refer
to
many other SOPs, and can also be referenced by many other SOPs.

The JT needs two fields: ReferenceFrom and ReferenceTo. These should
both
be the same data type as, and be related to the primary key of your SOP
table. The records describing the references in your example would look
like this:

From To
----- -----
SOP1 SOP2
SOP1 SOP3

To display the references on a form (or report) you use a subform (or
subreport).

Your subform should have the JT as its recordsource, and should be linked
to
the main form by SOPNumber (LinkMasterFields) and ReferenceFrom
(LinkChildFields). On the subform, in a row, place a combo box, a
textbox,
and a command button. Set the DefaultView to "Continuous forms".

Set the following properties for the combo box:
Name: cboReferenceTo
ControlSource: ReferenceTo
RowSource: Select SOPNumber, SOPName from SOPTable;
Columns: 2
BoundColumn: 1
ColumnWidths: same as width of the combo box
ListWidth: same as the width of the combo AND the textbox combined

Set the following properties for the textbox:
Name: txtSOPName
ControlSource: =cboReferenceTo.Column(1)
Enabled: No
Locked: Yes

Set the following properties for the command button:
Name: cmdView
Caption: View reference
OnClick: [Event Procedure]

Click on the "..." next to the OnClick cell and add the following code to
your form module:

Private Sub cmdView_Click()
Dim frmP as form
Set frmP = Me.parent
With frmP.RecordsetClone
.FindFirst "SOPNumber='" & cboReferenceTo & "'"
' if SopNumber is a numeric field, use this instead:
' .FindFirst "SOPNumber=" & cboReferenceTo
if not .NoMatch then frmP.Bookmark = .Bookmark
End With
End Sub

[You might need to change some of this to reflect your actual fieldnames
etc]

Now, you should see in the subform a list of the refences. To add a new
one, select it from the combo box in the blank record at the end of the
list. To delete a reference, delete the record from the subform. To
switch
the main form to view a refence, click on the command button.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

I want to be able to enter in data in a field and have another field
automatically pull in the value based on what is entered in the first
field
(i.e. I have a list of Standard Operating Procedures stored in a
database.
I
have an SOP number and a title. I want to be able to reference other
SOPs
that are mentioned in the SOPs. Say SOP1 references SOP2 and 3, I want
to
be
able to enter into one box the SOP Number - SOP2 and have the title
field
be
pulled in automatically). Can this be done and if so how is the best
way
to
tackle it?