By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,625 Members | 1,497 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,625 IT Pros & Developers. It's quick & easy.

Inserting records into a table

P: n/a
Hello everybody

As an apprentice developer I have a question.

In an application I have the tables Accounts (with columns AccountID,
LandID, etc.), AccountDocuments (with columns AccountID, DocumentID, etc.)
and LandDocuments (with columns LandID and DocumentID). When in the form
Accounts the field Land is filled, I want that the records would be inserted
into the table AccountDocuments from the table LandDocuments (Land/document
combinations with the same LandID) together with the related AccountID.

For example the following data is in the table Land Documents:

LandID DocumentID

D 12

D 15

D 22

US 12

If D is filled as LandID in a record of the form Accounts with AccountID =
23411 then the following records should be inserted in the table
AccountDocuments:

AccountID LandID DocumentID

23411 D 12

23411 D 15

23411 D 22

Who has the answer to this question?

Thank you in advance for your precious help.

Leo
Nov 13 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
"Levent Erdil" <le*********@wanadoo.nl> wrote in message news:<41***********************@news.wanadoo.nl>.. .
Hello everybody

As an apprentice developer I have a question.

In an application I have the tables Accounts (with columns AccountID,
LandID, etc.), AccountDocuments (with columns AccountID, DocumentID, etc.)
and LandDocuments (with columns LandID and DocumentID). When in the form
Accounts the field Land is filled, I want that the records would be inserted
into the table AccountDocuments from the table LandDocuments (Land/document
combinations with the same LandID) together with the related AccountID.

For example the following data is in the table Land Documents:

LandID DocumentID

D 12

D 15

D 22

US 12

If D is filled as LandID in a record of the form Accounts with AccountID =
23411 then the following records should be inserted in the table
AccountDocuments:

AccountID LandID DocumentID

23411 D 12

23411 D 15

23411 D 22

Who has the answer to this question?

Thank you in advance for your precious help.

Leo


Greetings Leo,

Something like:

INSERT INTO AccountDocuments ( AccountID, LandID, DocumentID ) SELECT
Accounts.AccountID, Accounts.LandID, LandDocuments.DocumentID FROM
Accounts INNER JOIN LandDocuments ON Accounts.LandID =
LandDocuments.LandID WHERE
((Accounts.AccountID)=[Forms]![Accounts]![txtAccountID].[Value]) AND
((Accounts.LandID) Like [Forms]![Accounts]![txtLandID].[Value]);

executed when a command button on the Accounts form is clicked should
get you in the ballpark.

James A. Fortune
Nov 13 '05 #2

P: n/a
Leo
Hello James,

Thank you for your help.
I tried it as a query which is called from a button in the form Accounts.
When the button is pressed, it opens a parameter window and I have to fill
the LandID and AccountID manually. Then the records are inserted in the
table. Is there a way to do it automatically, without filling the parameters
in the parameter windows?

Thank you again for your help

Leo
Nov 13 '05 #3

P: n/a
Leo wrote:
Hello James,

Thank you for your help.
I tried it as a query which is called from a button in the form Accounts.
When the button is pressed, it opens a parameter window and I have to fill
the LandID and AccountID manually. Then the records are inserted in the
table. Is there a way to do it automatically, without filling the parameters
in the parameter windows?


True, parameters out of interface objects (forms, mostly) are invisible
to Jet (the database engine trying to execute the query).

dim qd as querydef
set qd=currentdb.querydefs("yourquery")
qd.parameters("landID")=thatvalue
qd.parameters("accountID)=thatothervalue
qd.execute
set qd=nothing

will do the trick

--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html
I prefer human mail above automated so in my address
replace the queue with a tea
Nov 13 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.