f



Need Help with SQL Statement

 need help with the following code in the Double Click event of a form field (Account). Double clicking results in the error, 

"Run-time error '2465': Application-Defined or object-defined error" 

I can't figure out what I have done wrong. "78" is the ID number of the StatusCode for "CUST" 

Here is the code, 

Private Sub ACCOUNT_DblClick(Cancel As Integer) 
Dim stSQL As String 

stSQL = "INSERT into Status (StatusCompany, StatusCode, StatusNotes) " & _ 
  "Values (" & Forms!ProspectForm.ProspectID & ", " & "78" & ", " & [ACCOUNT] & ")" 
End Sub 

Thanks in advance for your help, 

magmike 
0
magmike
12/18/2016 2:58:44 AM
comp.databases.ms-access 42670 articles. 0 followers. Post Follow

7 Replies
442 Views

Similar Articles

[PageSpeed] 10

On Saturday, December 17, 2016 at 9:58:53 PM UTC-5, magmike wrote:
> need help with the following code in the Double Click event of a form field (Account). Double clicking results in the error, 
> 
> "Run-time error '2465': Application-Defined or object-defined error" 
> 
> I can't figure out what I have done wrong. "78" is the ID number of the StatusCode for "CUST" 
> 
> Here is the code, 
> 
> Private Sub ACCOUNT_DblClick(Cancel As Integer) 
> Dim stSQL As String 
> 
> stSQL = "INSERT into Status (StatusCompany, StatusCode, StatusNotes) " & _ 
>   "Values (" & Forms!ProspectForm.ProspectID & ", " & "78" & ", " & [ACCOUNT] & ")" 
> End Sub 
> 
> Thanks in advance for your help, 
> 
> magmike

This is just a suggestion.  Prior to the SQL statement enter the word
 Stop

This will throw you into debug mode when you run the code.  Put your cursor over the variables you are listing to see their values.
 
Also, enter in the immediate window 
  Debug.print strSQL
and study the result.  Do the values look correct?  Are you putting a string value when a number should be used?

I find Stop to be a lifesaver...regarding code.
0
Patrick
12/18/2016 1:23:54 PM
On Saturday, December 17, 2016 at 9:58:53 PM UTC-5, magmike wrote:
> need help with the following code in the Double Click event of a form fie=
ld (Account). Double clicking results in the error,=20
>=20
> "Run-time error '2465': Application-Defined or object-defined error"=20
>=20
> I can't figure out what I have done wrong. "78" is the ID number of the S=
tatusCode for "CUST"=20
>=20
> Here is the code,=20
>=20
> Private Sub ACCOUNT_DblClick(Cancel As Integer)=20
> Dim stSQL As String=20
>=20
> stSQL =3D "INSERT into Status (StatusCompany, StatusCode, StatusNotes) " =
& _=20
>   "Values (" & Forms!ProspectForm.ProspectID & ", " & "78" & ", " & [ACCO=
UNT] & ")"=20
> End Sub=20
>=20
> Thanks in advance for your help,=20
>=20
> magmike

Oh yeah, after I do a=20
  Debug.print strSQL=20
in the immediate window, I then copy the string result into the clipboard. =
 I then minimize the window, and create a new query.  Don't add any tables,=
 close the table list.  Then press the SQL button paste it, and then run it=
, go into design mode, whatever.  Oftentimes you'll spot the error immediat=
ely.
0
Patrick
12/18/2016 1:28:48 PM
Hi magmike,

Am 18.12.2016 um 03:58 schrieb magmike:
>   need help with the following code in the Double Click event of a form field (Account). Double clicking results in the error,
>
> "Run-time error '2465': Application-Defined or object-defined error"
>
> I can't figure out what I have done wrong. "78" is the ID number of the StatusCode for "CUST"
>
> Here is the code,
>
> Private Sub ACCOUNT_DblClick(Cancel As Integer)
> Dim stSQL As String
>
> stSQL = "INSERT into Status (StatusCompany, StatusCode, StatusNotes) " & _
>    "Values (" & Forms!ProspectForm.ProspectID & ", " & "78" & ", " & [ACCOUNT] & ")"
> End Sub

assuming the statusnotes field is defined as text the statement should 
look like this

strSQL = "INSERT INTO Status (StatusCompany, StatusCode, StatusNotes) " _
             & "VALUES (  " & Forms!ProspectForm!ProspectID & " , 78,  
'" & [Account] & "')"

You have to quote a textfield value with a single or double apostrophe.

Btw. you should distinguish between a bound field and a control value. 
To do this, normally the programmers add a prefix like 'txt' to a 
textbox name. So you will be able to reference the bound field for 
example as [Account] without the prefix and the control value itself as 
txtAccount.value, just to avoid side effects.

Ulrich

0
UTF
12/18/2016 1:43:53 PM
On Sunday, December 18, 2016 at 7:28:50 AM UTC-6, Patrick Finucane wrote:
> On Saturday, December 17, 2016 at 9:58:53 PM UTC-5, magmike wrote:
> > need help with the following code in the Double Click event of a form f=
ield (Account). Double clicking results in the error,=20
> >=20
> > "Run-time error '2465': Application-Defined or object-defined error"=20
> >=20
> > I can't figure out what I have done wrong. "78" is the ID number of the=
 StatusCode for "CUST"=20
> >=20
> > Here is the code,=20
> >=20
> > Private Sub ACCOUNT_DblClick(Cancel As Integer)=20
> > Dim stSQL As String=20
> >=20
> > stSQL =3D "INSERT into Status (StatusCompany, StatusCode, StatusNotes) =
" & _=20
> >   "Values (" & Forms!ProspectForm.ProspectID & ", " & "78" & ", " & [AC=
COUNT] & ")"=20
> > End Sub=20
> >=20
> > Thanks in advance for your help,=20
> >=20
> > magmike
>=20
> Oh yeah, after I do a=20
>   Debug.print strSQL=20
> in the immediate window, I then copy the string result into the clipboard=
..  I then minimize the window, and create a new query.  Don't add any table=
s, close the table list.  Then press the SQL button paste it, and then run =
it, go into design mode, whatever.  Oftentimes you'll spot the error immedi=
ately.

When I Debug.print strSQL, I get the error of "Expected: Line Number or lab=
el..."

IT is noteworthy to say that I have never used the immediate window before,=
 so It may be something I am not doing that I should.

I run the code and the debug window comes up with the yellow arrow on "Stop=
" I then enter ">Debug.print strSQL" and hit ENTER. Then I get the error.
0
magmike
12/18/2016 2:50:36 PM
Am 18.12.2016 um 15:50 schrieb magmike:
> On Sunday, December 18, 2016 at 7:28:50 AM UTC-6, Patrick Finucane wrote:
>> On Saturday, December 17, 2016 at 9:58:53 PM UTC-5, magmike wrote:
>>> need help with the following code in the Double Click event of a form field (Account). Double clicking results in the error,
>>>
>>> "Run-time error '2465': Application-Defined or object-defined error"
>>>
>>> I can't figure out what I have done wrong. "78" is the ID number of the StatusCode for "CUST"
>>>
>>> Here is the code,
>>>
>>> Private Sub ACCOUNT_DblClick(Cancel As Integer)
>>> Dim stSQL As String
>>>
>>> stSQL = "INSERT into Status (StatusCompany, StatusCode, StatusNotes) " & _
>>>    "Values (" & Forms!ProspectForm.ProspectID & ", " & "78" & ", " & [ACCOUNT] & ")"
>>> End Sub
>>>
>>> Thanks in advance for your help,
>>>
>>> magmike
>> Oh yeah, after I do a
>>    Debug.print strSQL
>> in the immediate window, I then copy the string result into the clipboard.  I then minimize the window, and create a new query.  Don't add any tables, close the table list.  Then press the SQL button paste it, and then run it, go into design mode, whatever.  Oftentimes you'll spot the error immediately.
> When I Debug.print strSQL, I get the error of "Expected: Line Number or label..."
>
> IT is noteworthy to say that I have never used the immediate window before, so It may be something I am not doing that I should.
>
> I run the code and the debug window comes up with the yellow arrow on "Stop" I then enter ">Debug.print strSQL" and hit ENTER. Then I get the error.
Use the defined local variablename stSQL instead of strSQL!

Ulrich
0
UTF
12/18/2016 3:12:01 PM
On Sunday, December 18, 2016 at 9:10:50 AM UTC-6, Ulrich M=C3=B6ller wrote:
> Am 18.12.2016 um 15:50 schrieb magmike:
> > On Sunday, December 18, 2016 at 7:28:50 AM UTC-6, Patrick Finucane wrot=
e:
> >> On Saturday, December 17, 2016 at 9:58:53 PM UTC-5, magmike wrote:
> >>> need help with the following code in the Double Click event of a form=
 field (Account). Double clicking results in the error,
> >>>
> >>> "Run-time error '2465': Application-Defined or object-defined error"
> >>>
> >>> I can't figure out what I have done wrong. "78" is the ID number of t=
he StatusCode for "CUST"
> >>>
> >>> Here is the code,
> >>>
> >>> Private Sub ACCOUNT_DblClick(Cancel As Integer)
> >>> Dim stSQL As String
> >>>
> >>> stSQL =3D "INSERT into Status (StatusCompany, StatusCode, StatusNotes=
) " & _
> >>>    "Values (" & Forms!ProspectForm.ProspectID & ", " & "78" & ", " & =
[ACCOUNT] & ")"
> >>> End Sub
> >>>
> >>> Thanks in advance for your help,
> >>>
> >>> magmike
> >> Oh yeah, after I do a
> >>    Debug.print strSQL
> >> in the immediate window, I then copy the string result into the clipbo=
ard.  I then minimize the window, and create a new query.  Don't add any ta=
bles, close the table list.  Then press the SQL button paste it, and then r=
un it, go into design mode, whatever.  Oftentimes you'll spot the error imm=
ediately.
> > When I Debug.print strSQL, I get the error of "Expected: Line Number or=
 label..."
> >
> > IT is noteworthy to say that I have never used the immediate window bef=
ore, so It may be something I am not doing that I should.
> >
> > I run the code and the debug window comes up with the yellow arrow on "=
Stop" I then enter ">Debug.print strSQL" and hit ENTER. Then I get the erro=
r.
> Use the defined local variablename stSQL instead of strSQL!
>=20
> Ulrich

I had updated the variable to StrSQL before running the debug and got the e=
rror.=20

The Code is working now - thank you all for your help. Now if I could just =
figure out how to use the immediate window correctly!
0
magmike
12/18/2016 3:26:47 PM
On Sunday, December 18, 2016 at 10:26:49 AM UTC-5, magmike wrote:
> On Sunday, December 18, 2016 at 9:10:50 AM UTC-6, Ulrich M=C3=B6ller wrot=
e:
> > Am 18.12.2016 um 15:50 schrieb magmike:
> > > On Sunday, December 18, 2016 at 7:28:50 AM UTC-6, Patrick Finucane wr=
ote:
> > >> On Saturday, December 17, 2016 at 9:58:53 PM UTC-5, magmike wrote:
> > >>> need help with the following code in the Double Click event of a fo=
rm field (Account). Double clicking results in the error,
> > >>>
> > >>> "Run-time error '2465': Application-Defined or object-defined error=
"
> > >>>
> > >>> I can't figure out what I have done wrong. "78" is the ID number of=
 the StatusCode for "CUST"
> > >>>
> > >>> Here is the code,
> > >>>
> > >>> Private Sub ACCOUNT_DblClick(Cancel As Integer)
> > >>> Dim stSQL As String
> > >>>
> > >>> stSQL =3D "INSERT into Status (StatusCompany, StatusCode, StatusNot=
es) " & _
> > >>>    "Values (" & Forms!ProspectForm.ProspectID & ", " & "78" & ", " =
& [ACCOUNT] & ")"
> > >>> End Sub
> > >>>
> > >>> Thanks in advance for your help,
> > >>>
> > >>> magmike
> > >> Oh yeah, after I do a
> > >>    Debug.print strSQL
> > >> in the immediate window, I then copy the string result into the clip=
board.  I then minimize the window, and create a new query.  Don't add any =
tables, close the table list.  Then press the SQL button paste it, and then=
 run it, go into design mode, whatever.  Oftentimes you'll spot the error i=
mmediately.
> > > When I Debug.print strSQL, I get the error of "Expected: Line Number =
or label..."
> > >
> > > IT is noteworthy to say that I have never used the immediate window b=
efore, so It may be something I am not doing that I should.
> > >
> > > I run the code and the debug window comes up with the yellow arrow on=
 "Stop" I then enter ">Debug.print strSQL" and hit ENTER. Then I get the er=
ror.
> > Use the defined local variablename stSQL instead of strSQL!
> >=20
> > Ulrich
>=20
> I had updated the variable to StrSQL before running the debug and got the=
 error.=20
>=20
> The Code is working now - thank you all for your help. Now if I could jus=
t figure out how to use the immediate window correctly!

One thing to do is press the ribbon/menu option that has the Step options; =
Step Into, Step Over, etc.  Those step options help get you thru code.  Als=
o Debug.Print variablename.  Plus hovering your mouse over variables in you=
r code. 
0
Patrick
12/18/2016 5:36:27 PM
Reply: