That's clearer. If there's no link between the tables I would create a second form as I suggested in my first reply.
However, you have a problem in that you need the description to be consistent or you're not going to get a match. I have a similar problem with my main database because it is mostly based on names and names tend to get spelt differently - Jon & John, for example. I get around this by using a "contains" selection so I create a menu that either contains "Jo" or is like "Jo%'. I chose to go for the contains option because I can then lookup things like "air bu" or "n doc" as well as "alastair" & "john". The downside is a loss of speed compared to a straight comparison. By coincidence, the questions that I have asked in the last couple of days about "Variable Radio Groups" are to do with the selection of names. I have artists, writers, musicians, instruments, etc., and in order to populate a dblookupcombobox with the correct selections I pop-up a small form with a radio group and a DBEdit where the name-type can be chosen and a few letters of the name entered. These two then go back to the main form as variables and the lookup uses them to display the refined full names from which one can be selected. In your case, it sounds as if you need to decide how you are going to handle your descriptions. There's really only two choices: force similarity or allow a free for all. The obvious choice is to force similarity but it often can't be done so you'll probably end up with a free for all. If you can offer a menu to choose the description from then you'll probably find that a certain amount of similarity happens anyhow although you'll need to allow an "add new" option. Without seeing what you're doing I think that I would create a form based on your schedules table with VariableLookUpComboBox with the descriptions displayed. You then take that value and work your magic on it as you have been doing with your original selection in an "on exit from" eep on the combobox which then pops-up the second form based on the Rates table. On this second form you select the correct rate from another VariableLookUpComboBox which is based on the selection from the first form. When you have this value you close the form and go back to your first form and use your eep to display the rate in a Variable Label. Then you can update the table either when you leave the row or, if you prefer, with a button to confirm and move on the next row. If you need to limit the number of rows selected in the lookup on the first form you could have a Variable Edit (vLimit) for the user to key in a few letters and use that in you lookup's where clause. So the lookup setting tab may look something like: Source - Schedule / Key - Description Load - Description Where - description contains .vLimit The "on exit from" eep may look something like: edit using [Rates form] where and &val recalc var return The lookup on the second form is straight from the Rates table and the value goes back to the first form to be displayed with the recalc var command. You then update the table with that value. So the lookup setting tab in this case may look something like: Source - Rates / Key - Rate Load - Rate Where - &val Once you know the size of the second form you can then adjust the first one so that the second one pops up in an empty space in the middle over text that says "Rates will appear here" or however you want to make it look slick. I hope that helps but, in any case, wait and see if you get any better suggestions once everyone's back at work tomorrow. Good luck, Regards, Alastair. ----- Original Message ----- From: "John Docherty" <[EMAIL PROTECTED]> To: "RBG7-L Mailing List" <[EMAIL PROTECTED]> Sent: Sunday, September 19, 2004 11:35 AM Subject: [RBG7-L] - Re: Matching Values Alastair, Thanks for your reply. To explain further, there is no link between the two tables as such. The aim is to allow the entry of a row in the Schedule table, via a form, with a description, de, that can vary a little, and then to use an EEP to determine the matching item in the Rates table, based on the predefined select clause which is also a column, al, in the Rates table. (The select clause 'analyses' the description from the Schedule table to select a unique row in the Rates table that matches the criteria used to define the item.) The appropriate labour and material rates would then be selected from that row in the Rates table, and entered into the row in the Schedule table. Hence the EEP needs to contain a select clause that finds the row in the rates table where the select clause in column al is true. Something like Sel rseq from rates where &al is true, would choose the correct row, but how does one actually achieve this ? The alternative I suppose would be to limit the description entered so that it was actually selected from the rates table via a list. Maybe this would be the simplest solution. (The original reason for setting up the select clause as a column was that we receive schedules from various consultants who differ in the way they describe the same item. The solution I have been using with the cursor works when analysing a schedule that has already been set up - what I now want to do is to allow this to be done dynamically when we do our own schedules, without requiring the descriptions to be too rigid.) I hope that this explains what I want to achieve. Any further thoughts would be appreciated. Thank you. Regards, John Docherty -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Alastair Burr Sent: Sunday, September 19, 2004 6:43 PM To: RBG7-L Mailing List Subject: [RBG7-L] - Re: Matching Values John, it's usually a bit quiet on this list over the weekend and I'm not at all sure that I follow what you're trying to do. However, here's a couple of suggestions that you can throw back if you want with some more detail if you can: Firstly, it sounds as if you can use the same "where and &val" clause to select the row that you want to edit as you do at the moment. If so, you can use this to display the row on your form based on the Rates table in a DBEdit based on the Schedules table. However, it sounds as if you don't have a simple link between your tables so this could be a problem. Alternatively, and especially if a link between the tables is your problem, you can use the "where and &val" clause in your EEP to pop-up a second form based on the Schedules table: edit using [new form] ... where and &val If the row doesn't already exist in the Schedules table then I would create it in your EEP first and pop-up a second form based on the new row to edit. Regards, Alastair. ----- Original Message ----- From: "John Docherty" <[EMAIL PROTECTED]> To: "RBG7-L Mailing List" <[EMAIL PROTECTED]> Sent: Saturday, September 18, 2004 8:47 PM Subject: [RBG7-L] - Matching Values I have an estimating package which has a RATES table and a SCHEDULE table. The RATES table includes an item description (column de) and, as the description for the same item can vary depending on the consultant preparing the schedule, another column which includes the basis of a select clause used to identify the item (column al). Each row in the RATES table has a unique number identified by column rseq. eg: de: 400 dia spigot with damper al: (de contains ‘400’ and de contains ‘spigot’) rseq: 100 While using a cursor to step through the RATES table, and an update clause, I can extract the appropriate rates and update the SCHEDULE table (update schedule set .... where and &val; val being the variable extracted from the al column using the fetch statement) I am trying to develop this package to allow manual entry of the data into the SCHEDULE table (so far this is imported from a spreadsheet supplied). As part of this I need to determine a way of doing this from an EEP in a form as soon as the description is entered into a form. This would require a command which selects a row in the RATES table where a select clause returns a match between the description in the SCHEDULE table (which would be a variable from the form) and the predefined select clause stored in the RATES table. Select rseq from RATES where &al is true If anyone has any suggestions as to how this can be achieved I would appreciate your assistance. (Maybe I have just missed the obvious here.) Thank you, John Docherty -- Outgoing mail is certified Virus Free. Checked by AVG Anti-Virus (http://www.grisoft.com). Version: 7.0.271 / Virus Database: 264.9.3 - Release Date: 17/09/2004 -- Incoming mail is certified Virus Free. Checked by AVG Anti-Virus (http://www.grisoft.com). Version: 7.0.271 / Virus Database: 264.9.3 - Release Date: 17/09/2004 -- Outgoing mail is certified Virus Free. Checked by AVG Anti-Virus (http://www.grisoft.com). Version: 7.0.271 / Virus Database: 264.9.3 - Release Date: 17/09/2004
