473,322 Members | 1,781 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

can't find field

I'm trying to update a table using SQL but I'm Getting an error message saying that access can't find the field 'l' referred to in your expression. I'm sure I've got my quotes wrong but can't seem to get it worked out.

Here's my code:
Expand|Select|Wrap|Line Numbers
  1. Dim db As DAO.Database
  2.         Dim strSQL As String
  3.         strSQL = "UPDATE tblTrucks" _
  4.         & "SET tblTrucks.ReplaceSpeedometer = False" _
  5.         & "WHERE ((tblTrucks.TruckID)= '" & [Me].[TruckID] & "'" & ");"
  6.  
  7.         Set db = DBEngine(0)(0)
  8.         db.Execute strSQL, dbFailOnError
  9.         Set db = Nothing
  10.  
Thanks for your help!
Dec 10 '13 #1

✓ answered by zmbd

[Me].[TruckID]
to
Me![TruckID]
if TruckID is part of the bound recordset of the form

8 1377
Seth Schrock
2,965 Expert 2GB
The only issue that I see is that you don't have any spaces to go between the table name and the key word "Set". If you put a Debug.Print strSQL in line 6, it will show you what the broken string concatenates into. This is a really good debugging tool that is useful to know. Anyway, try this
Expand|Select|Wrap|Line Numbers
  1.         strSQL = "UPDATE tblTrucks " _
  2.         & "SET tblTrucks.ReplaceSpeedometer = False " _
  3.         & "WHERE ((tblTrucks.TruckID)= '" & [Me].[TruckID] & "');"
Dec 10 '13 #2
I added the spaces but that didn't help.
I also added the debug.print strSQL at line 6 but it stalls before going to line 6.
I verified the spelling of the field name and also added brackets and ! in place of the period. Still getting the same error.
Thanks again!
Dec 10 '13 #3
ADezii
8,834 Expert 8TB
  1. If TruckID is a String:
    Expand|Select|Wrap|Line Numbers
    1. strsql = "UPDATE tblTrucks SET tblTrucks.ReplaceSpeedometer = False " _
    2.         & "WHERE tblTrucks.TruckID = '" & [Me].[TruckID] & "'"
  2. If TruckID is Numeric:
    Expand|Select|Wrap|Line Numbers
    1. strsql = "UPDATE tblTrucks SET tblTrucks.ReplaceSpeedometer = False " _
    2.         & "WHERE tblTrucks.TruckID = " & [Me].[TruckID]
Dec 10 '13 #4
Still getting the same error. I tried with and without the ending semicolon. I also tried brackets around the table field names, all with the same results.
Dec 11 '13 #5
Seth Schrock
2,965 Expert 2GB
Which line does it highlight when you get the error? If you are getting the error before it gets to the debug.print line, then you either have code above what you have shown, or it is a compile error before the code actually runs as the strSQL variable wouldn't be checked to see if it is a valid SQL string until after the debug.print during the execution of the query.
Dec 11 '13 #6
Here is the entire code:
Expand|Select|Wrap|Line Numbers
  1. If DLookup("ReplaceSpeedometer", "tblTrucks", _
  2.             "TruckID='" & Forms!frmTrips!TruckID & "'") = True Then
  3.  
  4.  
  5.     Dim db As DAO.Database
  6.         Dim strSQL As String
  7.         strSQL = "UPDATE tblTrucks SET tblTrucks.ReplaceSpeedometer = False " & _
  8.             "WHERE ((tblTrucks.TruckID) = '" & [Me].[TruckID] & "');"
  9.         Debug.Print strSQL
  10.         Set db = DBEngine(0)(0)
  11.         db.Execute strSQL, dbFailOnError
  12.         Set db = Nothing
  13.  
  14.     End If
All of the strSQL lines are highlighted when it fails.
Dec 11 '13 #7
zmbd
5,501 Expert Mod 4TB
[Me].[TruckID]
to
Me![TruckID]
if TruckID is part of the bound recordset of the form
Dec 11 '13 #8
Thanks! That did it.
Dec 11 '13 #9

Sign in to post your reply or Sign up for a free account.

Similar topics

1
by: Daniel Bello Urizarri | last post by:
Hello: I have a web service that runs well on ASP 1.0. Now I'm migrating it to ASP 2.0, and now im at the point in wich the project runs under the ASP Developer server od the visual studio 2005,...
2
by: yasin | last post by:
I have to refresh my site every 5 secs but I cant find my sites meta tag in my site I think there is no meta tag clearly pages includes masterpages. How can I access the meta tag of my page whit...
1
by: Eric | last post by:
This program is running fine in my office i just copy it and run at home it start given that error and highlight Chr(13). I dont know is this is what the error is or some thing else Compiler...
4
by: Bruce Skamser | last post by:
I added a field to a table and when I go to the form to add a textbox control, I can't bind it to the field in the table. It doesn't come up in the list of fields in the control source dropdown. ...
1
by: niraj | last post by:
I am getting the following error /usr/bin/ld: cant find -lutl Please advice which module i need to install .I am unable to find it . TIA Niraj
2
by: andrewanderson | last post by:
hi can anyone help me with this prog. cant find the prob why it cant display cout<<"This is the display of your transaction"<<endl; ifstream fobj; //declare input file stream ...
1
by: foocc | last post by:
Hi, im trying to convert data reports (.dsr) in vb6 to crystal reports in vb.net. However, I cant seems to find it in my Microsoft Visual Studio 2005 Tools for Office. I've tried going to...
4
by: gawasthi | last post by:
Hi, I am absolutely new to perl and for a specific reqmt I am running the following script: #!/usr/bin/perl use warnings; use lib '/opt/InCharge65/IP/smarts/perl/5.6.1/lib'; use...
1
by: figaro | last post by:
Hi All, I have developed an application in .net, now the thing is I have to display the data on a report. I thought of using a crystal report. Here I understand that I have to add Crystal...
11
by: dowlingm815 | last post by:
the following code is generating an error; Mircosoft Access cant find the field '|" referred to in your expression" any suggestions would be appreciated. Private Sub...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.