Searching tables

[abbyanu]

Hi... I need some help writing some code:
I have two tables: Process (primary key: Process ID e.g. PR01) and Accounts (primary key: Account ID e.g. PR).
When entering a new process, I wish to make sure that it is valid, in other words, the first two characters of Process ID must match an Account ID. If it doesn't match, the system should generate an error message and make sure that the input is not accepted unless valid.

Is there a way of doing this?

I tried modifying the code below, but apparently it doesn't differentiate valid and invalid entries:

[tt]
Dim Db As DAO.Database
Dim rsTable As DAO.Recordset
Dim MyString As String

Set Db = CurrentDb
Set rsTable = Db.OpenRecordset("Accounts&quot

MyString = Left(Me![Process ID], 2)

Set rsTable = Db.OpenRecordset("Select * FROM [Accounts] WHERE [Account ID] = '" & MyString & "'&quot

If Not rsTable.RecordCount > 0 Then 'Duplicates have not been found
Me![Account] = Left(Me![Process ID], 2)
Else
GoTo EntityID_Err
End If

rsTable.Close

EntityID_Err:
MsgBox "Invalid Entity Process ID."
'Want it to then return to same textbox
'so that user doesn't proceed until
'error corrected
'How can I do this?
End Sub

[/tt]

Many thanks for your assistance.

Sincerely,
Abbyanu.

 

[KarMac21]

Give this a shot Abbyanu:

If IsNull(DLookUp("Account ID","Accounts","[Account ID] = '" & Left(Me![Process ID], 2) & "'&quot) then

MsgBox "Invalid Entity Process ID."
Me![Process ID].SetFocus

End If

Enter this code into Either the LostFocus or AfterUpdate actions in your Process ID field and the user won't be able to leave that field until valid data is entered into it.

Hope this helps,
Shane

 

[abbyanu]

Thanks, Shane

Worked like charm, with just a little more tweaking.

Peace and God bless!

Abbyanu.