I have been trying to figure our why this code wont work for hours so now it is time to ask for some help. I have exhausted every debugging tool I know of and obviously can;t find the problem. A user adds information into a form and clicks a button to add a new record, however the record never gets added to the table. -
Private Sub cmdSearch_Click()
-
On Error GoTo Err_cmdsearch_Click
-
Dim strSQL As String
-
If CEASE_SEARCHING Then
-
strSQL = "INSERT INTO tblpbtinformation"
-
strSQL = strSQL & "( Organizer, PrometricID, CASNumber, City, State, Country, SiteCode, TestDate, PMIGTN, DeadlineDate, MaxCan, OpenClosed, SpecialAcc, Confirm, Notes, RosterReconcile, ReconcileDate, ConfirmationDate, Results, SpecialAccNotes )"
-
strSQL = strSQL & "SELECT [Forms]![fdlgPBTSearch]![IfOrganizer] AS Organizer,"
-
strSQL = strSQL & "[Forms]![fdlgPBTSearch]![IfProID] AS PrometricID,"
-
strSQL = strSQL & "[Forms]![fdlgPBTSearch]![IfCAS] AS CASNumber,"
-
strSQL = strSQL & "[Forms]![fdlgPBTSearch]![IfCity] AS City,"
-
strSQL = strSQL & "[Forms]![fdlgPBTSearch]![IfState] AS State,"
-
strSQL = strSQL & "[Forms]![fdlgPBTSearch]![IfCountry] AS Country,"
-
strSQL = strSQL & "[Forms]![fdlgPBTSearch]![IfSiteCode] AS SiteCode,"
-
strSQL = strSQL & "[Forms]![fdlgPBTSearch]![IfTestDate] AS TestDate,"
-
strSQL = strSQL & "[Forms]![fdlgPBTSearch]![IfPMIGTN] AS PMIGTN,"
-
strSQL = strSQL & "[Forms]![fdlgPBTSearch]![IfDeadline] AS DeadlineDate,"
-
strSQL = strSQL & "[Forms]![fdlgPBTSearch]![IfMaxCan] AS MaxCan,"
-
strSQL = strSQL & "[Forms]![fdlgPBTSearch]![IfOpen] AS OpenClosed,"
-
strSQL = strSQL & "[Forms]![fdlgPBTSearch]![IfSpecialAcc] AS SpecialAcc,"
-
strSQL = strSQL & "[Forms]![fdlgPBTSearch]![IfConfirm] AS Confirm,"
-
strSQL = strSQL & "[Forms]![fdlgPBTSearch]![IfNote] AS Notes,"
-
strSQL = strSQL & "[Forms]![fdlgPBTSearch]![IfReconcile] AS RosterReconcile,"
-
strSQL = strSQL & "[Forms]![fdlgPBTSearch]![IfReconcileDate] AS ReconcileDate,"
-
strSQL = strSQL & "[Forms]![fdlgPBTSearch]![IfConfirmDate] AS ConfirmationDate,"
-
strSQL = strSQL & "[Forms]![fdlgPBTSearch]![IfResults] AS Results,"
-
strSQL = strSQL & "[Forms]![fdlgPBTSearch]![IfSpecialAccNotes] AS SpecialAccNotes;"
-
If IsNull(Me!IfProID) Then
-
FALLS_SHORT = True
-
Else
-
FALLS_SHORT = False
-
End If
-
If FALLS_SHORT Then
-
DoCmd.Beep
-
MsgBox "You must provide at least the Prometric ID", vbExclamation, "System Message"
-
Exit Sub
-
End If
-
DoCmd.SetWarnings False
-
DoCmd.RunSQL strSQL
You turn off the warnings so you're not going to get error messages. You should never turn it off until you know the code is working.
Also, you should test it in a query by pasting the SQL it's going to run into the SQL view to make sure you have everything formatted correctly.
One thing I can say right now is that you have no FROM clause in your SELECT. In Access SQL, you can't have a SELECT without a FROM. If you don't have a table as the source of the data, then you should be using the INSERT INTO () VALUES () syntax instead.
Also, there could be other errors that I can't see and you won't see unless you turn warnings back on. For example, if you misspelled a reference, or have the wrong number of inputs.
9  1404 
You can't reference form values in a string when using RunSQL. You will have to concatenate the literal values outside the string.
Thanks for the info Rabbit.
Can you give a short example of the changes I would have to make. Or the process to do that.
- strSQL = strSQL & "'" & [Forms]![fdlgPBTSearch]![IfCity] & "' AS City,"
For the Select statement would it read as such... - strSQL = strSQL & "SELECT'" & [Forms]![fdlgPBTSearch]![IfOrganizer] & "' AS Organizer,"
At this point after all the code my sql statement is as such.
INSERT INTO tblpbtinformation( Organizer, PrometricID, CASNumber, City, State, Country, SiteCode, TestDate, PMIGTN, DeadlineDate, MaxCan, OpenClosed, SpecialAcc, Confirm, Notes, RosterReconcile, ReconcileDate, ConfirmationDate, Results, SpecialAccNotes )SELECT 'TestTestTest' AS Organizer,'123123123123' AS PrometricID,'' AS CASNumber,'' AS City,'' AS State,'' AS Country,'' AS SiteCode,'' AS TestDate,'' AS PMIGTN,'' AS DeadlineDate,'' AS MaxCan,'' AS OpenClosed,'' AS SpecialAcc,'' AS Confirm,'' AS Notes,'' AS RosterReconcile,'' AS ReconcileDate,'' AS ConfirmationDate,'' AS Results,'' AS SpecialAccNotes,
I do not receive any type of error. The new record just never appears in the table.
You turn off the warnings so you're not going to get error messages. You should never turn it off until you know the code is working.
Also, you should test it in a query by pasting the SQL it's going to run into the SQL view to make sure you have everything formatted correctly.
One thing I can say right now is that you have no FROM clause in your SELECT. In Access SQL, you can't have a SELECT without a FROM. If you don't have a table as the source of the data, then you should be using the INSERT INTO () VALUES () syntax instead.
Also, there could be other errors that I can't see and you won't see unless you turn warnings back on. For example, if you misspelled a reference, or have the wrong number of inputs.
Yeah that's the conclusion I made as well. I'm just wondering do I need to keep the "AS XXXXX" in the code? Would it just take blanks for the values not provided by the user.
I got it to work! There were problems with my original table as well. If anyone wants the finished code just let me know.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Lars Rasmussen |
last post by:
I tried that, but i dont work either.
I need to insert a way that mysql doese'nt complain when i copy some
records that have the same id (or that it just gives it an id according
to the...
|
by: Nick |
last post by:
Im trying to insert a bunch of rows into a table. If the row already
exists id like to update the row 'counter'. For example...
INSERT INTO table1
SELECT field1, field2
FROM table2
ON...
|
by: Frank Py |
last post by:
I need a query that looks at one table and appends another if new
customer data is added. I think I need an Insert, Select statement using
the NOT IN clause.
I need to compare Division,...
|
by: soni29 |
last post by:
hi,
i have a small question regarding sql, there are two tables that i
need to work with on this, one has fields like:
Table1:
(id, name, street, city, zip, phone, fax, etc...) about 20 more...
|
by: m3ckon |
last post by:
Hi there
I have a stored procedure which uses an INSERT ... SELECT statement to
transfer records from one table to another. However, I also need to
insert an extra field to the table which is...
|
by: Donius |
last post by:
Hello team. I'm running mysql 4.0.20-standard and i'm trying to do a
query like this:
INSERT IGNORE
INTO `DonorPledges` (
nDonor_id,
nPledgeYear,
nTotalPaid,
nTotalPledge )
SELECT
|
by: 4.spam |
last post by:
Hello.
v8.2.
Is it possible?
Example:
---------------
create function t(v varchar(1))
modifies sql data
returns table(c varchar(1))
|
by: Peter Nurse |
last post by:
For reasons that are not relevant (though I explain them below *), I
want, for all my users whatever privelige level, an SP which creates
and inserts into a temporary table and then another SP...
|
by: Tarik Monem |
last post by:
OK! I've gone through a few tutorials and I cannot understand what I'm doing wrong
casting_registration.php
<table>
<tr>
<td>
<form enctype="multipart/form-data" action="thankyou.php"...
|
by: pinkyana |
last post by:
Help me plzz...
I insert data into pohonLatihan table from senarailatihan table. It done!!
But how can i insert the 'user id' that i capture from the 'session' into the same table.?
...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
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,...
| |
