By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,652 Members | 1,473 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,652 IT Pros & Developers. It's quick & easy.

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

P: 25
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.

Expand|Select|Wrap|Line Numbers
  1. 'Dim db As DAO.Database
  2. 'Dim rs As DAO.Recordset
  3. Dim stDocName As String
  4. Dim lnCompID As Long
  5.  
  6. Set db = CuurentDb
  7. Set rs = db.OpenRecordset("Table2")
  8. lnCompID = Me![ComputerID]
  9.  
  10. ' Searches table for matching computer ID.
  11. rs.FindFirst "ComputerID =" & Me![ComputerID]
  12.  
  13. ' If none exists, creates new record with current ID.
  14. If rs.NoMatch Then
  15.     rs.AddNew
  16.     rs![ComputerID] = Me![ComputerID]
  17.     rs.Update
  18.     rs.Close
  19.     Set rs = Nothing
  20. End If
  21.  
  22. ' Open the query that filters for this record,
  23. ' whether it is new or not.
  24. stDocName = "qryEditData"
  25. DoCmd.OpenQuery stDocName, acNormal, acEdit
  26.  
Nov 7 '07 #1
Share this Question
Share on Google+
10 Replies


Rabbit
Expert Mod 10K+
P: 12,324
Is ComputerID text?
Because if it is then you need:
Expand|Select|Wrap|Line Numbers
  1. rs.FindFirst "ComputerID = '" & Me![ComputerID] & "'"
  2.  
Nov 7 '07 #2

P: 25
Is ComputerID text?
Because if it is then you need:
Expand|Select|Wrap|Line Numbers
  1. rs.FindFirst "ComputerID = '" & Me![ComputerID] & "'"
  2.  
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:

Expand|Select|Wrap|Line Numbers
  1. Dim stDocName As String
  2. Dim stDocName2 As String
  3. Dim stDocName3 As String
  4.  
  5.     stDocName2 = "AppendToTable2"
  6.     stDocName3 = "UpdateDoNotAddRecord"
  7.  
  8. ' If none exists, creates new record with current ID.
  9. If Me!DoNotAddRecord = False Then
  10.     DoCmd.SetWarnings False
  11.     DoCmd.OpenQuery stDocName2, acNormal, acEdit 'appends the record
  12.     DoCmd.OpenQuery stDocName3, acNormal, acEdit 
  13.          'updates DoNotAddRecord toTrue
  14.     DoCmd.SetWarnings True
  15. End If
  16.  
  17. ' Open the query that filters for this record,
  18. ' whether it is new or not.
  19. stDocName = "QueryToEditTable2"
  20. DoCmd.OpenQuery stDocName, acNormal, acEdit
This seems to work so far.

Doug
Nov 7 '07 #3

Rabbit
Expert Mod 10K+
P: 12,324
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?
Nov 7 '07 #4

P: 25
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
Nov 8 '07 #5

Rabbit
Expert Mod 10K+
P: 12,324
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.
Nov 8 '07 #6

P: 25
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!
Nov 8 '07 #7

Rabbit
Expert Mod 10K+
P: 12,324
Object required? Does it tell you which object? Did you uncomment the Dims?
Nov 8 '07 #8

P: 25
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:
Expand|Select|Wrap|Line Numbers
  1. rs.FindFirst "ComputerID =" & Me![ComputerID]
I'll investigate this further.
Nov 8 '07 #9

P: 25
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:

Expand|Select|Wrap|Line Numbers
  1. Set rs = DBEngine(0)(0).OpenRecordset("Table2", dbOpenDynaset)
After making the change, the sub worked flawlessly.

Thank you for your time.
Nov 8 '07 #10

Rabbit
Expert Mod 10K+
P: 12,324
No problem, good luck.
Nov 8 '07 #11

Post your reply

Sign in to post your reply or Sign up for a free account.