Contact Management Template: Filtering subform based on expression

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am endeavouring to teach myself about access by using the contact
management template provided in access and on the Microsoft Office site as
well as these forums. I am using Access 2003 and trying to copy some of the
same functionality from this template across to an access project but am
having great difficulty in filtering the embedded call notes subform (called
[Call Details Subform] in the template.

I have 2 subforms as per the template. The first [subTransactionDetails]
form contains general information such as [callID],[callDate], [user],
[transactionType], [subject] in a datasheet view and works correctly (well
seems to). The subform [subTransactionNotes] contains 1 field only called
[notes].

I want to be able to display only the notes in the [notes] field which
correspond to the which ever general information row I have selected in the
[subTransactionDetails] form.

I have followed the contacts management template and set up an expression in
a text box [txtLink] as follows;

=[subTransactionDetails].[Form]![callID]

Currently this returns the error #name

Can anyone advise where I am going wrong?

Apologies upfront if I havent provided enough information
 
How are these connected to the main form? What common value is on the main
form and your two subs?
 
Paul - thanks for your response. I will try and answer your questions but
will outline what I am trying to achieve first.

I am trying to copy the functionality associated with contact management
template with regards to the way it displays call details and call notes. I
want the call details [subTransactionDetails] subform to display only the
calls associated with the opportunity and I want the call notes
[subTransactionNotes] subform to display the notes for the particular call
selected.

My main form contains information relating to opportunities. The first
subform [subTransactionDetails] contains the call details for each
opportunity. This subform is linked to the displayed opportunity by
referencing the JobID field which is in the mainform query as well as the
query the subform uses [qryTransaction]. This seems to work correctly.

The second subform [subTransactionNotes] uses the same [qryTransaction] but
only contains the Notes field.

Trying to follow the Contact Management example I set up a txt box [callID]
on the main form and used the formula provided in the example but edited. My
formula is as follows;

=[subTransactionDetails].[Form]![callID]

This returns the #name error. I have no idea what I have done wrong here

I have set the Master/Child links for [subTransactionNotes] to [callID] and
assume that the subform is supposed to reference this to know which call
notes to display.
Currently for each opportunity record I do get the correct notes for the
first call listed in the [subTransactionDetails] datasheet but these do not
change when I select other calls in the [subTransactionNotes] datasheet.

I am working on an ADP connected to MSSQL server which means that the
environment I am working in is slightly different from a typical mdb. in that
when trying to build the formula or setting the Master/Child links I don’t
have access to the 3 dots (…) which open the expression builder and the
subform field linker.

I have also posted my [qryTransaction] in case this helps.
SELECT TOP 100 PERCENT dbo.[Transaction].timestamp, dbo.Employee.username,
dbo.TransactionType.type, dbo.[Transaction].subject, dbo.[Transaction].inout,
dbo.[Transaction].jobID, dbo.[Transaction].notes, dbo.[Transaction].callID
FROM dbo.Job INNER JOIN
dbo.[Transaction] ON dbo.Job.id = dbo.[Transaction].jobID INNER JOIN
dbo.Employee ON dbo.[Transaction].employeeID = dbo.Employee.id INNER JOIN
dbo.TransactionType ON dbo.[Transaction].typeID = dbo.TransactionType.id
ORDER BY dbo.[Transaction].timestamp DESC
 
Back
Top