Database - functionality for front end application

[Jan Schenkel]

--- Melvin Cox <melcox at hotmail.com> wrote:
> 
> I am evaluating Runtime Revolution, but have been
> growing increasingly 
> frustrated in attempting to build the shell for a
> front end database 
> application.
> 
> Although I have been successful in connecting to a
> local database via ODBC, 
> I have run into numerous difficulties implementing
> certain on-form 
> functionalities.
> 

Hi Melvin,

Let's see if we can get you back en route :-)

> 
> Problem One: Posting of results from "lookup" query
> -------------------
> 
> Whenever a new row is displayed (via Query, Next or
> Previous calls), or 
> whenever an entry or modification is made to the
> form's Affiliation field, I 
> wish to automatically populate the form's (display
> only) CompanyName field 
> with the results of the following SQL query:
> 
> select Org_Name from Business where Bid = "<value of
> the form's currently 
> displayed affiliation field>"
> 
> How can this best be accomplished in Revolution?
> 
> [Note: I do not understand the process by which the
> currently value of a 
> field is captured and used as a variable within a
> SQL query].
> 

The easiest way to refresh a related query, is the
following :
- I'll assume that the card field "affilitation" is
linked to the database field of the same name
- to make it easier for yourself if you would like to
add more fields from the affiliated Business record,
make a new query "AffiliatedBusiness"
- connect that query to the database, and set its SQL
query to :
  SELECT * FROM Business
- if you don't have a field "CompanyName" on the card
yet, create it, and link it to the new query and its
Org_Name field
- group the record navigation buttons (first / prev /
next / last)
- now set the script of that navigation group to :
on mouseUp
  put "SELECT * FROM Business WHERE Bid=" \
      into tSQLQuery
  put field "affiliation" after tSQLQuery
  revSetSQLOfQuery "AffiliatedBusiness", tSQLQuery
end mouseUp
- from now on, whenever you click on one of the
buttons ion the navigation group, any fields you have
on the card that are linked to the AffiliatedBusiness
query, will be automatically updated.

> 
> Problem Two: Placing the current time into a field
> ------------------
> 
> Just prior to an insert or update of any row, I wish
> to populate the form's 
> Last_update field with a datetime value reflecting
> the current date and time 
> (ie. "2003-11-14 13:17:39.250").
> 
> How can this best be implemented?
> 

Okay, it's not down to the millisecond, but try the
following tactic :
- whenever the content of a field is cha nged, a
closeField will be sent ; so to update the timestamp
field with the right info whenever that happens, put
the following in the card script :
on closeField
  put the date && the long time into tDate
  convert tDate to dateItems
  put item 1 of tDate & "-" & \
      item 2 of tDate & "-" & \
      item 3 of tDate & " " & \
      item 4 of tDate & ":" & \
      item 5 of tDate & ":" & \
      item 6 of tDate into fld "Last_Update"
end closeField
- now when you do an update of the record to the
database, you can simply include the content of the
field in your Update and Insert query.

> 
> Problem Three: Forcing All Caps
> --------------------
> 
> To insure data integrity, my application design
> requires entry of data into 
> the "Pid" field in UPPER CASE format.
> 
> While I have been successful in limiting the length
> of the data entered in 
> this field to ten (10) characters or less, my
> attempts to force 
> CAPITALIZATION have to date failed.  The following
> script is currently 
> applied to the field:
> 
> ===================================
> 
> on keyDown theKey
>   if the length of me >= 10 then beep
>   else pass keyDown
> end keyDown
> 
> ===================================
> 

Try the following script instead :

on keyDown pWhichKey
  if the length of me > 10 then beep
  else
    send "Capitalize" to me in 5 milliseconds
    pass keyDown
  end if
end keyDown

on Capitalize
  put the selectedChunk of me into tChunk
  put upper(me) into me
  select tChunk
end Capitalize

Beware that the above script doens't fix 'pasted'
text, nor text that gets into your field via
'drag-and-drop'. There are ways of handling that,
however.
Have a look at the Transcript Dictionary entries for
the 'pasteKey' and 'dragDrop' messages.

> Any assistance in these areas is most sincerely
> appreciated.
> 
> 
> 
> Melvin Cox
> BCM Productions
> 

Hope this helped ; don't hesitate to poke the list for
more information ; there are plenty of very smart
people out here :-)

Jan Schenkel.

=====
"As we grow older, we grow both wiser and more foolish at the same time."  (La Rochefoucauld)

__________________________________
Do you Yahoo!?
Protect your identity with Yahoo! Mail AddressGuard
http://antispam.yahoo.com/whatsnewfree