Hi I'm a new user with Access and I need help with some VBA syntax. I am trying to run an append query using a string SQL statement. Basically, I need to append to "tbl_All" various fields from two different tables.
I need this to be dynamic because depending on what the user selects, it needs to take fields from a specific table. I am therefore using a variable to refer to this table: EquipmentTbl is the variable that i have already defined.
This is my code: -
Dim StrSQL As String
-
-
StrSQL = "INSERT INTO tbl_All ( EquipName, AmtRequest, [Min], [Max], AmtReceived, Region, [Zone], Woreda, HealthCenter, ManualExp, AmtShipped, Notes, Supplier ) SELECT '" & EquipmentTbl & "'.EquipName, '" & EquipmentTbl & "'.AmtRequest, '" & EquipmentTbl & "'.Min, '" & EquipmentTbl & "'.Max, '" & EquipmentTbl & "'.AmtReceived, tbl_selectsite.Region, tbl_selectsite.Zone, tbl_selectsite.Woreda, tbl_selectsite.HealthCenter, '" & EquipmentTbl & "'.ManualExp, '" & EquipmentTbl & "'.AmtShipped, '" & EquipmentTbl & "'.Notes, '" & EquipmentTbl & "'.Supplier FROM '" & EquipmentTbl & "', tbl_selectsite;"
-
-
-
DoCmd.RunSQL (StrSQL)
-
When I run this I get an error that says "Syntax error in query. Incomplete query clause" Can anyone help me decipher what its wrong in the code??
Thanks!
4  5750  Rabbit 12,516
Recognized Expert Moderator MVP
Why are you surrounding your table names using single quotes?
Your're doing: -
SELECT 'Table1'.* FROM 'Table1';
-
Do you have spaces in your table names? If you do then you need brackets, [ ], not single quotes.
Why are you surrounding your table names using single quotes?
Your're doing:
-
SELECT 'Table1'.* FROM 'Table1';
-
Do you have spaces in your table names? If you do then you need brackets, [ ], not single quotes.
The actual name of my table is not EquipmentTbl. The value of this variable is the name of my table.
FishVal 2,653
Recognized Expert Specialist
Hi, there.
What all these single quotes are supposed to mean?
Kill'em to death.
Regards,
Fish
P.S. As Rabbit said - enclose all names breaking Access naming rules in square brackets. If you copypasted exactly the code you have, then the fieldname [Supplier FROM] has a space and must be enclosed in square brackets.
Hi, there.
What all these single quotes are supposed to mean?
Kill'em to death.
Regards,
Fish
P.S. As Rabbit said - enclose all names breaking Access naming rules in square brackets. If you copypasted exactly the code you have, then the fieldname [Supplier FROM] has a space and must be enclosed in square brackets.
Thanks all. It worked.
Sign in to post your reply or Sign up for a free account.
Similar topics | |
by: Paul M. |
last post by:
Hi,
I am trying to populate 3 paramters in an asp .net (vb) page redirect,
the first one is ok ok and gets populated by the other two get inserted into
the url for the redirect as empty strings! Anyone got any clues?? The code
is below and I have checked that the variables beginning with "g_str" are
all populated before the code is called. I think its something to do with
the "&" in the "&Title" and "&Version", do I need to do something to...
|
by: karinski |
last post by:
Hi All,
I have a split f/e - b/e setup on my database with RWOP queries on the
front end. The code below gets a PO number(s), and vendor name from a
multi choice list box on another form. It then assigns these values,
plus a new receiving number, to the current form. The SQL string is an
append query that copies select fields from the PO line items table
into the receiving line items table and appends the current receiving
number to...
|
by: gtg974p |
last post by:
Sub: Append a string to all the values in a column SQL
Hi all,
This might be a very simple query. But I am new to SQL programming.
Hope someone can help me.
I have a table ---
1634 Fred los angeles
123 Sam
|
by: robboll |
last post by:
When I try to use an append query from an oracle link it takes forever.
I am exploring the idea of doing an append action using a pass-through
query.
If I have an Oracle ODBC connection to server OraTest. User: User1
Password: password
and I am trying to append all records in table: tblTEST that are code:
"abc"
|
by: brino |
last post by:
hi all !
i needed to append about 20 fields from a query into the 1 field of
another table so that i can produce a report. i tried to do this in an
append query but it would not work because i was appending all of the
fields to the 1 field in the destination table. is there a way of doing
this with the 1 query. at present i have to do over 20 querys , each
one appending a single field only.
im not sure if its possible to do this in one...
| | |
by: kabradley |
last post by:
Hello Everyone,
So, thanks to nico's help I was finally able to 'finish' our companies access database. For the past week or so though,I have been designing forms that contain a subform and an option group so that whenever a certain button on the option group is pressed the correct subform source object property is changed to display the correct form. For instance, if they click "add new investment" the subform's source object is now changed to...
|
by: clarencemo |
last post by:
Hello all,
I have a append query that works great if I just run the query via Access. I
need to convert that query into VBA code. Here is the SQL View of the query
I'm trying to convert:
INSERT INTO TblInitiator IN 'X:\Archive\TestFinal\080107\TS3000v101_be.mdb'
SELECT tblAddInitiator.*
FROM tblAddInitiator;
|
by: sesling |
last post by:
In VB, I have created an append query that will write the results to a table. To each of the records being written, I want to include the user name of the person running the query. I know how to get the user name but do not know how to insert it, even if I can, into the append query. Is this possible? I have the statement I am running now. I get an invalid syntax error now when I run it
Private Sub Command89_Click()
On Error GoTo...
|
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 an excel
spreadsheet.
There are two tables. One is a list of general contacts, and the
other is a list of clubs. The clubs contain members who are within
the contacts table. When I add a list of new club members from the
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
| | |
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 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 a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
