Lookup value in table

[ya5irha55an]

Hi,

I have a form which is linked to a table. The table contains the field 'Code'. The form is used for entering data into the table. I would like to write some code so that when the 'Code' field is filled in, it checks if this code already exists in the table, and if it does a message comes up and blocks the entry of the data in 'code', otherwise it allows the code to be entered.

I am familiar with dlookup, but have not used it in a vba code before- i am guessing that this is the right function to get what i want. Can anyone help?

 

[*Pete*]

Hi, Your best bet if Possible would to make your Code field a Primary Key in the table.

If not a primary key, then set the field propeties of Indexed in the table to Yes (No Duplicates)

Regards

Pete

 

[Bogzla]

Hey, I wrote a sub to do this recently, which will check the value as soon as you have finished entering it on the form

assuming the field in your table is of type 'text' and the box on your form is called 'txtCode' this should work (obiously, substitute YourFormName, YourFieldName and YourTableName for whatever names they have in your DB )

Private Sub txtCode_LostFocus()
    On Error GoTo Err_txtCode_LostFocus

Dim strCode As String

    '1st check txtCode box on form is not empty, if so complain and send user back
    If Len(Forms![YourFormName]!txtCode) = 0 Then
        Me.txtCode.SetFocus
        MsgBox "Please enter a value"
        Resume Exit_txtCode_LostFocus
           
    'Otherwise assign strCode variable with value - if the value in txtCode already exists in your table it is assigned to the variable,
    'otherwise  it is assigned '0' - if there is a possibilty of '0' being entered, change this to something that won't be..
    Else
        strCode = Nz(DLookup("[YourFieldName]", "[YourTableName]", "[YourFieldName] = '" & Forms![YourFormName]!txtCode & "'"), "0")

            If strCode = "0" Then
                'call the sub or function that sends the value in txtCode to your table
            Else
                MsgBox "This value has already been entered"
                'code for anything else you would like to do if the value is duplicate goes in here
                Resume Exit_txtCode_LostFocus
            End If
    End If

Exit_txtCode_LostFocus:
    Exit Sub
Err_txtCode_LostFocus:
Select Case Err.Number
    Case 20 'suppress "close with no error" messages
        Resume Next
    Case Else
    MsgBox Err.Description
    Resume Exit_txtCode_LostFocus
End Select
End Sub

Hope this is of some use..

Regards,
Bogzla

 

[Pat Hartman]

LostFocus is not the appropriate event to use for two reasons.
1. This event will run if the user simply tabs through the field.
2. This code will not prevent the record from being saved with bad data.

Edit code should be placed in the field's BeforeUpdate event and if an error is found, the update event should be cancelled with:

Cancel = True

You will not need to set focus back to the field with the error because cancelling the update event will prevent the cursor from leaving the field.

 

[Bogzla]

Pat, thanks, I somehow missed the 'linked to a table' bit. Heat must be getting to me

Edit - problem below is due to null handling problems, I found
'If Len(Nz(Forms![YourFormName]!txtCode, "")) = 0'
seems to work if value in field is deleted. This won't happen if it is just left blank, however, as BeforeUpdate isn't called...

[Only thing with BeforeUpdate is I can't seem to get a prompt to enter something if the field is left blank.. why might
'If Len(Forms![YourFormName]!txtCode) = 0'
not be triggering?
I can set the Required setting to true in the original table but this only seems to trigger a message when the whole record is updated

Then again this is only necessary if the field must have a value]

(I use the above code with an undo action to clear the form if a duplicate value is entered - but my form is not bound and uses SQL to update)

ya5irha55an, Sorry if I misled you, gotta remember to check my assumptions..

regards,
Bogzla

 

[Pat Hartman]

If you need to check for null values, do it in the FORM's BeforeUpdate event. There is no way to do this at the control level since the user may never tab into the field. It is also best to define required fields at the table level.