Hi,
I'm trying to create a button on a form that:
1. Takes the ComputerID from the form linked to Table 1,
2. Checks Table 2 for a matching ComputerID, and
3. Opens the query qryEditData, and
4. If no match can be found, adds a new record in Table 2 and enters the ComputerID automatcially.
The goal is to create a new record in Table 2 when a new record is created in table 1 and have the ComputerID fields match with a 1-to-1 relationship. Yes, it would be easier to have them in one table, and they originally were, but when I'm trying to create conditional field-level security in a multiuser environment I have to be a little creative:
Table 1 would be read-write, Table 2 would be read-only, except through qryEditData, a query with OWNERACCESS OPTION and limtited to the current form ComputerID and a True/False criteria set to False. Eventually I'll add a MsgBox prompt if the True/False field is set to True.
So the uesr clicks on the button,
If no record exists, it creates a new record, then opens the qryEditData query.
If the record exists and set to False, it opens the query.
If the record exists and set to True, it disploays a MsgBox instead.
Unfortunately, when clicking the button, I get a "Type Mismatch" error.
I'm using Access 2003 with Windows XP. The database itself still in 2000 format, however. It was created over 5 years ago. This is part of the project to convert it to a multiuser database because more people wanted access and set limits to different parties. It's going great, and this seems to be the only problem I have left to solve.
-
'Dim db As DAO.Database
-
'Dim rs As DAO.Recordset
-
Dim stDocName As String
-
Dim lnCompID As Long
-
-
Set db = CuurentDb
-
Set rs = db.OpenRecordset("Table2")
-
lnCompID = Me![ComputerID]
-
-
' Searches table for matching computer ID.
-
rs.FindFirst "ComputerID =" & Me![ComputerID]
-
-
' If none exists, creates new record with current ID.
-
If rs.NoMatch Then
-
rs.AddNew
-
rs![ComputerID] = Me![ComputerID]
-
rs.Update
-
rs.Close
-
Set rs = Nothing
-
End If
-
-
' Open the query that filters for this record,
-
' whether it is new or not.
-
stDocName = "qryEditData"
-
DoCmd.OpenQuery stDocName, acNormal, acEdit
-
