Queries :: Using Insert SQL In VBA?

Dec 6, 2013

I am trying to insert a new record directly into a table using INSERT INTO sql. The field data is coming from some text boxes on an unbound form.

I keep getting an error telling me "too few parameters. Expected 5"

This is my code

Dim db As DAO.Database
Set db = DBEngine(0)(0)
Text7.SetFocus
pJobID = Text7
Text1.SetFocus
pFitter = Text1

[code]....

This works if i substitute the line:

& "(pJobID,pFitter,pNetValue,pInvNotes,pBookingRef); "

With specific test data (ie i put specific values in the VALUES section of the SQL). but not when I am using variables.

View Replies

Queries :: Insert Into One To Many PK And FK Insert

Mar 25, 2015

I have a normalized DB with one to many relationships, using Primary and Foreign Keys.

I need to do inserts and maintain the PK/FK relationship, which means when I add a new PK I need to insert that PK as FK in other tables in the same transaction.

How do I do this in Access? SQL Server I use transactions, but I can't lock up the tables in Access like that.

I did a bunch of searching and found nothing, which leads me to believe I am way off in my thinking. Below are the dirty details

I have 2 tables, tblName and tblPhone. 1 name can have many phones.

tblName has PKName. tblPhone has PKPhone, FKName.

I have a form where user enters a new Name and PhoneNumber.

Name gets inserted to tblName, assigned with PKName = 100

Phone should get inserted into tblPhone with PKName.

IE Insert into tblPhone (FKName, PhoneNum) VALUES (100,"212-555-1212").

The dumb way I am doing it now is I insert to tblName, query tblname for the PK, then write to tblPhone. This can't be right.

View 2 Replies View Related

Queries :: SQL INSERT INTO - Use Of Variable

Mar 17, 2013

Public strOldTable,strNewTable as string

OldTable has data, NewTable is empty but with same field structure.

I assign names of tables to these variables and attempt to fill up the NewTable with the data from the OldTable using :

DoCmd.RunSQL "INSERT INTO ('" & strNewTable & "') SELECT * FROM " & ('" & strOldTable & "')

I can find examples of use of the OldTable variable but not of use of the NewTable in this SQL.

View 7 Replies View Related

Insert Data From Queries To A Table

Apr 29, 2008

Is it possible to select some data in queries in the same db to a table?

Many thanks!

View 3 Replies View Related

Queries :: Insert Blank Rows At Once?

Oct 2, 2013

How to insert x number of empty rows at once in a query? All fields allow null.

Say, if I wanted to "resize" the table to 3000 records...

View 6 Replies View Related

Queries :: Insert Works In SQL VIEW But Not In VBA?

Mar 5, 2014

This code:

Code:
Private Sub txtPart_LostFocus()
Dim db As Database
Dim strSQL As String

[Code]....

But no entries are added to the table.

I open a query in design mode, paste the first debug above in SQL VIEW and run it:

Code:
INSERT INTO tblPartSpecs (PartID, SpecID, Sequence) VALUES (19, 14, 1);

And the entry is added in the table.

PartID and SpecID are long integer, Sequence is integer.

I originally had Me.txtKey in the SQL, but I saw a comment in a post about that being a possible culprit. So I copied it into a long integer, still didn't work (latest trial above).

View 2 Replies View Related

Queries :: Insert Multiple Record

Apr 11, 2014

View 1 Replies View Related

Queries :: Add Textbox Value To Each Line Of Insert

Nov 20, 2014

I'm trying to set up a data import function as follows: The user fills out a form, generating an entry in the Orders table. After pushing a button, line item data will be imported from an excel spreadsheet. I've got most of this working- I have a temp table that gets cleared out and then populated from the excel spreadsheet which is then deleted, all working fine. My hangup is when I try and get the line item data out of the temp table. I need to append the Order unique ID from the textbox on the form to each line item. Here's the select statement part of the insert into:

SELECT Forms!frmOrder!OrderID AS OrderID, ItemsID, NumUsed
FROM tblTempOrder

This gets the line items fine, but puts an Ankh symbol as the OrderID for each line item.

View 2 Replies View Related

Queries :: Insert Carriage Return

Apr 13, 2014

I am trying to reformat a string of text so that it is easier to read on a report.Based on the text, it seems like the best thing to do is to insert a carriage return before a date/time stamp.Here is the data (it's a conversation log)...it is three unique conversation log entries:

04/13/2014 8:21PM - all work and no play is no fun - Bob Smith04/13/2014 8:21PM - this is a test of the emergency broadcast system - Bob Smith04/13/2014 8:20PM - log testing - Bob Smith

If the carriage return is before the date/time string, then all entries would look the same. If the carriage return is after the date/time string.A user had suggested a child table to break out the data into separate records, but I'm sad to say that I never worked with child tables. Any way for replacing 'Date/time stamp' with 'carriage return' & 'Date/time stamp"

View 2 Replies View Related

Queries :: Put INSERT INTO Statement In A Query?

Apr 3, 2013

Where would you put the INSERT INTO statement in a query? Would it go after the select statement but before the From statement or would it go at the end?

View 2 Replies View Related

Noob Insert/update/delete Queries

Dec 2, 2007

Hi guys,

I was wondering if someone could help? I am using Access 2002 and I am struggling to find out out how you can insert/update/delete records through a form using the design view. Is this possible or do you need to do this another way?

Could some one point me in the direction of a comprehensive tutorial or outline some instructions for what I need to do?

I need to create a form that inserts people's details into a table

When user types in a surname as a parameter query, up pops the form with the details of the person stored in the database, and the user can update the details through the form and the details are saved to the table they came from.

Thanks in advance!

View 9 Replies View Related

Can You INSERT INTO Table With SELECT.. UNION Queries

Mar 29, 2005

I have a UNION of two SELECT queries. It works very well but I need the end-user to be able to modify the data so I am using INSERT INTO tablename. This will work for one query at a time but if I try to use the UNION it reports �Syntax error in FROM clause�. Can you use UNION in this case or can anyone see my error?

The full statement is rather complex, I have added a few spaces and linebreaks to make it readable as follows:

INSERT INTO TableCompleteForMailing

SELECT [Address List].[FamilySurname] AS Surname,

[Address List].[DEARFirstnames] AS FirstName,

[Address List].[Mailing] AS Mailing,

[Address List].[Christmas Mailing] AS OtherMailing, [Address List].[Address 1] AS Address1,

[Address List].[Postcode] AS Postcode

FROM [Address List]

UNION

SELECT [Names].[LastName] AS Surname,

[Names].[FirstName] AS FirstName,

[Names].[MailingList] AS Mailing,

[Names].[Selected] AS OtherMailing,

[Address List].[Address 1] AS Address1,

[Address List].[Postcode] AS Postcode

FROM [Names],[Address List]

WHERE [Names].[AddressListID]=[Address List].[AddressListID]

ORDER BY Surname, FirstName;

View 3 Replies View Related

Queries :: Insert Query Losing Decimals

Feb 4, 2014

So, I am inserting new records into a table. The values in the decimals are being stored as zeroes.

Field Properties
Long Integer
Standard (I also tried Currency)
2 Decimal Places

When I remove the "INSERT INTO" part of the query, the decimal data is in the results. I started out not using the FORMAT function. Then I tried it using "Standard", "Currency", and "#,##0.00"

View 3 Replies View Related

Queries :: INSERT QUERY Not Inserting One Field

Nov 27, 2014

I have a form with many fields and one field has an OnChange event to run an insert query or log an entry in a table about the change. I get insert entry written with everything except one field.The OnChange event code is:

Code:
If AddressStatusFld = 2 Then
'If it has changed from Current to Non-Current create a Contact Log entry for this matte
If (Val([MatterShortNoFld]) Mod 2 = 1) Then
Me.OperatorFld = 16

[code]....

I just can't work out why the record in MatterContactsMade table has a null value for the field Operator.

View 4 Replies View Related

Queries :: Insert If Statement In Access Report

Jul 25, 2013

I want to insert an if statement in Access report that states.If the interviewer field is not null them put in the interviewer. If it is null then don't put anything.I have 5 of the interviewer fields and don't want empty lines in the report.

View 1 Replies View Related

Queries :: Insert Query Result Into A Table

Jul 19, 2013

there is a query which i made which counts data from two tables RAUTH AND PERS

SELECT RAUTH.coy, RAUTH.Auth, Count(RAUTH.coy) AS Held
FROM pers INNER JOIN RAUTH ON pers.coy = RAUTH.coy
GROUP BY RAUTH.coy, RAUTH.Auth;

THERE IS A BLANK FIELD NAMED HELD in table RAUTH i want to update the held result which i quired into HELD fiedl of RAUTH table where COY (Field) is COY field of PERS table

RAUTH table structure

Coy Auth Held

Tata 12
Dell 11
HP 21
Opera 11
Bangour 10
pers table

Persno Name Coy Qual

124578 Gita Tata Bsc
235689 Sonu Dell BA
748596 Kiran HP BCA
459687 Sony Tata MCA
965214 Baiju Bangour CCNA
963656 Ashok Opera MCA

View 4 Replies View Related

Queries :: Insert Data Into Backend Without Major Bloating

May 1, 2013

I need to know the best way to insert data into the backend without cause major bloating of the backend. I am currently inserting about 20k records at a time using an INSERT INTO statement. The backend went from 83mb to 511mb trying to insert the last 20k records.

View 2 Replies View Related

Queries :: How To Insert Data In Text Field At The Beginning

Sep 21, 2013

I created an update query to a text field in a table. However, it inserts the data at the end of the string and I need to insert it at the beginning of the string that already exists. How do I move it to the front instead of the end?

This works but puts it at the end:

[Field Name] & "BlahBlah"

View 4 Replies View Related

Queries :: Basic Delete Insert Select Statement

Nov 8, 2013

what is wrong with the following statements. I'm new to access SQl. This would work in ms sql.

Code:
DELETE from Table1
INSERT INTO Table1(email, productid, datecreated, datesend) values ('adf', 5, '10/10/2012','10/10/2012')
Select * from Table1

View 1 Replies View Related

Queries :: Update Or Insert / Append Record In Database

Jun 5, 2013

I have two tables "TABLEA" and "TEMP"

fields in both tables are
Cust ID (Primary key)
Cust Name
Address
Cheque No
Amount
Location
Zone

I need query when i click on command button on form

if "Cust ID" which is primary key in "TEMP" Table match with "Cust ID" from "TABLEA"

It will update the record in "TABLEA" if not then append the record

View 1 Replies View Related

Queries :: How To Insert Multiple Conditions / Criteria For A Field

Jul 24, 2013

I am trying to make a query that outputs the minimum "Need Year" AND ALSO if the need year was equal to 9999 it shows "NO DATA".

This is what I have so far for checking the minimum value:

field: Need Year: MinofList(PMS_output!pqi_ny,PMS_output!iri_ny,PMS_ output!sdi_ny,pms_output!sai_ny)

I am not sure if I should be putting it in the criteria to check whether this minimum value (need year) equals to 9999 or not and if it does, it says "NO DATA" instead of 9999.

View 3 Replies View Related

Queries :: Append Query To Insert Data From Excel

Feb 5, 2014

I want to create a append query in access 2003 to insert data into an existing table from Excel workbook.My Table name is TokenDetail in Access 2003.

And Excel File is TokenCreation.i want to create query with msg box and requered file path for data becuase my excel files have various path and name.

View 8 Replies View Related

Queries :: Insert Query Result Into Table Column

Jul 19, 2013

How to take the results of query and copy them to a column in a table?

I am using sharepoint and ms access together, and Sharpoint in this instance will not reference the result of the query. I can only use table fields with Sharepoint...major pain....

Example, I need riders name, weight and height from the column in ALL INfo query to automatically fill the column table in ALL Info

queryreservations!AllINfo.value=tablereservations! Allinfo.value

and how to loop through records that are not filled out already?

seems like it should be a simple Do.command but I am not sure how to code it..

View 1 Replies View Related

Queries :: Insert Into Subtable For Each Record In Main Table

Nov 27, 2013

I have a sub table that acts as a revision history for the items in my main table. I've just finished uploading all the records into the main table and now I want to insert one record into the subtable for each record in the main table to start the history with the record creation.

The sub table looks like this:

tblRevisions
- RevID (auto Number / primary Key)
- RevDate (date of revision)
- RevName (Who made the revision)
- RevDesc (What revision was made)
- RevAuthorized (Who authorized the revision)
- RevLabel (Foreign key to the primary table)

The info I want to insert is:

RevID - Auto Number
RevDate - 11/27/2013
RevName - 3
RevDesc - "Added Label to database"
RevAuthorized - 1
RevLabel - (One for each record ID in tblLabels)

I suspect that I want to start with an insert into statement, something along the lines of:

Code:

Insert into tblRevisions (Revdate, RevName, RevDesc, RevAuthorized, RevLabel)
Values ("11/27/2013",3,"Added label to database", 1, ?)

I'm not sure how to indicate that there should be one label for each Record in tblLabels or that revLabel value should match the ID from tblLabels though.

View 6 Replies View Related

Queries :: How To Insert Comments And Notes Using Singular Quote

Feb 5, 2015

I know that you can insert comments and notes in VBA code using a singular quote, like the following:

Code:
'Comment about what the code does

Is there a similar way to insert comments in the SQL of queries? I found other forum posts that say this isn't possible but those were all for pre-2010 Access. Has this changed at all?

View 5 Replies View Related

Tables :: Use Insert Into Command To Insert Filepath Into Table That Adds Blank Label

Aug 28, 2014

I am at work, and I have acquired a database that prints labels. They now want the database to be coded so that after certain labels are printed the database will print a blank label. I have the code figured out as a Do While statement in order to print the blank label. The problem I am having is that I am trying to use the Insert Into command to insert the filepath into the table that adds the blank label.

|DoCmd.RunSQL "INSERT INTO Rod_tmakLabels ( Print, [Order] ) SELECT Yes AS Expr1, 'Rods Labels' AS Expr2"|

If I run the above command, it just adds the text "Rods Labels" at the end of the table. Is there anyway with the INSERT INTO command that I can insert the new label between the 2nd and 3rd row and add another row? Or is the command designed only to add a new row to the end? I haven't had any luck searching for this yet.

View 1 Replies View Related