Find record, add record or do nothing

[Hafa1961]

I have an AfterUpdate code that uses the DLookup function to find the matching record in a corresponding table.

If the matching record exists, the field is changed to the correct value based on DLookup of corresponding table.

Here is my problem:
If matching record does not exists the current data (or is not found in the table) is removed/erased.

I need the current data that was entered not to be erased. Maybe something like:
If matching record does not exists ask user do they wish to change/overide the current data (Yes/No Message Box):

If Yes, removed the current data and Me.ConsigneeID.SetFocus
If No, leave data unchanged.


Any help would be appreciated….

‘-----------------------------------------------------
Private Sub SpecNo_AfterUpdate()
On Error GoTo Err_SpecNo_AfterUpdate

Dim strFilter As String
strFilter = "CODE = " & Me!SpecNo


Me!ConsigneeID = DLookup("CENTRE", "CodeLook", strFilter)

If IsNull(DLookup("CENTRE", "CodeLook", strFilter)) Then
MsgBox "Match Not Found. Continue or Enter a vaild Cost Centre?", vbOKOnly + vbInformation, "Default Consignee"

Else
MsgBox "Match Found", vbInformation, "Default Consignee"
End If


Exit_SpecNo_AfterUpdate:
Exit Sub

Err_SpecNo_AfterUpdate:
MsgBox Err.Description
Resume Exit_SpecNo_AfterUpdate

End Sub

‘--------------------------------------------------------

Any help would be appreciated….

 

[Remou]

How about:

Private Sub SpecNo_AfterUpdate()
On Error GoTo Err_SpecNo_AfterUpdate

Dim strFilter As String
strFilter = "CODE = " & Me!SpecNo

FoundConsigneeID = DLookup("CENTRE", "CodeLook", strFilter)

If Trim(FoundConsigneeID & " ")="" Then
  If MsgBox ("Match Not Found. Keep current Cost Centre?", vbOKYesNo + vbInformation, "Default Consignee") = vbNo Then
    Me!ConsigneeID=""
  End If
Else
  MsgBox "Match Found", vbInformation, "Default Consignee"
  Me!ConsigneeID=FoundConsigneeID 
End If

Exit_SpecNo_AfterUpdate:
Exit Sub

Err_SpecNo_AfterUpdate:
MsgBox Err.Description
Resume Exit_SpecNo_AfterUpdate

End Sub

 

[Hafa1961]

Thanks Remou

But I am getting a "Invalid use of Null" error

Appreciate your help....

 

[Remou]

Which line gives the error?

I have a typo in this line:

[tt]If MsgBox ("Match Not Found. Keep current Cost Centre?", vbOKYesNo + vbInformation, "Default Consignee") = vbNo Then
Me!ConsigneeID=""[/tt]

That is, it should read vbYesNo.

 

[Hafa1961]

Yes, I corrected that part already.

When a number is entered in the SpecNo field (e.g. a number that would not have a match (DLookup)in the table CodeLook)the "Invalid use of Null" error occurs.

When a text is entered "You canceled the previous operarion" error occurs.

When a number is entered in the SpecNo field with a matching DLookUp the code runs OK.

ConsigneeID is numerical
SpecNo is Text
CENTRE is Text
CODE is Text

'----------------------------------------------------------
Private Sub SpecNo_AfterUpdate()
On Error GoTo Err_SpecNo_AfterUpdate

Dim strFilter As String
Dim FoundConsigneeID As String
strFilter = "CODE = " & Me!SpecNo

FoundConsigneeID = DLookup("CENTRE", "CodeLook", strFilter)

If Trim(FoundConsigneeID & "") = "" Then
If MsgBox("Match Not Found. Keep current Cost Centre?", vbYesNo + vbInformation, "Default Consignee") = vbNo Then
Me!ConsigneeID = ""
End If
Else
MsgBox "Match Found", vbInformation, "Default Consignee"
Me!ConsigneeID = FoundConsigneeID
End If

Exit_SpecNo_AfterUpdate:
Exit Sub

Err_SpecNo_AfterUpdate:
MsgBox Err.Description
Resume Exit_SpecNo_AfterUpdate


End Sub
'----------------------------------------------------------

Thanks Again,

 

[Remou]

Firstly, you will need:

[tt]strFilter = "CODE = '" & Me!SpecNo & "'"[/tt]

Text fields need quotes.

Secondly, in your original code you say:

[tt]Me!ConsigneeID = DLookup("CENTRE", "CodeLook", strFilter)[/tt]

Which makes ConsigneeID = CENTRE. However, you also say that Centre is text and ConsigneeID is numeric, so is this what you want?

 

[ssatech]

Try this:

FoundConsigneeID = Nz(DLookup("CENTRE", "CodeLook", strFilter))

If Trim(FoundConsigneeID & "") = "" Then
MsgBox "Match Not Found. Keep current Cost Centre?", vbOKOnly + vbInformation, "Default Consignee"

Else
MsgBox "Match Found", vbInformation, "Default Consignee"
Me!ConsigneeID = FoundConsigneeID
End If