UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V  1 2 >  (Go to first unread post)
   Reply to this topicStart new topic
> Trouble With Append Query, Access 2013    
 
   
DominicG
post Feb 21 2020, 04:30 PM
Post#1



Posts: 313
Joined: 3-July 07
From: Richboro PA


I have a table called tblPRDCtrlValve001 (yes, I know it's a long name. It was the first table in this database and I've since gone with more abbreviated names)

This table is the source for a form that performs a series of calculations. I have it set so that the user inputs some information in a data entry form, and then loads the calc form. In the OnLoad Event of the calc form I have some VBA code that looks at several related tables and shoots that info to the appropriate text box on the calculation form. For existing records this works AWESOME! But there is one small problem.......in order to do this and avoid any problems with referential integrity, I need to have a record in the table tblPRDCtrlValve001. If I try to start a new record it gets all crazy on me.

With the lead sheet form opened, I opened the table tblPRDCtrlValve001 and added a value to the field ScenChkDataID, saved it and it thought tere was an existing record and the form opened PERFECTLY. It imported all the data and performed flawlessly. BTW, this field ScenChkDataID is not the primary key for this table. It just refers to the primary field of a different table

So I reasoned that I could embed some VBA code in a control on the data entry form to take the value for ScenChkDataID (held in a textbox) and use it for a source use an append query to start a new record in the table tblPRDCtrlValve001 and then add a number to a field. So far it hasn't worked.

Can I get some help here?


Here's a copy of the SQL for the append query

CODE
INSERT INTO tblPRDCtrValve001 ( ScenChkDataID )
SELECT tblPRDCtrValve001.ScenChkDataID
FROM tblPRDCtrValve001
WHERE (((tblPRDCtrValve001.ScenChkDataID)=[Forms]![frmMain]![ScenChkData2subform].[Form]![txtScenChkDataID]));


Go to the top of the page
 
theDBguy
post Feb 21 2020, 04:40 PM
Post#2


UA Moderator
Posts: 77,703
Joined: 19-June 07
From: SunnySandyEggo


Hi. Maybe you could just do something like:
CODE
INSERT INTO TableName (FieldName) VALUES (Forms!FormName.TextboxName)

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
DominicG
post Feb 24 2020, 07:54 AM
Post#3



Posts: 313
Joined: 3-July 07
From: Richboro PA


Thanks for the assist! What's this, the 100th time you've helped me out? thumbup.gif As always, I appreciate the help immensely

I just tried that statement you provided and get the following error message: "Compile error: Expected: end of statement". If it means anything, the INTO is highlighted

CODE
ScIndex = CInt (Me.txtScenChkDataID)

DoCmd.RunSQL INSERT INTO tblPRDCtrValve001 (ScenChkDataID) VALUES (ScIndex)


Any thoughts?

Edit to update: I realized I had a typo and was trying to insert a value into the ID of the table tblPRDCtrValve001 (which you cannot do!). I want to insert the value from the text box "txtScenChkDataID" into the field "ScenChkDataID" in the table tblPRDCtrValve001

I think part of the problem is that the subform where the control is located. I made a test command button on it and can use a message box to show me the value for me.txtScenChkData. But for some reason the SQL isn't accepting it. It acts like the value is blank
This post has been edited by DominicG: Feb 24 2020, 08:19 AM
Go to the top of the page
 
DanielPineault
post Feb 24 2020, 08:08 AM
Post#4


UtterAccess VIP
Posts: 7,237
Joined: 30-June 11



Quotes!
CODE
DoCmd.RunSQL "INSERT INTO tblPRDCtrValve001 (ID) VALUES (Forms!Main.ScenChkDataID)"


I prefer
CODE
DoCmd.Execute "INSERT INTO tblPRDCtrValve001 (ID) VALUES (Forms!Main.ScenChkDataID)", dbFailOnError

--------------------
Daniel Pineault (2010-2019 Microsoft MVP, UA VIP, EE Distinguished Expert 2018)
Professional Help: https://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: https://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
DominicG
post Feb 24 2020, 08:24 AM
Post#5



Posts: 313
Joined: 3-July 07
From: Richboro PA


I get an error message, "Compile Error Method or Data member not found"
Go to the top of the page
 
cheekybuddha
post Feb 24 2020, 08:38 AM
Post#6


UtterAccess Moderator
Posts: 12,600
Joined: 6-December 03
From: Telegraph Hill


HI, you will need to concatenate the value in:
CODE
  Dim ScIndex  AS Integer
  ScIndex = CInt (Me.txtScenChkDataID)

  CurrentDb.Execute "INSERT INTO tblPRDCtrValve001 (ScenChkDataID) VALUES (" & ScIndex & ");", dbFailOnError


hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
DominicG
post Feb 24 2020, 08:38 AM
Post#7



Posts: 313
Joined: 3-July 07
From: Richboro PA


Something wonky is going on with my SQL code where it is not recognizing the value from the textbox

I'm working in a subform (ScenChkData2subform) that is in "Single Form" viewing mode. There is a textbox called me.txtScenChkData that is holding the value of primary key for the table "tblScenChkData". I used a simple command button to test if I can pull the value from the textbox. Msgbox (Cint(me.txtScenChkData.value) and it displays it with no problem

But I try to use that value in the SQL statement and this shows up (image attached).

CODE
DoCmd.RunSQL "INSERT INTO tblPRDCtrValve001 (ScenChkDataID) VALUES (CInt(me.txtScenChkDataID.value))"


BUT if I type in the value in the parameter box it works PERFECTLY! The value gets added to the table and I'm ready to go. Not sure what is going on here.
This post has been edited by DominicG: Feb 24 2020, 08:38 AM
Attached File(s)
Attached File  parameter_error.jpg ( 17.14K )Number of downloads: 0
 
Go to the top of the page
 
cheekybuddha
post Feb 24 2020, 08:39 AM
Post#8


UtterAccess Moderator
Posts: 12,600
Joined: 6-December 03
From: Telegraph Hill


See above

--------------------


Regards,

David Marten
Go to the top of the page
 
DominicG
post Feb 24 2020, 08:49 AM
Post#9



Posts: 313
Joined: 3-July 07
From: Richboro PA


Yes!!!!! That did it!! uarulez2.gif

THANK you!
Go to the top of the page
 
cheekybuddha
post Feb 24 2020, 08:50 AM
Post#10


UtterAccess Moderator
Posts: 12,600
Joined: 6-December 03
From: Telegraph Hill


yw.gif

We're all pleased that we could help. thumbup.gif

d

--------------------


Regards,

David Marten
Go to the top of the page
 
cheekybuddha
post Feb 24 2020, 08:53 AM
Post#11


UtterAccess Moderator
Posts: 12,600
Joined: 6-December 03
From: Telegraph Hill


Just FYI, DBG's example will work if you are operating within a stored query, but using SQL in VBA requires the value to be reconciled before execution.

hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
DominicG
post Mar 20 2020, 08:44 AM
Post#12



Posts: 313
Joined: 3-July 07
From: Richboro PA


While the SQL statement worked perfectly when I had the table name hard coded in, when I tried to substitute a variable in it gave me an error

Here's the way it was
CODE
CurrentDb.Execute "INSERT INTO tblPRDCtrValve001 (ScenChkDataID) VALUES (" & ScIndex & ");", dbFailOnError



Here is what I attempted to do
CODE
CurrentDb.Execute "INSERT INTO (CalcTable) (ScenChkDataID) VALUES (" & ScIndex & ");", dbFailOnError


You see, the whole idea of this exercise was to make it so that this bit of code inserted a value into a table. The table name is based on the user selection and is stored in the variable "CalcTable"

I imagine this is just a formatting error but since I am unfamiliar with SQL I could use some help
Go to the top of the page
 
theDBguy
post Mar 20 2020, 11:27 AM
Post#13


UA Moderator
Posts: 77,703
Joined: 19-June 07
From: SunnySandyEggo


Hi. It's just a matter of "constructing" the string for the SQL. For example, if the SQL string was this:

INSERT INTO TableName (FieldName) VALUES (SomeValue)

and you want a variable for TableName, FieldName, and SomeValue; then we basically want something like:

INSERT INTO TableVariable (FieldVariable) VALUES (ValueVariable)

But of course, we want the values assigned to those variables instead of their names, so that if TableVariable was Table1, we want it to say "INSERT INTO Table1..."

To do that, we "concatenate" the variable into the SQL string. So:

"INSERT INTO " & TableVariable & "(" & FieldVariable & ") VALUES (" & ValueVariable & ")"

That would result in a SQL string with the values of the variables resolved and added into the SQL string. One point to remember is if the field value needs delimiters, then you need to add them too into the string.

Take a look at this blog article: How to Build Strings in VBA

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
cheekybuddha
post Mar 20 2020, 11:33 AM
Post#14


UtterAccess Moderator
Posts: 12,600
Joined: 6-December 03
From: Telegraph Hill


One small nit in DBG's solution:
CODE
"INSERT INTO " & TableVariable & " (" & FieldVariable & ") VALUES (" & ValueVariable & ")"  
                                  ^
                                  |
                            Add space here

--------------------


Regards,

David Marten
Go to the top of the page
 
theDBguy
post Mar 20 2020, 12:11 PM
Post#15


UA Moderator
Posts: 77,703
Joined: 19-June 07
From: SunnySandyEggo


Good point, David. However, I just tested it ('cause I was curious to know), and it didn't seem to matter. Cheers!

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
cheekybuddha
post Mar 20 2020, 01:31 PM
Post#16


UtterAccess Moderator
Posts: 12,600
Joined: 6-December 03
From: Telegraph Hill


>> and it didn't seem to matter <<

Oh really! blush.gif

I'll have to tuck that bit of info away! thumbup.gif

d

--------------------


Regards,

David Marten
Go to the top of the page
 
theDBguy
post Mar 20 2020, 02:28 PM
Post#17


UA Moderator
Posts: 77,703
Joined: 19-June 07
From: SunnySandyEggo


Tested it again, and the same goes for the VALUES() part as well. Oh well...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
DominicG
post Mar 20 2020, 03:18 PM
Post#18



Posts: 313
Joined: 3-July 07
From: Richboro PA


Thanks again to both of you for all your help
Go to the top of the page
 
theDBguy
post Mar 20 2020, 03:24 PM
Post#19


UA Moderator
Posts: 77,703
Joined: 19-June 07
From: SunnySandyEggo


Hi. No sweat. We're here to help. Good luck with your project.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
DominicG
post Mar 23 2020, 07:52 AM
Post#20



Posts: 313
Joined: 3-July 07
From: Richboro PA


Previously I used this statement
CODE
CurrentDb.Execute "INSERT INTO tblPRDCtrValve001 (ScenChkDataID) VALUES (" & ScIndex & ");", dbFailOnError



Which was revised to this

CODE
CurrentDb.Execute "INSERT INTO " & CalcTable & " (" & ScenChkDataID & ") VALUES (" & ScIndex & ");", dbFailOnError


However, now I get an error that reads

Runtime error '3127'
The INSERT INTO statement contains the following unknown field name: '2'. make sure you have typed the name correctly, and try the operation again


The "2" is the value held in the variable ScenChkDataID. So instead of taking the fieldname, it's taking the value held in it.


I'm trying to wrap my head around the syntax for this and (regrettably) I'm failing. Part of my confusion is that the previous version of this code (the first line in code tags above) worked FLAWLESSLY. And if you look (ScenChkDataID) is a variable that's inserted in. But when you got to add a variable for CalcTable it gets all goofy
This post has been edited by DominicG: Mar 23 2020, 08:19 AM
Go to the top of the page
 
2 Pages V  1 2 >


Custom Search


RSSSearch   Top   Lo-Fi    28th March 2020 - 09:29 AM