Recordset/'separate' source for textboxes

  • Thread starter Thread starter TM
  • Start date Start date
T

TM

Okay, I have a report which is being used as a
newsletter. For the most part all the information is the
same. However, occassionally the user will need to write
notes specific to the client(s). So, I have that data
stored in a separate table. I am now having problems
trying to access that information from the report. There
may be several records for each customer, and there may
be several for each newsletter. So, there is
a "mindsetid" which identifies the newsletter (one/month)
and a "customerid" which identifies who has special
notes - but only one customerid per mindsetid. So, I am
getting an error when I try to include the memo table
with my main recordsource. I believe I am going to have
to create a new recordsource in order to access this
additional information... but how?? I'm confused! I was
going to try to use "dlookup" but I don't believe I can
base that on the two conditions (customerid/mindsetid).
Hope this makes sense!! THanks for the help!
 
DLookup will accept 2 conditions.

Another option may be to include this field in your report's record source
and just hide it for those records that don't use it.
 
I just noticed a post this morning on a different group
where the conditions were concantenated (sp?)... would
that be how you can use 2 conditions?? What I have so
far is really not working...

strMindsetCrit = ("mindsetid= " & Forms![frmMarketing
Mindset Individual]![mindsetid])

strCustCrit = ("companyid= " & Forms![frmMarketing
Mindset Individual]![CustomerID])

strCriteria = strMindsetCrit & strCustCrit

strBin = (DLookup("In Bin", "tblMindset Memo",
strCriteria))

Or, what would be better (?)... the report is already set
to a specific "mindsetid" - so would it be better to
create a recordset clone and then filter those by
customer which will change/page? I cannot add the table
to the source, I get an error message about an ambiguous
joins.

THANKS for all the help & suggestions!

I am trying to get this done before the end of the day,
so any responses/suggestions are appreciated!
 
You're close on the concatenation. Both fields need to be in the table that
the DLookup is running on. Have you done a Debug.Print on strCriteria after
you set it, if you do, I think you'll see the problem.

Try
strCriteria = strMindsetCrit & " And " & strCustCrit

Note the spaces around the word And. Also, are MindSetID and CutomerID
defined as Number fields or Text fields in the table? The way you currently
have the criteria written, they should be number fields.

--
Wayne Morgan
MS Access MVP


TM said:
I just noticed a post this morning on a different group
where the conditions were concantenated (sp?)... would
that be how you can use 2 conditions?? What I have so
far is really not working...

strMindsetCrit = ("mindsetid= " & Forms![frmMarketing
Mindset Individual]![mindsetid])

strCustCrit = ("companyid= " & Forms![frmMarketing
Mindset Individual]![CustomerID])

strCriteria = strMindsetCrit & strCustCrit

strBin = (DLookup("In Bin", "tblMindset Memo",
strCriteria))

Or, what would be better (?)... the report is already set
to a specific "mindsetid" - so would it be better to
create a recordset clone and then filter those by
customer which will change/page? I cannot add the table
to the source, I get an error message about an ambiguous
joins.

THANKS for all the help & suggestions!

I am trying to get this done before the end of the day,
so any responses/suggestions are appreciated!
-----Original Message-----
DLookup will accept 2 conditions.

Another option may be to include this field in your report's record source
and just hide it for those records that don't use it.

--
Wayne Morgan
Microsoft Access MVP





.
Click to expand...
Click to expand...
 
Back
Top