Problems With DLookup code

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Can anybody tell me what i'm doin wrong with this code:
Private Sub PartName_AfterUpdate()
On Error GoTo Err_PartName_AfterUpdate

Dim strFilter As String

strFilter = "[PartName] = " & Me![PartName]

Me!PartNo = DLookup("PartNo", "Parts", strFilter)

Exit_PartName_AfterUpdate:
Exit Sub

Err_PartName_AfterUpdate:

MsgBox Err.Description
Resume Exit_PartName_AfterUpdate
End Sub

Its placed in the afterupdate of a combo box to update another combo box.
Originally it was copied from a simlar instance in my database which work
fine. But this gives the error message: "You canceled the previous
operation". Now i have looked up this error message in the forum and done
all the compacting a decompiling the few entries suggest. I have also started
a completely new data base and rewritten the code and all the tables and
forms from stratch. So i dont think it is corrupt. Any suggestions much
appreciated.

TIA

Rico
 
If PartName is text, you need to concatenate in some quotes also.

strFilter = "[PartName] = """ & Me![PartName] & """"
 
Thanks for the advice, the error message does not appear now, but the updated
field now returns blank. Any further thoughts?

TIA

Rico

Wayne Morgan said:
If PartName is text, you need to concatenate in some quotes also.

strFilter = "[PartName] = """ & Me![PartName] & """"

--
Wayne Morgan
MS Access MVP


rico said:
Can anybody tell me what i'm doin wrong with this code:
Private Sub PartName_AfterUpdate()
On Error GoTo Err_PartName_AfterUpdate

Dim strFilter As String

strFilter = "[PartName] = " & Me![PartName]

Me!PartNo = DLookup("PartNo", "Parts", strFilter)

Exit_PartName_AfterUpdate:
Exit Sub

Err_PartName_AfterUpdate:

MsgBox Err.Description
Resume Exit_PartName_AfterUpdate
End Sub

Its placed in the afterupdate of a combo box to update another combo box.
Originally it was copied from a simlar instance in my database which work
fine. But this gives the error message: "You canceled the previous
operation". Now i have looked up this error message in the forum and done
all the compacting a decompiling the few entries suggest. I have also
started
a completely new data base and rewritten the code and all the tables and
forms from stratch. So i dont think it is corrupt. Any suggestions much
appreciated.

TIA

Rico
Click to expand...
Click to expand...
 
For future reference, here are some guidelines that I found somewhere
in cyberspace.

DLookup Usage Samples

Note ......... that the same logic applies to most Domain Aggregate
Functions (DMax, DMin etc.)

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

Normal usage

For numerical values:
DLookup("FieldName" , "TableName" , "Criteria = n")

For strings: (note the apostrophe before and after the value)
DLookup("FieldName" , "TableName" , "Criteria= 'string'")

For dates:
DLookup("FieldName" , "TableName" , "Criteria= #date#")

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

Refering to a Form Control

For numerical values:
DLookup("FieldName", "TableName", "Criteria = " &
forms!FormName!ControlName)

For strings: (note the apostrophe before and after the value)
DLookup("FieldName", "TableName", "Criteria = '" &
forms!FormName!ControlName & "'")

For dates:
DLookup("FieldName", "TableName", "Criteria = #" &
forms!FormName!ControlName & "#")

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

Mix-n-Match

DLookup("FieldName", "TableName", "Criteria1 = " &
Forms!FormName!Control1 _
& " AND Criteria2 = '" & Forms!FormName!Control2 & "'" _
& " AND Criteria3 =#" & Forms!FormName!Control3 & "#")

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

Refering to Field Values in a Recordsource

DLookUp("[Field1] - [Field2]", "TableOrQueryName", "[CriteriaField] =
{criteria expression}")
 
DLookup will return Null if it doesn't find a value specified by the
filters. You have the code in the AfterUpdate event of the textbox that has
the name of the part. You are then searching on this name. The problem is,
the record isn't updated in the table when it is changed in the control on
the form. It is updated in the table when you save the record. This will be
done automatically when you move to another record or close the form. You
can also force a save of the record with

Me.Dirty = False

If you are changing the value of an old record, rather than creating a new
record, you could use the OldValue property of the textbox for the filter
value. This way you will be searching on the value that is in the table.
However, saving the changes to the table would probably be the better of
these two options.
 
Back
Top