 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Forms Programming / December 2007Lookup table / redundant data problem
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 Answers in-line.  SignatureAllen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. > Two questions in one: > [quoted text clipped - 5 lines] > 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: > [quoted text clipped - 5 lines] > 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 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 > useful stuff for my library Not sure about the dingos, but I can take you to a place where the kangaroos hop down the street. :-) SignatureAllen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. > 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 [quoted text clipped - 5 lines] > >> useful stuff for my library > 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 > 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 |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.