473,324 Members | 2,567 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

Check if record exist in table Not working

Jerry Maiapu
259 100+
Hello every one.

I have spend an humble time - 4-5 months far away from city and could not get into this forum. ( no internet)

I am now back into programming trying to create a small application to check if a number entered on a unbound form exist in the table. However the following code generates
runtime error "3464" - Data mismatch in Criteria expression.

Could someone figure out what would be the problem.

I am using Access 97


Expand|Select|Wrap|Line Numbers
  1.  
  2. Dim strSQL          As String
  3.   Dim db            As DAO.Database
  4.   Dim rs            As DAO.Recordset
  5.  
  6. Set db = CurrentDb()
  7.   'Set rs = db.OpenRecordset("tblcheck", dbOpenDynaset, dbAppendOnly) 
  8. Set rs = db.OpenRecordset("Select System_ID FROM tblcheck WHERE System_ID ='" & Me.System_ID & "'")
  9.  
  10. If rs.RecordCount <> 0 Then
  11. MsgBox "System_ID already exixt !"
  12. Else
  13. 'insert into table
  14.    End If
  15.  
  16.  
  17. End Sub
Thanks
Apr 28 '11 #1

✓ answered by colintis

I think TheSmileyCoder must have forgot to remove some extra stuffs.
Expand|Select|Wrap|Line Numbers
  1. Dim strSQL as string 
  2. strSQL="Select System_ID FROM tblcheck WHERE System_ID ='" & Me.System_ID & "'" 
  3. MsgBox strSQL 
  4. set rs = db.OpenRecordset(strSQL) 
  5.  
And I also have the same question that TheSmileyCoder has, if the System_ID is Numerical, then the single quotes are not neccessary.

3 4996
TheSmileyCoder
2,322 Expert Mod 2GB
Whenever you have trouble with a SQL string that is parsed together in VBA, the first approach should be to adjust your code like the following:
Expand|Select|Wrap|Line Numbers
  1. Dim strSQL as string
  2. strSQL="Set rs = db.OpenRecordset("Select System_ID FROM tblcheck WHERE System_ID ='" & Me.System_ID & "'")
  3. MsgBox strSQL
  4. set rs = db.OpenRecordset(strSQL)
This is to make sure your string looks like you expect it to.

What type of field is System_ID? Numerical KEY or string?
Apr 28 '11 #2
colintis
255 100+
I think TheSmileyCoder must have forgot to remove some extra stuffs.
Expand|Select|Wrap|Line Numbers
  1. Dim strSQL as string 
  2. strSQL="Select System_ID FROM tblcheck WHERE System_ID ='" & Me.System_ID & "'" 
  3. MsgBox strSQL 
  4. set rs = db.OpenRecordset(strSQL) 
  5.  
And I also have the same question that TheSmileyCoder has, if the System_ID is Numerical, then the single quotes are not neccessary.
Apr 29 '11 #3
Jerry Maiapu
259 100+
Though the posts were quick,I had limited time so have just decided to create a form based filtered query as recodsource for a subform and do a total count of the ID of the subform on the main form.

In the on click event of a check button I did this>
Expand|Select|Wrap|Line Numbers
  1. if totalcount_of_subform>0 then insert into table 
  2. else
  3. msgbox "Already exist"
  4. exit.
Hereafter the subform and total count on parent are hidden

This worked and the posted code works as well so thanks anyway Colintis and the Smiley One.
May 16 '11 #4

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

Similar topics

3
by: DottingTheNet | last post by:
i promise no more silly questions after this but how do i check if a table exists?? it is my understanding that the exists can only be used in the where clause of the query. i just want s'thing...
6
by: Kenneth Osenbroch | last post by:
Hi. I want to create a trigger that only allows delete from table A if corresponding record in table B does not exist. Any idea on how this can be done? Thanks, Kenneth.
0
by: Mark | last post by:
All, Excuse the re-post but I have found something which works for the UserID but not for the Password (see previous post below). The problem is I don't understand how it works and therefore...
0
by: Cyberwolf | last post by:
OK, how to explain this. I have a table that will feed into another table using an append query. What I want to do is look at the table I am appending the record to to check for a duplicate...
1
by: gaurkamal | last post by:
I want to delete record in table B when i delete record in Table A both table have a common column. I want to do it using trigger .Can any body give some idea. Table details are. Table A: ...
5
by: krwill | last post by:
I'm trying to automate a combo box to add a record to the source table if it's "Not In List". I've tried many different examples and none have worked. Combo Box Name = Combo24 Source Table...
1
by: prokopis | last post by:
am using c# windows application. how can i check if a table exist in an sql server?? can someone help me pls?
5
by: saga git | last post by:
how to display nth record from table
3
by: debo sniffa | last post by:
I want to add a value from a record in Table A to a record in table B if the value is same as 2 other fields in the Table A. How could i get this done in access?? Pls help me... ;-)
0
by: Neven Huynh | last post by:
Hi Everyone, Here i my LINQ query to get record in Table Menu with condition are parentID == 0(get root menu) and ID != (parentID list) (which is parent ID list is are id of menu record that have...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

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.