Hello.

I am creating an Access 2000 database to keep track of employee's Ergonomic Information and their related Cubicle Information. The database has five tables:
tbl_Cubicle_Info
tbl_Emp_Data
tbl_Ergo_Info
tbl_Keyboard
tbl_Pointing_Device

I want ot be able to enter either an Employee Number, First Name, or Last Name on a form and click on a "Search Button" and have the related Ergonomic Info and the Cubicle Info displayed on two seperate subforms.

I have seen this in an Access 2 database. I tried coping the code into my database but some of the code isn't recognized by 2000.

Here is the code:

Private Sub cmdSearch_Click()
On Error GoTo cmdSearchErrorTrap
Dim MySQL As String, MyCriteria As String, MyRecordSource As String
Dim ArgCount As Integer
Dim Tmp As Variant
ArgCount = 0
MySQL = "Select * from [Employee Data] where "
MyCriteria = ""
AddToWhere [Last Name], "[Last Name]", MyCriteria, ArgCount
AddToWhere [First Name], "[First Name]", MyCriteria, ArgCount
AddToWhere [Employee Number], "[Employee Number]", MyCriteria, ArgCount
AddToWhere [User ID], "[User ID]", MyCriteria, ArgCount
AddToWhere [Phone ID], "[Phone ID]", MyCriteria, ArgCount
AddToWhere [Initials], "[Initials]", MyCriteria, ArgCount
If MyCriteria = "" Then
MyCriteria = "True"
End If
MyRecordSource = MySQL & MyCriteria
Me!frmEmpInf.Form.RecordSource = MyRecordSource
If Me!frmEmpInf.Form.RecordsetClone.RecordCount = 0 Then
MsgBox "No records match the criteria you entered.", 48, "No Records Found"
Me!cmdClear.SetFocus
Else
'Tmp = EnableControls("Detail", True)
Me!frmEmpInf.SetFocus
End If
cmdSearchResume:
Exit Sub
cmdSearchErrorTrap:
MsgBox Error$
Resume cmdSearchResume
End Sub

The error

Compile error:
Sub or Function not defined

comes in at the first AddToWhere line. I noticed that for his database he has all of the data coming from one table.

I have little to no experience with VB or VBA. Is there a way to make this work without having to consolidate all of my data into one table?

The Sub "AddtoWhere" is not an Access pre-defined sub. It's a user-defined sub that someone programmed to work with the code you listed. You cut and pasted your code into your form, but you forgot to ALSO cut and past the Sub AddtoWhere into your code, too.


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division