I'm using A2K.
I'm inputing data from a text file into my DB, and I need to check for
the data already existing in the DB. If it's already in the DB, I
don't want to reenter it.
The two tables being used are tblPersonal and tblListData.
tblPersonal contains names, SSNs, etc. SSN is the PrimaryKey.
tblListData is keyed on the combination of SSN and ExamNum. In
tblListData, an SSN can be paired with more than one ExamNum, but the
combination must be unique.
I need to do this:
If SSN is NOT in tblPersonal Then
add new record in tblPersonal
Else ' SSN is already in DB - check if SSN is already paired with
ExamNum
If the combinaton of SSN and ExamNum is NOT in tblListData Then
add new record in tblListData
End if
End If
The short version of my question is: How can I check programmatically
for the existence of the combinaton of SSN and ExamNum in tblListData?
Some details of what I have so far:
To do the first part (If SSN is NOT in tblPersonal then) I've written
a function using the Seek method:
Public Function Exists(strRS As String, _
strIndex As String, _
strTarget As String) As Boolean
' strRS is the table beign searched
' strIndex is the index of the table
' strTarget is the value being searched for
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset(strRS)
rs.Index = strIndex
rs.Seek "=", strTarget
If rs.NoMatch = True Then
Exists = False
Else
Exists = True
End If
rs.Close
End Function
and used it like this:
' this code fragment is inside a while loop
' enter data into DB
' if SSN is not already in DB
If Not Exists("tblPersonal", "SSN", strSSN) Then
' create new record in tblPesonal
rsPersonal.AddNew
rsPersonal!SSN = strSSN
rsPersonal!EligName = strName 7 1784
Maybe try using DCount; it's used like this:
if DCount("[SSN]", "tblPersonal", "[SSN] = " & mySSN) = 0 then
.....
end if
if DCount("[SSN]", "tblListData", "[SSN] = " & mySSN & " and [ExamNum] = " &
myExamNum) = 0 then
.....
end if
Use single quotes if any of your data is char: e.g.
DCount("[OrderID]", "Orders", "[ShipRegion] = 'CA'")
HTH -Linda
"RBohannon" <ra*****@hotmail.com> wrote in message
news:ad**************************@posting.google.c om... I'm using A2K.
I'm inputing data from a text file into my DB, and I need to check for the data already existing in the DB. If it's already in the DB, I don't want to reenter it.
The two tables being used are tblPersonal and tblListData. tblPersonal contains names, SSNs, etc. SSN is the PrimaryKey. tblListData is keyed on the combination of SSN and ExamNum. In tblListData, an SSN can be paired with more than one ExamNum, but the combination must be unique.
I need to do this: If SSN is NOT in tblPersonal Then add new record in tblPersonal Else ' SSN is already in DB - check if SSN is already paired with ExamNum If the combinaton of SSN and ExamNum is NOT in tblListData Then add new record in tblListData End if End If
The short version of my question is: How can I check programmatically for the existence of the combinaton of SSN and ExamNum in tblListData?
Some details of what I have so far: To do the first part (If SSN is NOT in tblPersonal then) I've written a function using the Seek method: Public Function Exists(strRS As String, _ strIndex As String, _ strTarget As String) As Boolean
' strRS is the table beign searched ' strIndex is the index of the table ' strTarget is the value being searched for
Dim db As DAO.Database Dim rs As DAO.Recordset
Set db = CurrentDb Set rs = db.OpenRecordset(strRS)
rs.Index = strIndex rs.Seek "=", strTarget
If rs.NoMatch = True Then Exists = False Else Exists = True End If
rs.Close
End Function
and used it like this: ' this code fragment is inside a while loop ' enter data into DB ' if SSN is not already in DB If Not Exists("tblPersonal", "SSN", strSSN) Then ' create new record in tblPesonal rsPersonal.AddNew
rsPersonal!SSN = strSSN rsPersonal!EligName = strName . . . rsPersonal.Update rsPersonal.MoveNext
intCount = intCount + 1
But I don't know how I would check for the combinaton of SSN and ExamNum in tblListData.
I apologize for the length of this post, and I hope I've been clear enough. Any help would be greatly appreciated.
Thanks for your help. I won't have a chance to try this until Monday, 8/23.
Randy
Of course, you could always append to an intermediate table and then
use an outer join to get the records that aren't already in the table
and import just those. All that with maybe two lines of code...
docmd.transferText...
docmd.OpenQuery ...
An excellent idea. Thank you. Of course, you could always append to an intermediate table and then use an outer join to get the records that aren't already in the table and import just those. All that with maybe two lines of code...
docmd.transferText... docmd.OpenQuery ...
I'm getting an error message that says "Error 3464: Data type mismatch
in criteria expression."
My code:
If DCount("[SSN]", "tblListData", "[SSN] = " & strSSN & " And
[ExamNum] = " & strExamNum) = 0 then
I got it.
I needed to enclose my criteria expression in single quotes:
If DCount("[SSN]", "tblListData", "[SSN] = '" & strSSN & " And
[ExamNum] = " & strExamNum & "'") = 0 Then
Well, it appeared to be working, but it was not. I have finally
figured it out. So, for the benefit of anyone else whom this may
help:
If the criteria is not literal, it must be enclosed in single quotes,
e.g:
If DCount("[SSN]", "tblListData", "[[SSN] = '" & strSSN & "' And
[ExamNum] = '" & strExamNum & "'") = 0 Then This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: lawrence |
last post by:
I'm trying to read up on the rfc's that govern form inputs. Much of
what I'm reading is stuff I didn't know before and some of it is
alarming. This one left with me questions:
...
|
by: Jay |
last post by:
Hi everybody!
Please help me with this problem. I try to write code for server side
data validation. Initially, I have a html file called "form.html" which
just contains all the necessary fields...
|
by: KathyB |
last post by:
Sorry this is a bit of a repost because I wasn't quite accurate in my
original.
I have an hmtl page with a function to see if there are any input
type=text
boxes. If so, that means a user did...
|
by: headware |
last post by:
I realize that when making a web application, performing input
validation in the browser is good because it prevents postbacks.
However, input checking that goes beyond making sure a value exists...
|
by: Jim B |
last post by:
I am new to VB.NET and have a question on validating user input from a
Text Box. I'm writing a small program where I want to check the users
input data and determine if it's an Integer or a...
|
by: Michel Rouzic |
last post by:
I know it must sound like a newbie question, but I never really had to
bother with that before, and I didn't even find an answer in the c.l.c
FAQ
I'd like to know what's the really proper way...
|
by: MadMike42 |
last post by:
This is really starting to annoy me,
I've got a form, that has some input boxes,
a example of the code is here:-
<form action="admin_save_stock.asp" method="post" name="MyFormData">
<input...
|
by: DavidB |
last post by:
I have a situation where a user needs to clone an existing BE database
(stored on a server) to his local machine. Assume the followings...
User goes into the database FE which has linked BE. ...
|
by: Bert |
last post by:
Hi, I'm unhappy: why doesn't this work?
char enc;
char temp;
for(int i=0;i<10000;i++){
fscanf(in,"%s",&temp);
if(temp=='#')break;
else
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
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,...
|
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...
| |