Refer to control in closed form

[Guest]

I have a form set up to grab email addresses stored in a text box on a
different form. When I try to send the emails, an error occurs stating that
the closed form cannont be found. Is there a way to grab data from a text
box on a closed form?

 

[Dirk Goldgar]

I have a form set up to grab email addresses stored in a text box on a
different form. When I try to send the emails, an error occurs
stating that the closed form cannont be found. Is there a way to
grab data from a text box on a closed form?

Forms don't hold data -- the tables to which they are bound do. Forms
are just windows on the data. You can't get data from a form that is
closed, but you can go out to the table where the data is stored and get
it.

Check the form design to find out what table contains the email-address
field you want, and what the name of the field is in that table (it may
be the same as the name of the text box, or it may not). Then you can
use the DLookup function to pull the value in that field from that
table, for a record whose criteria you specify. Note that you will
probably have to specify criteria in the DLookup expression to identify
which, of all the records in the table, holds the specific e-mail
address you want.

 

[ScottK]

No, you can't grab something that isn't there.

There are several options.

Leave the form open and minimize or hide it. (If you use the Run-Time
version to deploy to your user(s), you can control this completely.

Is the form you want to read from bound? If so, grab the information
from its source.

Consider passing the information in a table or a global variable.

One clue, the plural "email addresses" suggests you might be better off
with a query from a table if this is a repetitive process. If it's a
one-off, open the second form before closing the first (get the email
address, call the second form, in the second form, grab the info, then
close the first.)

You really do have a lot of options, it just depends upon what you're
trying to do.

-ScottK

 

[Guest]

Oops, you are right. Here is what I am trying to do...
I have a muliselect list box (lstProgram_Manager) on the form
Frm_ProgramManager where emails are selected from the table
TblProgramManager. Once selected, I need to copy the selected emails to the
field T_Reminder_Mailing_List on the table TblCompliance. When the selected
emails are pasted to T_Reminder_Mailing_List I need them to be in a list
separated by a semicolon so that the SendObject can pull from that list to
send the emails. I put the following code into a "Save and Exit" command
button on the form Frm_ProgramManager and I get an "Object Required" error.
Any suggestions?
--------------------------
Dim varItem As Variant
Dim strList As String

With Me!lstProgram_Manager
If Me!lstProgram_Manager.MultiSelect = Null Then
TblCompliance!T_Reminder_Mailing_List = Null

Else
For Each varItem In Me!lstProgram_Manager.ItemsSelected
strList = strList & .Column(1, varItem) & ";"
Next varItem
strList = Left$(strList, Len(strList) - 1)
TblCompliance!T_Reminder_Mailing_List = strList
End If
End With

 

[ScottK]

These are all assumptions;
You don't say which line is triggering the error, but looking at the
code it should be:
TblCompliance!T_Reminder_Mailing_List = strList
What you're trying to do is to put the string strList into the
T_Reminder_Mailing_List field of the TblCompliance table.

You can't reference it directly because you're only referencing two of
the three dimensions; you mention the table and field, but not the
record--even if it's only a one record table for storing the
information as I have sometimes done, Access still wants a record and
that isn't the way to do it.

If you're using DAO, once you have strList filled with email addresses,
the code would be something like:
Dim db As Database, rs As Recordset
Set db = CurrentDb 'Needed for the "db." of the next stmt
Set rs = db.OpenRecordset("TblCompliance", dbOpenDynaset) 'Gets the
table
rs.MoveFirst 'Gets the record
rs.Edit 'allows changes
rs("T_Reminder_Mailing_List") = strList 'Gets the field
rs.Update 'push changes
Set rs = Nothing 'Always clean up after yourself!
Set db = Nothing 'Ditto

If you're not using DAO, you can run the SQL directly. This is
something like what you'd use:
Dim strSQL As String
strSQL = "UPDATE TblCompliance SET
TblCompliance.T_Reminder_Mailing_List = " & _
vbQuote & strList & vbQuote & ";"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

The SetWarnings statements will temporarily turn off the message box
that ask if you're sure you want to do the update.

Good Luck,
-ScottK