How to return a result of a SQL query within an Event Procedure(&what am I doing wrong here)?

[raylopez99]

I am trying to return the results of a SQL query in an Event
Procedure, that returns the number of rows (COUNT) that satisfies
something. I ran the query manually and I get "five" hits for "Table
1" below.

But when I run the query programmically (see the below--it's the first
time I've tried to run a parametized SQL query within Visual Basic), I
always seem to get a RecordCount of 1. Is RecordCount the number of
hits returned by a SQL query, or is it something else? If something
else, what property from the Recordset object will give the number of
hits returned by a SQL query?

Please advise.

If it matters, here is the simple SQL query that I call "Query1" and
yields five hits for the letter 'a' in field Text for the table Table1
I'm using: SELECT Count(*) AS MyCount1 FROM Table1 WHERE
(((Table1.Text1)=[Text]));

RL

Private Sub OUTPUT_Click() '<--this is a textbox OUTPUT and the Event
is "Click"

Dim rs As DAO.Recordset 'holds query resultset
Dim db As DAO.Database
Dim qdfParmQry As QueryDef 'actual query object
Set db = CurrentDb
Set qdfParmQry = db.QueryDefs("Query1") '<--the name of the query is
Query1

qdfParmQry![Text] = "a" <--if "a" is input into the query, you get
five (5) hits in Query1

'qdfParmQry("TEXT") = a 'this format didn't work, despite a source on
the Net saying it would

Set rs = qdfParmQry.OpenRecordset()

Dim TempVar As Variant
TempVar = rs.RecordCount ' I assign rs.RecordCount to a temporary
variable but same thing: always get 1

If TempVar = 1 Then

Dim msg1 As String
Dim title As String
'Dim style As MsgBoxStyle
Dim response
msg1 = "rs.RecordCount is always 1???!!!" ' Define message.
title = "MsgBox Output" ' Define title.
' Display message.
response = MsgBox(msg1, 3, title)

End If


End Sub

 

[Marshall Barton]

I am trying to return the results of a SQL query in an Event
Procedure, that returns the number of rows (COUNT) that satisfies
something. I ran the query manually and I get "five" hits for "Table
1" below.

But when I run the query programmically (see the below--it's the first
time I've tried to run a parametized SQL query within Visual Basic), I
always seem to get a RecordCount of 1. Is RecordCount the number of
hits returned by a SQL query, or is it something else? If something
else, what property from the Recordset object will give the number of
hits returned by a SQL query?

Please advise.

If it matters, here is the simple SQL query that I call "Query1" and
yields five hits for the letter 'a' in field Text for the table Table1
I'm using: SELECT Count(*) AS MyCount1 FROM Table1 WHERE
(((Table1.Text1)=[Text]));

RL

Private Sub OUTPUT_Click() '<--this is a textbox OUTPUT and the Event
is "Click"

Dim rs As DAO.Recordset 'holds query resultset
Dim db As DAO.Database
Dim qdfParmQry As QueryDef 'actual query object
Set db = CurrentDb
Set qdfParmQry = db.QueryDefs("Query1") '<--the name of the query is
Query1

qdfParmQry![Text] = "a" <--if "a" is input into the query, you get
five (5) hits in Query1

'qdfParmQry("TEXT") = a 'this format didn't work, despite a source on
the Net saying it would

Set rs = qdfParmQry.OpenRecordset()

Dim TempVar As Variant
TempVar = rs.RecordCount ' I assign rs.RecordCount to a temporary
variable but same thing: always get 1

If TempVar = 1 Then

Dim msg1 As String
Dim title As String
'Dim style As MsgBoxStyle
Dim response
msg1 = "rs.RecordCount is always 1???!!!" ' Define message.
title = "MsgBox Output" ' Define title.
' Display message.
response = MsgBox(msg1, 3, title)

End If
End Sub

RecordCount returns the number of records that have been
accessed so far. This is often just one record immediately
after the recordset is opened. You can do a rs.MoveLast to
force all the records to be accessed, but this is the long
way around to just get a count.

If you are enamored with recordsets then you should use a
Totals type query that just counts the records, but even
this is more code than needed without any significant
performance benefit.

I suggest that you remove the parameter from the query and
just use the DCount function:

Private Sub OUTPUT_Click()
Dim TempVar As Long
TempVar = DCount("*", "Query1", "[some field] = 'a' ")
. . .

 

[raylopez99]

Thanks, I figured out a workaround, see below.

RL


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Private Sub Text6_Click() 'Some textbox that you want to run the SQL
query when you click on it

Dim TempVar As Variant
Dim Str01 As String
Str01 = Str001 'global string Str001 set to local string Str01

Dim Str02 As String

Str02 = "SELECT Count(" + Str01 + ") AS Expr1 FROM Table1;" 'combines
two strings, Str02 and Str01, as one string, with Str02 having the
query and the parameter being supplied by the Str01 string, which is
what you want, note how the apostrophes are used

TempVar = GetResult(Str02) ' the result of the SQL query, usually a
scalar number for simple queries

Dim TempText As String

TempText = CStr(TempVar) 'convert number to string

Text6.Value = TempText 'output the number, as a string, to your
textbox Text6

End Sub

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
' put this code in your Global area of your [Event Procedure]
namespace, outside any Sub

Dim Str001 As String 'global variable Str001

Public Function GetResult(sSQL As String) As Variant

On Error GoTo err01 'add error trap
Dim rs As DAO.Recordset
Dim db As DAO.Database

Set db = CurrentDb
Set rs = db.OpenRecordset(sSQL)
If Not rs.EOF Then
GetResult = rs(0)
rs.Close
End If

db.Close

Set rs = Nothing
Set db = Nothing

GoTo line001XYZ 'you need this I've found to avoid running the next
few lines, for some strange reason

err01: Debug.Print "GetResult error: SQL=" & sSQL & ". Error: " & Error
$
'Debug prints to debug window (useful when debugging, after the
program has run)


line001XYZ: 'skips line err01 and the ones after, if no mistake at
GoTo err001

End Function
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

 

[Baz]

You really aren't very good at this, are you?

Thanks, I figured out a workaround, see below.

RL


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Private Sub Text6_Click() 'Some textbox that you want to run the SQL
query when you click on it

Dim TempVar As Variant
Dim Str01 As String
Str01 = Str001 'global string Str001 set to local string Str01

Dim Str02 As String

Str02 = "SELECT Count(" + Str01 + ") AS Expr1 FROM Table1;" 'combines
two strings, Str02 and Str01, as one string, with Str02 having the
query and the parameter being supplied by the Str01 string, which is
what you want, note how the apostrophes are used

TempVar = GetResult(Str02) ' the result of the SQL query, usually a
scalar number for simple queries

Dim TempText As String

TempText = CStr(TempVar) 'convert number to string

Text6.Value = TempText 'output the number, as a string, to your
textbox Text6

End Sub

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
' put this code in your Global area of your [Event Procedure]
namespace, outside any Sub

Dim Str001 As String 'global variable Str001

Public Function GetResult(sSQL As String) As Variant

On Error GoTo err01 'add error trap
Dim rs As DAO.Recordset
Dim db As DAO.Database

Set db = CurrentDb
Set rs = db.OpenRecordset(sSQL)
If Not rs.EOF Then
GetResult = rs(0)
rs.Close
End If

db.Close

Set rs = Nothing
Set db = Nothing

GoTo line001XYZ 'you need this I've found to avoid running the next
few lines, for some strange reason

err01: Debug.Print "GetResult error: SQL=" & sSQL & ". Error: " & Error
$
'Debug prints to debug window (useful when debugging, after the
program has run)


line001XYZ: 'skips line err01 and the ones after, if no mistake at
GoTo err001

End Function
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

 

[Marshall Barton]

That looks like you copied some code from another post and
mashed it around to make it eventually get what you want.
While I applaud your industriousness in locating and
utilizing a query to do the calculation, it still seems like
a convoluted approach to what a simple DCount function call
can do in one line of code. I can't tell for sure what all
that code is doing, but I think it can all be done with a
proceduer like:

Private Sub Text6_Click()
Text6 = DCount(Str001, "Table1")
End Sub

I also don't see why you would want to require a click to
run the procedure. Wouldn't it make more sense to put the
line of code in the same procedure that sets the global
variable? (which would then raise the querstion of why use a
global variable?)
--
Marsh
MVP [MS Access]

Thanks, I figured out a workaround, see below.


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Private Sub Text6_Click() 'Some textbox that you want to run the SQL
query when you click on it

Dim TempVar As Variant
Dim Str01 As String
Str01 = Str001 'global string Str001 set to local string Str01

Dim Str02 As String

Str02 = "SELECT Count(" + Str01 + ") AS Expr1 FROM Table1;" 'combines
two strings, Str02 and Str01, as one string, with Str02 having the
query and the parameter being supplied by the Str01 string, which is
what you want, note how the apostrophes are used

TempVar = GetResult(Str02) ' the result of the SQL query, usually a
scalar number for simple queries

Dim TempText As String

TempText = CStr(TempVar) 'convert number to string

Text6.Value = TempText 'output the number, as a string, to your
textbox Text6

End Sub

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
' put this code in your Global area of your [Event Procedure]
namespace, outside any Sub

Dim Str001 As String 'global variable Str001

Public Function GetResult(sSQL As String) As Variant

On Error GoTo err01 'add error trap
Dim rs As DAO.Recordset
Dim db As DAO.Database

Set db = CurrentDb
Set rs = db.OpenRecordset(sSQL)
If Not rs.EOF Then
GetResult = rs(0)
rs.Close
End If

db.Close

Set rs = Nothing
Set db = Nothing

GoTo line001XYZ 'you need this I've found to avoid running the next
few lines, for some strange reason

err01: Debug.Print "GetResult error: SQL=" & sSQL & ". Error: " & Error
$
'Debug prints to debug window (useful when debugging, after the
program has run)


line001XYZ: 'skips line err01 and the ones after, if no mistake at
GoTo err001

End Function
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

I suggest that you remove the parameter from the query and
just use the DCount function:

Private Sub OUTPUT_Click()
Dim TempVar As Long
TempVar = DCount("*", "Query1", "[some field] = 'a' ")
        . . .

 

[raylopez99]

That looks like you copied some code from another post and
mashed it around to make it eventually get what you want.

Right. Then as a project I decided to do your DCount suggestion for a
slightly different variant of this problem. Big mistake. I spent well
over three hours and counting and I finally, after much hair pulling
and anxiety, got a somewhat stable version (below).

Here's the variant of the problem: I have two text boxes, and I want
to see if they both have the same pair of values at any other record
(for data integrity of course). In other words, I don't want to
repeat any pair of combinations from text boxes A and B ("alpha" and
"beta" in text boxes A and B for example, not to be repeated again).
Case insensitive, so ALPHA = alpha. Both textboxes are bound to a
table having two text columns and at the same time the Event Procedure
works on the textboxes, using the After Update event (this could be
the problem, but I'm not a s/w engineer to know how DCount is set up
to work).

What I found was that DCount is very unstable under this scenario.
Sometimes it chokes for no known reason. I thought it might be due to
the fact global variables were being used, but I don't think so. I'm
running Vista OS with a Core2 Duo fast machine and Access 2003--is it
too fast, too slow? Some caching going on? The order of inputting
the text into the textboxes sometimes makes a difference: input "Cat"
into textbox A, then "dog" into B, then repeat in same order, and you
get nothing (sometimes), but if you then vary by putting "dog" into A,
and Cat into B (opposite boxes), the program in subsequent records
comes alive and records (returns) the number of redundant, duplicative
rows from previous "frozen" records. Strange. The .err trap also
springs but no "error number" is given.

I hate VB! As soon as this project is done I'm going back to C#/C++,
which is better for debugging (for example, in my version of VB/Access
I can only access the Intermediate Window after the program runs, and
can't do a real-time "Watch" or "Step through"--don't know why),
though since I'm new to dB programming I suspect they also have flaws
given they have to work with SQL.

Any suggestions appreciated.

RL

Below is the code, from the most stable version (don't even ask about
the unstable versions--for example, i had versions where the focus
being switched back and forth between textboxes resulted in infinite
loops, where Variant was used and DCount doesn't work with Variant,
even if you cast the Variant as a String, check for Null using Nz,
etc. What a nightmare! Going to bed now, a half day wasted with
VB...

PS--UPDATE: I added a line below, see "UPDATE", but it makes no
difference. Again, the behavior is that varying the order of input
'unlocks' the application. In short, it's toggling behavior of sorts,
so that if you enter "XYZ" into Textbox0, then "ABC" into Textbox1,
then in the next record repeat, nothing happens (zero hits), but in
the third record enter "ABC" into Textbox0, and "XYZ" into Textbox 1,
you "unlock" the app and it correctly tells you that you have two
records that are duplicates. I think it might be the way DCount works
behind the scenes, with the global variables in place it remembers
these, and something strange is happening, like an XOR of sorts.
Notice that I do toggle the global variables MyCField1 and 2 for both
textboxes, thinking this would help, but I doubt altering this order
would change things much.

Option Compare Database
Dim MyCField1 As String 'global variables required otherwise runtime
error (null error) if these variables are kept local
Dim MyCField2 As String '""



Public Function CompoundCount(ByVal MyControlField1 As String, _
ByVal MyControlField2 As String) As Integer
On Error GoTo err02 'add error trap
CompoundCount = DCount("[Text1] & [Text0]", "Table3", "[Text1] =
'" & MyControlField1 & _
"' AND [Text0] = '" & MyControlField2 & "'")

' long winded DCount but I think I have it right--hard to tell, but it
works on occasion so it has to be correct

'' note bizarre format req'd with apostrophes
' note use of spaces using apostrophe and underscore for <line
break>

err02: Debug.Print "GetResult error: MyControlField1=" &
MyControlField1 & ". Error: " & Error$ 'Debug prints to debug window


End Function


Private Sub Text0Textbox_AfterUpdate()

MyCField1 = Me.Text0Textbox.Text 'Two textboxes-- Text0Textbox and
Text1Textbox

Text1Textbox.SetFocus
MyCField2 = Me.Text1Textbox.Text
Text0Textbox.SetFocus 'added to give back focus here otherwise runtime
error

Dim MyInt As Integer
MyInt = CompoundCount(MyCField1, MyCField2) 'The integer value of
DCount

Dim MyString02 As String
MyString02 = CStr(MyInt) 'The value of the DCount in a string
'''''''''''''''''''''''''''''''''''''''''''''''
Dim title2 As String
'Dim style As MsgBoxStyle
Dim msg3 As String
Dim response2
msg3 = "Value DCount0:" + MyString02 ' Define message.
title2 = "MsgBox Text0Textbox" ' Define title.
' Display message.
response2 = MsgBox(msg3, 0, title2) 'o is OK only button
'NOTE: I use a MsgBox for outputting, and perhaps this slows down the
application and causes a problem?
'''''''''''''''''''''''''''''''''''''''''''''''
End Sub



Private Sub Text1Textbox_AfterUpdate()
MyCField2 = Me.Text1Textbox.Text 'since AfterUpdate event,
Text1Textbox is guaranteed to have a value now (presumeably)

Text0Textbox.SetFocus 'set focus to other textbox, to read it, once
you read from present textbox
MyCField1 = Me.Text0Textbox.Text '*UPDATE*: added this line but
makes no difference
Dim MyInt As Integer
MyInt = CompoundCount(MyCField1, MyCField2)
'''''''''''''''''''''''''''''''''''''''''''''''
Dim MyString02 As String
MyString02 = CStr(MyInt)
Dim title2 As String
'Dim style As MsgBoxStyle
Dim msg3 As String
Dim response2
msg3 = "Value DCount0:" + MyString02 ' Define message.
title2 = "MsgBox Text0Textbox" ' Define title.
' Display message.
response2 = MsgBox(msg3, 0, title2) 'o is OK only button
'''''''''''''''''''''''''''''''''''''''''''''''

End Sub

 

[Beetle]

(for data integrity of course). In other words, I don't want to

repeat any pair of combinations from text boxes A and B ("alpha" and
"beta" in text boxes A and B for example, not to be repeated again).
Case insensitive, so ALPHA = alpha. Both textboxes are bound to a
table

Maybe I'm missing something here, but why don't you just index the fields in
the table?

I hate VB! As soon as this project is done I'm going back to C#/C++,
which is better for debugging (for example, in my version of VB/Access
I can only access the Intermediate Window after the program runs, and
can't do a real-time "Watch" or "Step through"--don't know why),
though since I'm new to dB programming I suspect they also have flaws
given they have to work with SQL.

Last month I wanted to visit my next door neighbor, who lives directly north
of me. I decided to travel via the south pole. Now I hate my neighbor. It
shouldn't take so long to get to his house!

Access/VBA is not the problem.

_________

Sean Bailey

That looks like you copied some code from another post and
mashed it around to make it eventually get what you want.

Right. Then as a project I decided to do your DCount suggestion for a
slightly different variant of this problem. Big mistake. I spent well
over three hours and counting and I finally, after much hair pulling
and anxiety, got a somewhat stable version (below).

Here's the variant of the problem: I have two text boxes, and I want
to see if they both have the same pair of values at any other record
(for data integrity of course). In other words, I don't want to
repeat any pair of combinations from text boxes A and B ("alpha" and
"beta" in text boxes A and B for example, not to be repeated again).
Case insensitive, so ALPHA = alpha. Both textboxes are bound to a
table having two text columns and at the same time the Event Procedure
works on the textboxes, using the After Update event (this could be
the problem, but I'm not a s/w engineer to know how DCount is set up
to work).

What I found was that DCount is very unstable under this scenario.
Sometimes it chokes for no known reason. I thought it might be due to
the fact global variables were being used, but I don't think so. I'm
running Vista OS with a Core2 Duo fast machine and Access 2003--is it
too fast, too slow? Some caching going on? The order of inputting
the text into the textboxes sometimes makes a difference: input "Cat"
into textbox A, then "dog" into B, then repeat in same order, and you
get nothing (sometimes), but if you then vary by putting "dog" into A,
and Cat into B (opposite boxes), the program in subsequent records
comes alive and records (returns) the number of redundant, duplicative
rows from previous "frozen" records. Strange. The .err trap also
springs but no "error number" is given.

I hate VB! As soon as this project is done I'm going back to C#/C++,
which is better for debugging (for example, in my version of VB/Access
I can only access the Intermediate Window after the program runs, and
can't do a real-time "Watch" or "Step through"--don't know why),
though since I'm new to dB programming I suspect they also have flaws
given they have to work with SQL.

Any suggestions appreciated.

RL

Below is the code, from the most stable version (don't even ask about
the unstable versions--for example, i had versions where the focus
being switched back and forth between textboxes resulted in infinite
loops, where Variant was used and DCount doesn't work with Variant,
even if you cast the Variant as a String, check for Null using Nz,
etc. What a nightmare! Going to bed now, a half day wasted with
VB...

PS--UPDATE: I added a line below, see "UPDATE", but it makes no
difference. Again, the behavior is that varying the order of input
'unlocks' the application. In short, it's toggling behavior of sorts,
so that if you enter "XYZ" into Textbox0, then "ABC" into Textbox1,
then in the next record repeat, nothing happens (zero hits), but in
the third record enter "ABC" into Textbox0, and "XYZ" into Textbox 1,
you "unlock" the app and it correctly tells you that you have two
records that are duplicates. I think it might be the way DCount works
behind the scenes, with the global variables in place it remembers
these, and something strange is happening, like an XOR of sorts.
Notice that I do toggle the global variables MyCField1 and 2 for both
textboxes, thinking this would help, but I doubt altering this order
would change things much.

Option Compare Database
Dim MyCField1 As String 'global variables required otherwise runtime
error (null error) if these variables are kept local
Dim MyCField2 As String '""



Public Function CompoundCount(ByVal MyControlField1 As String, _
ByVal MyControlField2 As String) As Integer
On Error GoTo err02 'add error trap
CompoundCount = DCount("[Text1] & [Text0]", "Table3", "[Text1] =
'" & MyControlField1 & _
"' AND [Text0] = '" & MyControlField2 & "'")

' long winded DCount but I think I have it right--hard to tell, but it
works on occasion so it has to be correct

'' note bizarre format req'd with apostrophes
' note use of spaces using apostrophe and underscore for <line
break>

err02: Debug.Print "GetResult error: MyControlField1=" &
MyControlField1 & ". Error: " & Error$ 'Debug prints to debug window


End Function


Private Sub Text0Textbox_AfterUpdate()

MyCField1 = Me.Text0Textbox.Text 'Two textboxes-- Text0Textbox and
Text1Textbox

Text1Textbox.SetFocus
MyCField2 = Me.Text1Textbox.Text
Text0Textbox.SetFocus 'added to give back focus here otherwise runtime
error

Dim MyInt As Integer
MyInt = CompoundCount(MyCField1, MyCField2) 'The integer value of
DCount

Dim MyString02 As String
MyString02 = CStr(MyInt) 'The value of the DCount in a string
'''''''''''''''''''''''''''''''''''''''''''''''
Dim title2 As String
'Dim style As MsgBoxStyle
Dim msg3 As String
Dim response2
msg3 = "Value DCount0:" + MyString02 ' Define message.
title2 = "MsgBox Text0Textbox" ' Define title.
' Display message.
response2 = MsgBox(msg3, 0, title2) 'o is OK only button
'NOTE: I use a MsgBox for outputting, and perhaps this slows down the
application and causes a problem?
'''''''''''''''''''''''''''''''''''''''''''''''
End Sub



Private Sub Text1Textbox_AfterUpdate()
MyCField2 = Me.Text1Textbox.Text 'since AfterUpdate event,
Text1Textbox is guaranteed to have a value now (presumeably)

Text0Textbox.SetFocus 'set focus to other textbox, to read it, once
you read from present textbox
MyCField1 = Me.Text0Textbox.Text '*UPDATE*: added this line but
makes no difference
Dim MyInt As Integer
MyInt = CompoundCount(MyCField1, MyCField2)
'''''''''''''''''''''''''''''''''''''''''''''''
Dim MyString02 As String
MyString02 = CStr(MyInt)
Dim title2 As String
'Dim style As MsgBoxStyle
Dim msg3 As String
Dim response2
msg3 = "Value DCount0:" + MyString02 ' Define message.
title2 = "MsgBox Text0Textbox" ' Define title.
' Display message.
response2 = MsgBox(msg3, 0, title2) 'o is OK only button
'''''''''''''''''''''''''''''''''''''''''''''''

End Sub

 

[Baz]

Right. Then as a project I decided to do your DCount suggestion for a
slightly different variant of this problem. Big mistake. I spent well
over three hours and counting and I finally, after much hair pulling
and anxiety, got a somewhat stable version (below).

DCount as a project? Three hours? LOL! Have you noticed yet that
*everything* you try turns into a mammoth undertaking resulting in hours of
wasted time and the most convoluted, incomprehensible solutions? There must
be a common factor to all these problems of yours, I wonder what it might
be?

but I'm not a s/w engineer

Well you got that bit right.

What I found was that DCount is very unstable under this scenario.
Sometimes it chokes for no known reason.

Where I come from we have a saying: "A bad workman always blames his tools"

The .err trap also
springs but no "error number" is given.

That's because your "err trap" is inline with the code, dumbo.

I hate VB! As soon as this project is done I'm going back to C#/C++,
which is better for debugging

From the "solutions" you have posted so far it is blindingly obvious that
you can't program, period. The language is immaterial.

(for example, in my version of VB/Access
I can only access the Intermediate Window after the program runs, and
can't do a real-time "Watch" or "Step through"--don't know why),

It's because you're an idiot.

Any suggestions appreciated.

Get a job at WalMart.

 

[raylopez99]

Maybe I'm missing something here, but why don't you just index the fields in
the table?

I think you're missing something, though thanks for replying. WHen
you say index to you mean set up an array? (don't even know how to do
that in VB). Or the "index" you can specify in Access regarding
speeding up record searches? Or what? The exercise is to see if you
have redunant information in a pair of textboxes bound to a table. If
you have a fast and elegant way of doing that, please let me know. A
sort of DCount that accepts many parameters rather than just one would
work. Right now I'm going to use a SQL query that is parameterized to
accept numerous inputs corresponding to the textbox contents.

RL

 

[raylopez99]

DCount as a project?  Three hours?  LOL!  Have you noticed yet that
*everything* you try turns into a mammoth undertaking resulting in hours of
wasted time and the most convoluted, incomprehensible solutions?  There must
be a common factor to all these problems of yours, I wonder what it might
be?

I'm new to the language. Did you get born just to flame people on
this board?

That's because your "err trap" is inline with the code, dumbo.

Well give me an alternative, Einstein.

ar it is blindingly obvious that

you can't program, period.  The language is immaterial.

Yes I can, you should see my work in alt.geneology, it was a beauty in
C# that took a month but proved a fine point on Galton's work on
extinction of surnames in populations. Though my program didn't prove
this point, what Galton found in general was that as a population
grows, it weakens due to idiots reproducing more than smart people.
Why you're around Baz. And so many of your cousins.

Get a job at WalMart.

You seem to know a lot about Walmart--you posting from work there?

Get a life clown. You're fired--your boss.

RL

 

[Baz]

I'm new to the language.

And you think that continually telling everyone how crap it is is going to
get you help with it, do you?

Did you get born just to flame people on
this board?

No, only blowhards like you.

That's because your "err trap" is inline with the code, dumbo.

Well give me an alternative, Einstein.

What, me insult a hotshot like you by offering you advice? I wouldn't dream
of it.

ar it is blindingly obvious that
you can't program, period. The language is immaterial.

Yes I can, you should see my work in alt.geneology, it was a beauty in
C# that took a month but proved a fine point on Galton's work on
extinction of surnames in populations.

Yeah, right, whatever. I imagine it was a thousand lines of spaghetti code
to do something that anyone else could've done in an hour.

Though my program didn't prove
this point

No shit Sherlock!

what Galton found in general was that as a population
grows, it weakens due to idiots reproducing more than smart people.
Why you're around Baz. And so many of your cousins.

Ooooh, you bitch LOL!

Get a job at WalMart.

You seem to know a lot about Walmart--you posting from work there?

ROTFLMAO! Enjoy your next 3-hour project learning how not to use ONE
function!

 

[raylopez99]

ROTFLMAO!  Enjoy your next 3-hour project learning how not to use ONE
function!

Hey Bus-turd--you're fired. Don't let the door hit your head on the
way out.

RL

 

[Beetle]

I can't tell for sure, but if all you are trying to do is make it so that any
combination of values in those two fields cannot be repeated, then you can do
it at the table level (no VB required).

Open the table in design view, go to View / Indexes. Select a blank row in
the first column and give the index a name like "AlphaBeta Index" (or
whatever),select your first field in the second column, and the sort order in
the third column. On the next row leave the index name blank an select your
second field in column 2. make sure the Unique property is checked.

"a" in field1 and "b" field2 would be allowed

"b" in field1 and "a" in field2 would be allowed

"a" in field1 and "b" in field2 (a second time) would not be allowed.

Again, I'm not sure if that is what you are trying do do or not.

 

[raylopez99]

I can't tell for sure, but if all you are trying to do is make it so that any
combination of values in those two fields cannot be repeated, then you cando
it at the table level (no VB required).

Interesting, are you talking about "compound keys"? Good idea, and it
would solve my problem, but as I related in another thread, I tried to
replace the artificial keys with natural compound keys, but because so
much data has been entered, and despite my best efforts, I could not
get the compound keys to work with the particular form in this table
(though some posters claim there's not a nexus between form and table,
but I disagree, seems that the form breaks if you play with the table,
in particular replace the Primary Key). In any event, I've elected
not to switch to compound keys, though I do understand the principal
and have built a tiny dB to show that compound keys do work as
intended in Access.

Open the table in design view, go to View / Indexes. Select a blank row in
the first column and give the index a name like "AlphaBeta Index" (or
whatever),select your first field in the second column, and the sort orderin
the third column. On the next row leave the index name blank an select your
second field in column 2. make sure the Unique property is checked.

"a" in field1 and "b" field2 would be allowed

"b" in field1 and "a" in field2 would be allowed

"a" in field1 and "b" in field2 (a second time) would not be allowed.

Again, I'm not sure if that is what you are trying do do or not.

I tried this and everytime Access 2003 hangs. From my version, the
indexes don't seem to allow you to "insert" a row, as you imply.
Everytime the program crashes when I try to do so (tried it twice).
Also I was under the impression that indexes are for speeding up a
search, and not for enforcing a relationship.

Thanks for your input.

RL

 

[Marshall Barton]

Interesting, are you talking about "compound keys"? Good idea, and it
would solve my problem, but as I related in another thread, I tried to
replace the artificial keys with natural compound keys, but because so
much data has been entered, and despite my best efforts, I could not
get the compound keys to work with the particular form in this table
(though some posters claim there's not a nexus between form and table,
but I disagree, seems that the form breaks if you play with the table,
in particular replace the Primary Key). In any event, I've elected
not to switch to compound keys, though I do understand the principal
and have built a tiny dB to show that compound keys do work as
intended in Access.



I tried this and everytime Access 2003 hangs. From my version, the
indexes don't seem to allow you to "insert" a row, as you imply.
Everytime the program crashes when I try to do so (tried it twice).
Also I was under the impression that indexes are for speeding up a
search, and not for enforcing a relationship.

Your impression is wrong. Indexes can speed up a query, but
they have several other capabilities that are more
important.

First, indexes are essential to referential integrity and
the table designer tries to make this point when it reminds
you that you should have a primary key, Futhermore, you can
not create a reliable relationship between two tables if the
foreign key is not indexed.

There is no restriction that disallows multiple indexes in a
table, there can be even more than one unique index (which
is what Beetle is trying to tell you. So keep your
surrogate pk and add another unique (compound) index for the
two fields you are struggling with.

 

[Beetle]

Having an index can help speed up searches, but indexes can also be used to
enforce data integrity rules at the table level. In the scenario I described,
the fields are not part of any relationship.

If you already have a lot of records in your table, then perhaps you have
existing data that would violate the index you are trying to create,
therefore Access will not let you implement it.

 

[raylopez99]

First, indexes are essential to referential integrity and
the table designer tries to make this point when it reminds
you that you should have a primary key,  Futhermore, you can
not create a reliable relationship between two tables if the
foreign key is not indexed.

OK, got that, this is standard for me, nothing new here.

There is no restriction that disallows multiple indexes in a
table, there can be even more than one unique index (which
is what Beetle is trying to tell you.  So keep your
surrogate pk and add another unique (compound) index for the
two fields you are struggling with.

Aha! This is news. I've never (in my short few weeks of dB
designing) had a table with TWO primary keys. Obviously the second
surrogate key is not going to be the main primary key, since only one
main PK can exist by defintion, but, as you suggest, it will enforce a
relationship with another table akin to how foreign keys work with
main primary keys (I've had tables with two foreign keys of course,
where a table is linked to two other tables for example, but never two
foreign keys from one table having two surrrogate keys, as is being
discussed here).

I will put this task into my "to do" list, sounds intriguing. It
explains what another poster was saying about "belt and suspenders"
approach to data integrity--check and warn the user at the Forms level
if there is redundant data, using programmic techniques, but also
check at the back-end server level using I guess what is being
described here.

Thanks Marsh and Beetle.

RL

 

[Beetle]

You seem to be under the impression that a field or fields must be a Key
(Primary or otherwise) in order to be indexed. That is not the case.


_________

Sean Bailey

 

[raylopez99]

You seem to be under the impression that a field or fields must be a Key
(Primary or otherwise) in order to be indexed. That is not the case.

Thanks for your input. I played around with a mock series of tables
and found that indeed Indexes as you describe are great, but in Access
2003 they only enforce uniqueness in one key, not a pair of keys which
is my "problem" (again, a problem if I don't handle data entry
programically, but try to do it through the tables).

What I found was that Access 2003 would not allow me (from what I
could tell) to set up an index for two keys where the COMBINATION of
the two keys was unique, but neither key being unique.

The scenario is this (***and why I choose to do this schema over other
more rational schemes is a good question, I must admit, but so be
it): every stock account number can be entered twice in a record, so
long as, when it is entered, it is paired with a unique stock symbol.
Likewise, every stock symbol can be entered twice, if it is associated
with different stock account numbers.

So, account Ray123 with IBM, Beetle234 with KO, Beetle234 with IBM,
Ray123 with KO, but not (again) Ray123 with IBM--error.

***Now you might question why I don't have just a single unique number
associated with a person, called a stock number, that can contain any
number of non-unique stock symbols. That way you would avoid this
"problem". True, but I found that the form got very cluttered when I
did this, unless I had two or more subforms and/or popup tables for
the many stocks in each account--so they would all appear in the
window as you move from record to record. Right now I have one
subform for each stock, and one unique stock symbol - account number
for each record, so each stock with a particular account gets its own
single subform for each record--nice and clean visually, for record
entry (of course in a report you can slice and dice the data so all
stocks appear on the same page, but that's another matter). Perhaps
an instance of the table schema being dictated by the table form,
which is 'bad form', pun intended, but so be it.

If I'm missing anything (about not being able to set up an index with
a compound key in Access, where the table already has a primary key
that is non-compound) , please let me know.

RL

 

[Rick Brandt]

You seem to be under the impression that a field or fields must be
a Key (Primary or otherwise) in order to be indexed. That is not
the case.

Thanks for your input. I played around with a mock series of tables
and found that indeed Indexes as you describe are great, but in Access
2003 they only enforce uniqueness in one key, not a pair of keys [snip]

Incorrect. A unique key can have multiple fields. Open the index dialog while
in table design view and enter like this...

IndexName FieldName1
FieldName2
etc...

(at the bottom)

Unique = Yes