Expressions in a ComboBox?

[Guest]

Hi, context first ....

DB is a Student DB.

Form has student details, including Caregiver 1 & Caregiver 2 Details.

I have a subform for donations that allows users to select [from a Combo]
where the donation is to be spent. The other fields are Amount and Date.
The last field is 'Donated By'. This is where I need help. The Donation can
be made either as a:

Couple, or Caregiver 1 only or Caregiver 2 only.

I want the user to be able to select one of these three options from a
combobox [rather than typing in the names] but the names are made up by
expressions - eg:

=IIf(IsNull([CG2FirstName]),[CG1FirstName],[CG1FirstName] & " and " &
[CG2FirstName])

How do I get my requirement to work?

TIA

 

[Douglas J. Steele]

What you show looks as though it should handle the cases Couple, or
Caregiver 1 only. What's your specific problem? Are you uncertain how to
extend it to handle Caregiver 2 only, or is there some error that you're not
reporting?

To handle Caregiver 2 only, try:

=IIf(IsNull([CG1FirstName]), Nz([CG2FirstName],""),
IIf(IsNull([CG2FirstName]),[CG1FirstName],[CG1FirstName] & " and " &
[CG2FirstName]))

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi, context first ....

DB is a Student DB.

Form has student details, including Caregiver 1 & Caregiver 2 Details.

I have a subform for donations that allows users to select [from a Combo]
where the donation is to be spent. The other fields are Amount and Date.
The last field is 'Donated By'. This is where I need help. The Donation
can
be made either as a:

Couple, or Caregiver 1 only or Caregiver 2 only.

I want the user to be able to select one of these three options from a
combobox [rather than typing in the names] but the names are made up by
expressions - eg:

=IIf(IsNull([CG2FirstName]),[CG1FirstName],[CG1FirstName] & " and " &
[CG2FirstName])

How do I get my requirement to work?

TIA

 

[Guest]

Hi Doug ...

Thanks for being so prompt. My problem was that I didn't know HOW to get
these 'choices' of names INTO the combobox dropdown .... Are you saying that
I simply write these expressions in the Row Source of the ComboBox, and that
the Row Source Type is 'Value List'? I'm a newbie and can only 'do code' if
it's supplied.

Thanks -
--
Sue Compelling

What you show looks as though it should handle the cases Couple, or
Caregiver 1 only. What's your specific problem? Are you uncertain how to
extend it to handle Caregiver 2 only, or is there some error that you're not
reporting?

To handle Caregiver 2 only, try:

=IIf(IsNull([CG1FirstName]), Nz([CG2FirstName],""),
IIf(IsNull([CG2FirstName]),[CG1FirstName],[CG1FirstName] & " and " &
[CG2FirstName]))

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi, context first ....

DB is a Student DB.

Form has student details, including Caregiver 1 & Caregiver 2 Details.

I have a subform for donations that allows users to select [from a Combo]
where the donation is to be spent. The other fields are Amount and Date.
The last field is 'Donated By'. This is where I need help. The Donation
can
be made either as a:

Couple, or Caregiver 1 only or Caregiver 2 only.

I want the user to be able to select one of these three options from a
combobox [rather than typing in the names] but the names are made up by
expressions - eg:

=IIf(IsNull([CG2FirstName]),[CG1FirstName],[CG1FirstName] & " and " &
[CG2FirstName])

How do I get my requirement to work?

TIA

 

[Douglas J. Steele]

I'd assumed that [CG1FirstName] and [CG2FirstName] were the names of two
controls on your form: it sounds as though that might not be the case.
You'd need to post more details about both your tables and forms in order
for someone to give you a comprehensive answer.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi Doug ...

Thanks for being so prompt. My problem was that I didn't know HOW to get
these 'choices' of names INTO the combobox dropdown .... Are you saying
that
I simply write these expressions in the Row Source of the ComboBox, and
that
the Row Source Type is 'Value List'? I'm a newbie and can only 'do code'
if
it's supplied.

Thanks -
--
Sue Compelling

What you show looks as though it should handle the cases Couple, or
Caregiver 1 only. What's your specific problem? Are you uncertain how to
extend it to handle Caregiver 2 only, or is there some error that you're
not
reporting?

To handle Caregiver 2 only, try:

=IIf(IsNull([CG1FirstName]), Nz([CG2FirstName],""),
IIf(IsNull([CG2FirstName]),[CG1FirstName],[CG1FirstName] & " and " &
[CG2FirstName]))

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



message

Hi, context first ....

DB is a Student DB.

Form has student details, including Caregiver 1 & Caregiver 2 Details.

I have a subform for donations that allows users to select [from a
Combo]
where the donation is to be spent. The other fields are Amount and
Date.
The last field is 'Donated By'. This is where I need help. The
Donation
can
be made either as a:

Couple, or Caregiver 1 only or Caregiver 2 only.

I want the user to be able to select one of these three options from a
combobox [rather than typing in the names] but the names are made up by
expressions - eg:

=IIf(IsNull([CG2FirstName]),[CG1FirstName],[CG1FirstName] & " and " &
[CG2FirstName])

How do I get my requirement to work?

TIA

 

[Guest]

Hi Douglas

My Student form [Tab Control Page] has a number of fields, capturing student
details and their caregivers details. In this form it includes the fields
[CG1FirstName],[CG1LastName], [CG2FirstName], [CG1LastName].

Then on the next Tab Conrol Page - I have included a subform for donations.
This subform has the following fields:

StudentID
DonationID
Funds [ComboBox to select]
Amount
Date
DonatedBy

The DonatedBy is important, as donations can be received as a couple or from
an individual [in the case of separated parents]. What I wanted to do was to
present the 'choices' of these 3 scenarios in a picklist of some sort - to
stop the users from having to type the details [as I will be creating reports
on top donors, recent donors etc - and wanted the spelling obviously to be
consistent]. So..... how do I get these 'choices' of concatenated fields
into the combobox.

Really appreciate your help.
--
Sue Compelling

I'd assumed that [CG1FirstName] and [CG2FirstName] were the names of two
controls on your form: it sounds as though that might not be the case.
You'd need to post more details about both your tables and forms in order
for someone to give you a comprehensive answer.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi Doug ...

Thanks for being so prompt. My problem was that I didn't know HOW to get
these 'choices' of names INTO the combobox dropdown .... Are you saying
that
I simply write these expressions in the Row Source of the ComboBox, and
that
the Row Source Type is 'Value List'? I'm a newbie and can only 'do code'
if
it's supplied.

Thanks -
--
Sue Compelling

What you show looks as though it should handle the cases Couple, or
Caregiver 1 only. What's your specific problem? Are you uncertain how to
extend it to handle Caregiver 2 only, or is there some error that you're
not
reporting?

To handle Caregiver 2 only, try:

=IIf(IsNull([CG1FirstName]), Nz([CG2FirstName],""),
IIf(IsNull([CG2FirstName]),[CG1FirstName],[CG1FirstName] & " and " &
[CG2FirstName]))


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



message
Hi, context first ....

DB is a Student DB.

Form has student details, including Caregiver 1 & Caregiver 2 Details.

I have a subform for donations that allows users to select [from a
Combo]
where the donation is to be spent. The other fields are Amount and
Date.
The last field is 'Donated By'. This is where I need help. The
Donation
can
be made either as a:

Couple, or Caregiver 1 only or Caregiver 2 only.

I want the user to be able to select one of these three options from a
combobox [rather than typing in the names] but the names are made up by
expressions - eg:

=IIf(IsNull([CG2FirstName]),[CG1FirstName],[CG1FirstName] & " and " &
[CG2FirstName])

How do I get my requirement to work?

TIA

 

[Douglas J. Steele]

Ah, I'm with you now.

Try using something like the following in the form's OnCurrent event:

Dim strRowSource As String

If IsNull(Me.CG1FirstName) = False Then
strRowSource = strRowSource & Me.CG1FirstName & ";"
If IsNull(Me.CG2FirstName) = False Then
strRowSource = strRowSource & Me.CG2FirstName & ";"
strRowSource = strRowSource & Me.CG1FirstName & " and " &
Me.CG2FirstName & ";"
End If
Else
If IsNull(Me.CG2FirstName) = False Then
strRowSource = strRowSource & Me.CG2FirstName & ";"
End If
End If

If Len(strRowSource) > 0 Then
strRowSource = Left$(strR
Me.MyComboBox.RowSourceType = "Value List"
Me.MyComboBox.RowSource = strRowSource
Else
Me.MyComboBox.RowSourceType = ""
Me.MyComboBox.RowSource = ""
End If


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi Douglas

My Student form [Tab Control Page] has a number of fields, capturing
student
details and their caregivers details. In this form it includes the fields
[CG1FirstName],[CG1LastName], [CG2FirstName], [CG1LastName].

Then on the next Tab Conrol Page - I have included a subform for
donations.
This subform has the following fields:

StudentID
DonationID
Funds [ComboBox to select]
Amount
Date
DonatedBy

The DonatedBy is important, as donations can be received as a couple or
from
an individual [in the case of separated parents]. What I wanted to do was
to
present the 'choices' of these 3 scenarios in a picklist of some sort - to
stop the users from having to type the details [as I will be creating
reports
on top donors, recent donors etc - and wanted the spelling obviously to be
consistent]. So..... how do I get these 'choices' of concatenated fields
into the combobox.

Really appreciate your help.
--
Sue Compelling

I'd assumed that [CG1FirstName] and [CG2FirstName] were the names of two
controls on your form: it sounds as though that might not be the case.
You'd need to post more details about both your tables and forms in order
for someone to give you a comprehensive answer.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



message

Hi Doug ...

Thanks for being so prompt. My problem was that I didn't know HOW to
get
these 'choices' of names INTO the combobox dropdown .... Are you saying
that
I simply write these expressions in the Row Source of the ComboBox, and
that
the Row Source Type is 'Value List'? I'm a newbie and can only 'do
code'
if
it's supplied.

Thanks -
--
Sue Compelling


:

What you show looks as though it should handle the cases Couple, or
Caregiver 1 only. What's your specific problem? Are you uncertain how
to
extend it to handle Caregiver 2 only, or is there some error that
you're
not
reporting?

To handle Caregiver 2 only, try:

=IIf(IsNull([CG1FirstName]), Nz([CG2FirstName],""),
IIf(IsNull([CG2FirstName]),[CG1FirstName],[CG1FirstName] & " and " &
[CG2FirstName]))


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



message
Hi, context first ....

DB is a Student DB.

Form has student details, including Caregiver 1 & Caregiver 2
Details.

I have a subform for donations that allows users to select [from a
Combo]
where the donation is to be spent. The other fields are Amount and
Date.
The last field is 'Donated By'. This is where I need help. The
Donation
can
be made either as a:

Couple, or Caregiver 1 only or Caregiver 2 only.

I want the user to be able to select one of these three options from
a
combobox [rather than typing in the names] but the names are made up
by
expressions - eg:

=IIf(IsNull([CG2FirstName]),[CG1FirstName],[CG1FirstName] & " and "
&
[CG2FirstName])

How do I get my requirement to work?

TIA

 

[Guest]

Hi Doug - yep, ['patience of a Saint Doug']. I copied the code (closed the
bracket on the Left$(strR - line) though ACCESS came back with the following
error:

'The expression OnCurrent you entered as the event property setting produced
the following error: A problem occurred while MSFT A ... was communicating
with the OLE server or ActiveX Control
* The expression may not result in the name of a macro, the name of a
user-defined function or [Event Procedure]
* or there may have been an error evaluationing the function, event or macro.

What now?
--
Sue Compelling

Ah, I'm with you now.

Try using something like the following in the form's OnCurrent event:

Dim strRowSource As String

If IsNull(Me.CG1FirstName) = False Then
strRowSource = strRowSource & Me.CG1FirstName & ";"
If IsNull(Me.CG2FirstName) = False Then
strRowSource = strRowSource & Me.CG2FirstName & ";"
strRowSource = strRowSource & Me.CG1FirstName & " and " &
Me.CG2FirstName & ";"
End If
Else
If IsNull(Me.CG2FirstName) = False Then
strRowSource = strRowSource & Me.CG2FirstName & ";"
End If
End If

If Len(strRowSource) > 0 Then
strRowSource = Left$(strR
Me.MyComboBox.RowSourceType = "Value List"
Me.MyComboBox.RowSource = strRowSource
Else
Me.MyComboBox.RowSourceType = ""
Me.MyComboBox.RowSource = ""
End If


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi Douglas

My Student form [Tab Control Page] has a number of fields, capturing
student
details and their caregivers details. In this form it includes the fields
[CG1FirstName],[CG1LastName], [CG2FirstName], [CG1LastName].

Then on the next Tab Conrol Page - I have included a subform for
donations.
This subform has the following fields:

StudentID
DonationID
Funds [ComboBox to select]
Amount
Date
DonatedBy

The DonatedBy is important, as donations can be received as a couple or
from
an individual [in the case of separated parents]. What I wanted to do was
to
present the 'choices' of these 3 scenarios in a picklist of some sort - to
stop the users from having to type the details [as I will be creating
reports
on top donors, recent donors etc - and wanted the spelling obviously to be
consistent]. So..... how do I get these 'choices' of concatenated fields
into the combobox.

Really appreciate your help.
--
Sue Compelling

I'd assumed that [CG1FirstName] and [CG2FirstName] were the names of two
controls on your form: it sounds as though that might not be the case.
You'd need to post more details about both your tables and forms in order
for someone to give you a comprehensive answer.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



message
Hi Doug ...

Thanks for being so prompt. My problem was that I didn't know HOW to
get
these 'choices' of names INTO the combobox dropdown .... Are you saying
that
I simply write these expressions in the Row Source of the ComboBox, and
that
the Row Source Type is 'Value List'? I'm a newbie and can only 'do
code'
if
it's supplied.

Thanks -
--
Sue Compelling


:

What you show looks as though it should handle the cases Couple, or
Caregiver 1 only. What's your specific problem? Are you uncertain how
to
extend it to handle Caregiver 2 only, or is there some error that
you're
not
reporting?

To handle Caregiver 2 only, try:

=IIf(IsNull([CG1FirstName]), Nz([CG2FirstName],""),
IIf(IsNull([CG2FirstName]),[CG1FirstName],[CG1FirstName] & " and " &
[CG2FirstName]))


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



message
Hi, context first ....

DB is a Student DB.

Form has student details, including Caregiver 1 & Caregiver 2
Details.

I have a subform for donations that allows users to select [from a
Combo]
where the donation is to be spent. The other fields are Amount and
Date.
The last field is 'Donated By'. This is where I need help. The
Donation
can
be made either as a:

Couple, or Caregiver 1 only or Caregiver 2 only.

I want the user to be able to select one of these three options from
a
combobox [rather than typing in the names] but the names are made up
by
expressions - eg:

=IIf(IsNull([CG2FirstName]),[CG1FirstName],[CG1FirstName] & " and "
&
[CG2FirstName])

How do I get my requirement to work?

TIA

 

[Douglas J. Steele]

Slight typo there: the line should have been

strRowSource = Left$(strRowSource, Len(strRowSource)-1)

(it's taking the superfluous final semicolon off the string)

See whether making that correction solves the problem.

If not, paste the entire code you've got in the Event.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi Doug - yep, ['patience of a Saint Doug']. I copied the code (closed
the
bracket on the Left$(strR - line) though ACCESS came back with the
following
error:

'The expression OnCurrent you entered as the event property setting
produced
the following error: A problem occurred while MSFT A ... was communicating
with the OLE server or ActiveX Control
* The expression may not result in the name of a macro, the name of a
user-defined function or [Event Procedure]
* or there may have been an error evaluationing the function, event or
macro.

What now?
--
Sue Compelling

Ah, I'm with you now.

Try using something like the following in the form's OnCurrent event:

Dim strRowSource As String

If IsNull(Me.CG1FirstName) = False Then
strRowSource = strRowSource & Me.CG1FirstName & ";"
If IsNull(Me.CG2FirstName) = False Then
strRowSource = strRowSource & Me.CG2FirstName & ";"
strRowSource = strRowSource & Me.CG1FirstName & " and " &
Me.CG2FirstName & ";"
End If
Else
If IsNull(Me.CG2FirstName) = False Then
strRowSource = strRowSource & Me.CG2FirstName & ";"
End If
End If

If Len(strRowSource) > 0 Then
strRowSource = Left$(strR
Me.MyComboBox.RowSourceType = "Value List"
Me.MyComboBox.RowSource = strRowSource
Else
Me.MyComboBox.RowSourceType = ""
Me.MyComboBox.RowSource = ""
End If


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



message

Hi Douglas

My Student form [Tab Control Page] has a number of fields, capturing
student
details and their caregivers details. In this form it includes the
fields
[CG1FirstName],[CG1LastName], [CG2FirstName], [CG1LastName].

Then on the next Tab Conrol Page - I have included a subform for
donations.
This subform has the following fields:

StudentID
DonationID
Funds [ComboBox to select]
Amount
Date
DonatedBy

The DonatedBy is important, as donations can be received as a couple or
from
an individual [in the case of separated parents]. What I wanted to do
was
to
present the 'choices' of these 3 scenarios in a picklist of some sort -
to
stop the users from having to type the details [as I will be creating
reports
on top donors, recent donors etc - and wanted the spelling obviously to
be
consistent]. So..... how do I get these 'choices' of concatenated
fields
into the combobox.

Really appreciate your help.
--
Sue Compelling


:

I'd assumed that [CG1FirstName] and [CG2FirstName] were the names of
two
controls on your form: it sounds as though that might not be the
case.
You'd need to post more details about both your tables and forms in
order
for someone to give you a comprehensive answer.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



message
Hi Doug ...

Thanks for being so prompt. My problem was that I didn't know HOW
to
get
these 'choices' of names INTO the combobox dropdown .... Are you
saying
that
I simply write these expressions in the Row Source of the ComboBox,
and
that
the Row Source Type is 'Value List'? I'm a newbie and can only 'do
code'
if
it's supplied.

Thanks -
--
Sue Compelling


:

What you show looks as though it should handle the cases Couple, or
Caregiver 1 only. What's your specific problem? Are you uncertain
how
to
extend it to handle Caregiver 2 only, or is there some error that
you're
not
reporting?

To handle Caregiver 2 only, try:

=IIf(IsNull([CG1FirstName]), Nz([CG2FirstName],""),
IIf(IsNull([CG2FirstName]),[CG1FirstName],[CG1FirstName] & " and
" &
[CG2FirstName]))


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



message
Hi, context first ....

DB is a Student DB.

Form has student details, including Caregiver 1 & Caregiver 2
Details.

I have a subform for donations that allows users to select [from
a
Combo]
where the donation is to be spent. The other fields are Amount
and
Date.
The last field is 'Donated By'. This is where I need help. The
Donation
can
be made either as a:

Couple, or Caregiver 1 only or Caregiver 2 only.

I want the user to be able to select one of these three options
from
a
combobox [rather than typing in the names] but the names are made
up
by
expressions - eg:

=IIf(IsNull([CG2FirstName]),[CG1FirstName],[CG1FirstName] & " and
"
&
[CG2FirstName])

How do I get my requirement to work?

TIA

 

[Guest]

Oh Doug - grrrrrrrrrrr

It still didn't work - I took the event procedure OFF the Donations SubForm
'OnCurrent' and put it on the Donated By ComboBox 'On Enter', though it comes
up with the same error as before. This is the code exactly as it's in the
event procedure. I don't want to give up [self taught and all] .... though
I'm willing to persevere if you are... [Is there additional info you need
from me that would help?] - does this forum allow screen shots to be attached?

Private Sub Donated_By_Enter()

Dim strRowSource As String

If IsNull(Me.CG1FirstName) = False Then
strRowSource = strRowSource & Me.CG1FirstName & ";"
If IsNull(Me.CG2FirstName) = False Then
strRowSource = strRowSource & Me.CG2FirstName & ";"
strRowSource = strRowSource & Me.CG1FirstName & " and " &
Me.CG2FirstName & ";"
End If
Else
If IsNull(Me.CG2FirstName) = False Then
strRowSource = strRowSource & Me.CG2FirstName & ";"
End If
End If

If Len(strRowSource) > 0 Then
strRowSource = Left$(strRowSource, Len(strRowSource) - 1)
Me.Donated By.RowSourceType = "Value List"
Me.Donated By.RowSource = strRowSource
Else
Me.Donated By.RowSourceType = ""
Me.Donated By.RowSource = ""
End If

End Sub
--
Sue Compelling

Slight typo there: the line should have been

strRowSource = Left$(strRowSource, Len(strRowSource)-1)

(it's taking the superfluous final semicolon off the string)

See whether making that correction solves the problem.

If not, paste the entire code you've got in the Event.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi Doug - yep, ['patience of a Saint Doug']. I copied the code (closed
the
bracket on the Left$(strR - line) though ACCESS came back with the
following
error:

'The expression OnCurrent you entered as the event property setting
produced
the following error: A problem occurred while MSFT A ... was communicating
with the OLE server or ActiveX Control
* The expression may not result in the name of a macro, the name of a
user-defined function or [Event Procedure]
* or there may have been an error evaluationing the function, event or
macro.

What now?
--
Sue Compelling

Ah, I'm with you now.

Try using something like the following in the form's OnCurrent event:

Dim strRowSource As String

If IsNull(Me.CG1FirstName) = False Then
strRowSource = strRowSource & Me.CG1FirstName & ";"
If IsNull(Me.CG2FirstName) = False Then
strRowSource = strRowSource & Me.CG2FirstName & ";"
strRowSource = strRowSource & Me.CG1FirstName & " and " &
Me.CG2FirstName & ";"
End If
Else
If IsNull(Me.CG2FirstName) = False Then
strRowSource = strRowSource & Me.CG2FirstName & ";"
End If
End If

If Len(strRowSource) > 0 Then
strRowSource = Left$(strR
Me.MyComboBox.RowSourceType = "Value List"
Me.MyComboBox.RowSource = strRowSource
Else
Me.MyComboBox.RowSourceType = ""
Me.MyComboBox.RowSource = ""
End If


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



message
Hi Douglas

My Student form [Tab Control Page] has a number of fields, capturing
student
details and their caregivers details. In this form it includes the
fields
[CG1FirstName],[CG1LastName], [CG2FirstName], [CG1LastName].

Then on the next Tab Conrol Page - I have included a subform for
donations.
This subform has the following fields:

StudentID
DonationID
Funds [ComboBox to select]
Amount
Date
DonatedBy

The DonatedBy is important, as donations can be received as a couple or
from
an individual [in the case of separated parents]. What I wanted to do
was
to
present the 'choices' of these 3 scenarios in a picklist of some sort -
to
stop the users from having to type the details [as I will be creating
reports
on top donors, recent donors etc - and wanted the spelling obviously to
be
consistent]. So..... how do I get these 'choices' of concatenated
fields
into the combobox.

Really appreciate your help.
--
Sue Compelling


:

I'd assumed that [CG1FirstName] and [CG2FirstName] were the names of
two
controls on your form: it sounds as though that might not be the
case.
You'd need to post more details about both your tables and forms in
order
for someone to give you a comprehensive answer.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



message
Hi Doug ...

Thanks for being so prompt. My problem was that I didn't know HOW
to
get
these 'choices' of names INTO the combobox dropdown .... Are you
saying
that
I simply write these expressions in the Row Source of the ComboBox,
and
that
the Row Source Type is 'Value List'? I'm a newbie and can only 'do
code'
if
it's supplied.

Thanks -
--
Sue Compelling


:

What you show looks as though it should handle the cases Couple, or
Caregiver 1 only. What's your specific problem? Are you uncertain
how
to
extend it to handle Caregiver 2 only, or is there some error that
you're
not
reporting?

To handle Caregiver 2 only, try:

=IIf(IsNull([CG1FirstName]), Nz([CG2FirstName],""),
IIf(IsNull([CG2FirstName]),[CG1FirstName],[CG1FirstName] & " and
" &
[CG2FirstName]))


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



message
Hi, context first ....

DB is a Student DB.

Form has student details, including Caregiver 1 & Caregiver 2
Details.

I have a subform for donations that allows users to select [from
a
Combo]
where the donation is to be spent. The other fields are Amount
and
Date.
The last field is 'Donated By'. This is where I need help. The
Donation
can
be made either as a:

Couple, or Caregiver 1 only or Caregiver 2 only.

I want the user to be able to select one of these three options
from
a
combobox [rather than typing in the names] but the names are made
up
by
expressions - eg:

=IIf(IsNull([CG2FirstName]),[CG1FirstName],[CG1FirstName] & " and
"
&
[CG2FirstName])

How do I get my requirement to work?

TIA

 

[Guest]

Doug - this might help ....

I want the following THREE options to be available from the combobox

CG1FirstName & " " & CG1LastName & " & " & CG2FirstName & " " & CG2LastName

or

CG1FirstName & " " & CG1LastName

or

CG2FirstName & " " & CG2LastName


Cheers

--
Sue Compelling

Oh Doug - grrrrrrrrrrr

It still didn't work - I took the event procedure OFF the Donations SubForm
'OnCurrent' and put it on the Donated By ComboBox 'On Enter', though it comes
up with the same error as before. This is the code exactly as it's in the
event procedure. I don't want to give up [self taught and all] .... though
I'm willing to persevere if you are... [Is there additional info you need
from me that would help?] - does this forum allow screen shots to be attached?

Private Sub Donated_By_Enter()

Dim strRowSource As String

If IsNull(Me.CG1FirstName) = False Then
strRowSource = strRowSource & Me.CG1FirstName & ";"
If IsNull(Me.CG2FirstName) = False Then
strRowSource = strRowSource & Me.CG2FirstName & ";"
strRowSource = strRowSource & Me.CG1FirstName & " and " &
Me.CG2FirstName & ";"
End If
Else
If IsNull(Me.CG2FirstName) = False Then
strRowSource = strRowSource & Me.CG2FirstName & ";"
End If
End If

If Len(strRowSource) > 0 Then
strRowSource = Left$(strRowSource, Len(strRowSource) - 1)
Me.Donated By.RowSourceType = "Value List"
Me.Donated By.RowSource = strRowSource
Else
Me.Donated By.RowSourceType = ""
Me.Donated By.RowSource = ""
End If

End Sub
--
Sue Compelling

Slight typo there: the line should have been

strRowSource = Left$(strRowSource, Len(strRowSource)-1)

(it's taking the superfluous final semicolon off the string)

See whether making that correction solves the problem.

If not, paste the entire code you've got in the Event.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi Doug - yep, ['patience of a Saint Doug']. I copied the code (closed
the
bracket on the Left$(strR - line) though ACCESS came back with the
following
error:

'The expression OnCurrent you entered as the event property setting
produced
the following error: A problem occurred while MSFT A ... was communicating
with the OLE server or ActiveX Control
* The expression may not result in the name of a macro, the name of a
user-defined function or [Event Procedure]
* or there may have been an error evaluationing the function, event or
macro.

What now?
--
Sue Compelling


:

Ah, I'm with you now.

Try using something like the following in the form's OnCurrent event:

Dim strRowSource As String

If IsNull(Me.CG1FirstName) = False Then
strRowSource = strRowSource & Me.CG1FirstName & ";"
If IsNull(Me.CG2FirstName) = False Then
strRowSource = strRowSource & Me.CG2FirstName & ";"
strRowSource = strRowSource & Me.CG1FirstName & " and " &
Me.CG2FirstName & ";"
End If
Else
If IsNull(Me.CG2FirstName) = False Then
strRowSource = strRowSource & Me.CG2FirstName & ";"
End If
End If

If Len(strRowSource) > 0 Then
strRowSource = Left$(strR
Me.MyComboBox.RowSourceType = "Value List"
Me.MyComboBox.RowSource = strRowSource
Else
Me.MyComboBox.RowSourceType = ""
Me.MyComboBox.RowSource = ""
End If


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



message
Hi Douglas

My Student form [Tab Control Page] has a number of fields, capturing
student
details and their caregivers details. In this form it includes the
fields
[CG1FirstName],[CG1LastName], [CG2FirstName], [CG1LastName].

Then on the next Tab Conrol Page - I have included a subform for
donations.
This subform has the following fields:

StudentID
DonationID
Funds [ComboBox to select]
Amount
Date
DonatedBy

The DonatedBy is important, as donations can be received as a couple or
from
an individual [in the case of separated parents]. What I wanted to do
was
to
present the 'choices' of these 3 scenarios in a picklist of some sort -
to
stop the users from having to type the details [as I will be creating
reports
on top donors, recent donors etc - and wanted the spelling obviously to
be
consistent]. So..... how do I get these 'choices' of concatenated
fields
into the combobox.

Really appreciate your help.
--
Sue Compelling


:

I'd assumed that [CG1FirstName] and [CG2FirstName] were the names of
two
controls on your form: it sounds as though that might not be the
case.
You'd need to post more details about both your tables and forms in
order
for someone to give you a comprehensive answer.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



message
Hi Doug ...

Thanks for being so prompt. My problem was that I didn't know HOW
to
get
these 'choices' of names INTO the combobox dropdown .... Are you
saying
that
I simply write these expressions in the Row Source of the ComboBox,
and
that
the Row Source Type is 'Value List'? I'm a newbie and can only 'do
code'
if
it's supplied.

Thanks -
--
Sue Compelling


:

What you show looks as though it should handle the cases Couple, or
Caregiver 1 only. What's your specific problem? Are you uncertain
how
to
extend it to handle Caregiver 2 only, or is there some error that
you're
not
reporting?

To handle Caregiver 2 only, try:

=IIf(IsNull([CG1FirstName]), Nz([CG2FirstName],""),
IIf(IsNull([CG2FirstName]),[CG1FirstName],[CG1FirstName] & " and
" &
[CG2FirstName]))


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



message
Hi, context first ....

DB is a Student DB.

Form has student details, including Caregiver 1 & Caregiver 2
Details.

I have a subform for donations that allows users to select [from
a
Combo]
where the donation is to be spent. The other fields are Amount
and
Date.
The last field is 'Donated By'. This is where I need help. The
Donation
can
be made either as a:

Couple, or Caregiver 1 only or Caregiver 2 only.

I want the user to be able to select one of these three options
from
a
combobox [rather than typing in the names] but the names are made
up
by
expressions - eg:

=IIf(IsNull([CG2FirstName]),[CG1FirstName],[CG1FirstName] & " and
"
&
[CG2FirstName])

How do I get my requirement to work?

TIA

 

[Douglas J. Steele]

Given that you've named the routine Donated_By_Enter(), what are you putting
in the OnCurrent events?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Oh Doug - grrrrrrrrrrr

It still didn't work - I took the event procedure OFF the Donations
SubForm
'OnCurrent' and put it on the Donated By ComboBox 'On Enter', though it
comes
up with the same error as before. This is the code exactly as it's in the
event procedure. I don't want to give up [self taught and all] ....
though
I'm willing to persevere if you are... [Is there additional info you need
from me that would help?] - does this forum allow screen shots to be
attached?

Private Sub Donated_By_Enter()

Dim strRowSource As String

If IsNull(Me.CG1FirstName) = False Then
strRowSource = strRowSource & Me.CG1FirstName & ";"
If IsNull(Me.CG2FirstName) = False Then
strRowSource = strRowSource & Me.CG2FirstName & ";"
strRowSource = strRowSource & Me.CG1FirstName & " and " &
Me.CG2FirstName & ";"
End If
Else
If IsNull(Me.CG2FirstName) = False Then
strRowSource = strRowSource & Me.CG2FirstName & ";"
End If
End If

If Len(strRowSource) > 0 Then
strRowSource = Left$(strRowSource, Len(strRowSource) - 1)
Me.Donated By.RowSourceType = "Value List"
Me.Donated By.RowSource = strRowSource
Else
Me.Donated By.RowSourceType = ""
Me.Donated By.RowSource = ""
End If

End Sub
--
Sue Compelling

Slight typo there: the line should have been

strRowSource = Left$(strRowSource, Len(strRowSource)-1)

(it's taking the superfluous final semicolon off the string)

See whether making that correction solves the problem.

If not, paste the entire code you've got in the Event.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



message

Hi Doug - yep, ['patience of a Saint Doug']. I copied the code (closed
the
bracket on the Left$(strR - line) though ACCESS came back with the
following
error:

'The expression OnCurrent you entered as the event property setting
produced
the following error: A problem occurred while MSFT A ... was
communicating
with the OLE server or ActiveX Control
* The expression may not result in the name of a macro, the name of a
user-defined function or [Event Procedure]
* or there may have been an error evaluationing the function, event or
macro.

What now?
--
Sue Compelling


:

Ah, I'm with you now.

Try using something like the following in the form's OnCurrent event:

Dim strRowSource As String

If IsNull(Me.CG1FirstName) = False Then
strRowSource = strRowSource & Me.CG1FirstName & ";"
If IsNull(Me.CG2FirstName) = False Then
strRowSource = strRowSource & Me.CG2FirstName & ";"
strRowSource = strRowSource & Me.CG1FirstName & " and " &
Me.CG2FirstName & ";"
End If
Else
If IsNull(Me.CG2FirstName) = False Then
strRowSource = strRowSource & Me.CG2FirstName & ";"
End If
End If

If Len(strRowSource) > 0 Then
strRowSource = Left$(strR
Me.MyComboBox.RowSourceType = "Value List"
Me.MyComboBox.RowSource = strRowSource
Else
Me.MyComboBox.RowSourceType = ""
Me.MyComboBox.RowSource = ""
End If


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



message
Hi Douglas

My Student form [Tab Control Page] has a number of fields, capturing
student
details and their caregivers details. In this form it includes the
fields
[CG1FirstName],[CG1LastName], [CG2FirstName], [CG1LastName].

Then on the next Tab Conrol Page - I have included a subform for
donations.
This subform has the following fields:

StudentID
DonationID
Funds [ComboBox to select]
Amount
Date
DonatedBy

The DonatedBy is important, as donations can be received as a couple
or
from
an individual [in the case of separated parents]. What I wanted to
do
was
to
present the 'choices' of these 3 scenarios in a picklist of some
sort -
to
stop the users from having to type the details [as I will be
creating
reports
on top donors, recent donors etc - and wanted the spelling obviously
to
be
consistent]. So..... how do I get these 'choices' of concatenated
fields
into the combobox.

Really appreciate your help.
--
Sue Compelling


:

I'd assumed that [CG1FirstName] and [CG2FirstName] were the names
of
two
controls on your form: it sounds as though that might not be the
case.
You'd need to post more details about both your tables and forms in
order
for someone to give you a comprehensive answer.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



message
Hi Doug ...

Thanks for being so prompt. My problem was that I didn't know
HOW
to
get
these 'choices' of names INTO the combobox dropdown .... Are you
saying
that
I simply write these expressions in the Row Source of the
ComboBox,
and
that
the Row Source Type is 'Value List'? I'm a newbie and can only
'do
code'
if
it's supplied.

Thanks -
--
Sue Compelling


:

What you show looks as though it should handle the cases Couple,
or
Caregiver 1 only. What's your specific problem? Are you
uncertain
how
to
extend it to handle Caregiver 2 only, or is there some error
that
you're
not
reporting?

To handle Caregiver 2 only, try:

=IIf(IsNull([CG1FirstName]), Nz([CG2FirstName],""),
IIf(IsNull([CG2FirstName]),[CG1FirstName],[CG1FirstName] & "
and
" &
[CG2FirstName]))


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



in
message
Hi, context first ....

DB is a Student DB.

Form has student details, including Caregiver 1 & Caregiver 2
Details.

I have a subform for donations that allows users to select
[from
a
Combo]
where the donation is to be spent. The other fields are
Amount
and
Date.
The last field is 'Donated By'. This is where I need help.
The
Donation
can
be made either as a:

Couple, or Caregiver 1 only or Caregiver 2 only.

I want the user to be able to select one of these three
options
from
a
combobox [rather than typing in the names] but the names are
made
up
by
expressions - eg:

=IIf(IsNull([CG2FirstName]),[CG1FirstName],[CG1FirstName] & "
and
"
&
[CG2FirstName])

How do I get my requirement to work?

TIA

 

[Guest]

I took the event procedure out of the OnCurrent of the subform (and put in in
the OnEnter of the ComboBox) .... is this a bad thing? Is this where the
mistake is? When had I tried this same code on the OnCurrent of the subform
though it came back with the error I previously described.
--
Sue Compelling

Given that you've named the routine Donated_By_Enter(), what are you putting
in the OnCurrent events?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Oh Doug - grrrrrrrrrrr

It still didn't work - I took the event procedure OFF the Donations
SubForm
'OnCurrent' and put it on the Donated By ComboBox 'On Enter', though it
comes
up with the same error as before. This is the code exactly as it's in the
event procedure. I don't want to give up [self taught and all] ....
though
I'm willing to persevere if you are... [Is there additional info you need
from me that would help?] - does this forum allow screen shots to be
attached?

Private Sub Donated_By_Enter()

Dim strRowSource As String

If IsNull(Me.CG1FirstName) = False Then
strRowSource = strRowSource & Me.CG1FirstName & ";"
If IsNull(Me.CG2FirstName) = False Then
strRowSource = strRowSource & Me.CG2FirstName & ";"
strRowSource = strRowSource & Me.CG1FirstName & " and " &
Me.CG2FirstName & ";"
End If
Else
If IsNull(Me.CG2FirstName) = False Then
strRowSource = strRowSource & Me.CG2FirstName & ";"
End If
End If

If Len(strRowSource) > 0 Then
strRowSource = Left$(strRowSource, Len(strRowSource) - 1)
Me.Donated By.RowSourceType = "Value List"
Me.Donated By.RowSource = strRowSource
Else
Me.Donated By.RowSourceType = ""
Me.Donated By.RowSource = ""
End If

End Sub
--
Sue Compelling

Slight typo there: the line should have been

strRowSource = Left$(strRowSource, Len(strRowSource)-1)

(it's taking the superfluous final semicolon off the string)

See whether making that correction solves the problem.

If not, paste the entire code you've got in the Event.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



message
Hi Doug - yep, ['patience of a Saint Doug']. I copied the code (closed
the
bracket on the Left$(strR - line) though ACCESS came back with the
following
error:

'The expression OnCurrent you entered as the event property setting
produced
the following error: A problem occurred while MSFT A ... was
communicating
with the OLE server or ActiveX Control
* The expression may not result in the name of a macro, the name of a
user-defined function or [Event Procedure]
* or there may have been an error evaluationing the function, event or
macro.

What now?
--
Sue Compelling


:

Ah, I'm with you now.

Try using something like the following in the form's OnCurrent event:

Dim strRowSource As String

If IsNull(Me.CG1FirstName) = False Then
strRowSource = strRowSource & Me.CG1FirstName & ";"
If IsNull(Me.CG2FirstName) = False Then
strRowSource = strRowSource & Me.CG2FirstName & ";"
strRowSource = strRowSource & Me.CG1FirstName & " and " &
Me.CG2FirstName & ";"
End If
Else
If IsNull(Me.CG2FirstName) = False Then
strRowSource = strRowSource & Me.CG2FirstName & ";"
End If
End If

If Len(strRowSource) > 0 Then
strRowSource = Left$(strR
Me.MyComboBox.RowSourceType = "Value List"
Me.MyComboBox.RowSource = strRowSource
Else
Me.MyComboBox.RowSourceType = ""
Me.MyComboBox.RowSource = ""
End If


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



message
Hi Douglas

My Student form [Tab Control Page] has a number of fields, capturing
student
details and their caregivers details. In this form it includes the
fields
[CG1FirstName],[CG1LastName], [CG2FirstName], [CG1LastName].

Then on the next Tab Conrol Page - I have included a subform for
donations.
This subform has the following fields:

StudentID
DonationID
Funds [ComboBox to select]
Amount
Date
DonatedBy

The DonatedBy is important, as donations can be received as a couple
or
from
an individual [in the case of separated parents]. What I wanted to
do
was
to
present the 'choices' of these 3 scenarios in a picklist of some
sort -
to
stop the users from having to type the details [as I will be
creating
reports
on top donors, recent donors etc - and wanted the spelling obviously
to
be
consistent]. So..... how do I get these 'choices' of concatenated
fields
into the combobox.

Really appreciate your help.
--
Sue Compelling


:

I'd assumed that [CG1FirstName] and [CG2FirstName] were the names
of
two
controls on your form: it sounds as though that might not be the
case.
You'd need to post more details about both your tables and forms in
order
for someone to give you a comprehensive answer.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



message
Hi Doug ...

Thanks for being so prompt. My problem was that I didn't know
HOW
to
get
these 'choices' of names INTO the combobox dropdown .... Are you
saying
that
I simply write these expressions in the Row Source of the
ComboBox,
and
that
the Row Source Type is 'Value List'? I'm a newbie and can only
'do
code'
if
it's supplied.

Thanks -
--
Sue Compelling


:

What you show looks as though it should handle the cases Couple,
or
Caregiver 1 only. What's your specific problem? Are you
uncertain
how
to
extend it to handle Caregiver 2 only, or is there some error
that
you're
not
reporting?

To handle Caregiver 2 only, try:

=IIf(IsNull([CG1FirstName]), Nz([CG2FirstName],""),
IIf(IsNull([CG2FirstName]),[CG1FirstName],[CG1FirstName] & "
and
" &
[CG2FirstName]))


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



in
message
Hi, context first ....

DB is a Student DB.

Form has student details, including Caregiver 1 & Caregiver 2
Details.

I have a subform for donations that allows users to select
[from
a
Combo]
where the donation is to be spent. The other fields are
Amount
and
Date.
The last field is 'Donated By'. This is where I need help.
The
Donation
can
be made either as a:

Couple, or Caregiver 1 only or Caregiver 2 only.

I want the user to be able to select one of these three
options
from
a
combobox [rather than typing in the names] but the names are
made
up
by
expressions - eg:

=IIf(IsNull([CG2FirstName]),[CG1FirstName],[CG1FirstName] & "
and
"

 

[Guest]

Hi Doug - I also meant to say, I thought you'd given up on me [as I hadn't
heard anything for a day]. I wasn't quite sure what the protocol is in these
situations, so made another post. Please don't leave me - I want to crack
this.
--
Sue Compelling

I took the event procedure out of the OnCurrent of the subform (and put in in
the OnEnter of the ComboBox) .... is this a bad thing? Is this where the
mistake is? When had I tried this same code on the OnCurrent of the subform
though it came back with the error I previously described.
--
Sue Compelling

Given that you've named the routine Donated_By_Enter(), what are you putting
in the OnCurrent events?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Oh Doug - grrrrrrrrrrr

It still didn't work - I took the event procedure OFF the Donations
SubForm
'OnCurrent' and put it on the Donated By ComboBox 'On Enter', though it
comes
up with the same error as before. This is the code exactly as it's in the
event procedure. I don't want to give up [self taught and all] ....
though
I'm willing to persevere if you are... [Is there additional info you need
from me that would help?] - does this forum allow screen shots to be
attached?

Private Sub Donated_By_Enter()

Dim strRowSource As String

If IsNull(Me.CG1FirstName) = False Then
strRowSource = strRowSource & Me.CG1FirstName & ";"
If IsNull(Me.CG2FirstName) = False Then
strRowSource = strRowSource & Me.CG2FirstName & ";"
strRowSource = strRowSource & Me.CG1FirstName & " and " &
Me.CG2FirstName & ";"
End If
Else
If IsNull(Me.CG2FirstName) = False Then
strRowSource = strRowSource & Me.CG2FirstName & ";"
End If
End If

If Len(strRowSource) > 0 Then
strRowSource = Left$(strRowSource, Len(strRowSource) - 1)
Me.Donated By.RowSourceType = "Value List"
Me.Donated By.RowSource = strRowSource
Else
Me.Donated By.RowSourceType = ""
Me.Donated By.RowSource = ""
End If

End Sub
--
Sue Compelling


:

Slight typo there: the line should have been

strRowSource = Left$(strRowSource, Len(strRowSource)-1)

(it's taking the superfluous final semicolon off the string)

See whether making that correction solves the problem.

If not, paste the entire code you've got in the Event.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



message
Hi Doug - yep, ['patience of a Saint Doug']. I copied the code (closed
the
bracket on the Left$(strR - line) though ACCESS came back with the
following
error:

'The expression OnCurrent you entered as the event property setting
produced
the following error: A problem occurred while MSFT A ... was
communicating
with the OLE server or ActiveX Control
* The expression may not result in the name of a macro, the name of a
user-defined function or [Event Procedure]
* or there may have been an error evaluationing the function, event or
macro.

What now?
--
Sue Compelling


:

Ah, I'm with you now.

Try using something like the following in the form's OnCurrent event:

Dim strRowSource As String

If IsNull(Me.CG1FirstName) = False Then
strRowSource = strRowSource & Me.CG1FirstName & ";"
If IsNull(Me.CG2FirstName) = False Then
strRowSource = strRowSource & Me.CG2FirstName & ";"
strRowSource = strRowSource & Me.CG1FirstName & " and " &
Me.CG2FirstName & ";"
End If
Else
If IsNull(Me.CG2FirstName) = False Then
strRowSource = strRowSource & Me.CG2FirstName & ";"
End If
End If

If Len(strRowSource) > 0 Then
strRowSource = Left$(strR
Me.MyComboBox.RowSourceType = "Value List"
Me.MyComboBox.RowSource = strRowSource
Else
Me.MyComboBox.RowSourceType = ""
Me.MyComboBox.RowSource = ""
End If


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



message
Hi Douglas

My Student form [Tab Control Page] has a number of fields, capturing
student
details and their caregivers details. In this form it includes the
fields
[CG1FirstName],[CG1LastName], [CG2FirstName], [CG1LastName].

Then on the next Tab Conrol Page - I have included a subform for
donations.
This subform has the following fields:

StudentID
DonationID
Funds [ComboBox to select]
Amount
Date
DonatedBy

The DonatedBy is important, as donations can be received as a couple
or
from
an individual [in the case of separated parents]. What I wanted to
do
was
to
present the 'choices' of these 3 scenarios in a picklist of some
sort -
to
stop the users from having to type the details [as I will be
creating
reports
on top donors, recent donors etc - and wanted the spelling obviously
to
be
consistent]. So..... how do I get these 'choices' of concatenated
fields
into the combobox.

Really appreciate your help.
--
Sue Compelling


:

I'd assumed that [CG1FirstName] and [CG2FirstName] were the names
of
two
controls on your form: it sounds as though that might not be the
case.
You'd need to post more details about both your tables and forms in
order
for someone to give you a comprehensive answer.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



message
Hi Doug ...

Thanks for being so prompt. My problem was that I didn't know
HOW
to
get
these 'choices' of names INTO the combobox dropdown .... Are you
saying
that
I simply write these expressions in the Row Source of the
ComboBox,
and
that
the Row Source Type is 'Value List'? I'm a newbie and can only
'do
code'
if
it's supplied.

Thanks -
--
Sue Compelling


:

What you show looks as though it should handle the cases Couple,
or
Caregiver 1 only. What's your specific problem? Are you
uncertain
how
to
extend it to handle Caregiver 2 only, or is there some error
that
you're
not
reporting?

To handle Caregiver 2 only, try:

=IIf(IsNull([CG1FirstName]), Nz([CG2FirstName],""),
IIf(IsNull([CG2FirstName]),[CG1FirstName],[CG1FirstName] & "
and
" &
[CG2FirstName]))


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



in
message
Hi, context first ....

DB is a Student DB.

Form has student details, including Caregiver 1 & Caregiver 2
Details.

I have a subform for donations that allows users to select
[from
a
Combo]
where the donation is to be spent. The other fields are
Amount
and
Date.
The last field is 'Donated By'. This is where I need help.
The
Donation
can
be made either as a:

Couple, or Caregiver 1 only or Caregiver 2 only.

I want the user to be able to select one of these three
options
from

 

[Douglas J. Steele]

Sorry: I only typically get to this particular newsgroup once a day.

Looking more closely at your code, it looks as though you've named your
combobox Donated By, with a space in there. That means that Me.Donated
By.RowSourceType and Me.Donated By.RowSource won't work: the embedded space
is throwing them off.

Either rename your combobox to remove the space (my recommendation), or try
Me.Donated_By.RowSourceType

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi Doug - I also meant to say, I thought you'd given up on me [as I hadn't
heard anything for a day]. I wasn't quite sure what the protocol is in
these
situations, so made another post. Please don't leave me - I want to crack
this.
--
Sue Compelling

I took the event procedure out of the OnCurrent of the subform (and put
in in
the OnEnter of the ComboBox) .... is this a bad thing? Is this where the
mistake is? When had I tried this same code on the OnCurrent of the
subform
though it came back with the error I previously described.
--
Sue Compelling

Given that you've named the routine Donated_By_Enter(), what are you
putting
in the OnCurrent events?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



message
Oh Doug - grrrrrrrrrrr

It still didn't work - I took the event procedure OFF the Donations
SubForm
'OnCurrent' and put it on the Donated By ComboBox 'On Enter', though
it
comes
up with the same error as before. This is the code exactly as it's
in the
event procedure. I don't want to give up [self taught and all] ....
though
I'm willing to persevere if you are... [Is there additional info you
need
from me that would help?] - does this forum allow screen shots to be
attached?

Private Sub Donated_By_Enter()

Dim strRowSource As String

If IsNull(Me.CG1FirstName) = False Then
strRowSource = strRowSource & Me.CG1FirstName & ";"
If IsNull(Me.CG2FirstName) = False Then
strRowSource = strRowSource & Me.CG2FirstName & ";"
strRowSource = strRowSource & Me.CG1FirstName & " and " &
Me.CG2FirstName & ";"
End If
Else
If IsNull(Me.CG2FirstName) = False Then
strRowSource = strRowSource & Me.CG2FirstName & ";"
End If
End If

If Len(strRowSource) > 0 Then
strRowSource = Left$(strRowSource, Len(strRowSource) - 1)
Me.Donated By.RowSourceType = "Value List"
Me.Donated By.RowSource = strRowSource
Else
Me.Donated By.RowSourceType = ""
Me.Donated By.RowSource = ""
End If

End Sub
--
Sue Compelling


:

Slight typo there: the line should have been

strRowSource = Left$(strRowSource, Len(strRowSource)-1)

(it's taking the superfluous final semicolon off the string)

See whether making that correction solves the problem.

If not, paste the entire code you've got in the Event.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



message
Hi Doug - yep, ['patience of a Saint Doug']. I copied the code
(closed
the
bracket on the Left$(strR - line) though ACCESS came back with the
following
error:

'The expression OnCurrent you entered as the event property
setting
produced
the following error: A problem occurred while MSFT A ... was
communicating
with the OLE server or ActiveX Control
* The expression may not result in the name of a macro, the name
of a
user-defined function or [Event Procedure]
* or there may have been an error evaluationing the function,
event or
macro.

What now?
--
Sue Compelling


:

Ah, I'm with you now.

Try using something like the following in the form's OnCurrent
event:

Dim strRowSource As String

If IsNull(Me.CG1FirstName) = False Then
strRowSource = strRowSource & Me.CG1FirstName & ";"
If IsNull(Me.CG2FirstName) = False Then
strRowSource = strRowSource & Me.CG2FirstName & ";"
strRowSource = strRowSource & Me.CG1FirstName & " and "
&
Me.CG2FirstName & ";"
End If
Else
If IsNull(Me.CG2FirstName) = False Then
strRowSource = strRowSource & Me.CG2FirstName & ";"
End If
End If

If Len(strRowSource) > 0 Then
strRowSource = Left$(strR
Me.MyComboBox.RowSourceType = "Value List"
Me.MyComboBox.RowSource = strRowSource
Else
Me.MyComboBox.RowSourceType = ""
Me.MyComboBox.RowSource = ""
End If


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



in
message
Hi Douglas

My Student form [Tab Control Page] has a number of fields,
capturing
student
details and their caregivers details. In this form it includes
the
fields
[CG1FirstName],[CG1LastName], [CG2FirstName], [CG1LastName].

Then on the next Tab Conrol Page - I have included a subform
for
donations.
This subform has the following fields:

StudentID
DonationID
Funds [ComboBox to select]
Amount
Date
DonatedBy

The DonatedBy is important, as donations can be received as a
couple
or
from
an individual [in the case of separated parents]. What I
wanted to
do
was
to
present the 'choices' of these 3 scenarios in a picklist of
some
sort -
to
stop the users from having to type the details [as I will be
creating
reports
on top donors, recent donors etc - and wanted the spelling
obviously
to
be
consistent]. So..... how do I get these 'choices' of
concatenated
fields
into the combobox.

Really appreciate your help.
--
Sue Compelling


:

I'd assumed that [CG1FirstName] and [CG2FirstName] were the
names
of
two
controls on your form: it sounds as though that might not be
the
case.
You'd need to post more details about both your tables and
forms in
order
for someone to give you a comprehensive answer.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



"Sue Compelling" <[email protected]>
wrote in
message
Hi Doug ...

Thanks for being so prompt. My problem was that I didn't
know
HOW
to
get
these 'choices' of names INTO the combobox dropdown .... Are
you
saying
that
I simply write these expressions in the Row Source of the
ComboBox,
and
that
the Row Source Type is 'Value List'? I'm a newbie and can
only
'do
code'
if
it's supplied.

Thanks -
--
Sue Compelling


:

What you show looks as though it should handle the cases
Couple,
or
Caregiver 1 only. What's your specific problem? Are you
uncertain
how
to
extend it to handle Caregiver 2 only, or is there some
error
that
you're
not
reporting?

To handle Caregiver 2 only, try:

=IIf(IsNull([CG1FirstName]), Nz([CG2FirstName],""),
IIf(IsNull([CG2FirstName]),[CG1FirstName],[CG1FirstName]
& "
and
" &
[CG2FirstName]))


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



"Sue Compelling" <[email protected]>
wrote
in
message
Hi, context first ....

DB is a Student DB.

Form has student details, including Caregiver 1 &
Caregiver 2
Details.

I have a subform for donations that allows users to
select
[from
a
Combo]
where the donation is to be spent. The other fields are
Amount
and
Date.
The last field is 'Donated By'. This is where I need
help.
The
Donation
can
be made either as a:

Couple, or Caregiver 1 only or Caregiver 2 only.

I want the user to be able to select one of these three
options
from

 

[i didnt say that i dont think so]

SEAN RICHARD MALLET,

18 Chalk Road,

Walpole st-Peter,

Wisbeech,

Camps,

Pe14 7pn.

 

[Guest]

Hi Doug

* Changed my ComboBox to DonatedBy [as suggested]
* Put the event procedure into the Subform OnCurrent - as follows:


Private Sub Form_Current()

Dim strRowSource As String

If IsNull(Me.CG1FirstName) = False Then
strRowSource = strRowSource & Me.CG1FirstName & ";"
If IsNull(Me.CG2FirstName) = False Then
strRowSource = strRowSource & Me.CG2FirstName & ";"
strRowSource = strRowSource & Me.CG1FirstName & " and " &
Me.CG2FirstName & ";"
End If
Else
If IsNull(Me.CG2FirstName) = False Then
strRowSource = strRowSource & Me.CG2FirstName & ";"
End If
End If

If Len(strRowSource) > 0 Then
strRowSource = Left$(strRowSource, Len(strRowSource) - 1)
Me.DonatedBy.RowSourceType = "Value List"
Me.DonatedBy.RowSource = strRowSource
Else
Me.DonatedBy.RowSourceType = ""
Me.DonatedBy.RowSource = ""
End If

Though is till comes back with the error:


'The expression OnCurrent you entered as the event property setting produced
the following error: A problem occurred while MSFT A ... was communicating
with the OLE server or ActiveX Control
* The expression may not result in the name of a macro, the name of a
user-defined function or [Event Procedure]
* or there may have been an error evaluationing the function, event or macro.
--
Sue Compelling

Sorry: I only typically get to this particular newsgroup once a day.

Looking more closely at your code, it looks as though you've named your
combobox Donated By, with a space in there. That means that Me.Donated
By.RowSourceType and Me.Donated By.RowSource won't work: the embedded space
is throwing them off.

Either rename your combobox to remove the space (my recommendation), or try
Me.Donated_By.RowSourceType

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi Doug - I also meant to say, I thought you'd given up on me [as I hadn't
heard anything for a day]. I wasn't quite sure what the protocol is in
these
situations, so made another post. Please don't leave me - I want to crack
this.
--
Sue Compelling

I took the event procedure out of the OnCurrent of the subform (and put
in in
the OnEnter of the ComboBox) .... is this a bad thing? Is this where the
mistake is? When had I tried this same code on the OnCurrent of the
subform
though it came back with the error I previously described.
--
Sue Compelling


:

Given that you've named the routine Donated_By_Enter(), what are you
putting
in the OnCurrent events?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



message
Oh Doug - grrrrrrrrrrr

It still didn't work - I took the event procedure OFF the Donations
SubForm
'OnCurrent' and put it on the Donated By ComboBox 'On Enter', though
it
comes
up with the same error as before. This is the code exactly as it's
in the
event procedure. I don't want to give up [self taught and all] ....
though
I'm willing to persevere if you are... [Is there additional info you
need
from me that would help?] - does this forum allow screen shots to be
attached?

Private Sub Donated_By_Enter()

Dim strRowSource As String

If IsNull(Me.CG1FirstName) = False Then
strRowSource = strRowSource & Me.CG1FirstName & ";"
If IsNull(Me.CG2FirstName) = False Then
strRowSource = strRowSource & Me.CG2FirstName & ";"
strRowSource = strRowSource & Me.CG1FirstName & " and " &
Me.CG2FirstName & ";"
End If
Else
If IsNull(Me.CG2FirstName) = False Then
strRowSource = strRowSource & Me.CG2FirstName & ";"
End If
End If

If Len(strRowSource) > 0 Then
strRowSource = Left$(strRowSource, Len(strRowSource) - 1)
Me.Donated By.RowSourceType = "Value List"
Me.Donated By.RowSource = strRowSource
Else
Me.Donated By.RowSourceType = ""
Me.Donated By.RowSource = ""
End If

End Sub
--
Sue Compelling


:

Slight typo there: the line should have been

strRowSource = Left$(strRowSource, Len(strRowSource)-1)

(it's taking the superfluous final semicolon off the string)

See whether making that correction solves the problem.

If not, paste the entire code you've got in the Event.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



message
Hi Doug - yep, ['patience of a Saint Doug']. I copied the code
(closed
the
bracket on the Left$(strR - line) though ACCESS came back with the
following
error:

'The expression OnCurrent you entered as the event property
setting
produced
the following error: A problem occurred while MSFT A ... was
communicating
with the OLE server or ActiveX Control
* The expression may not result in the name of a macro, the name
of a
user-defined function or [Event Procedure]
* or there may have been an error evaluationing the function,
event or
macro.

What now?
--
Sue Compelling


:

Ah, I'm with you now.

Try using something like the following in the form's OnCurrent
event:

Dim strRowSource As String

If IsNull(Me.CG1FirstName) = False Then
strRowSource = strRowSource & Me.CG1FirstName & ";"
If IsNull(Me.CG2FirstName) = False Then
strRowSource = strRowSource & Me.CG2FirstName & ";"
strRowSource = strRowSource & Me.CG1FirstName & " and "
&
Me.CG2FirstName & ";"
End If
Else
If IsNull(Me.CG2FirstName) = False Then
strRowSource = strRowSource & Me.CG2FirstName & ";"
End If
End If

If Len(strRowSource) > 0 Then
strRowSource = Left$(strR
Me.MyComboBox.RowSourceType = "Value List"
Me.MyComboBox.RowSource = strRowSource
Else
Me.MyComboBox.RowSourceType = ""
Me.MyComboBox.RowSource = ""
End If


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



in
message
Hi Douglas

My Student form [Tab Control Page] has a number of fields,
capturing
student
details and their caregivers details. In this form it includes
the
fields
[CG1FirstName],[CG1LastName], [CG2FirstName], [CG1LastName].

Then on the next Tab Conrol Page - I have included a subform
for
donations.
This subform has the following fields:

StudentID
DonationID
Funds [ComboBox to select]
Amount
Date
DonatedBy

The DonatedBy is important, as donations can be received as a
couple
or
from
an individual [in the case of separated parents]. What I
wanted to
do
was
to
present the 'choices' of these 3 scenarios in a picklist of
some
sort -
to
stop the users from having to type the details [as I will be
creating
reports
on top donors, recent donors etc - and wanted the spelling
obviously
to
be
consistent]. So..... how do I get these 'choices' of
concatenated
fields
into the combobox.

Really appreciate your help.
--
Sue Compelling


:

I'd assumed that [CG1FirstName] and [CG2FirstName] were the
names
of
two
controls on your form: it sounds as though that might not be
the
case.
You'd need to post more details about both your tables and
forms in
order
for someone to give you a comprehensive answer.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



"Sue Compelling" <[email protected]>
wrote in
message
Hi Doug ...

Thanks for being so prompt. My problem was that I didn't
know
HOW
to
get
these 'choices' of names INTO the combobox dropdown .... Are
you
saying
that
I simply write these expressions in the Row Source of the
ComboBox,
and
that
the Row Source Type is 'Value List'? I'm a newbie and can
only
'do
code'
if
it's supplied.

Thanks -
--
Sue Compelling


"Douglas J. Steele" wrote:

 

[Douglas J. Steele]

I'm starting to run out of ideas!

Are the controls on the form named the same as the fields in the
recordsource? Try renaming the controls, to something like txtCG1FirstName,
txtCG1LastName, txtCG2FirstName and CG1LastName, and change the code to use
Me.txtCG1FirstName and so on.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi Doug

* Changed my ComboBox to DonatedBy [as suggested]
* Put the event procedure into the Subform OnCurrent - as follows:


Private Sub Form_Current()

Dim strRowSource As String

If IsNull(Me.CG1FirstName) = False Then
strRowSource = strRowSource & Me.CG1FirstName & ";"
If IsNull(Me.CG2FirstName) = False Then
strRowSource = strRowSource & Me.CG2FirstName & ";"
strRowSource = strRowSource & Me.CG1FirstName & " and " &
Me.CG2FirstName & ";"
End If
Else
If IsNull(Me.CG2FirstName) = False Then
strRowSource = strRowSource & Me.CG2FirstName & ";"
End If
End If

If Len(strRowSource) > 0 Then
strRowSource = Left$(strRowSource, Len(strRowSource) - 1)
Me.DonatedBy.RowSourceType = "Value List"
Me.DonatedBy.RowSource = strRowSource
Else
Me.DonatedBy.RowSourceType = ""
Me.DonatedBy.RowSource = ""
End If

Though is till comes back with the error:


'The expression OnCurrent you entered as the event property setting
produced
the following error: A problem occurred while MSFT A ... was communicating
with the OLE server or ActiveX Control
* The expression may not result in the name of a macro, the name of a
user-defined function or [Event Procedure]
* or there may have been an error evaluationing the function, event or
macro.
--
Sue Compelling

Sorry: I only typically get to this particular newsgroup once a day.

Looking more closely at your code, it looks as though you've named your
combobox Donated By, with a space in there. That means that Me.Donated
By.RowSourceType and Me.Donated By.RowSource won't work: the embedded
space
is throwing them off.

Either rename your combobox to remove the space (my recommendation), or
try
Me.Donated_By.RowSourceType

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



message

Hi Doug - I also meant to say, I thought you'd given up on me [as I
hadn't
heard anything for a day]. I wasn't quite sure what the protocol is in
these
situations, so made another post. Please don't leave me - I want to
crack
this.
--
Sue Compelling


:

I took the event procedure out of the OnCurrent of the subform (and
put
in in
the OnEnter of the ComboBox) .... is this a bad thing? Is this where
the
mistake is? When had I tried this same code on the OnCurrent of the
subform
though it came back with the error I previously described.
--
Sue Compelling


:

Given that you've named the routine Donated_By_Enter(), what are you
putting
in the OnCurrent events?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



message
Oh Doug - grrrrrrrrrrr

It still didn't work - I took the event procedure OFF the
Donations
SubForm
'OnCurrent' and put it on the Donated By ComboBox 'On Enter',
though
it
comes
up with the same error as before. This is the code exactly as
it's
in the
event procedure. I don't want to give up [self taught and all]
....
though
I'm willing to persevere if you are... [Is there additional info
you
need
from me that would help?] - does this forum allow screen shots to
be
attached?

Private Sub Donated_By_Enter()

Dim strRowSource As String

If IsNull(Me.CG1FirstName) = False Then
strRowSource = strRowSource & Me.CG1FirstName & ";"
If IsNull(Me.CG2FirstName) = False Then
strRowSource = strRowSource & Me.CG2FirstName & ";"
strRowSource = strRowSource & Me.CG1FirstName & " and " &
Me.CG2FirstName & ";"
End If
Else
If IsNull(Me.CG2FirstName) = False Then
strRowSource = strRowSource & Me.CG2FirstName & ";"
End If
End If

If Len(strRowSource) > 0 Then
strRowSource = Left$(strRowSource, Len(strRowSource) - 1)
Me.Donated By.RowSourceType = "Value List"
Me.Donated By.RowSource = strRowSource
Else
Me.Donated By.RowSourceType = ""
Me.Donated By.RowSource = ""
End If

End Sub
--
Sue Compelling


:

Slight typo there: the line should have been

strRowSource = Left$(strRowSource, Len(strRowSource)-1)

(it's taking the superfluous final semicolon off the string)

See whether making that correction solves the problem.

If not, paste the entire code you've got in the Event.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



in
message
Hi Doug - yep, ['patience of a Saint Doug']. I copied the code
(closed
the
bracket on the Left$(strR - line) though ACCESS came back with
the
following
error:

'The expression OnCurrent you entered as the event property
setting
produced
the following error: A problem occurred while MSFT A ... was
communicating
with the OLE server or ActiveX Control
* The expression may not result in the name of a macro, the
name
of a
user-defined function or [Event Procedure]
* or there may have been an error evaluationing the function,
event or
macro.

What now?
--
Sue Compelling


:

Ah, I'm with you now.

Try using something like the following in the form's OnCurrent
event:

Dim strRowSource As String

If IsNull(Me.CG1FirstName) = False Then
strRowSource = strRowSource & Me.CG1FirstName & ";"
If IsNull(Me.CG2FirstName) = False Then
strRowSource = strRowSource & Me.CG2FirstName & ";"
strRowSource = strRowSource & Me.CG1FirstName & " and
"
&
Me.CG2FirstName & ";"
End If
Else
If IsNull(Me.CG2FirstName) = False Then
strRowSource = strRowSource & Me.CG2FirstName & ";"
End If
End If

If Len(strRowSource) > 0 Then
strRowSource = Left$(strR
Me.MyComboBox.RowSourceType = "Value List"
Me.MyComboBox.RowSource = strRowSource
Else
Me.MyComboBox.RowSourceType = ""
Me.MyComboBox.RowSource = ""
End If


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



"Sue Compelling" <[email protected]>
wrote
in
message
Hi Douglas

My Student form [Tab Control Page] has a number of fields,
capturing
student
details and their caregivers details. In this form it
includes
the
fields
[CG1FirstName],[CG1LastName], [CG2FirstName], [CG1LastName].

Then on the next Tab Conrol Page - I have included a subform
for
donations.
This subform has the following fields:

StudentID
DonationID
Funds [ComboBox to select]
Amount
Date
DonatedBy

The DonatedBy is important, as donations can be received as
a
couple
or
from
an individual [in the case of separated parents]. What I
wanted to
do
was
to
present the 'choices' of these 3 scenarios in a picklist of
some
sort -
to
stop the users from having to type the details [as I will be
creating
reports
on top donors, recent donors etc - and wanted the spelling
obviously
to
be
consistent]. So..... how do I get these 'choices' of
concatenated
fields
into the combobox.

Really appreciate your help.
--
Sue Compelling


:

I'd assumed that [CG1FirstName] and [CG2FirstName] were the
names
of
two
controls on your form: it sounds as though that might not
be
the
case.
You'd need to post more details about both your tables and
forms in
order
for someone to give you a comprehensive answer.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



"Sue Compelling" <[email protected]>
wrote in
message
Hi Doug ...

Thanks for being so prompt. My problem was that I didn't
know
HOW
to
get
these 'choices' of names INTO the combobox dropdown ....
Are
you
saying
that
I simply write these expressions in the Row Source of the
ComboBox,
and
that
the Row Source Type is 'Value List'? I'm a newbie and
can
only
'do
code'
if
it's supplied.

Thanks -
--
Sue Compelling


"Douglas J. Steele" wrote:

 

[Guest]

Alas - tried the changes and no joy. Thanks for your time Doug, I'm afraid
I'm giving up. I will just have to make sure the users type the Donors names
exactly as presented in the Caregiver records, on pain of death.
--
Sue Compelling

I'm starting to run out of ideas!

Are the controls on the form named the same as the fields in the
recordsource? Try renaming the controls, to something like txtCG1FirstName,
txtCG1LastName, txtCG2FirstName and CG1LastName, and change the code to use
Me.txtCG1FirstName and so on.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi Doug

* Changed my ComboBox to DonatedBy [as suggested]
* Put the event procedure into the Subform OnCurrent - as follows:


Private Sub Form_Current()

Dim strRowSource As String

If IsNull(Me.CG1FirstName) = False Then
strRowSource = strRowSource & Me.CG1FirstName & ";"
If IsNull(Me.CG2FirstName) = False Then
strRowSource = strRowSource & Me.CG2FirstName & ";"
strRowSource = strRowSource & Me.CG1FirstName & " and " &
Me.CG2FirstName & ";"
End If
Else
If IsNull(Me.CG2FirstName) = False Then
strRowSource = strRowSource & Me.CG2FirstName & ";"
End If
End If

If Len(strRowSource) > 0 Then
strRowSource = Left$(strRowSource, Len(strRowSource) - 1)
Me.DonatedBy.RowSourceType = "Value List"
Me.DonatedBy.RowSource = strRowSource
Else
Me.DonatedBy.RowSourceType = ""
Me.DonatedBy.RowSource = ""
End If

Though is till comes back with the error:


'The expression OnCurrent you entered as the event property setting
produced
the following error: A problem occurred while MSFT A ... was communicating
with the OLE server or ActiveX Control
* The expression may not result in the name of a macro, the name of a
user-defined function or [Event Procedure]
* or there may have been an error evaluationing the function, event or
macro.
--
Sue Compelling

Sorry: I only typically get to this particular newsgroup once a day.

Looking more closely at your code, it looks as though you've named your
combobox Donated By, with a space in there. That means that Me.Donated
By.RowSourceType and Me.Donated By.RowSource won't work: the embedded
space
is throwing them off.

Either rename your combobox to remove the space (my recommendation), or
try
Me.Donated_By.RowSourceType

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



message
Hi Doug - I also meant to say, I thought you'd given up on me [as I
hadn't
heard anything for a day]. I wasn't quite sure what the protocol is in
these
situations, so made another post. Please don't leave me - I want to
crack
this.
--
Sue Compelling


:

I took the event procedure out of the OnCurrent of the subform (and
put
in in
the OnEnter of the ComboBox) .... is this a bad thing? Is this where
the
mistake is? When had I tried this same code on the OnCurrent of the
subform
though it came back with the error I previously described.
--
Sue Compelling


:

Given that you've named the routine Donated_By_Enter(), what are you
putting
in the OnCurrent events?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



message
Oh Doug - grrrrrrrrrrr

It still didn't work - I took the event procedure OFF the
Donations
SubForm
'OnCurrent' and put it on the Donated By ComboBox 'On Enter',
though
it
comes
up with the same error as before. This is the code exactly as
it's
in the
event procedure. I don't want to give up [self taught and all]
....
though
I'm willing to persevere if you are... [Is there additional info
you
need
from me that would help?] - does this forum allow screen shots to
be
attached?

Private Sub Donated_By_Enter()

Dim strRowSource As String

If IsNull(Me.CG1FirstName) = False Then
strRowSource = strRowSource & Me.CG1FirstName & ";"
If IsNull(Me.CG2FirstName) = False Then
strRowSource = strRowSource & Me.CG2FirstName & ";"
strRowSource = strRowSource & Me.CG1FirstName & " and " &
Me.CG2FirstName & ";"
End If
Else
If IsNull(Me.CG2FirstName) = False Then
strRowSource = strRowSource & Me.CG2FirstName & ";"
End If
End If

If Len(strRowSource) > 0 Then
strRowSource = Left$(strRowSource, Len(strRowSource) - 1)
Me.Donated By.RowSourceType = "Value List"
Me.Donated By.RowSource = strRowSource
Else
Me.Donated By.RowSourceType = ""
Me.Donated By.RowSource = ""
End If

End Sub
--
Sue Compelling


:

Slight typo there: the line should have been

strRowSource = Left$(strRowSource, Len(strRowSource)-1)

(it's taking the superfluous final semicolon off the string)

See whether making that correction solves the problem.

If not, paste the entire code you've got in the Event.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



in
message
Hi Doug - yep, ['patience of a Saint Doug']. I copied the code
(closed
the
bracket on the Left$(strR - line) though ACCESS came back with
the
following
error:

'The expression OnCurrent you entered as the event property
setting
produced
the following error: A problem occurred while MSFT A ... was
communicating
with the OLE server or ActiveX Control
* The expression may not result in the name of a macro, the
name
of a
user-defined function or [Event Procedure]
* or there may have been an error evaluationing the function,
event or
macro.

What now?
--
Sue Compelling


:

Ah, I'm with you now.

Try using something like the following in the form's OnCurrent
event:

Dim strRowSource As String

If IsNull(Me.CG1FirstName) = False Then
strRowSource = strRowSource & Me.CG1FirstName & ";"
If IsNull(Me.CG2FirstName) = False Then
strRowSource = strRowSource & Me.CG2FirstName & ";"
strRowSource = strRowSource & Me.CG1FirstName & " and
"
&
Me.CG2FirstName & ";"
End If
Else
If IsNull(Me.CG2FirstName) = False Then
strRowSource = strRowSource & Me.CG2FirstName & ";"
End If
End If

If Len(strRowSource) > 0 Then
strRowSource = Left$(strR
Me.MyComboBox.RowSourceType = "Value List"
Me.MyComboBox.RowSource = strRowSource
Else
Me.MyComboBox.RowSourceType = ""
Me.MyComboBox.RowSource = ""
End If


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



"Sue Compelling" <[email protected]>
wrote
in
message
Hi Douglas

My Student form [Tab Control Page] has a number of fields,
capturing
student
details and their caregivers details. In this form it
includes
the
fields
[CG1FirstName],[CG1LastName], [CG2FirstName], [CG1LastName].

Then on the next Tab Conrol Page - I have included a subform
for
donations.
This subform has the following fields:

StudentID
DonationID
Funds [ComboBox to select]
Amount
Date
DonatedBy

 

[Douglas J Steele]

Now you've got me curious!

Is it possible for you to send me a cut-down version of your application?
All I need is the one form, and some sample data. No need to any other
tables except those directly relevant to the form in question.

Zip it up, please, and send it to djsteele<at>canada<dot>com

I'm going to be away for the long weekend that's coming up, so I may not be
able to get back to you until next week.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Alas - tried the changes and no joy. Thanks for your time Doug, I'm afraid
I'm giving up. I will just have to make sure the users type the Donors names
exactly as presented in the Caregiver records, on pain of death.
--
Sue Compelling

I'm starting to run out of ideas!

Are the controls on the form named the same as the fields in the
recordsource? Try renaming the controls, to something like txtCG1FirstName,
txtCG1LastName, txtCG2FirstName and CG1LastName, and change the code to use
Me.txtCG1FirstName and so on.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi Doug

* Changed my ComboBox to DonatedBy [as suggested]
* Put the event procedure into the Subform OnCurrent - as follows:


Private Sub Form_Current()

Dim strRowSource As String

If IsNull(Me.CG1FirstName) = False Then
strRowSource = strRowSource & Me.CG1FirstName & ";"
If IsNull(Me.CG2FirstName) = False Then
strRowSource = strRowSource & Me.CG2FirstName & ";"
strRowSource = strRowSource & Me.CG1FirstName & " and " &
Me.CG2FirstName & ";"
End If
Else
If IsNull(Me.CG2FirstName) = False Then
strRowSource = strRowSource & Me.CG2FirstName & ";"
End If
End If

If Len(strRowSource) > 0 Then
strRowSource = Left$(strRowSource, Len(strRowSource) - 1)
Me.DonatedBy.RowSourceType = "Value List"
Me.DonatedBy.RowSource = strRowSource
Else
Me.DonatedBy.RowSourceType = ""
Me.DonatedBy.RowSource = ""
End If

Though is till comes back with the error:


'The expression OnCurrent you entered as the event property setting
produced
the following error: A problem occurred while MSFT A ... was communicating
with the OLE server or ActiveX Control
* The expression may not result in the name of a macro, the name of a
user-defined function or [Event Procedure]
* or there may have been an error evaluationing the function, event or
macro.
--
Sue Compelling


:

Sorry: I only typically get to this particular newsgroup once a day.

Looking more closely at your code, it looks as though you've named your
combobox Donated By, with a space in there. That means that Me.Donated
By.RowSourceType and Me.Donated By.RowSource won't work: the embedded
space
is throwing them off.

Either rename your combobox to remove the space (my recommendation), or
try
Me.Donated_By.RowSourceType

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



message
Hi Doug - I also meant to say, I thought you'd given up on me [as I
hadn't
heard anything for a day]. I wasn't quite sure what the protocol is in
these
situations, so made another post. Please don't leave me - I want to
crack
this.
--
Sue Compelling


:

I took the event procedure out of the OnCurrent of the subform (and
put
in in
the OnEnter of the ComboBox) .... is this a bad thing? Is this where
the
mistake is? When had I tried this same code on the OnCurrent of the
subform
though it came back with the error I previously described.
--
Sue Compelling


:

Given that you've named the routine Donated_By_Enter(), what are you
putting
in the OnCurrent events?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



message
Oh Doug - grrrrrrrrrrr

It still didn't work - I took the event procedure OFF the
Donations
SubForm
'OnCurrent' and put it on the Donated By ComboBox 'On Enter',
though
it
comes
up with the same error as before. This is the code exactly as
it's
in the
event procedure. I don't want to give up [self taught and all]
....
though
I'm willing to persevere if you are... [Is there additional info
you
need
from me that would help?] - does this forum allow screen shots to
be
attached?

Private Sub Donated_By_Enter()

Dim strRowSource As String

If IsNull(Me.CG1FirstName) = False Then
strRowSource = strRowSource & Me.CG1FirstName & ";"
If IsNull(Me.CG2FirstName) = False Then
strRowSource = strRowSource & Me.CG2FirstName & ";"
strRowSource = strRowSource & Me.CG1FirstName & " and " &
Me.CG2FirstName & ";"
End If
Else
If IsNull(Me.CG2FirstName) = False Then
strRowSource = strRowSource & Me.CG2FirstName & ";"
End If
End If

If Len(strRowSource) > 0 Then
strRowSource = Left$(strRowSource, Len(strRowSource) - 1)
Me.Donated By.RowSourceType = "Value List"
Me.Donated By.RowSource = strRowSource
Else
Me.Donated By.RowSourceType = ""
Me.Donated By.RowSource = ""
End If

End Sub
--
Sue Compelling


:

Slight typo there: the line should have been

strRowSource = Left$(strRowSource, Len(strRowSource)-1)

(it's taking the superfluous final semicolon off the string)

See whether making that correction solves the problem.

If not, paste the entire code you've got in the Event.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



in
message
Hi Doug - yep, ['patience of a Saint Doug']. I copied the code
(closed
the
bracket on the Left$(strR - line) though ACCESS came back with
the
following
error:

'The expression OnCurrent you entered as the event property
setting
produced
the following error: A problem occurred while MSFT A ... was
communicating
with the OLE server or ActiveX Control
* The expression may not result in the name of a macro, the
name
of a
user-defined function or [Event Procedure]
* or there may have been an error evaluationing the function,
event or
macro.

What now?
--
Sue Compelling


:

Ah, I'm with you now.

Try using something like the following in the form's OnCurrent
event:

Dim strRowSource As String

If IsNull(Me.CG1FirstName) = False Then
strRowSource = strRowSource & Me.CG1FirstName & ";"
If IsNull(Me.CG2FirstName) = False Then
strRowSource = strRowSource & Me.CG2FirstName & ";"
strRowSource = strRowSource & Me.CG1FirstName & " and
"
&
Me.CG2FirstName & ";"
End If
Else
If IsNull(Me.CG2FirstName) = False Then
strRowSource = strRowSource & Me.CG2FirstName & ";"
End If
End If

If Len(strRowSource) > 0 Then
strRowSource = Left$(strR
Me.MyComboBox.RowSourceType = "Value List"
Me.MyComboBox.RowSource = strRowSource
Else
Me.MyComboBox.RowSourceType = ""
Me.MyComboBox.RowSource = ""
End If


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



"Sue Compelling" <[email protected]>
wrote
in
message
Hi Douglas

My Student form [Tab Control Page] has a number of fields,
capturing
student
details and their caregivers details. In this form it
includes
the
fields
[CG1FirstName],[CG1LastName], [CG2FirstName], [CG1LastName].

Then on the next Tab Conrol Page - I have included a subform
for
donations.
This subform has the following fields:

StudentID
DonationID
Funds [ComboBox to select]
Amount
Date
DonatedBy

 

[Tal]

Hi Sue and Doug,

I hate to say this Sue, but I tried the code and it worked like a charm. I
placed it in the LostFocus event of the combo box that selects the keyClient,
which I guess in your instance would be the "CG." I am including the code
here just in case there is a little subtlety that helps.

In any event, I wanted to thank you Doug. This issue had stumped me for a
long time.

Cheers,
Talia


Private Sub cboSelectDonor_LostFocus()
Dim strRowSource As String

If IsNull(Me.txtClientLastName) = False Then
strRowSource = strRowSource & Me.txtClientFirstName & " " &
Me.txtClientLastName & ";"
End If
If IsNull(Me.txtClientCompanyOnly) = False Then
strRowSource = strRowSource & Me.txtClientCompanyOnly & ";"
End If
If IsNull(Me.txtClientSpouseFirstName) = False And
IsNull(Me.txtClientSpouseLastName) Then
strRowSource = strRowSource & Me.txtClientSpouseFirstName & " " &
Me.txtClientLastName & ";"
strRowSource = strRowSource & Me.txtClientFirstName & " or " &
Me.txtClientSpouseFirstName & " " & Me.txtClientLastName & ";"
End If
If IsNull(Me.txtClientSpouseLastName) = False Then
strRowSource = strRowSource & Me.txtClientSpouseFirstName & " " &
Me.txtClientSpouseLastName & ";"
strRowSource = strRowSource & Me.txtClientFirstName & " " &
Me.txtClientLastName & " or " & Me.txtClientSpouseFirstName & " " &
Me.txtClientSpouseLastName & ";"
End If

If Len(strRowSource) > 0 Then
strRowSource = Left$(strRowSource, Len(strRowSource) - 1)
Me.cboReceiptTo.RowSourceType = "Value List"
Me.cboReceiptTo.RowSource = strRowSource
Me.cboReceiptTo.Requery
Else
Me.cboReceiptTo.RowSourceType = ""
Me.cboReceiptTo.RowSource = ""
End If
End Sub

Hi Doug

* Changed my ComboBox to DonatedBy [as suggested]
* Put the event procedure into the Subform OnCurrent - as follows:


Private Sub Form_Current()

Dim strRowSource As String

If IsNull(Me.CG1FirstName) = False Then
strRowSource = strRowSource & Me.CG1FirstName & ";"
If IsNull(Me.CG2FirstName) = False Then
strRowSource = strRowSource & Me.CG2FirstName & ";"
strRowSource = strRowSource & Me.CG1FirstName & " and " &
Me.CG2FirstName & ";"
End If
Else
If IsNull(Me.CG2FirstName) = False Then
strRowSource = strRowSource & Me.CG2FirstName & ";"
End If
End If

If Len(strRowSource) > 0 Then
strRowSource = Left$(strRowSource, Len(strRowSource) - 1)
Me.DonatedBy.RowSourceType = "Value List"
Me.DonatedBy.RowSource = strRowSource
Else
Me.DonatedBy.RowSourceType = ""
Me.DonatedBy.RowSource = ""
End If

Though is till comes back with the error:


'The expression OnCurrent you entered as the event property setting produced
the following error: A problem occurred while MSFT A ... was communicating
with the OLE server or ActiveX Control
* The expression may not result in the name of a macro, the name of a
user-defined function or [Event Procedure]
* or there may have been an error evaluationing the function, event or macro.
--
Sue Compelling

Sorry: I only typically get to this particular newsgroup once a day.

Looking more closely at your code, it looks as though you've named your
combobox Donated By, with a space in there. That means that Me.Donated
By.RowSourceType and Me.Donated By.RowSource won't work: the embedded space
is throwing them off.

Either rename your combobox to remove the space (my recommendation), or try
Me.Donated_By.RowSourceType

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi Doug - I also meant to say, I thought you'd given up on me [as I hadn't
heard anything for a day]. I wasn't quite sure what the protocol is in
these
situations, so made another post. Please don't leave me - I want to crack
this.
--
Sue Compelling


:

I took the event procedure out of the OnCurrent of the subform (and put
in in
the OnEnter of the ComboBox) .... is this a bad thing? Is this where the
mistake is? When had I tried this same code on the OnCurrent of the
subform
though it came back with the error I previously described.
--
Sue Compelling


:

Given that you've named the routine Donated_By_Enter(), what are you
putting
in the OnCurrent events?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



message
Oh Doug - grrrrrrrrrrr

It still didn't work - I took the event procedure OFF the Donations
SubForm
'OnCurrent' and put it on the Donated By ComboBox 'On Enter', though
it
comes
up with the same error as before. This is the code exactly as it's
in the
event procedure. I don't want to give up [self taught and all] ....
though
I'm willing to persevere if you are... [Is there additional info you
need
from me that would help?] - does this forum allow screen shots to be
attached?

Private Sub Donated_By_Enter()

Dim strRowSource As String

If IsNull(Me.CG1FirstName) = False Then
strRowSource = strRowSource & Me.CG1FirstName & ";"
If IsNull(Me.CG2FirstName) = False Then
strRowSource = strRowSource & Me.CG2FirstName & ";"
strRowSource = strRowSource & Me.CG1FirstName & " and " &
Me.CG2FirstName & ";"
End If
Else
If IsNull(Me.CG2FirstName) = False Then
strRowSource = strRowSource & Me.CG2FirstName & ";"
End If
End If

If Len(strRowSource) > 0 Then
strRowSource = Left$(strRowSource, Len(strRowSource) - 1)
Me.Donated By.RowSourceType = "Value List"
Me.Donated By.RowSource = strRowSource
Else
Me.Donated By.RowSourceType = ""
Me.Donated By.RowSource = ""
End If

End Sub
--
Sue Compelling


:

Slight typo there: the line should have been

strRowSource = Left$(strRowSource, Len(strRowSource)-1)

(it's taking the superfluous final semicolon off the string)

See whether making that correction solves the problem.

If not, paste the entire code you've got in the Event.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



message
Hi Doug - yep, ['patience of a Saint Doug']. I copied the code
(closed
the
bracket on the Left$(strR - line) though ACCESS came back with the
following
error:

'The expression OnCurrent you entered as the event property
setting
produced
the following error: A problem occurred while MSFT A ... was
communicating
with the OLE server or ActiveX Control
* The expression may not result in the name of a macro, the name
of a
user-defined function or [Event Procedure]
* or there may have been an error evaluationing the function,
event or
macro.

What now?
--
Sue Compelling


:

Ah, I'm with you now.

Try using something like the following in the form's OnCurrent
event:

Dim strRowSource As String

If IsNull(Me.CG1FirstName) = False Then
strRowSource = strRowSource & Me.CG1FirstName & ";"
If IsNull(Me.CG2FirstName) = False Then
strRowSource = strRowSource & Me.CG2FirstName & ";"
strRowSource = strRowSource & Me.CG1FirstName & " and "
&
Me.CG2FirstName & ";"
End If
Else
If IsNull(Me.CG2FirstName) = False Then
strRowSource = strRowSource & Me.CG2FirstName & ";"
End If
End If

If Len(strRowSource) > 0 Then
strRowSource = Left$(strR
Me.MyComboBox.RowSourceType = "Value List"
Me.MyComboBox.RowSource = strRowSource
Else
Me.MyComboBox.RowSourceType = ""
Me.MyComboBox.RowSource = ""
End If


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



in
message
Hi Douglas

My Student form [Tab Control Page] has a number of fields,
capturing
student
details and their caregivers details. In this form it includes
the
fields
[CG1FirstName],[CG1LastName], [CG2FirstName], [CG1LastName].

Then on the next Tab Conrol Page - I have included a subform
for
donations.
This subform has the following fields:

StudentID
DonationID
Funds [ComboBox to select]
Amount
Date
DonatedBy

The DonatedBy is important, as donations can be received as a
couple
or
from
an individual [in the case of separated parents]. What I
wanted to
do
was
to
present the 'choices' of these 3 scenarios in a picklist of
some
sort -
to
stop the users from having to type the details [as I will be
creating
reports
on top donors, recent donors etc - and wanted the spelling
obviously
to
be
consistent]. So..... how do I get these 'choices' of
concatenated
fields
into the combobox.

Really appreciate your help.
--
Sue Compelling


:

I'd assumed that [CG1FirstName] and [CG2FirstName] were the
names
of
two