Drop down, look up lists 2

[cobweb]

Hi there:

I am a big fan of Paradox...except for the look up lists!
I am setting up a paradox database where I want a drop down list on a field that reads one field from the lookup table but writes another...it is a 'reason for failure" so i want the drop down list to read to the user "broken in transit" but return to the field 'reason' in the master table the primary key of the reason table..which is a number.

I know Access does this thing sweet as pie; can paradox?The 'Ctrl & spacebar' routine is not what the users want!

Thanks!

 

[Kliot]

Sooo close and on a Friday, I hope we can get it figured out before the weekend.

The else statement changes the isListDown to false if it's true. I think you may have the code in the wrong spot, it needs to be in the "action" event of the list portion of the combo box field, not the "action" event of the field.

I hope I'm making sence here, From the object exploere click the "+" next to the field to bring up the #list properties.

To be sure it's working add a view() or msginfo() after the isListDown is assigned, and it should change wheverer the list goes up or down either by your code or by the user using the arrow. I hope this makes sence to you, I think I'm confusing myself. Let me know if this was the problem

method action(var eventInfo ActionEvent)
doDefault
if eventinfo.id() = EditDropDownList then
if isListDown = false then
isListDown = true
else
isListDown = false
endif
isListDown.view()
endif
endMethod

Perrin

 

[LWB]

Perrin,

You are right. I have the code in the "action" event of the field. I will put in on the List.

I'll let you know if it works. Thanks.

Lynn

 

[LWB]

Perrin,

Something is still wrong. It doesn't detect all the changes in the opening and closing of the drop down list. I can end up with the view() or MsgInfo out of sync with the actual status of the drop down list. Somehow the list can be closed without trigging the action event.

I did removed the code on the depart and changevalue methods for the field that set the isListDown to false.

All the pieces of code we have discussed are on the field except for the action event which is on the list.

Lynn

 

[LWB]

Perrin,

I added the isListDown = False to the Depart method of the field. That fixes everything until I click on the down arrow to open the list. Then it appears that the action event on the list is called twice in a row (with no chance to enter any data). First it says it is true, then it closes the list and says it is false. If I leave the field the isListDown=False in the Depart method resets things, but the same thing happens next time the user clicks on the down arrow.

Any ideas?

Lynn

 

[Kliot]

Lynn,

Unfortunatly the action is not always called as I had thought it would have, darn that Paradox. The isListDown=false on the depart seems to help but still not perfect.

Got a new Idea, it looks like the "arrive" and "depart" event on the #list are called everytime the list is opened and closed. Try assigning isListDown = true on the "arrive" event and isListDown = false on the "depart" event and remove the code from the action event and the field depart event. I just tried it and it seems to work perfect, but then you're pretty good at finding bugs.

 

[LWB]

Perrin,

You did it!!!!!!!!!!!!!!!!

It works perfectly!!!!!!!!!!!!!!!!!

Thanks!!!!!!!!!!!!!!

I never would have figured that out myself. I've been really impressed with the expertise here.

Lynn

 

[Kliot]

Hurray,

I was begining to wonder if we were going to have to find another approach.

I'm glad we was able to solve the problem, I learned a lot by helping you and I'm sure I'll use this procedure in future applications.

Take care and let me know if you get stuck again.
Perrin

 

[LWB]

Perrin,

I've been using this code awhile now and there is one thing I would like to change, but I don't know how.

If I type in a new entry that does not match anything in the edit list (as we made it operate in the code above) and I hit the enter key it picks the closest match. I have to either click elsewhere on the form or close the drop down edit window for the new value to be excepted. Is there a way to make it accept the new value if it doesn't match what has been typed?

In other words, if "Model A" and "Model C" are in the drop down list and I typed "Model B" and hit the return key the field value will say "Model A" (the closest match). I want it to take "Model B" as a new entry. However if I just type "Mod" (bringing up "Model A" in the drop down edit box and hit return I would want it to take "Model A" and not just "Mod"(otherwise complete fields would have to be typed all the time and there would be no value in a drop down edit box).

I hope this is clear.

Lynn

 

[footpad]

Lynn,

If I'm following you, yes, this is possible; however, it's technicaly difficult to know whether or not the non-matching value is a new value or a shortened version of a possible matching value.

I suppose the best way to handle it would be to see if the existing values have any possible matches. if so, then pick the first one. If there are no matching values, then it asks the use to save the value as a new one. With this in mind, here's how I'd approach that particular problem:

method canDepart(var eventInfo MoveEvent)
var
   tc   tCursor
   str  String
endVar

   tc.open( "LISTVALS" )
   str = self.Value
   
   tc.setRange( str, str + "zz" )
   if tc.nRecords() > 1 then
      str = tc.( 1) 
      self.Value = str
   else
      if msgQuestion( "New Value Detected", 
                      "Would you like to add " + 
                      "this to the list?" ) = "Yes" then
         tc.edit()
         tc.insertRecord()
         tc.( 1 ) = str
         tc.postRecord()
         refreshList()
      endIf
   endIf
   
   siCounter,
   siIndexNo smallInt
   uiListObj uiObject
   astrNames Array[] String
endVar

(Mind you, this is off the top of my head, so may need some tweaking to properly compile.

Now, you'll note that this assumes your list's values are stored in a single field table with a key defined on the single field. (Perhaps your values are assigned to the list using its datasource property).

Also, refreshList() is a custom procedure for code that replaces the items in your list. For some ideas on how to structure the list assignment code, please see

http://techtricks.com/paradox/poplists.php

and

http://techtricks.com/paradox/limitlists.php

for what I heop is useful information.

Finally, please note that this assumes that the code is placed on the canDepart event of the field object, not the list object.

Hope this helps...

-- Lance

 

[footpad]

Lynn,

Oops, sorry. I didn't preview that post and the code got messed up. Sorry. The correct version should be:

method canDepart(var eventInfo MoveEvent)
var
   tc   tCursor
   str  String
endVar

   tc.open( "LISTVALS" )
   str = self.Value
   
   tc.setRange( str, str + "zz" )
   if tc.nRecords() > 1 then
      str = tc.( 1)
      self.Value = str
   else
      if msgQuestion( "New Value Detected",
                      "Would you like to add " +
                      "this to the list?" ) = "Yes" then
         tc.edit()
         tc.insertRecord()
         tc.( 1 ) = str
         tc.postRecord()
         refreshList()
      endIf
   endIf
   if tc.isAssigned() then
      tc.close()
   endIf
endVar

Again, my apologies...

-- Lance

 

[LWB]

Lance,

Thanks. You understood what I am trying to do.

Actually my drop down list is computed on the fly after the user types in the first two letters (see Perrin's 5 March entry above). So I don't really want to add a new item to the list (it will change the next time anyway) but just accept the value typed (or the first match as you say).

I understand (I think) how your code works, but I'm not sure how to incorporate into what I have going on. But maybe I'm just confused by my use of KeyChar and nothing will interfer. I will have to experiment.

Lynn

 

[Kliot]

Lynn,

A simple solution would be to press the Tab key insted of the Enter key when a match is not found. I'm sure you could program around the problem but I don't see an easy solution.

If the tab key doesn't work for you let me know and I'll see if I can come up with something else.

Perrin

 

[LWB]

Perrin,

Yes, the Tab key does work.

I was just hoping for a more elegant solution.

I already have some code on the CanDepart(). I haven't tried Lance's suggestion yet.

Lynn

 

[footpad]

Lynn,

In that case, you could simply remove the ELSE clause or modify the limit lists article I pointed you to earlier.

Hope this helps...

-- Lance

 

[Kliot]

Lynn,

I have a question with Logic here, suppose you have a list that containg the word "Mountain" and a user types in "Mount" and presses enter how will you decide if the user wants the list selection "Mountain" or wants to enter "Mount" as a new value? Using the "Enter" and "Tab" key allows the user to make the decision. If you only want to use the "Enter" key you can adapt Lances limit lists article to see if the typed value is contained in the list.

Perrin

 

[LWB]

Perrin,

I was assuming that if I wanted "Mount" instead of "Mountain" I would have to either close the drop-down window or click elsewhere on the form after typing "Mount", just as I must do now to make it accept a value not in the list (well I just learned that Tab has the same effect).

I was just looking for the case where when "Mount.." didn't match anything in the list, it would automatically assume that it was a new value.

Using TAB may be be the best solution (it is certainly the easiest). I just have to educate myself to use it, as well as educate other users!

Lynn

 

[Kliot]

Lynn,

Here is something to try that I think will do what you want. Attach the code to the keyPhysical properties of the list (be sure to attach it to the list and not the field)

method keyPhysical(var eventInfo KeyEvent)
var
n longint
listVal string
endvar

if eventinfo.vchar() = "VK_RETURN" then
listVal = self.list.value
ignoreCaseInStringCompares(Yes)
n = listVal.search(combofield.value) ; replace combofield with the name of the field
if n = 0 then
action(EditDropDownList)
endif
endif
endMethod

What I am doing is trapping the enter key and comparing the list value with the value typed in if the list contains that value I accept the default behavior, if it doesn't I close the list and accept the typed in value.

Let me know how it works
Perrin

 

[LWB]

Perrin,

Once I figured out that in the following line:
n = listVal.search(combofield.value)
that combofield was the field name and not the list name it worked perfectly!

Thanks. That was exactly what I wanted. You guys are great!

Lynn

 

[Kliot]

Lynn,

I guess you missed the comment " ; replace combofield with the name of the field", happens to the best of us

An added bonus is that in a situation such as the Mount, Mountain scenario where you want Mount to be a new entry you can press the Tab key to override the menu.

Glad to help
Perrin