EDIT VALUES SHOWN ON A FORM PRIOR TO DATABASE STORAGE

[w860098]

Having built a new system with a variety of forms, I have been presented with a particular user request and I am unsure how best to solve it.
This area of functionality involves the user supply certain information which will be turned into SQL instructions. these include the use of the terms 'EQUAL' and 'NOT EQUAL'- to satisfy the whims of (Oracle) SQL it is necessary to store these on the database as '=' and '<>'.
These 'operators' (and others) are being held within the MS Access database (and presented as a pulldown list) but then being used to formulate information which is then forwarded for storage on the Oracle database.
I need to present these 2 operators in textual form on the screen but pass them forward in SQL form, i.e. when existing detail is displayed in the form, I need to convert from SQL to text but when a new entry is created, I need to convert from text to SQL when I store that detail on the Oracle database.
The two options I have thought of are :-
a) Use the 'On Change' event to recognise when either of these two values is selected from the pulldown list so that the value can be converted before storage.
b) Wait until the form is closed and then process the database entries to convert any rows contain either of the two values.
Note that create/modification of this detail will be relatively low-key (after the initial set of detail has been created) and that the number of entries in the Oracle database table will be relatively small. [However there will be a number of tables utilising these operators and each form will have a maximum of 3 entry points for these values]
Hopefully this level of description is enough to 'get the ball rolling' - I would be interested to hear of any thoughts (+ possible code ?) regarding this issue, together with any other suggestions of how the problem might best be solved.

 

[SeeThru]

I have done similar (only using Access SQL, mind) and have always built the SQL string in codewhen the user clicked a command button.

I usually had three stages:

Error Check - made sure all the entries made sense - i.e. if you had a > the related value was present and numeric.

Build String - I often built two in parallel - one for the actualy query (normally an update) and the other that could return a count of records and possibly display them to the user.
I used lots of IF and loops to get things build properly. You often need to finish with a custom trim to drop unnneccesary 'AND's and stuff.

Wait for confirmation (used a messagebox, normally with numbers of records, etc) and then run query.

 

[w860098]

Thanks for the reply; unfortunately this is not quite the solution I require (I had a feeling my description might be misunderstood !).
The MS Access front-end is only being used to create certain information which will then be utilised by Oracle SQL processing - in this particular case, certain 'criteria' is defined by the user and then these details are turned into appropriate SQL code for Oracle to execute.
To allow the user to use 'English text' to define his query, a pulldown list is presented to them to allow them to define the 'operator', i.e. it contains such values as 'EQUAL', 'NOT EQUAL','IS','IN','LIKE', etc. Most of these can be stored 'as seen' but in the case of 'EQUAL' and 'NOT EQUAL' it is necessary to translate them into the values '=' and '<>' before that detail is stored on the Oracle database for subsequent use.
My requirement is to know how best (and when) to achieve that translation.

 

[PHV]

Why not a lookup table ?[tt]
code friendly
---- --------
= EQUAL
<> NOT EQUAL
Like LIKE
...[/tt]
And then you may use a ComboBox.


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[w860098]

GREAT !
I must admit I had thought that this would be OK for the initial input but would not work correctly (i.e. give me the 'friendly' value) when I subsequently retrieved that value back from the Oracle database. How wrong I was !