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

append table query help

P: n/a
I've got a database for creating cost estimates for construction jobs.
Within the database there are 2 tables

1. ProjectIdentifier Table
2. CostEstimate Table

The relationship is a 1 to many (projectidentifier to costestimate). There
is an autonumber for the key (ProjID) in the ProjectIdentifier.

During data entry, the user created a new project which the data gets added
as a field in the ProjectIdentifier table, then adds items to the
costestimate portion. This works fine.

They want to be able to copy items from an existing cost estimate into a new
cost estimate (for similar type jobs). I set up forms and queries. The
form lets the user choose which job they want to copy,
Query 1 appends the new Project to the ProjectIdentifier table
Query 2 should append the cost estimate data into the costestimate table.
The problem is, I cannot figure out how to get the new ProjID number which
is the number that ties the relationship together.

I need help figuring out the logic behind this so I can get it working.
Jan 25 '06 #1
Share this Question
Share on Google+
1 Reply


P: n/a
from what I understand about your project, you have a form with some
selections. When the user makes the selections, the user submits these
to some tables tblProject and tblCost (for the explanation). It sounds
like these tables are not connected to the form

In your submit button click event you can have something like this:

Private Sub cmd1_Click()
Dim i As Integer
DoCmd.SetWarnings False
DoCmd.RunSql "Insert Into tblProject(ProjName)" _
& Values('" & txtProject & "')"

i = DLookUp("IDnum", "tblProjet", "ProjName = '" & txtProject & "'")

DoCmd.RunSql "Insert Into tblEstimate(IDnum, Estimate) Values(" & IDnum
& ", '" & txtEstimate & "')"
DoCmd.SetWarnings True
End Sub

Note: you delimit text data with single quotes inside the query string.
Numeric data does not need to be delimited. DateTime data gets
delimited with # sign in Access. In this example, you insert the
Project first, then you retrieve the newly generated ID number for that
project and you insert that into the Estimate table along with the
Estimate. If you have several estimates listed on your form, or if the
form has like a listbox where the user can select several estimates, you
could make the 2nd part of this example separate - like a 2nd button to
insert into the Estimate Table.

HTH

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Jan 25 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.