Full Version: Insert not working
UtterAccess Forums > Microsoft® Access > Access Forms
Good morning I have this insert command and I got "INVALID PROPERTY VALUE" error some times. The field ordcomments is a memo type.
Isql = "INSERT INTO ordStatus ( stDate, RecLoc, StatusID, ordcomments, empid )"
'Isql = Isql & " Values (date(),[Forms]![orders].form![RecLocationFrm]![RecLoc],'XSL', [Forms]![requestReportsDialog]![txtStrStatusComments] & ' ' & [Forms]![requestReportsDialog]![Text97], [Forms]![requestReportsDialog]![intempNumber]);"
'Set qdf = CurrentDb.CreateQueryDef("", Isql)
'For Each prm In qdf.Parameters
'prm.Value = Eval(prm.name)
'Next prm
'qdf.Execute dbFailOnError

And this command works ok always.
DoCmd.Hourglass True
Set rs = db.OpenRecordset("ordstatus", dbOpenDynaset)

rs!RecLoc = Forms!orders.Form!RecLocationFrm!RecLoc
rs!StDate = Date
rs!StatusID = "XSL"
rs!OrdComments = ([Forms]![requestReportsDialog]![txtStrStatusComments] & "...>" & [Forms]![requestReportsDialog]![Text97]) '"Status Letter To CLIENT"
rs!Empid = intEmpIDNumber 'DLookup("[Empid]", "Employees", "[UserName]= '" & strUserLoginName & "'")


I like to know what command is faster is the best one to use in my program. In advance thank you for your help.
Which command will work faster: the first one (qdf.Execute dbFailOnError). Since you can perform the entire process in fewer lines of code. Build the INSERT command string and execute. The other way requires more work for Access.
The reason why your first example is not working is that you need to remove the references to all Form controls Isql = Isql & " Values (date(),[Forms]![orders].form![RecLocationFrm]![RecLoc],
change it to be like this:
Isql = Isql & " Values (date()," & Forms!orders.Form!RecLocationsFrm!RecLoc & ","
If Forms!orders.Form!RecLocationsFrm!RecLoc is a text/string value you will need to add single quotes around it. If it is a number you will to convert it to a string with the STR() function. When the command is all done the variable Isql should contain all the values to be inserted.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.