RunSQl in Access with Dlookup issue.

What am I doing wrong in the following line of VBA.

DoCmd.RunSQL "update [job reports] set PLANT = " & DLookup("[plant]", "[EquipmentList]", "[No]=forms!navigationform!navigationform!jrsub!equipref") & " where [Job Number ID] =Forms![NavigationForm]![NavigationForm]![Job Number ID] "

I have this in an Afterupdate event on a form. When I run it it asks for parameter value.

Your help would be gratefully received.
SparkyPAsked:
Who is Participating?
 
Dale FyeConnect With a Mentor Commented:
You might want to try:

Plant = DLookup("[plant]", "[EquipmentList]", "[No] = '" & equipref & "'")

if equipref is a string
0
 
Ryan ChongCommented:
try this instead:

DLookup("[plant]", "[EquipmentList]", "[No]='" & forms!navigationform!navigationform!jrsub!equipref & "'")
0
 
ste5anConnect With a Mentor Senior DeveloperCommented:
Clean up your code. This helps a lot:

Dim JobNumberID As Long
Dim Plant As Long
Dim equipref As Long

equipref = forms!navigationform!navigationform!jrsub!equipref
JobNumberID = Forms![NavigationForm]![NavigationForm]![Job Number ID]
Plant = DLookup("[plant]", "[EquipmentList]", "[No] = " & equipref) 
DoCmd.RunSQL "UPDATE [job reports] SET PLANT = " & Plant & " WHERE [Job Number ID] = " & JobNumberID

Open in new window

0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
Gustav BrockCIOCommented:
PLANT is probably a string, and the navigationform expression seems incorrect, so try:

DoCmd.RunSQL "update [job reports] set PLANT = '" & DLookup("[plant]", "[EquipmentList]", "[No] = " & Forms!navigationform.form!jrsub!equipref & "") & "' where [Job Number ID] = " & Forms![NavigationForm].Form![Job Number ID] & ""

Open in new window

0
 
Dale FyeCommented:
I always find that the simpler you can make the code, the better.  For that reason alone, I'd go with ste4an's code.

The other thing I like to do is define the entire SQL string and print it before actually executing it:
strSQL = "UPDATE [job reports] SET PLANT = " & Plant & " WHERE [Job Number ID] = " & JobNumberID
debug.print strsql

Open in new window

And finally, instead of using the RunSQL method, use the Execute method and add dbFailOnError as one of the option arguments.  This will do two things:
1.  It eliminates the need for SetWarnings False / True, which is a very bad syntax to use, and
2.  It will raise an error when your SQL fails to run for any reason.
currentdb.Execute strsql, dbFailonError

Open in new window

If you are doing this inside a loop, I would declare a database variable and use that in the Execute method because there is a little bit of overhead when you use currentdb recursively.
Dim db as DAO.Database
set db = CurrentDB
db.Execute strSQL

Open in new window

0
 
ste5anSenior DeveloperCommented:
In addition to Dale's post:

Use Michael Kaplan's CurrentDbC and use a wrapper for executing SQL statements. Both in standard modules.

Option Compare Database
Option Explicit

Private m_CurrentDb As DAO.Database

Public Property Get CurrentDbC() As DAO.Database

  If m_CurrentDb Is Nothing Then
    Set m_CurrentDb = CurrentDb
  End If

  Set CurrentDbC = m_CurrentDb

End Property

Open in new window

and

Public Function SqlExecute(AStatement As String, Optional ASilent As Boolean, Optional ARecordsAffected As Long) As Boolean

  Const ccHourglass = 11
  '  Member of MSComctlLib.MousePointerConstants
  '  Hourglass mouse pointer

  On Local Error GoTo LocalError

  Dim DBEngineError As Variant
  Dim ErrorDescription As String
  Dim OldMousePointer  As Long

  OldMousePointer = Screen.MousePointer
  Screen.MousePointer = ccHourglass
  SqlExecute = False
  CurrentDbC.Execute AStatement, dbFailOnError Or dbSeeChanges
  ARecordsAffected = CurrentDbC.RecordsAffected
  SqlExecute = True
  Screen.MousePointer = OldMousePointer
  Exit Function

LocalError:
  Screen.MousePointer = OldMousePointer
  Debug.Print "Fehler: " & "Fehler währende der Ausführung von SQLExecute."
  Debug.Print "Quelle: " & Err.Source
  Debug.Print "Nummer: &H" & Hex(Err.Number)
  ErrorDescription = Err.Description
  If DBEngine.Errors.Count > 0 Then
    ErrorDescription = ErrorDescription & vbCrLf
  End If
  
  For Each DBEngineError In DBEngine.Errors
    If DBEngineError <> Err.Description And InStr(DBEngineError, "Batch") = 0 Then
      ErrorDescription = ErrorDescription & vbCrLf & DBEngineError
    End If
  Next DBEngineError
  
  Debug.Print "Beschreibung: " & ErrorDescription
  Debug.Print "SQL: " & AStatement
  If Not ASilent Then
     FormMsgBox _
       "Fehler währende der Ausführung von SQLExecute." & vbCrLf & vbCrLf & _
       "Drücken Sie Nein, um den SQL Befehl anzuzeigen." & vbCrLf & vbCrLf & _
       "Quelle: " & Err.Source & vbCrLf & _
       "Nummer: &H" & Hex(Err.Number) & vbCrLf & vbCrLf & _
       "Beschreibung: " & ErrorDescription, _
       vbCritical + vbYesNo
  End If

End Function

Open in new window


dbSeeChanges is also necessary.
0
 
SparkyPAuthor Commented:
Hi Ste5an/Dale

If I use the code, it throws a type mismatch error in Plant = DLookup("[plant]", "[EquipmentList]", "[No] = " & EquipRef)

I replaced Dim Plant as Long with String (as Plant is the equipment description), but this took me back to my original problem where a parameter box appears (the box is based on the lookup field).

Please excuse my lack of knowledge.
0
 
Dale FyeCommented:
Working with navigation forms sucks!

The general syntax for referring to a control on a subform looks like:
X = Forms!FormName!subFormControlName!Form.controlName

Open in new window

but it looks like you may actually have a subform embedded within a subform, so that syntax would change to:
X = Forms!FormName!subFormControlName!Form!2ndSubformControlName!Form.controlName

Open in new window

In your case, I think this might look like:
forms!navigationform!navigationform!FORM!jrsub!FORM!equipref

Open in new window

Try opening your navigation form to the appropriate screen and then open the VBE window and try to print out the value in the immediate window.
debug.print forms!navigationform!navigationform!FORM!jrsub!FORM!equipref

Open in new window

0
 
SparkyPAuthor Commented:
Wow Ste5an

Some code on your last post!!

Not sure if relevant. If I type data into the parameter value box that data appears in the table, if I cancel out I get an error 3270 "Reserve error"
0
 
ste5anSenior DeveloperCommented:
Did you inspect the SQL string as Dale wrote? When string content is involved, you need to escape it.

E.g.

Dim JobNumberID As Long
Dim Plant As String
Dim equipref As Long
Dim Sql As String

equipref = forms!navigationform!navigationform!jrsub!equipref
JobNumberID = Forms![NavigationForm]![NavigationForm]![Job Number ID]
Plant = DLookup("[plant]", "[EquipmentList]", "[No] = " & equipref)
Sql "UPDATE [job reports] SET PLANT = " & SqlQuote(Plant) & " WHERE [Job Number ID] = " & JobNumberID
CurrentDbC.Execute Sql, dbFailOnError Or dbSeeChanges

Open in new window


With

Public Function SqlQuote(AText As String, Optional ADelimiter As String = "'") As String

  SqlQuote = ADelimiter & Replace(AText, ADelimiter, ADelimiter & ADelimiter) & ADelimiter

End Function

Open in new window

0
 
SparkyPAuthor Commented:
Hi Dale

If I put the code in with the 2 FORM it tells me it cannot find the field Form. Take them both out and I get the correct equipment No.
0
 
SparkyPAuthor Commented:
Sorry Dale

I get data mismatch with the string code.
0
 
SparkyPAuthor Commented:
Just a tweek witch worked (not sure why but:


Dim tJobNumberID As Long
Dim tPlant As String
Dim tEquipRef As Long

DoCmd.SetWarnings False
'tEquipRef = Forms!NavigationForm!NavigationForm!JRSUB!EquipRef
tJobNumberID = Forms![NavigationForm]![NavigationForm]![Job Number ID]
tPlant = DLookup("[plant]", "[EquipmentList]", "[No] = Forms!NavigationForm!NavigationForm!JRSUB!EquipRef")

DoCmd.RunSQL "UPDATE [job reports] SET PLANT = '" & tPlant & "' WHERE [Job Number ID] = " & tJobNumberID

DoCmd.SetWarnings True
0
 
Dale FyeCommented:
can you post the code you are using now?  and where the code is failing.

it helps, in these threads, if you would indicate which comment you are replying to, as sometimes they overlap
0
 
Dale FyeCommented:
You totally ignored the recommendations to get rid of docmd.SetWarnings and replace it with the Execute method.

This is a recipe for disaster, and will not advise you when your SQL statement fails to execute as you expected.
0
 
ste5anSenior DeveloperCommented:
As Dale and I wrote: Strings must be escaped, when used to concatenate SQL statements.

CAVEAT: Your solutions still carries the possibility to fail, much worse it allows SQL injection. Thus either use your code with Replace() added

DoCmd.RunSQL _
  "UPDATE [job reports] SET PLANT = '" & Replace(tPlant, "'", "''") & _
  "' WHERE [Job Number ID] = " & tJobNumberID

Open in new window

or easier to read

DoCmd.RunSQL _
  "UPDATE [job reports] SET PLANT = " & SqlQuote(tPlant) & _
  " WHERE [Job Number ID] = " & tJobNumberID

Open in new window

and lesser quotes to count..

Consider plant contains "O'Donell"..
1
 
SparkyPAuthor Commented:
Really appreciate both of your attention to detail.

Have a good weekend.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.