Code to look for and match a text string in a field

[deejow]

Hi,

In a new record in a "Jobs" table, I enter the "ClaimantID" (the person who this job involves) but I want to know whether or not this particular Claimant has had jobs done on them previously, and if so, who the person responsible for working on that job was (found in a separate field called "PI").

Can I write code that asks the database to:
- search for and match a ClaimantID that I type in a text box in a form
- output who the PI (found in a different field) that worked on that previous job was
- sometimes a claimant might have several previous jobs

I will pay $50(au) to anyone's BSB account if they help me with this code. I have been working on this for too long and I am over it. I am not a programmer but have been forced into a situation where I need some code because Access is not capable of such a function without it.

deejow

[barry07]

I do this sort of thing quite often using the Dlookup function. Here's a sample that should work for you - I've used Message boxes to return the result of the lookup but obviously you can change this to call whatever process is appropriate in your case. If you want to determine the number of previous records for this claimant you can use Dcount with the same syntax.

Using nz forces the return of a null string in the event the lookup fails and prevents the code falling over.

<Code(vb)>
Sub txtClaimID_AfterUpdate
Dim sPI as string
sPI=nz(Dlookup("PI","Jobs","ClaimantID='" & txtClaimID &"'"),"")
IF sPI>"" then
Msgbox "Previous PI=" & sPI & " for this claimant"
Else
Msgbox "No previous record for this claimant"
End if
End Sub
</Code>

[deejow]

I do this sort of thing quite often using the Dlookup function. Here's a sample that should work for you - I've used Message boxes to return the result of the lookup but obviously you can change this to call whatever process is appropriate in your case. If you want to determine the number of previous records for this claimant you can use Dcount with the same syntax.

Using nz forces the return of a null string in the event the lookup fails and prevents the code falling over.

<Code(vb)>
Sub txtClaimID_AfterUpdate
Dim sPI as string
sPI=nz(Dlookup("PI","Jobs","ClaimantID='" & txtClaimID &"'"),"")
IF sPI>"" then
Msgbox "Previous PI=" & sPI & " for this claimant"
Else
Msgbox "No previous record for this claimant"
End if
End Sub
</Code>

Thnx for reply. What is the Access equivalent for this code and where do I put it?

deejow