Referencing Another Table

Main Table = MainTable
Second Table = SecondTable (has relationship / tied to main table)
Main Form = MainForm

Have a button in MainForm, when clicked, lets say it copies the data from
Me.Test (from MainTable) to a new record in SecondTable

Does anyone have any pointers on how to go about this, any web sites?

Thanks
Curtis

Why are you storing the value in the second table if it's already stored in
the main table?

A way of doing it

Dim MyDb As DAO.Database, MyRec As DAO.Recordset
Set MyDb = CurrentDb
Set MyRec = MyDb.OpenRecordset("Select * From SecondTable")
MyRec.AddNew
MyRec!Test = Me.Test
' You can add other field here in the same way
MyRec!KeyField = Me.KeyField
MyRec.Update
--
Good Luck
BS"D


"Curtis Stevens" wrote:

Main Table = MainTable
Second Table = SecondTable (has relationship / tied to main table)
Main Form = MainForm

Have a button in MainForm, when clicked, lets say it copies the data from
Me.Test (from MainTable) to a new record in SecondTable

Does anyone have any pointers on how to go about this, any web sites?

Thanks
Curtis

I tried this, but errors point to DAO.Database.

I'll try to explain, I have a series of pull downs, to send a customized
email to my prospects. The first pull down is unbound & specified the
subject of the email, what I want it to be (pulls the data from a table).
The second pull down grabs the particular email I want or the html text I
want from my list of files I have on my pc (having access read them) and then
inserts it into the body. This data is stored temporarily in a field in the
main table (I did this & will explain below). When I hit my command button,
it then does it all. I was then wanting it to create a record in a sub table
I have called Call Data, which has a few fields - date, time & subject. I
want it to create a new record (date & time auto) and say something like
EMAILED Application in the subject, so I don't have to manually type it in
anymore.

I bounded that second pull down as I found a way to go about this, creating
an append query and an update to clean the db up. I don't think it is
possible, but my ultimate goal would be the pull down uses one field in the
table to grab the text from the html files and then when it pastes that
emailed app data, it actually grabs the data from another field in that
table, like a more descriptive name, instead of just the name of file, which
I have to use to be able to grab it & insert the stuff into the body of the
email.

I hope this all makes sense. I may just have to upload my database to the
web so you can see....

?????


Dim MyDb As DAO.Database, MyRec As DAO.Recordset
Set MyDb = CurrentDb
Set MyRec = MyDb.OpenRecordset("Select * From SecondTable")
MyRec.AddNew
MyRec!Test = Me.Test
' You can add other field here in the same way
MyRec!KeyField = Me.KeyField
MyRec.Update

What do you mean by "errors point to DAO.Database"? Are you saying you're
getting a compile error about an unknown user-defined type?

That would imply that you don't have a reference set to DAO in your
application. Go into the VB Editor, select Tools | References, scroll
through the list until you find the entry for "Microsoft DAO 3.6 Object
Library", check the box beside it to select it, then click on OK.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Curtis Stevens" wrote in message
...
I tried this, but errors point to DAO.Database.

I'll try to explain, I have a series of pull downs, to send a customized
email to my prospects. The first pull down is unbound & specified the
subject of the email, what I want it to be (pulls the data from a table).
The second pull down grabs the particular email I want or the html text I
want from my list of files I have on my pc (having access read them) and
then
inserts it into the body. This data is stored temporarily in a field in
the
main table (I did this & will explain below). When I hit my command
button,
it then does it all. I was then wanting it to create a record in a sub
table
I have called Call Data, which has a few fields - date, time & subject. I
want it to create a new record (date & time auto) and say something like
EMAILED Application in the subject, so I don't have to manually type it in
anymore.

I bounded that second pull down as I found a way to go about this,
creating
an append query and an update to clean the db up. I don't think it is
possible, but my ultimate goal would be the pull down uses one field in
the
table to grab the text from the html files and then when it pastes that
emailed app data, it actually grabs the data from another field in that
table, like a more descriptive name, instead of just the name of file,
which
I have to use to be able to grab it & insert the stuff into the body of
the
email.

I hope this all makes sense. I may just have to upload my database to the
web so you can see....

?????


Dim MyDb As DAO.Database, MyRec As DAO.Recordset
Set MyDb = CurrentDb
Set MyRec = MyDb.OpenRecordset("Select * From SecondTable")
MyRec.AddNew
MyRec!Test = Me.Test
' You can add other field here in the same way
MyRec!KeyField = Me.KeyField
MyRec.Update

Woo hooo!!!!

Btw, from what I said, is this the best way to handle this, the suggestion
in this post?

I'd probably just run an Insert query, rather than opening the recordset,
but it's not a big deal.

If you are going to open a recordset, though, it might be faster if you open
an empty one:

Set MyRec = MyDb.OpenRecordset("Select * From SecondTable WHERE False")


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Curtis Stevens" wrote in message
...
Woo hooo!!!!

Btw, from what I said, is this the best way to handle this, the suggestion
in this post?

When you say insert query, you mean append query right? But to be able to do
that, you have to create a field that will be checker, the one that is
required like yes/no, so you make that particular record to have that field
say yes, so when you do the append, it only grabs that particular one.


I'd probably just run an Insert query, rather than opening the recordset,
but it's not a big deal.

If you are going to open a recordset, though, it might be faster if you open
an empty one:

Set MyRec = MyDb.OpenRecordset("Select * From SecondTable WHERE False")


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Curtis Stevens" wrote in message
...
Woo hooo!!!!

Btw, from what I said, is this the best way to handle this, the suggestion
in this post?

Access calls it an Append query, but if you look at the SQL, it's "INSERT
INTO MyTable ...."

I don't really understand your point about needing a checker field.

There are 2 flavours for the INSERT INTO statement: one lets you specify the
values, as opposed to selecting them from a table.

The equivalent to

Set MyDb = CurrentDb
Set MyRec = MyDb.OpenRecordset("Select * From SecondTable")
MyRec.AddNew
MyRec!Test = Me.Test
' You can add other field here in the same way
MyRec!KeyField = Me.KeyField
MyRec.Update

would be:

Dim strSQL As String

strSQL = "INSERT INTO SecondTable (Test, KeyField) " & _
"VALUES (" & Me.Test & ", """ & Me.KeyField & "")"
CurrentDb.Execute strSQL, dbFailOnError

(I've assumed Test is a numeric field and KeyField is a text field. You'll
need to change the quotes if that's not the case.)

However, as I said, I think your approach should be fine.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Curtis Stevens" wrote in message
...
When you say insert query, you mean append query right? But to be able to
do
that, you have to create a field that will be checker, the one that is
required like yes/no, so you make that particular record to have that
field
say yes, so when you do the append, it only grabs that particular one.


I'd probably just run an Insert query, rather than opening the recordset,
but it's not a big deal.

If you are going to open a recordset, though, it might be faster if you
open
an empty one:

Set MyRec = MyDb.OpenRecordset("Select * From SecondTable WHERE False")


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Curtis Stevens" wrote in
message
...
Woo hooo!!!!

Btw, from what I said, is this the best way to handle this, the
suggestion
in this post?

Gotcha. Can you do both flavors of append or insert into using the append
query design view in access or can only do the second flavor or insert into
by hard coding it, can't use design view to do that?



Access calls it an Append query, but if you look at the SQL, it's "INSERT
INTO MyTable ...."

I don't really understand your point about needing a checker field.

There are 2 flavours for the INSERT INTO statement: one lets you specify the
values, as opposed to selecting them from a table.

The equivalent to

Set MyDb = CurrentDb
Set MyRec = MyDb.OpenRecordset("Select * From SecondTable")
MyRec.AddNew
MyRec!Test = Me.Test
' You can add other field here in the same way
MyRec!KeyField = Me.KeyField
MyRec.Update

would be:

Dim strSQL As String

strSQL = "INSERT INTO SecondTable (Test, KeyField) " & _
"VALUES (" & Me.Test & ", """ & Me.KeyField & "")"
CurrentDb.Execute strSQL, dbFailOnError

(I've assumed Test is a numeric field and KeyField is a text field. You'll
need to change the quotes if that's not the case.)

However, as I said, I think your approach should be fine.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Curtis Stevens" wrote in message
...
When you say insert query, you mean append query right? But to be able to
do
that, you have to create a field that will be checker, the one that is
required like yes/no, so you make that particular record to have that
field
say yes, so when you do the append, it only grabs that particular one.


I'd probably just run an Insert query, rather than opening the recordset,
but it's not a big deal.

If you are going to open a recordset, though, it might be faster if you
open
an empty one:

Set MyRec = MyDb.OpenRecordset("Select * From SecondTable WHERE False")


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Curtis Stevens" wrote in
message
...
Woo hooo!!!!

Btw, from what I said, is this the best way to handle this, the
suggestion
in this post?

I tried your code, maybe I'm getting it backwards.

ControlName of Field in SecondTable = Subject

ControlName of Field in Main Table (what is being copied) = EmailCreationsBody

Mind putting that in code for me?

Thanks
Curtis