Lookup table / redundant data problem

[raylopez99]

Two questions in one:

http://allenbrowne.com/ser-58.html has an example on an improved
lookup table. I'd like to know how in the SQL query "[Enter Bracket]"
is implimented--is this in an Event Procedure, and how exactly (what
syntax) is this done? A more complete example would be appreciated

SELECT Rate FROM Bracket WHERE [Enter Bracket:] BETWEEN
BracketLow and BracketHigh

Second question:

How can you spot whether redundant data has been entered into a
textbox (or a group of textboxes)? One solution that occurred to me
would be to store each value of a textbox or group of textboxes, as
the data is entered, into a table or file, then check as each new
data is entered against the lookup table. Another would be to write a
SQL query to check if more than one hit occurs--but can anybody
provide complete code for this? (for my library)

Thanks! If I could pay you I would!

RL

 

[Allen Browne]

Answers in-line.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Two questions in one:

http://allenbrowne.com/ser-58.html has an example on an improved
lookup table. I'd like to know how in the SQL query "[Enter Bracket]"
is implimented--is this in an Event Procedure, and how exactly (what
syntax) is this done? A more complete example would be appreciated

SELECT Rate FROM Bracket WHERE [Enter Bracket:] BETWEEN
BracketLow and BracketHigh

In Tom's first example, [Enter Bracket:] is just a parameter that Access
will pop up asking for a value when you run the query. His point is:
<quote>I would prefer not to use this solution.</quote>

He goes on to explain better solutions that work much more reliably. Just
look at this one as a bad example, i.e. you don't really want more details
of how to implement it.

Second question:

How can you spot whether redundant data has been entered into a
textbox (or a group of textboxes)? One solution that occurred to me
would be to store each value of a textbox or group of textboxes, as
the data is entered, into a table or file, then check as each new
data is entered against the lookup table. Another would be to write a
SQL query to check if more than one hit occurs--but can anybody
provide complete code for this? (for my library)

Use the BeforeUpdate event procedure of the *form* (not the event procedure
of the controls) to DLookup() the table and see if the combination of data
already exists. Cancel the event if the record should not be saved as it is.

For details on using DLookup() see:
http://allenbrowne.com/casu-07.html

 

[raylopez99]

Gee thanks Allen Browne! Perth, yeah I almost visited your fair city
last year. Somebody in Sydney said you can still hear the dingos howl
there (hope it's true)!

Ahhooooooo!

RL

 

[raylopez99]

Use the BeforeUpdate event procedure of the *form* (not the event procedure
of the controls) to DLookup() the table and see if the combination of data
already exists. Cancel the event if the record should not be saved as it is.

For details on using DLookup() see:
   http://allenbrowne.com/casu-07.html

Good stuff thanks; Just worked through an example and learned a lot
(for one thing, the subtle distinction between "After UPdate" and "Got
Focus" event procedures.

RL

 

[Allen Browne]

Not sure about the dingos, but I can take you to a place where the kangaroos
hop down the street.

 

[raylopez99]

Not sure about the dingos, but I can take you to a place where the kangaroos
hop down the street.

Excellent site--if you were less modest or more American you would put
a PayPal button on your site and I would pay! Very nice tutorials,
I'm going throught them now. Lots of traps for the unwary in Visual
Basic for Access (simultaneously I'm learning C# dB programming for
SQL Server Express, and it has a much better debugger and Intellisence
autocomplete at the cost of a steeper learning curve, especially for
the GUI, but strangely both languages are very much archaic, with a
macro look, in particular the DataSet library in C# SQL, maybe because
they derive or relate to SQL, which needs particular data adaptors).

RL