Capturing the unbound column of a control

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

Guest

I have a control on a main form that captures two columns worth of data from
a secondary form. The data is collected in the form of a select query that
draws an ID number and a first and last name all from the secondary
(contacts) form. It displays the first and last name, but the ID field is
the one saved via a query to the table.

Now, I've added a button on the main form that needs those same first and
last names for interaction with my email program. It nees the actual names,
not the id.

In the code for my email program interaction, it's set up where the name to
be emailed is "hard-wired" into the code, and appears like this "some name"
with the quotes being part of the code as well.

In VBA, can I:
1. Change that "some name" to a string variable somehow so that the value is
dynamic based on information found in the form on which the button is
located, and

2. Somehow use that first and last name that appears in the unbound column
as the value of the string variable created in #1?

Thanks for any help.
CW
 
Thanks Alex for the reply.

Normally, that would work. In this case, though, I believe the value of the
control box is going to be the bound column of the query I typed into the
rowsource for the control, but what I need is the 'other' column that the
query produces (the one it actually displays in the control).

I guess you could be right and I'm doing something else wrong (not properly
replacing the "some name" with the varible), but I really think in this case
the control value won't work.

Thanks,
CW
 
Cheese_whiz said:
Thanks Alex for the reply.

Normally, that would work. In this case, though, I believe the value of the
control box is going to be the bound column of the query I typed into the
rowsource for the control, but what I need is the 'other' column that the
query produces (the one it actually displays in the control).
Click to expand...

Forms!FormName!ComboBoxName.Column(n)

Where n is the column position you want starting at zero.
 
This is my third time trying to reply to this thread.....I have NO idea
what's going on with my replies, but they are going somewhere.

Anyway, I've tried that column(n) property early in the process and I still
can't get it to work. I have to believe there's something wrong with the way
I"m trying to declare and use my variable. So here's what I'm doing (for the
third time):

Declaring my variable at the top of the procedure (somebutton_onclick):
Dim strMyRecipient As String

Then, either just below the Dim declaration or just above where I need to
use the variable (I've tried both ways), I add this:
strMyRecipient = [Forms]![MyFormName]![Mycontrolname].column(1)
'where column 1 is the second column

Then, here's the line of code where the variable needs to replace an
existing hard-coded name:
myAppt.Recipients.Add ("John Doe")

which I've changed to:
myAppt.Recipients.Add ("(strMyRecipient)")

I've also tried just ("strMyRecipient") and various other things.

What's wrong with the syntax and/or am I not setting up the variable right
in the first place?

Thanks,
CW
 
Try:

myAppt.Recipients.Add strMyRecipient

(in case strMyRecipient already has correct value)

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com


Cheese_whiz said:
This is my third time trying to reply to this thread.....I have NO idea
what's going on with my replies, but they are going somewhere.

Anyway, I've tried that column(n) property early in the process and I
still
can't get it to work. I have to believe there's something wrong with the
way
I"m trying to declare and use my variable. So here's what I'm doing (for
the
third time):

Declaring my variable at the top of the procedure (somebutton_onclick):
Dim strMyRecipient As String

Then, either just below the Dim declaration or just above where I need to
use the variable (I've tried both ways), I add this:
strMyRecipient = [Forms]![MyFormName]![Mycontrolname].column(1)
'where column 1 is the second column

Then, here's the line of code where the variable needs to replace an
existing hard-coded name:
myAppt.Recipients.Add ("John Doe")

which I've changed to:
myAppt.Recipients.Add ("(strMyRecipient)")

I've also tried just ("strMyRecipient") and various other things.

What's wrong with the syntax and/or am I not setting up the variable right
in the first place?

Thanks,
CW

Rick Brandt said:
Forms!FormName!ComboBoxName.Column(n)

Where n is the column position you want starting at zero.
Click to expand...
Click to expand...
 
Cheese_whiz said:
This is my third time trying to reply to this thread.....I have NO idea
what's going on with my replies, but they are going somewhere.

Anyway, I've tried that column(n) property early in the process and I still
can't get it to work. I have to believe there's something wrong with the way
I"m trying to declare and use my variable. So here's what I'm doing (for the
third time):

Declaring my variable at the top of the procedure (somebutton_onclick):
Dim strMyRecipient As String

Then, either just below the Dim declaration or just above where I need to
use the variable (I've tried both ways), I add this:
strMyRecipient = [Forms]![MyFormName]![Mycontrolname].column(1)
'where column 1 is the second column

Then, here's the line of code where the variable needs to replace an
existing hard-coded name:
myAppt.Recipients.Add ("John Doe")

which I've changed to:
myAppt.Recipients.Add ("(strMyRecipient)")

I've also tried just ("strMyRecipient") and various other things.

What's wrong with the syntax and/or am I not setting up the variable right
in the first place?
Click to expand...

Try...

myAppt.Recipients.Add(strMyRecipient)
 
Thanks guys. Both those work.

Just seems such a simple option(s) to try looking back. I think maybe my
lack of confidence in the syntax and my placement of the variable code caused
some unnecessary anxiety.

CW

Rick Brandt said:
Cheese_whiz said:
This is my third time trying to reply to this thread.....I have NO idea
what's going on with my replies, but they are going somewhere.

Anyway, I've tried that column(n) property early in the process and I still
can't get it to work. I have to believe there's something wrong with the way
I"m trying to declare and use my variable. So here's what I'm doing (for the
third time):

Declaring my variable at the top of the procedure (somebutton_onclick):
Dim strMyRecipient As String

Then, either just below the Dim declaration or just above where I need to
use the variable (I've tried both ways), I add this:
strMyRecipient = [Forms]![MyFormName]![Mycontrolname].column(1)
'where column 1 is the second column

Then, here's the line of code where the variable needs to replace an
existing hard-coded name:
myAppt.Recipients.Add ("John Doe")

which I've changed to:
myAppt.Recipients.Add ("(strMyRecipient)")

I've also tried just ("strMyRecipient") and various other things.

What's wrong with the syntax and/or am I not setting up the variable right
in the first place?
Click to expand...

Try...

myAppt.Recipients.Add(strMyRecipient)
Click to expand...
 
Back
Top