iif statement

[R. Daniel Carlson]

I have 2 tables.

One table (table A) stores data about donors who have given over 100
dollars. This table is purged and new data is imported into it at least
twice a month. There are several fields in the form (FORM A) I use, most of
them are not editible, but there are a few that are - so donor callers can
leave simple notes about their phone converstaions, etc. One of the fields
is called REC_DONOR_ID.

The other table (table B) has only a few fields. One field stores comments
that the donor callers can leave about a donor, the other field is the DONOR
ID field. This table NEVER gets deleted.

Whenever the REC_DONOR_ID field in table A above has a corresponding record
in table B (based on DONOR ID), the button control I added to the form will
filter, a new form will pop up, and only those records from table B will show
(the comments about that particular donor). This works great. If the donor
caller would like to leave a new comment, they can simply type in the DONOR
ID and the comment.

I currently have no relationships between these two tables setup.

I would like to add one more feature to the form A. What it would do is
this: When there is a comment (or comments) about the donor in table B, the
words "Comments Available" would appear in an unbound text box.

I've tried different approaches...one worked well, but when using it, I
couldn't type in any of the editable fields in form A. I created a
one-to-many relationship between the two tables, and added table B to the
query I use. Again, the text would appear on every record that had
comments...but I couldn't edit, change or save anything in the form.

I realized that, because there could be more than one record in table B that
pertains to more than one record in table A, that a one to many wouldn't
work. So I read up on many-to-many relationships. I couldn't find a good
explaination on how this relationship works, so I gave up.

Does anyone have good way for me to make this work? The main reason I have
2 tables is because I want the donor comments to be around forever, and I
expect that there will be more than one entry for each donor....not to
mention that donors will appear more than once in the donor table. Is a
many-to many relationship the way to go? Is there some VB code that could do
the trick just as well??

 

[Steve Sanford]

Have you tried using a subform?

Form A would be the master form and Form B would be the child (sub form),
with the linking field "REC_DONOR_ID".


HTH

 

[R. Daniel Carlson]

I considered that early on, but the problem is, the form I'm using has at
least 20 different fields. I'm trying to keep it as un-confusing as
possible. I had thought that prompting the callers' attention to the fact
that there is comments would keep it simple...less information on one page.

If there isn't a good way to do what I've described above, I may find some
clever way to encorporate a subform, but only if there is no other way.

 

[Steve Sanford]

OK, here is one way.....


In a standard module, paste this code:

'------------code begin---------------------
Public Function udfHasComments(pDonor_ID As Long) As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String

udfHasComments = ""

Set db = CurrentDb

' Change "TableB" to the name of your 'Never Deleted table'
strSQL = "Select REC_DONOR_ID From TableB"
strSQL = strSQL & " Where [REC_DONOR_ID] = " & pDonor_ID

Set rs = db.OpenRecordset(strSQL)
If Not rs.BOF And Not rs.EOF Then

udfHasComments = "Comments Available"

End If

rs.Close
Set rs = Nothing
Set db = Nothing

End Function
'------------code end------------------------


Don't forget to change "TableB" (in the code) the the real name of your
table!!!

Now we need to modify "FormA". The recordsource for "FormA" should be a
query. Open the query and add this to an empty column:


HasComments: udfHasComments([REC_DONOR_ID])


Save the query. Run the query. You should now see a column with "Comments
Available" in some of the records.
Close the query.

Open "FormA" in design view. Add a text box. Selet the new text box. Open
the properties dialog box.

On the DATA tab, set:

Control Source - HasComments
Enabled - No
Locked - Yes


On the FORMAT tab, set these properties:

Back Style - Transparent
Border Style - Transparent


If you want the comments to stand out, also set:

Font Weight - Bold
Font Color - 255 (red)


Save "FormA", then open it.



HTH

 

[R. Daniel Carlson]

Sounds like that's what I'm looking for. Thanks a lot.
--
R. Daniel Carlson
Concordia University
Office of Development

OK, here is one way.....


In a standard module, paste this code:

'------------code begin---------------------
Public Function udfHasComments(pDonor_ID As Long) As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String

udfHasComments = ""

Set db = CurrentDb

' Change "TableB" to the name of your 'Never Deleted table'
strSQL = "Select REC_DONOR_ID From TableB"
strSQL = strSQL & " Where [REC_DONOR_ID] = " & pDonor_ID

Set rs = db.OpenRecordset(strSQL)
If Not rs.BOF And Not rs.EOF Then

udfHasComments = "Comments Available"

End If

rs.Close
Set rs = Nothing
Set db = Nothing

End Function
'------------code end------------------------


Don't forget to change "TableB" (in the code) the the real name of your
table!!!

Now we need to modify "FormA". The recordsource for "FormA" should be a
query. Open the query and add this to an empty column:


HasComments: udfHasComments([REC_DONOR_ID])


Save the query. Run the query. You should now see a column with "Comments
Available" in some of the records.
Close the query.

Open "FormA" in design view. Add a text box. Selet the new text box. Open
the properties dialog box.

On the DATA tab, set:

Control Source - HasComments
Enabled - No
Locked - Yes


On the FORMAT tab, set these properties:

Back Style - Transparent
Border Style - Transparent


If you want the comments to stand out, also set:

Font Weight - Bold
Font Color - 255 (red)


Save "FormA", then open it.



HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

I considered that early on, but the problem is, the form I'm using has at
least 20 different fields. I'm trying to keep it as un-confusing as
possible. I had thought that prompting the callers' attention to the fact
that there is comments would keep it simple...less information on one page.

If there isn't a good way to do what I've described above, I may find some
clever way to encorporate a subform, but only if there is no other way.

 

[R. Daniel Carlson]

Didn't work. Comes up with #error!.

Here is the code I used with explination:

'The Option Compare Database' put itself in the module when i created it'
Option Compare Database

' Changed pDonorID to pRec_Donor_ID, didn't work the other way either '
Public Function udfHasComments(pRec_Donor_ID As Long) As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String

udfHasComments = ""

Set db = CurrentDb
' REC_DONOR_ID is the name of the field in both tables, tbl_cont_notes is
the '
' name of the "tableB" table. '
strSQL = "Select REC_DONOR_ID From tbl_cont_notes"
strSQL = strSQL & " Where ([REC_DONOR_ID]) = " & pRec_Donor_ID

Set rs = db.OpenRecordset(strSQL)
If Not rs.BOF And Not rs.EOF Then

udfHasComments = "Donor Comments Available"

End If

rs.Close
Set rs = Nothing
Set db = Nothing

End Function

I added the HasComments: udfHasComments([REC_DONOR_ID]) line to a blank
column in the query I use, saved and ran the query. This is when the #error!
appears in every entry of that field. I also added the unbound text box to
the form, as, as expected, the #error! shows up there too.

Any suggestions?
--
R. Daniel Carlson
Concordia University
Office of Development

OK, here is one way.....


In a standard module, paste this code:

'------------code begin---------------------
Public Function udfHasComments(pDonor_ID As Long) As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String

udfHasComments = ""

Set db = CurrentDb

' Change "TableB" to the name of your 'Never Deleted table'
strSQL = "Select REC_DONOR_ID From TableB"
strSQL = strSQL & " Where [REC_DONOR_ID] = " & pDonor_ID

Set rs = db.OpenRecordset(strSQL)
If Not rs.BOF And Not rs.EOF Then

udfHasComments = "Comments Available"

End If

rs.Close
Set rs = Nothing
Set db = Nothing

End Function
'------------code end------------------------


Don't forget to change "TableB" (in the code) the the real name of your
table!!!

Now we need to modify "FormA". The recordsource for "FormA" should be a
query. Open the query and add this to an empty column:


HasComments: udfHasComments([REC_DONOR_ID])


Save the query. Run the query. You should now see a column with "Comments
Available" in some of the records.
Close the query.

Open "FormA" in design view. Add a text box. Selet the new text box. Open
the properties dialog box.

On the DATA tab, set:

Control Source - HasComments
Enabled - No
Locked - Yes


On the FORMAT tab, set these properties:

Back Style - Transparent
Border Style - Transparent


If you want the comments to stand out, also set:

Font Weight - Bold
Font Color - 255 (red)


Save "FormA", then open it.



HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

I considered that early on, but the problem is, the form I'm using has at
least 20 different fields. I'm trying to keep it as un-confusing as
possible. I had thought that prompting the callers' attention to the fact
that there is comments would keep it simple...less information on one page.

If there isn't a good way to do what I've described above, I may find some
clever way to encorporate a subform, but only if there is no other way.

 

[Douglas J. Steele]

By any chance did you name the module in which the function's stored
udfHasComments? If so, rename it: modules cannot have the same name as
functions or subs.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Didn't work. Comes up with #error!.

Here is the code I used with explination:

'The Option Compare Database' put itself in the module when i created it'
Option Compare Database

' Changed pDonorID to pRec_Donor_ID, didn't work the other way either '
Public Function udfHasComments(pRec_Donor_ID As Long) As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String

udfHasComments = ""

Set db = CurrentDb
' REC_DONOR_ID is the name of the field in both tables, tbl_cont_notes is
the '
' name of the "tableB" table. '
strSQL = "Select REC_DONOR_ID From tbl_cont_notes"
strSQL = strSQL & " Where ([REC_DONOR_ID]) = " & pRec_Donor_ID

Set rs = db.OpenRecordset(strSQL)
If Not rs.BOF And Not rs.EOF Then

udfHasComments = "Donor Comments Available"

End If

rs.Close
Set rs = Nothing
Set db = Nothing

End Function

I added the HasComments: udfHasComments([REC_DONOR_ID]) line to a blank
column in the query I use, saved and ran the query. This is when the
#error!
appears in every entry of that field. I also added the unbound text box
to
the form, as, as expected, the #error! shows up there too.

Any suggestions?
--
R. Daniel Carlson
Concordia University
Office of Development

OK, here is one way.....


In a standard module, paste this code:

'------------code begin---------------------
Public Function udfHasComments(pDonor_ID As Long) As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String

udfHasComments = ""

Set db = CurrentDb

' Change "TableB" to the name of your 'Never Deleted table'
strSQL = "Select REC_DONOR_ID From TableB"
strSQL = strSQL & " Where [REC_DONOR_ID] = " & pDonor_ID

Set rs = db.OpenRecordset(strSQL)
If Not rs.BOF And Not rs.EOF Then

udfHasComments = "Comments Available"

End If

rs.Close
Set rs = Nothing
Set db = Nothing

End Function
'------------code end------------------------


Don't forget to change "TableB" (in the code) the the real name of your
table!!!

Now we need to modify "FormA". The recordsource for "FormA" should be a
query. Open the query and add this to an empty column:


HasComments: udfHasComments([REC_DONOR_ID])


Save the query. Run the query. You should now see a column with
"Comments
Available" in some of the records.
Close the query.

Open "FormA" in design view. Add a text box. Selet the new text box. Open
the properties dialog box.

On the DATA tab, set:

Control Source - HasComments
Enabled - No
Locked - Yes


On the FORMAT tab, set these properties:

Back Style - Transparent
Border Style - Transparent


If you want the comments to stand out, also set:

Font Weight - Bold
Font Color - 255 (red)


Save "FormA", then open it.



HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

I considered that early on, but the problem is, the form I'm using has
at
least 20 different fields. I'm trying to keep it as un-confusing as
possible. I had thought that prompting the callers' attention to the
fact
that there is comments would keep it simple...less information on one
page.

If there isn't a good way to do what I've described above, I may find
some
clever way to encorporate a subform, but only if there is no other way.
--
R. Daniel Carlson
Concordia University
Office of Development


:

Have you tried using a subform?

Form A would be the master form and Form B would be the child (sub
form),
with the linking field "REC_DONOR_ID".


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


:

I have 2 tables.

One table (table A) stores data about donors who have given over
100
dollars. This table is purged and new data is imported into it at
least
twice a month. There are several fields in the form (FORM A) I
use, most of
them are not editible, but there are a few that are - so donor
callers can
leave simple notes about their phone converstaions, etc. One of
the fields
is called REC_DONOR_ID.

The other table (table B) has only a few fields. One field stores
comments
that the donor callers can leave about a donor, the other field is
the DONOR
ID field. This table NEVER gets deleted.

Whenever the REC_DONOR_ID field in table A above has a
corresponding record
in table B (based on DONOR ID), the button control I added to the
form will
filter, a new form will pop up, and only those records from table B
will show
(the comments about that particular donor). This works great. If
the donor
caller would like to leave a new comment, they can simply type in
the DONOR
ID and the comment.

I currently have no relationships between these two tables setup.

I would like to add one more feature to the form A. What it would
do is
this: When there is a comment (or comments) about the donor in
table B, the
words "Comments Available" would appear in an unbound text box.

I've tried different approaches...one worked well, but when using
it, I
couldn't type in any of the editable fields in form A. I created a
one-to-many relationship between the two tables, and added table B
to the
query I use. Again, the text would appear on every record that had
comments...but I couldn't edit, change or save anything in the
form.

I realized that, because there could be more than one record in
table B that
pertains to more than one record in table A, that a one to many
wouldn't
work. So I read up on many-to-many relationships. I couldn't find
a good
explaination on how this relationship works, so I gave up.

Does anyone have good way for me to make this work? The main
reason I have
2 tables is because I want the donor comments to be around forever,
and I
expect that there will be more than one entry for each donor....not
to
mention that donors will appear more than once in the donor table.
Is a
many-to many relationship the way to go? Is there some VB code
that could do
the trick just as well??

 

[R. Daniel Carlson]

No I didn't. It's just called mod_cont_notes.
--
R. Daniel Carlson
Concordia University
Office of Development

By any chance did you name the module in which the function's stored
udfHasComments? If so, rename it: modules cannot have the same name as
functions or subs.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Didn't work. Comes up with #error!.

Here is the code I used with explination:

'The Option Compare Database' put itself in the module when i created it'
Option Compare Database

' Changed pDonorID to pRec_Donor_ID, didn't work the other way either '
Public Function udfHasComments(pRec_Donor_ID As Long) As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String

udfHasComments = ""

Set db = CurrentDb
' REC_DONOR_ID is the name of the field in both tables, tbl_cont_notes is
the '
' name of the "tableB" table. '
strSQL = "Select REC_DONOR_ID From tbl_cont_notes"
strSQL = strSQL & " Where ([REC_DONOR_ID]) = " & pRec_Donor_ID

Set rs = db.OpenRecordset(strSQL)
If Not rs.BOF And Not rs.EOF Then

udfHasComments = "Donor Comments Available"

End If

rs.Close
Set rs = Nothing
Set db = Nothing

End Function

I added the HasComments: udfHasComments([REC_DONOR_ID]) line to a blank
column in the query I use, saved and ran the query. This is when the
#error!
appears in every entry of that field. I also added the unbound text box
to
the form, as, as expected, the #error! shows up there too.

Any suggestions?
--
R. Daniel Carlson
Concordia University
Office of Development

OK, here is one way.....


In a standard module, paste this code:

'------------code begin---------------------
Public Function udfHasComments(pDonor_ID As Long) As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String

udfHasComments = ""

Set db = CurrentDb

' Change "TableB" to the name of your 'Never Deleted table'
strSQL = "Select REC_DONOR_ID From TableB"
strSQL = strSQL & " Where [REC_DONOR_ID] = " & pDonor_ID

Set rs = db.OpenRecordset(strSQL)
If Not rs.BOF And Not rs.EOF Then

udfHasComments = "Comments Available"

End If

rs.Close
Set rs = Nothing
Set db = Nothing

End Function
'------------code end------------------------


Don't forget to change "TableB" (in the code) the the real name of your
table!!!

Now we need to modify "FormA". The recordsource for "FormA" should be a
query. Open the query and add this to an empty column:


HasComments: udfHasComments([REC_DONOR_ID])


Save the query. Run the query. You should now see a column with
"Comments
Available" in some of the records.
Close the query.

Open "FormA" in design view. Add a text box. Selet the new text box. Open
the properties dialog box.

On the DATA tab, set:

Control Source - HasComments
Enabled - No
Locked - Yes


On the FORMAT tab, set these properties:

Back Style - Transparent
Border Style - Transparent


If you want the comments to stand out, also set:

Font Weight - Bold
Font Color - 255 (red)


Save "FormA", then open it.



HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


:

I considered that early on, but the problem is, the form I'm using has
at
least 20 different fields. I'm trying to keep it as un-confusing as
possible. I had thought that prompting the callers' attention to the
fact
that there is comments would keep it simple...less information on one
page.

If there isn't a good way to do what I've described above, I may find
some
clever way to encorporate a subform, but only if there is no other way.
--
R. Daniel Carlson
Concordia University
Office of Development


:

Have you tried using a subform?

Form A would be the master form and Form B would be the child (sub
form),
with the linking field "REC_DONOR_ID".


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


:

I have 2 tables.

One table (table A) stores data about donors who have given over
100
dollars. This table is purged and new data is imported into it at
least
twice a month. There are several fields in the form (FORM A) I
use, most of
them are not editible, but there are a few that are - so donor
callers can
leave simple notes about their phone converstaions, etc. One of
the fields
is called REC_DONOR_ID.

The other table (table B) has only a few fields. One field stores
comments
that the donor callers can leave about a donor, the other field is
the DONOR
ID field. This table NEVER gets deleted.

Whenever the REC_DONOR_ID field in table A above has a
corresponding record
in table B (based on DONOR ID), the button control I added to the
form will
filter, a new form will pop up, and only those records from table B
will show
(the comments about that particular donor). This works great. If
the donor
caller would like to leave a new comment, they can simply type in
the DONOR
ID and the comment.

I currently have no relationships between these two tables setup.

I would like to add one more feature to the form A. What it would
do is
this: When there is a comment (or comments) about the donor in
table B, the
words "Comments Available" would appear in an unbound text box.

I've tried different approaches...one worked well, but when using
it, I
couldn't type in any of the editable fields in form A. I created a
one-to-many relationship between the two tables, and added table B
to the
query I use. Again, the text would appear on every record that had
comments...but I couldn't edit, change or save anything in the
form.

I realized that, because there could be more than one record in
table B that
pertains to more than one record in table A, that a one to many
wouldn't
work. So I read up on many-to-many relationships. I couldn't find
a good
explaination on how this relationship works, so I gave up.

Does anyone have good way for me to make this work? The main
reason I have
2 tables is because I want the donor comments to be around forever,
and I
expect that there will be more than one entry for each donor....not
to
mention that donors will appear more than once in the donor table.
Is a
many-to many relationship the way to go? Is there some VB code
that could do
the trick just as well??

 

[R. Daniel Carlson]

No, the module is called mod_cont_notes
--
R. Daniel Carlson
Concordia University
Office of Development

By any chance did you name the module in which the function's stored
udfHasComments? If so, rename it: modules cannot have the same name as
functions or subs.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Didn't work. Comes up with #error!.

Here is the code I used with explination:

'The Option Compare Database' put itself in the module when i created it'
Option Compare Database

' Changed pDonorID to pRec_Donor_ID, didn't work the other way either '
Public Function udfHasComments(pRec_Donor_ID As Long) As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String

udfHasComments = ""

Set db = CurrentDb
' REC_DONOR_ID is the name of the field in both tables, tbl_cont_notes is
the '
' name of the "tableB" table. '
strSQL = "Select REC_DONOR_ID From tbl_cont_notes"
strSQL = strSQL & " Where ([REC_DONOR_ID]) = " & pRec_Donor_ID

Set rs = db.OpenRecordset(strSQL)
If Not rs.BOF And Not rs.EOF Then

udfHasComments = "Donor Comments Available"

End If

rs.Close
Set rs = Nothing
Set db = Nothing

End Function

I added the HasComments: udfHasComments([REC_DONOR_ID]) line to a blank
column in the query I use, saved and ran the query. This is when the
#error!
appears in every entry of that field. I also added the unbound text box
to
the form, as, as expected, the #error! shows up there too.

Any suggestions?
--
R. Daniel Carlson
Concordia University
Office of Development

OK, here is one way.....


In a standard module, paste this code:

'------------code begin---------------------
Public Function udfHasComments(pDonor_ID As Long) As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String

udfHasComments = ""

Set db = CurrentDb

' Change "TableB" to the name of your 'Never Deleted table'
strSQL = "Select REC_DONOR_ID From TableB"
strSQL = strSQL & " Where [REC_DONOR_ID] = " & pDonor_ID

Set rs = db.OpenRecordset(strSQL)
If Not rs.BOF And Not rs.EOF Then

udfHasComments = "Comments Available"

End If

rs.Close
Set rs = Nothing
Set db = Nothing

End Function
'------------code end------------------------


Don't forget to change "TableB" (in the code) the the real name of your
table!!!

Now we need to modify "FormA". The recordsource for "FormA" should be a
query. Open the query and add this to an empty column:


HasComments: udfHasComments([REC_DONOR_ID])


Save the query. Run the query. You should now see a column with
"Comments
Available" in some of the records.
Close the query.

Open "FormA" in design view. Add a text box. Selet the new text box. Open
the properties dialog box.

On the DATA tab, set:

Control Source - HasComments
Enabled - No
Locked - Yes


On the FORMAT tab, set these properties:

Back Style - Transparent
Border Style - Transparent


If you want the comments to stand out, also set:

Font Weight - Bold
Font Color - 255 (red)


Save "FormA", then open it.



HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


:

I considered that early on, but the problem is, the form I'm using has
at
least 20 different fields. I'm trying to keep it as un-confusing as
possible. I had thought that prompting the callers' attention to the
fact
that there is comments would keep it simple...less information on one
page.

If there isn't a good way to do what I've described above, I may find
some
clever way to encorporate a subform, but only if there is no other way.
--
R. Daniel Carlson
Concordia University
Office of Development


:

Have you tried using a subform?

Form A would be the master form and Form B would be the child (sub
form),
with the linking field "REC_DONOR_ID".


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


:

I have 2 tables.

One table (table A) stores data about donors who have given over
100
dollars. This table is purged and new data is imported into it at
least
twice a month. There are several fields in the form (FORM A) I
use, most of
them are not editible, but there are a few that are - so donor
callers can
leave simple notes about their phone converstaions, etc. One of
the fields
is called REC_DONOR_ID.

The other table (table B) has only a few fields. One field stores
comments
that the donor callers can leave about a donor, the other field is
the DONOR
ID field. This table NEVER gets deleted.

Whenever the REC_DONOR_ID field in table A above has a
corresponding record
in table B (based on DONOR ID), the button control I added to the
form will
filter, a new form will pop up, and only those records from table B
will show
(the comments about that particular donor). This works great. If
the donor
caller would like to leave a new comment, they can simply type in
the DONOR
ID and the comment.

I currently have no relationships between these two tables setup.

I would like to add one more feature to the form A. What it would
do is
this: When there is a comment (or comments) about the donor in
table B, the
words "Comments Available" would appear in an unbound text box.

I've tried different approaches...one worked well, but when using
it, I
couldn't type in any of the editable fields in form A. I created a
one-to-many relationship between the two tables, and added table B
to the
query I use. Again, the text would appear on every record that had
comments...but I couldn't edit, change or save anything in the
form.

I realized that, because there could be more than one record in
table B that
pertains to more than one record in table A, that a one to many
wouldn't
work. So I read up on many-to-many relationships. I couldn't find
a good
explaination on how this relationship works, so I gave up.

Does anyone have good way for me to make this work? The main
reason I have
2 tables is because I want the donor comments to be around forever,
and I
expect that there will be more than one entry for each donor....not
to
mention that donors will appear more than once in the donor table.
Is a
many-to many relationship the way to go? Is there some VB code
that could do
the trick just as well??

 

[Steve Sanford]

In your first post, you said the names of the tables were "TableA" and
"TableB". What are the real names of the tables? Which table (the real name)
has the data delete and which is the one that is *never* deleted?

Does the table that is never deleted have a field "REC_DONOR_ID"? If not,
what is the name of the fields in both tables that is the common field?

Do you have a reference set for "Microsoft DAO 3.6 Library"?


--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

No, the module is called mod_cont_notes
--
R. Daniel Carlson
Concordia University
Office of Development

By any chance did you name the module in which the function's stored
udfHasComments? If so, rename it: modules cannot have the same name as
functions or subs.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Didn't work. Comes up with #error!.

Here is the code I used with explination:

'The Option Compare Database' put itself in the module when i created it'
Option Compare Database

' Changed pDonorID to pRec_Donor_ID, didn't work the other way either '
Public Function udfHasComments(pRec_Donor_ID As Long) As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String

udfHasComments = ""

Set db = CurrentDb
' REC_DONOR_ID is the name of the field in both tables, tbl_cont_notes is
the '
' name of the "tableB" table. '
strSQL = "Select REC_DONOR_ID From tbl_cont_notes"
strSQL = strSQL & " Where ([REC_DONOR_ID]) = " & pRec_Donor_ID

Set rs = db.OpenRecordset(strSQL)
If Not rs.BOF And Not rs.EOF Then

udfHasComments = "Donor Comments Available"

End If

rs.Close
Set rs = Nothing
Set db = Nothing

End Function

I added the HasComments: udfHasComments([REC_DONOR_ID]) line to a blank
column in the query I use, saved and ran the query. This is when the
#error!
appears in every entry of that field. I also added the unbound text box
to
the form, as, as expected, the #error! shows up there too.

Any suggestions?
--
R. Daniel Carlson
Concordia University
Office of Development


:

OK, here is one way.....


In a standard module, paste this code:

'------------code begin---------------------
Public Function udfHasComments(pDonor_ID As Long) As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String

udfHasComments = ""

Set db = CurrentDb

' Change "TableB" to the name of your 'Never Deleted table'
strSQL = "Select REC_DONOR_ID From TableB"
strSQL = strSQL & " Where [REC_DONOR_ID] = " & pDonor_ID

Set rs = db.OpenRecordset(strSQL)
If Not rs.BOF And Not rs.EOF Then

udfHasComments = "Comments Available"

End If

rs.Close
Set rs = Nothing
Set db = Nothing

End Function
'------------code end------------------------


Don't forget to change "TableB" (in the code) the the real name of your
table!!!

Now we need to modify "FormA". The recordsource for "FormA" should be a
query. Open the query and add this to an empty column:


HasComments: udfHasComments([REC_DONOR_ID])


Save the query. Run the query. You should now see a column with
"Comments
Available" in some of the records.
Close the query.

Open "FormA" in design view. Add a text box. Selet the new text box. Open
the properties dialog box.

On the DATA tab, set:

Control Source - HasComments
Enabled - No
Locked - Yes


On the FORMAT tab, set these properties:

Back Style - Transparent
Border Style - Transparent


If you want the comments to stand out, also set:

Font Weight - Bold
Font Color - 255 (red)


Save "FormA", then open it.



HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


:

I considered that early on, but the problem is, the form I'm using has
at
least 20 different fields. I'm trying to keep it as un-confusing as
possible. I had thought that prompting the callers' attention to the
fact
that there is comments would keep it simple...less information on one
page.

If there isn't a good way to do what I've described above, I may find
some
clever way to encorporate a subform, but only if there is no other way.
--
R. Daniel Carlson
Concordia University
Office of Development


:

Have you tried using a subform?

Form A would be the master form and Form B would be the child (sub
form),
with the linking field "REC_DONOR_ID".


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


:

I have 2 tables.

One table (table A) stores data about donors who have given over
100
dollars. This table is purged and new data is imported into it at
least
twice a month. There are several fields in the form (FORM A) I
use, most of
them are not editible, but there are a few that are - so donor
callers can
leave simple notes about their phone converstaions, etc. One of
the fields
is called REC_DONOR_ID.

The other table (table B) has only a few fields. One field stores
comments
that the donor callers can leave about a donor, the other field is
the DONOR
ID field. This table NEVER gets deleted.

Whenever the REC_DONOR_ID field in table A above has a
corresponding record
in table B (based on DONOR ID), the button control I added to the
form will
filter, a new form will pop up, and only those records from table B
will show
(the comments about that particular donor). This works great. If
the donor
caller would like to leave a new comment, they can simply type in
the DONOR
ID and the comment.

I currently have no relationships between these two tables setup.

I would like to add one more feature to the form A. What it would
do is
this: When there is a comment (or comments) about the donor in
table B, the
words "Comments Available" would appear in an unbound text box.

I've tried different approaches...one worked well, but when using
it, I
couldn't type in any of the editable fields in form A. I created a
one-to-many relationship between the two tables, and added table B
to the
query I use. Again, the text would appear on every record that had
comments...but I couldn't edit, change or save anything in the
form.

I realized that, because there could be more than one record in
table B that
pertains to more than one record in table A, that a one to many
wouldn't
work. So I read up on many-to-many relationships. I couldn't find
a good
explaination on how this relationship works, so I gave up.

Does anyone have good way for me to make this work? The main
reason I have
2 tables is because I want the donor comments to be around forever,
and I
expect that there will be more than one entry for each donor....not
to
mention that donors will appear more than once in the donor table.
Is a
many-to many relationship the way to go? Is there some VB code
that could do
the trick just as well??

 

[R. Daniel Carlson]

The table with the information that is never deleted is called tbl_cont_notes
and the table that is purged weekly is tbl_donor_calls. I have the fields in
both tables labeled as REC_DONOR_ID.

As far as DAO 3.6, I'm not sure. The IT dept at the school I attend does
not like to do campus-wide updates on anything, and some of our computers
still have XP SP1 on them...
--
R. Daniel Carlson
Concordia University
Office of Development

In your first post, you said the names of the tables were "TableA" and
"TableB". What are the real names of the tables? Which table (the real name)
has the data delete and which is the one that is *never* deleted?

Does the table that is never deleted have a field "REC_DONOR_ID"? If not,
what is the name of the fields in both tables that is the common field?

Do you have a reference set for "Microsoft DAO 3.6 Library"?


--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

No, the module is called mod_cont_notes
--
R. Daniel Carlson
Concordia University
Office of Development

By any chance did you name the module in which the function's stored
udfHasComments? If so, rename it: modules cannot have the same name as
functions or subs.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


message Didn't work. Comes up with #error!.

Here is the code I used with explination:

'The Option Compare Database' put itself in the module when i created it'
Option Compare Database

' Changed pDonorID to pRec_Donor_ID, didn't work the other way either '
Public Function udfHasComments(pRec_Donor_ID As Long) As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String

udfHasComments = ""

Set db = CurrentDb
' REC_DONOR_ID is the name of the field in both tables, tbl_cont_notes is
the '
' name of the "tableB" table. '
strSQL = "Select REC_DONOR_ID From tbl_cont_notes"
strSQL = strSQL & " Where ([REC_DONOR_ID]) = " & pRec_Donor_ID

Set rs = db.OpenRecordset(strSQL)
If Not rs.BOF And Not rs.EOF Then

udfHasComments = "Donor Comments Available"

End If

rs.Close
Set rs = Nothing
Set db = Nothing

End Function

I added the HasComments: udfHasComments([REC_DONOR_ID]) line to a blank
column in the query I use, saved and ran the query. This is when the
#error!
appears in every entry of that field. I also added the unbound text box
to
the form, as, as expected, the #error! shows up there too.

Any suggestions?
--
R. Daniel Carlson
Concordia University
Office of Development


:

OK, here is one way.....


In a standard module, paste this code:

'------------code begin---------------------
Public Function udfHasComments(pDonor_ID As Long) As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String

udfHasComments = ""

Set db = CurrentDb

' Change "TableB" to the name of your 'Never Deleted table'
strSQL = "Select REC_DONOR_ID From TableB"
strSQL = strSQL & " Where [REC_DONOR_ID] = " & pDonor_ID

Set rs = db.OpenRecordset(strSQL)
If Not rs.BOF And Not rs.EOF Then

udfHasComments = "Comments Available"

End If

rs.Close
Set rs = Nothing
Set db = Nothing

End Function
'------------code end------------------------


Don't forget to change "TableB" (in the code) the the real name of your
table!!!

Now we need to modify "FormA". The recordsource for "FormA" should be a
query. Open the query and add this to an empty column:


HasComments: udfHasComments([REC_DONOR_ID])


Save the query. Run the query. You should now see a column with
"Comments
Available" in some of the records.
Close the query.

Open "FormA" in design view. Add a text box. Selet the new text box. Open
the properties dialog box.

On the DATA tab, set:

Control Source - HasComments
Enabled - No
Locked - Yes


On the FORMAT tab, set these properties:

Back Style - Transparent
Border Style - Transparent


If you want the comments to stand out, also set:

Font Weight - Bold
Font Color - 255 (red)


Save "FormA", then open it.



HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


:

I considered that early on, but the problem is, the form I'm using has
at
least 20 different fields. I'm trying to keep it as un-confusing as
possible. I had thought that prompting the callers' attention to the
fact
that there is comments would keep it simple...less information on one
page.

If there isn't a good way to do what I've described above, I may find
some
clever way to encorporate a subform, but only if there is no other way.
--
R. Daniel Carlson
Concordia University
Office of Development


:

Have you tried using a subform?

Form A would be the master form and Form B would be the child (sub
form),
with the linking field "REC_DONOR_ID".


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


:

I have 2 tables.

One table (table A) stores data about donors who have given over
100
dollars. This table is purged and new data is imported into it at
least
twice a month. There are several fields in the form (FORM A) I
use, most of
them are not editible, but there are a few that are - so donor
callers can
leave simple notes about their phone converstaions, etc. One of
the fields
is called REC_DONOR_ID.

The other table (table B) has only a few fields. One field stores
comments
that the donor callers can leave about a donor, the other field is
the DONOR
ID field. This table NEVER gets deleted.

Whenever the REC_DONOR_ID field in table A above has a
corresponding record
in table B (based on DONOR ID), the button control I added to the
form will
filter, a new form will pop up, and only those records from table B
will show
(the comments about that particular donor). This works great. If
the donor
caller would like to leave a new comment, they can simply type in
the DONOR
ID and the comment.

I currently have no relationships between these two tables setup.

I would like to add one more feature to the form A. What it would
do is
this: When there is a comment (or comments) about the donor in
table B, the
words "Comments Available" would appear in an unbound text box.

I've tried different approaches...one worked well, but when using
it, I
couldn't type in any of the editable fields in form A. I created a
one-to-many relationship between the two tables, and added table B
to the
query I use. Again, the text would appear on every record that had
comments...but I couldn't edit, change or save anything in the
form.

I realized that, because there could be more than one record in
table B that
pertains to more than one record in table A, that a one to many
wouldn't
work. So I read up on many-to-many relationships. I couldn't find
a good
explaination on how this relationship works, so I gave up.

Does anyone have good way for me to make this work? The main
reason I have
2 tables is because I want the donor comments to be around forever,
and I
expect that there will be more than one entry for each donor....not
to
mention that donors will appear more than once in the donor table.
Is a
many-to many relationship the way to go? Is there some VB code
that could do
the trick just as well??

 

[Steve Sanford]

Open the module "mod_cont_notes" in design view.
In the menu bar, click on TOOLS/REFERENCES. You should see an entry
"Microsoft DAO 3.6 Library".

If it is there, close the dialog box. If not, scroll down until you see it
and put a check in front of it. Close the dialog box.


In the module (actually all code pages), the first two lines should be:

Option Compare Database
Option Explicit


In the tables, what is the data type of field "REC_DONOR_ID"?

**If "REC_DONOR_ID" field type is NUMBER, use this function:

'------------code begin--data type number---
Public Function udfHasComments(pDonor_ID As Long) As String
' "pDonor_ID" is a parameter of type LONG

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String

udfHasComments = ""

Set db = CurrentDb

' create SQL string
strSQL = "Select REC_DONOR_ID From tbl_cont_notes"
strSQL = strSQL & " Where [REC_DONOR_ID] = " & pDonor_ID

Set rs = db.OpenRecordset(strSQL)
If Not rs.BOF And Not rs.EOF Then

udfHasComments = "Comments Available"

End If

rs.Close
Set rs = Nothing
Set db = Nothing

End Function
'------------code end-----------------------



**If "REC_DONOR_ID" field type is TEXT, use this function:

The function should be (exactly):

'------------code begin--data type text---
Public Function udfHasComments(pDonor_ID As String) As String
' "pDonor_ID" is a parameter of type TEXT

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String

udfHasComments = ""

Set db = CurrentDb

' create SQL string
strSQL = "Select REC_DONOR_ID From tbl_cont_notes"
strSQL = strSQL & " Where [REC_DONOR_ID] = '" & pDonor_ID & "'"

Set rs = db.OpenRecordset(strSQL)
If Not rs.BOF And Not rs.EOF Then

udfHasComments = "Comments Available"

End If

rs.Close
Set rs = Nothing
Set db = Nothing

End Function
'------------code end-----------------------



The record source for "FormA" should be a query. Open the query and add the
following to a blank column:

HasComments: udfHasComments([REC_DONOR_ID])

Save the query and run it. The column "HasComments" should show "Comments
Available" where the field [REC_DONOR_ID] in table "tbl_donor_calls" equals
the field [REC_DONOR_ID] in table "tbl_cont_notes".


Now modify "FormA": add a text box that has the control source "HasComments".


IF this still gives an error, post back with the data types if the fields in
the two tables and the SQL of the query for "FormA".

HTH

 

[R. Daniel Carlson]

Thanks !! The form works perfectly now.
--
R. Daniel Carlson
Concordia University
Office of Development

Open the module "mod_cont_notes" in design view.
In the menu bar, click on TOOLS/REFERENCES. You should see an entry
"Microsoft DAO 3.6 Library".

If it is there, close the dialog box. If not, scroll down until you see it
and put a check in front of it. Close the dialog box.


In the module (actually all code pages), the first two lines should be:

Option Compare Database
Option Explicit


In the tables, what is the data type of field "REC_DONOR_ID"?

**If "REC_DONOR_ID" field type is NUMBER, use this function:

'------------code begin--data type number---
Public Function udfHasComments(pDonor_ID As Long) As String
' "pDonor_ID" is a parameter of type LONG

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String

udfHasComments = ""

Set db = CurrentDb

' create SQL string
strSQL = "Select REC_DONOR_ID From tbl_cont_notes"
strSQL = strSQL & " Where [REC_DONOR_ID] = " & pDonor_ID

Set rs = db.OpenRecordset(strSQL)
If Not rs.BOF And Not rs.EOF Then

udfHasComments = "Comments Available"

End If

rs.Close
Set rs = Nothing
Set db = Nothing

End Function
'------------code end-----------------------



**If "REC_DONOR_ID" field type is TEXT, use this function:

The function should be (exactly):

'------------code begin--data type text---
Public Function udfHasComments(pDonor_ID As String) As String
' "pDonor_ID" is a parameter of type TEXT

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String

udfHasComments = ""

Set db = CurrentDb

' create SQL string
strSQL = "Select REC_DONOR_ID From tbl_cont_notes"
strSQL = strSQL & " Where [REC_DONOR_ID] = '" & pDonor_ID & "'"

Set rs = db.OpenRecordset(strSQL)
If Not rs.BOF And Not rs.EOF Then

udfHasComments = "Comments Available"

End If

rs.Close
Set rs = Nothing
Set db = Nothing

End Function
'------------code end-----------------------



The record source for "FormA" should be a query. Open the query and add the
following to a blank column:

HasComments: udfHasComments([REC_DONOR_ID])

Save the query and run it. The column "HasComments" should show "Comments
Available" where the field [REC_DONOR_ID] in table "tbl_donor_calls" equals
the field [REC_DONOR_ID] in table "tbl_cont_notes".


Now modify "FormA": add a text box that has the control source "HasComments".


IF this still gives an error, post back with the data types if the fields in
the two tables and the SQL of the query for "FormA".

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

The table with the information that is never deleted is called tbl_cont_notes
and the table that is purged weekly is tbl_donor_calls. I have the fields in
both tables labeled as REC_DONOR_ID.

As far as DAO 3.6, I'm not sure. The IT dept at the school I attend does
not like to do campus-wide updates on anything, and some of our computers
still have XP SP1 on them...