"Type mismatch" error occurs, can't find cause

[dstorms]

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
 

[Rabbit]

Is ComputerID text?
Because if it is then you need:

rs.FindFirst "ComputerID = '" & Me![ComputerID] & "'"
 

[dstorms]

Is ComputerID text?
Because if it is then you need:

rs.FindFirst "ComputerID = '" & Me![ComputerID] & "'"
 

No, it's a long integer.
In Table 1 it is set by AutoNumber, and in Table 2 it is entered into a Long Integer. Would that have an effect?

I'm trying out this method instead:
1. Add a new field in Table 1 (Yes/No) labeled DoNotAddRecord
2. Run an update query to mark all previous records to Yes.
3. Modify the update query to limit it to the active record in the form
4. Create an append query to add new records to Table 2 using ithe ComputerID in the form's active record.
5. Then set the code to the following:

Dim stDocName As String
Dim stDocName2 As String
Dim stDocName3 As String
 
    stDocName2 = "AppendToTable2"
    stDocName3 = "UpdateDoNotAddRecord"
 
' If none exists, creates new record with current ID.
If Me!DoNotAddRecord = False Then
    DoCmd.SetWarnings False
    DoCmd.OpenQuery stDocName2, acNormal, acEdit 'appends the record
    DoCmd.OpenQuery stDocName3, acNormal, acEdit 
         'updates DoNotAddRecord toTrue
    DoCmd.SetWarnings True
End If
 
' Open the query that filters for this record,
' whether it is new or not.
stDocName = "QueryToEditTable2"
DoCmd.OpenQuery stDocName, acNormal, acEdit

This seems to work so far.

Doug

[Rabbit]

If it works then that's good. But if you still want to use the first version, we can try to figure out where it's throwing the error. Does it tell you which line of code is throwing the error?

[dstorms]

If it works then that's good. But if you still want to use the first version, we can try to figure out where it's throwing the error. Does it tell you which line of code is throwing the error?

No it doesn't. Is there a way to find out?

I would prefer to use the first version if I could get it to work.

Also as an aside I've commented out the first two lines because when I executed the code I recieved a "compile error: user-defined type not defined." It runs fine without Dims set. Do you have any idea why that happens?

Doug

[Rabbit]

Type not defined means that whatever data type you're trying to use, it can't find. Which most likely means that you don't have a reference to the Microsoft DAO Object Library.

[dstorms]

If it's not one thing, it's another. I added the reference to the DAO 3.6 library (ADO is unchecked), and now I'm getting an "object required" message. What's going on here?

At least I'm getting a crash course in Access and VBA programming!

[Rabbit]

Object required? Does it tell you which object? Did you uncomment the Dims?

[dstorms]

Does it tell you which object? Did you uncomment the Dims?

No. Yes.

By commenting out just about every line, I discovered that I had misspelled "CurrentDb."

Affter uncommenting all the lines I got another error message:

"Operation is not supported for this type of object."

I narrowed it down to this line:

rs.FindFirst "ComputerID =" & Me![ComputerID]

I'll investigate this further.

[dstorms]

Oh, Rabbit,

Google is my friend! After digging through several sites, I found the solution in an old post at another site. There I learned that this is how you set the recordset with a DAO reference:

Set rs = DBEngine(0)(0).OpenRecordset("Table2", dbOpenDynaset)

After making the change, the sub worked flawlessly.

Thank you for your time.

[Rabbit]

No problem, good luck.