Inserting form values into a table

We have a form with values taken from an sql query that comes from two
different tables.  We would like to enter the information into a third table.
Can some one direct me to code that will do the following:

1.  Provide the Insert sql that shows us how to add the form values to the
table
2.  Show us how to loop while inserting the information into the table (there
could be several lines on the form, each must be inserted one at a time).

I have worked with Access before and have never had a problem inserting
information.  However, I cannot quite figure out how to insert informtion
through an Access form.  By the way, we are using Access 2000.

Any help will be greatly appreciated.

-- 
Message posted via http://www.accessmonster.com

0
dufnobles
10/18/2007 4:15:55 PM
access.formscoding 7493 articles. 0 followers. Follow

4 Replies
795 Views

Similar Articles

[PageSpeed] 34

How does the form insert data when it comes from several different tables?

dufnobles wrote:
>We have a form with values taken from an sql query that comes from two
>different tables.  We would like to enter the information into a third table.
>Can some one direct me to code that will do the following:
>
>1.  Provide the Insert sql that shows us how to add the form values to the
>table
>2.  Show us how to loop while inserting the information into the table (there
>could be several lines on the form, each must be inserted one at a time).
>
>I have worked with Access before and have never had a problem inserting
>information.  However, I cannot quite figure out how to insert informtion
>through an Access form.  By the way, we are using Access 2000.
>
>Any help will be greatly appreciated.

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200710/1

0
dufnobles
10/18/2007 6:01:54 PM
I may be missing something...

It sounds like you want to take data you already have and duplicate it in 
another table.  Why?

If you can write a query that retrieves the data from the (two) table(s), 
why not use the query as your source, rather than adding another table?

More information, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

"dufnobles via AccessMonster.com" <u37896@uwe> wrote in message 
news:79e1c60005b27@uwe...
> We have a form with values taken from an sql query that comes from two
> different tables.  We would like to enter the information into a third 
> table.
> Can some one direct me to code that will do the following:
>
> 1.  Provide the Insert sql that shows us how to add the form values to the
> table
> 2.  Show us how to loop while inserting the information into the table 
> (there
> could be several lines on the form, each must be inserted one at a time).
>
> I have worked with Access before and have never had a problem inserting
> information.  However, I cannot quite figure out how to insert informtion
> through an Access form.  By the way, we are using Access 2000.
>
> Any help will be greatly appreciated.
>
> -- 
> Message posted via http://www.accessmonster.com
> 


0
Jeff
10/18/2007 6:09:02 PM
Thanks Jeff.

I need to clarify.  My form is a survey response form.  If I have a survey
question that needs four responses, then I display four rows, one for each
answer.  This works fine.

My problem is that once the user fills in the four rows, I do not know the
best procedure to store all four rows of text into another table that was
created to hold the responses.  I have a Save button but I am unsure of the
logic needed to get all four responses added to the table.  

Thanks.

Jeff Boyce wrote:
>I may be missing something...
>
>It sounds like you want to take data you already have and duplicate it in 
>another table.  Why?
>
>If you can write a query that retrieves the data from the (two) table(s), 
>why not use the query as your source, rather than adding another table?
>
>More information, please...
>
>Regards
>
>Jeff Boyce
>Microsoft Office/Access MVP
>
>> We have a form with values taken from an sql query that comes from two
>> different tables.  We would like to enter the information into a third 
>[quoted text clipped - 12 lines]
>>
>> Any help will be greatly appreciated.

-- 
Message posted via http://www.accessmonster.com

0
dufnobles
10/18/2007 7:31:12 PM
I'm still having trouble visualizing the data you have and what you are 
trying to do with it.

You've described the form ("four rows"...), but why, for example, are there 
four, rather than 3 or 5 (or just 1)?

You've described storing the rows/responses into a table to hold 
responses... but I don't have a clear picture of your data structure.

It all starts with the data.  Please post a description of the tables you 
currently have set up.

Regards

Jeff Boyce
Microsoft Office/Access MVP

"dufnobles via AccessMonster.com" <u37896@uwe> wrote in message 
news:79e37a2560add@uwe...
> Thanks Jeff.
>
> I need to clarify.  My form is a survey response form.  If I have a survey
> question that needs four responses, then I display four rows, one for each
> answer.  This works fine.
>
> My problem is that once the user fills in the four rows, I do not know the
> best procedure to store all four rows of text into another table that was
> created to hold the responses.  I have a Save button but I am unsure of 
> the
> logic needed to get all four responses added to the table.
>
> Thanks.
>
> Jeff Boyce wrote:
>>I may be missing something...
>>
>>It sounds like you want to take data you already have and duplicate it in
>>another table.  Why?
>>
>>If you can write a query that retrieves the data from the (two) table(s),
>>why not use the query as your source, rather than adding another table?
>>
>>More information, please...
>>
>>Regards
>>
>>Jeff Boyce
>>Microsoft Office/Access MVP
>>
>>> We have a form with values taken from an sql query that comes from two
>>> different tables.  We would like to enter the information into a third
>>[quoted text clipped - 12 lines]
>>>
>>> Any help will be greatly appreciated.
>
> -- 
> Message posted via http://www.accessmonster.com
> 


0
Jeff
10/19/2007 3:26:16 PM
Reply:

Similar Artilces:

Can I use "find / replace" to automatically "Insert Rows" in a do.
Have a long column of numbers Every time I see the number 5, I need to insert two rows. Doing this by hand is tedious. Can you help me find a shortcut? The IF function does not do this, right? Not 25, 35, 5351, just plain old 5? Option Explicit Sub testme01() Dim FirstRow As Long Dim LastRow As Long Dim iRow As Long With Worksheets("sheet1") FirstRow = 1 LastRow = .Cells(.Rows.Count, "C").End(xlUp).Row For iRow = LastRow To FirstRow Step -1 If .Cells(iRow, "C").Value = 5 Then ...

Pivot Table not pulling all fields or data
Hi All........ Without getting too verbose, I have a 900 row by 26 column database I am applying a Pivot Table to. The code seems to run fine, but it is not returning some of the fields, and of course the related data. I have tried reformatting the entire columns I am pulling from but no joy......however, when I reformat an errant cell from General to TEXT, that row comes in........and/or, if I change the value in one of the missing cells, it will bring that row in. The code runs fine, and was mostly derived by recording a macro in XL2k, but the data cannot be trusted becau...

Displaying a stored image on a form
I created an OLE Object type field in a table and then I inserted an graphic file (JPG). I then created a form on which I want to display my graphic file. I tried to display the graphic in a bound object control but the contol only displays the file name or an icon on the form. How can I get the actual graphic to display? I can display an image on a form but this is only a link to a graphic file and if I move or delete the original graphic file the database connection is lost. I want the image to be stored in the database so there is no linkage to outside files. Has anyone done th...

Control of exit from a Form opened within a subroutine
A DoCmd.OpenForm statement occurs part way down my Access 2000 subroutine "Prcs". The opened form presents the user with some screen info from which he/she must decide which of the two command buttons "Continue" and "Quit" should be clicked. "Quit" sends execution control elsewhere while "Continue" is designed to continue processing within Prcs. Sub Prcs tested vb code ' Includes evaluation of "strCrit" DoCmd.OpenForm "frmShowinfo", , , strCrit more tested vb code end sub I've not found a way to stop contr...

Running Balance in excel pivot table
Hi, Have rows: Product, transaction no have data fields: qty rec, qty iss, balance (calculated field: qty rec - qty iss) want balance to be a running total with base field of product so want something like: ======================================================= product--------doc no-----------------rec-------------------- iss-----------------------balance ======================================================= acetic acid -------------------opnbal/ 1-----------------4----------------------0---------------------- (4-0)=4 -------------------sales/ inv-----------------0--------------------...

Popup Form question
How can I set a pop-up form to only take one record? I have a popup form (single form) to take some customer info however when if they tab past the last field all the data disappears. Anyway to prevent this? Thanks in advance D "Dave" <dave@accessdatapros.com> wrote in news:95EBA52D-5DC8-4333-A18F-9A7EA4B65D77@microsoft.com: > How can I set a pop-up form to only take one record? > I have a popup form (single form) to take some customer info > however when if they tab past the last field all the data > disappears. Anyway to prevent this? > > Thanks in ...

How to use the countif() function to count items in a table
I have a table contains multple columns of info as shown in the below AcctID ProjectName PMName Phase Status EIG Project 1 Wellie Initiation New MLI Project 2 John Develop Active EIG Project 11 Wellie Qualify Active EIG Project UUU John Design InActive MLI Project XX Carol Deployment Completed MLI Project YYY Carol Design Active I'd like to use the countif() function to complete the following metric such that I know how many projects for each status for each AcctID. EIG ...

tables
i have created a table in excel but want the edges of table to be slightly curved instead of being a right angle....is there a way of doing it? -- Sam Not sure what you meant by "table." How did you draw/create the table? Have you checked out AutoShapes from the drawing toolbar? = Autoshapes>Basic shapes>first one second row is a rounded rectangle. = Draw the rectangle then right-click ...... Format AutoShape>Colors and = Lines tab>Fill - color - no fill or transparency 99%. Grid lines = optional. Is this what you want? Epinn=20 "Sam" <Sam@discuss...

Pivot Table Text Only, No calculations
:confused: I have a spreadsheet of data, text only. An address list. want to look at the table by city. Is there a way to do this withou calculation? For example, I want to have a city has the page field an look at all the addresses, including columns, within that city. Thanks -- rmm3 ----------------------------------------------------------------------- rmm30's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=835 View this thread: http://www.excelforum.com/showthread.php?threadid=26902 A pivot table is a good tool for summarizing data, but won't help...

Data vs. forms & Unload/reload questions
A .mdb file seems to encompass both the database and all the 'code' (forms, modules, and logic) used to CRUD the data in the tables. 1) Is there any way to separate the database from the code into separate files, eg. the db is mydata.mdb and the 'code is in a separate file, eg. code.mdb? 2) Say you have v1.0 of an app and the tables and attributes are populated with data. You then go and build v2.0 of the app, adding tables and columns to what already existed in v1.0, and perhaps moving some of the v1.0 attributes into different tables. What is the recommended way to unload/reloa...

Return a value when Date x is between Date y and z
Hi, I have a table something like this: A B C 1 From To Result 2 01/07/2009 01/07/2010 0-1 3 01/07/2010 01/07/2011 1-2 4 01/07/2011 01/07/2012 2-3 5 01/07/2012 01/07/2013 3-4 In another column (X) I have a list of Dates and I want return the "result" from the row where the date lies between. So for example in X3 I have 16/10/2011 and I can see this date is between the 2 dates in row 4 in the table above so I want to return "2-3" I've tried an array formula ...

Form Locked !
Hi, I created a form using the wizard based on a query, everything looks ok but i cannot enter data into the form as it appears to be locked ? what have i done wrong, i been trying to solve this for 2 hours now - please can you HELP!!! blake7, One common reason is the query used for the form. Open the query and see if you can add a new record to the query. If you can't, have a look here: http://www.allenbrowne.com/ser-61.html Jeanette Cunningham "blake7" <blake7@discussions.microsoft.com> wrote in message news:808F403F-8822-4619-9F50-DCA5E8B57DC1@microsoft.com... ...

URGENT: Exporting table to an SQL script
Hi guys I have a database in Access 2007 which I'm currently trying to rebuild in SQL Server Express in Visual Studio. Problem is, I can't seem to find an easy way of exporting tables. I've been using copy/paste on the data, but that only works with simple tables where I can build the table in SQLEx first. Is there a way in Access 2007 (or an Add-in I can download) to export a table as a list of SQL commands? Yknow.. CREATE TABLE, INSERT, etc!? Thanks, Dan Hi, you can use upsizing wizard to export whole database, or Export tables to ODBC connection to your sql server...

Default form in Public Calendar folder
I have a calendar in the public folders. Is there any way so that when the user clicks on the "New" button in Outlook that the default form that opens is a "Meeting Request" as opposed to an appointment? Thanks! Sylvia <sylvia@nospam.com> wrote: > I have a calendar in the public folders. Is there any way so that > when the user clicks on the "New" button in Outlook that the default > form that opens is a "Meeting Request" as opposed to an appointment? I don't think so, but you can certainly click the down-arrow next to the New ...

Unwanted Code Being Added to Form
This is a multi-part message in MIME format. ------=_NextPart_000_0085_01CAB57D.00862050 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Access 2007 sp2. I thought I seen it all. For some reason code was added to the OnClose of one of my forms that I = DID NOT put there! The code is a 1 liner using the NavigateTo command.(irrelevant I = believe). I recall testing using custom Navigation Panes about a year ago but = abandoned it. I recall having code in the OnClose of my form(s) that switched to a different Custom = Nav Pan...

forms not listed in database contents, yet can add data to them
Hi - there are no forms listed when I open up the database in design view. All the tables, queries, and macros are there. When I open up the database to add data, all the forms are there and can be used. The data is stored. So, it seems that the forms are still there, but just cannot be seen in design view. The database, when I open it up in design view, says Access 2000 file object even though I am using Access 2003 - could that be the problem? Thanks! You cannot open an entire database in Design View. Try opening it normally, selecting a form, and choosing Design View from the V...

Multiple combo boxes on a form
I have a form based on two tables. I would like a combo box for a field from each table that will fill in various fields from the relevant table. Does anyone have any suggestions? On Fri, 17 Aug 2007 23:58:37 GMT, "Malt" <u36720@uwe> wrote: >I have a form based on two tables. I would like a combo box for a field from >each table that will fill in various fields from the relevant table. Does >anyone have any suggestions? Why would you want to do that? It sounds like you're trying to store data from one table redundantly into a second table. This is essentially ...

inserting text form field in a text box
I'm designing a form that has several text boxes I want to be able to insert a text form field that I can type in different names in when needed, while keeping the rest of the text constant. Every time I go to insert form field from Legacy it is not available. Anybody have a solution. Your subject line has the missing clue. There is no way to insert a legacy form field (text or any other kind) in a text box, footnote, endnote, or header/footer. You can remove the text box and put the field into ordinary text, or you can use a text content control in a text box or elsewhe...

SeriesCollection Values Property Oddity
This has long perplexed me. Thought I would ask someone. The Values property of a series apparently returns an array of all the point values in the series. Howver, one apparently can't extract an element from this array. The following macros demo the issue. Looking for enlightenment: 'This works Sub Test1() Dim i As Long With ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1) For i = 1 To .Points.Count MsgBox Application.Index(.Values, i) Next End With End Sub 'This also works Sub Test2() Dim arr As Variant Dim i As Long With ActiveSheet.ChartObjects(1).Char...

Multiple table lookup
How do I set the LOOKUP formulas for this situation: TABLE 1: * Column A displays the names of salesmen (10) * Cells B1:F1 display the names of Products (5) * In cells B2:F11 are displayed the % of budget achieved by each salesman for each of the 5 products TABLE 2: In a separate table I set up a commission matrix showing the % commission due for each product according to the % of budget achieved; the names of the products run across the top, the % achieved vs. budget in column A, and the % commission payable in the intersecting cells TABLE 3: Contains the lookup formulas that calcul...

Pivot table bottom ten filter with conditions
Hi is it possible to have the bottom ten item of a pivot table but excluding specific value(s) eg I have a pivot table with the bottom 10 of cost item 1 -200 $ item 2 -300$ item 3 -0$ .... I would like to exclude item that have a cost = 0$ and have only items greater than 0. thanks Excel 2007 PivotTable Bottom 10, no zeros. Three ways. http://www.mediafire.com/file/kd3dydo3ww4/01_31_10.xlsx thank you for the spreadsheet. ...

How to set form caption / text?
I use MFC Visual C++ 4.2. I am developing an application with main window and a few childs window. How to set each child window text / caption everytime I call DoModal? If I set "Entry Form" then the window I call will displayed with "Entry Form" caption. Thank you. You can use SetWindowText to set it. For a dialog, you should do it any time after its created(IOW, not in constructor). OnInitDialog and after is fine. -- Ajay "Landon" <Landon@discussions.microsoft.com> wrote in message news:F18C3988-E5F2-413E-8CCF-78143066DB29@microsoft.com... >I ...

Not Print Zero values
Howdy, I need to do one of two things: 1) My zero values are hidden but they span down 300 cloumns. Is there away to hide these from printing? 2) The zero values above are a simple currency total between tw fields. I have filled the incrementing formulas down about 300 rows. I there a way to fill in the next incrementing formula [(b2)*(d2)] whe the calulation is made? I want to keep from having 300 growin formulas, I'd rather try to have them incrimentaly auto fill so m print outs wont have 20 extra blank pages. many thanks -- RubberSou ----------------------------------------------...

Fixed Assets ACRS tables
Is there a way to update/change ACRS tables? Can new ones be added? Our client here in sydney is reporting to their US headquarters, and they are using depreciation rates table from Gap 20. The rates are not on the ACRS tables of GP. Thanks. ...

Update multiselect field in table with code
Access 2007 under Vista Using a form to select variables for a report. One of the fields is a multi-value field in the underlying table. Have determined that following the selection of required items from the multi-select combo box and the clicking of the OK button, the underlying table is not immediately updated. The table is however properly update once the form is closed. This does not meet my purposes for the report generation, as exiting the form and returning is not a reasonable approach. I would like to use the After Update event from the multi-select combo box to ac...