473,320 Members | 1,821 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

append table query help

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
1 1373
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: John | last post by:
Hi - I am trying to perform a simple append query, with no luck. I have a table (MktPrices) that has the following fields: BondID, PriceDate, Price. The objective is to allow the user to input a...
1
by: PMB | last post by:
Thank you in advance for any and all assistance. I'm trying to use a make table query to pull the last transactionID, so I can use an append query to reset the transactionID to the next...
2
by: Danny | last post by:
I want to extract a subset of fields from one table into another the master table has many fields the subset has about half, but still many. Is there a way I can just append the master into the...
2
by: anita | last post by:
I am sorry if this sounds as a silly problem. I have table 1 with 10 records and table 2 with 20 records. I want to append 20 records from Table 2 to table 1. But when I run the append query, the...
3
by: JOEP | last post by:
What do I need to do to allow an append query to post null values to records in a field of the destination table? Basically I want to allow records with null values to post to the table. The append...
1
by: David Barger | last post by:
Greetings, It appears that an Append Query I run in Access XP is randomly failing to append a field. I have payroll data being entered into a payroll database. This data is exported daily to...
2
by: Ray Holtz | last post by:
I have a form that shows a single record based on a query criteria. When I click a button it is set to use an append query to copy that record to a separate table, then deletes the record from the...
5
by: solar | last post by:
I have copied a function that appends from table orders2 into table orders1 the row that has the value SubOrder = True in the table orders2. This function finds the highest ordered in the table...
4
by: dougmeece | last post by:
Morning Everyone... I have a table that needs to be append to and also updated. All the fields in the table are populated with data from the text boxes and combo boxes on a form. The Date...
4
by: franc sutherland | last post by:
Hello, I am using Access 2003. I am having trouble trapping the "can't append all the records in the append query" error message when appending data to a query from a table which is linked to...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.