Referencing Another Table

[Guest]

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

 

[Guest]

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

 

[Guest]

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....

?????

 

[Douglas J. Steele]

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.

 

[Guest]

Woo hooo!!!!

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

 

[Douglas J. Steele]

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")

 

[Guest]

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.

 

[Douglas J. Steele]

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.

 

[Guest]

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?

 

[Guest]

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

 

[Douglas J. Steele]

I just went in and pasted the SQL into the SQL View and switched back to the
Design view. Weird looking, but it runs fine. However, I don't see anyway to
create it.

 

[Douglas J. Steele]

I don't really understand what you're saying (and you're trimming far too
much from the posts, making it difficult for me to look back at what you
said you're trying to do).

What did you change the code to, and what exactly is it you want it to do?

 

[Guest]

I tried to modify what you gave me:

Dim strSQL As String

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

With this data:

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

Not working for me, can you plug it into your code you gave me?

Thanks
Curtis

 

[Douglas J. Steele]

I wanted to see what exactly you'd tried, and what you expected to see added
to your table.

 

[Guest]

I tried

Dim strSQL As String

strSQL = "INSERT INTO FollowUpNotes(Subject, EmailCreationsBody) " & _
"VALUES (" & Me.Subject& ", """ & Me.EmailCreationsBody& "")"
CurrentDb.Execute strSQL, dbFailOnError

I was expecting the same, whatever data is in me.emailcreationsbody, I want
it inserted into a new record in Followupnotes table in the subject field of
that table.

I tried switching subject/emailcreationsbody around & nothing.

Curtis

 

[SusanV]

Not trying to butt in, just watching...
Noticed you're using double quotes inside your VBA. Try singles instead:
"VALUES (" & Me.Subject& ", '" & Me.EmailCreationsBody& "')"

 

[Douglas J. Steele]

If all you want is to insert a single value into FollowUpNotes, you probably
only need

strSQL = "INSERT INTO FollowUpNotes(Subject) " & _
"VALUES (""" & Me.EmailCreationsBody& """)"
CurrentDb.Execute strSQL, dbFailOnError

(note that I was missing a quote in the original suggestion)