473,854 Members | 1,495 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

25 New Member

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
  6. Set db = CuurentDb
  7. Set rs = db.OpenRecordset("Table2")
  8. lnCompID = Me![ComputerID]
  10. ' Searches table for matching computer ID.
  11. rs.FindFirst "ComputerID =" & Me![ComputerID]
  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
  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
Nov 7 '07 #1
10 4593
12,516 Recognized Expert Moderator MVP
Is ComputerID text?
Because if it is then you need:
Expand|Select|Wrap|Line Numbers
  1. rs.FindFirst "ComputerID = '" & Me![ComputerID] & "'"
Nov 7 '07 #2
25 New Member
Is ComputerID text?
Because if it is then you need:
Expand|Select|Wrap|Line Numbers
  1. 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:

Expand|Select|Wrap|Line Numbers
  1. Dim stDocName As String
  2. Dim stDocName2 As String
  3. Dim stDocName3 As String
  5.     stDocName2 = "AppendToTable2"
  6.     stDocName3 = "UpdateDoNotAddRecord"
  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
  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.

Nov 7 '07 #3
12,516 Recognized Expert Moderator MVP
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
25 New Member
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?

Nov 8 '07 #5
12,516 Recognized Expert Moderator MVP
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
25 New Member
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
12,516 Recognized Expert Moderator MVP
Object required? Does it tell you which object? Did you uncomment the Dims?
Nov 8 '07 #8
25 New Member
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
25 New Member
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

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

Similar topics

by: Lou Pecora | last post by:
g++ compiler error question. I have a container C whose constructor takes a class B that is inherited from an abstract class A. So I have the line of code: B binstance; C cinstance(binstance); The compiler gives the error,
by: Andrew Proctor | last post by:
Hello This is not a big problem, but I was hoping someone a bit more knowlegable than I could explain something to me. I have a simple function behind a form which writes changed values to a table for tracking purposes : Public Sub RecordChanges() Dim strInsertValue As String strInsertValue = "INSERT INTO
by: hamstak | last post by:
While attempting to perform a build on an .aspx page from within VS 2005 I receive the "Could not load type" error pertaining to the class representing the page. The class is derived from a custom base class (which itself is derived from System.Web.UI.Page). This custom base class has been used in other projects successfully. The Inherits attribute of the @Page directive is properly assigned and the application root is properly...
by: Davros9 | last post by:
Trying to get Regular Expressions working....... ---------------- Public Function SepString(InField As String) As String ''seperates on space and comma Dim RE As New RegExp Dim Matches As Match RE.IgnoreCase = True
by: eros | last post by:
ALTER TABLE public.postcodes ALTER COLUMN machi TYPE varchar(100); Error: ERROR: syntax error at or near "TYPE"; Error while executing the query (State:42601, Native Code: 7) I am using WinSQL, Windows XP SP2 Japanese Version, PostgreSQL 8.x. I want to alter my machi field from varchar(30) to varchar(100) TYPE. This is my CREATE TABLE script:
by: bxscikid | last post by:
I am using VB 6 in order to clear a listbox (lststuff) containing material entered by the user via input boxes. I am trying to utilize a command button which will clear the list for the user. When testing the program, as soon as i click on the command button I receive a "Type mismatch" error. How can I solve this? This is the code I am using: Private Sub cmdclear_Click(Index As Integer) Dim warning As String warning =...
by: tejesh | last post by:
I am trying to compile the following code int backend_sm_run(struct interface_data *ctx) { xsup_assert((ctx != NULL), "ctx != NULL", TRUE); xsup_assert((ctx->statemachine != NULL), "ctx->statemachine != NULL", TRUE); backend_sm_check_globals(check);
by: =?Utf-8?B?U3dhcHB5?= | last post by:
hi, I am working on application in this i am using two files. In first (consider A) file i am calling the function of other file (consider B). In that function of file B i am calling the method of DLL. i.e Method of file A -Method of file B -Method of DLL. But in the file B's method while calling DLL's method it is giving me Error "Object does not match target type.".
by: DonRayner | last post by:
This one has me stumped. I'm getting a "Type Mismatch" error on one of my forms when it's being opened. It's hapening before the forms "On Open" event, I stuck a msgbox in there to check and I'm getting the error before it opens. The line of code that calls the form from another form is. DoCmd.OpenForm "NonConformanceAdd",,,,acFormAdd,acDialog I get the error, click ok, then the form opens and works exactly how it's supposed to. I even...
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.