Help With VFP Combo Box

[foxprox558]

Ok, so I have this program I'm building for my local dry cleaners (Who I am trying to transition out of a DOS foxpro System) and basically there are only a couple of databases, among the main ones are the orders.dbf and accounts.dbf

accounts=Customer Accounts
Orders = All orders by customers

I have a form that activates after you push new order on the main launcher, which is basically set up like this



Enter last Name
combo1
text1

Search button Select Customer Btn

what combo1 is, when you push search for the customer, it searches for all customers that have that last name, and puts their account number, first and last name in. I'm having trouble, because It only is allowing me to see 2 names in there at a time. say I search Smith and I have 3 smiths in my table, I can only see 2

here is the search code:

USE accounts
thisform.combo1.Clear
thisform.combo1.Enabled=.t.
thisform.command2.Enabled=.t.
nCol=0
nDx=0
LOCATE FOR ALLTRIM(accounts.last)=ALLTRIM(thisform.text1.Value)
nCol=nCol+1
nDx=nDx+1
DO WHILE ALLTRIM(thisform.text1.Value)=ALLTRIM(accounts.last) OR ALLTRIM(thisform.text1.Value)=ALLTRIM(accounts.phone) AND NOT EOF()
thisform.combo1.AddItem(TRANSFORM(accounts.custno)+": "+ALLTRIM(accounts.last)+", "+ALLTRIM(accounts.first),nDx,nCol)
GOTO RECNO()+1
enddo

 

[GriffMG]

Well I don't know about anything else, but :

GOTO RECNO()+1

is not a conventional way to process a table - is there an index on accounts?

I would suggest you rework the loop into either a SCAN/ENDSCAN (which most VFP programmers favour for speed) or like this:

USE accounts
thisform.combo1.Clear
thisform.combo1.Enabled=.t.
thisform.command2.Enabled=.t.
nCol=0
nDx=0
LOCATE FOR ALLTRIM(accounts.last)=ALLTRIM(thisform.text1.Value)
nCol=nCol+1
nDx=nDx+1
DO WHILE .not. EOF() .And. (ALLTRIM(thisform.text1.Value)=ALLTRIM(accounts.last) .OR. ALLTRIM(thisform.text1.Value)=ALLTRIM(accounts.phone))
  thisform.combo1.AddItem(TRANSFORM(accounts.custno)+": "+ALLTRIM(accounts.last)+", "+ALLTRIM(accounts.first),nDx,nCol)
  skip  
enddo

Or in the alternative, get your results into a cursor and then use that as the record source for the combo box.



Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

 

[Olaf Doschke]

Griff has made a right step in using skip rather than goto recno()+1, as that will move in index order rather than in record number order. I you don't have an index set to order records by last name this won't change behaviour, though. This loop can only find all Smiths in your data, if they are in order, as your loop stops, once you find the first non Smith.

It's much simpler to pull all Smiths from the table with SQL again.
SELECT * From customers WHERE Lastname = "Smith" Into Cursor cboCustomersFiltered READWRITE
Then either scan that result and additems or bind your combobox to that result cursor.

Bye, Olaf.

 

[foxprox558]

No luck, guys. I still only get 2 results put into the combo box

 

[Olaf Doschke]

>If you don't have an index set to order records by last name this won't change behaviour, though.

That's what bytes you for sure. I you do an SQL SELECT instead, you will catch all records with a given lastname.

Bye, Olaf.

 

[foxprox558]

The new index is working, but say I search smith, and all the smiths are at the beginning of the table, then after the smiths there is a taylor. I get a message that says "Error: Record Is Out Of Range

 

[EzLogic]

Post your code for that search

Ez Logic
Michigan

 

[foxprox558]

It is exactly what GriffMG has posted above

 

[foxprox558]

USE accounts
thisform.combo1.Clear
thisform.combo1.Enabled=.t.
thisform.command2.Enabled=.t.
nCol=0
nDx=0
LOCATE FOR ALLTRIM(accounts.last)=ALLTRIM(thisform.text1.Value)
nCol=nCol+1
nDx=nDx+1
DO WHILE .not. EOF() .And. (ALLTRIM(thisform.text1.Value)=ALLTRIM(accounts.last) .OR. ALLTRIM(thisform.text1.Value)=ALLTRIM(accounts.phone))
  thisform.combo1.AddItem(TRANSFORM(accounts.custno)+": "+ALLTRIM(accounts.last)+", "+ALLTRIM(accounts.first),nDx,nCol)
  skip  
enddo

 

[Olaf Doschke]

At what line is that error?

In your code .not. EOF() is ensuring you don't skip after the end of the table, and (ALLTRIM(thisform.text1.Value)=ALLTRIM(accounts.last) makes sure the record still has the lastname as entered in text1.

What's bad about the index solution is, you can only sort by either name or phone, not by both. Your second condition ALLTRIM(thisform.text1.Value)=ALLTRIM(accounts.phone) would find all records with the same phone number as entered in text1, alternatively. This kind of loop can only work for either last or phone, not for both, so you need to switch indexes depending on what the input would be.

I would sill suggest to replace all this with a query.

* In both cases
SELECT * FROM accounts WHERE (ALLTRIM(thisform.text1.Value)=ALLTRIM(accounts.last) .OR. ALLTRIM(thisform.text1.Value)=ALLTRIM(accounts.phone)) INTO CURSOR curFilteredAccounts

* Then EITHER 1) add these records to the combo:
SELECT curFilteredAccounts
SCAN
   thisform.combo1.AddItem(TRANSFORM(curFilteredAccounts.custno)+": "+ALLTRIM(curFilteredAccounts.last)+", "+ALLTRIM(curFilteredAccounts.first),nDx,nCol)
ENDSCAN

* OR 2) let the combobox display that cursor
WITH thisform.combo1
 .rowsourcetype = 0
 .rowsource = ""
 && may configure these two settings visually in the form designer instead
 .columncount = 3
 .columnwidths = "60,60,60" && adjust to your combo size
 * set the new rowsource
 thisform.combo1.rowsource = "curFilteredAccounts.custno,last,first"
 thisform.combo1.rowsourcetype = 6

Don't do both, just one or the other.

Bye, Olaf.

 

[Olaf Doschke]

Better yet change the sql to not contain thisform.text1.Value as expression. Ideally you'd decouple data access from UI and not put this into a form method at all, but into a separate data access / biz object method FilterAccounts() or similar, but that's leading too far off the problem for now. That aside, you need

lcFilter = ALLTRIM(thisform.text1.Value)

and then do (SET ANSI ON, if it isn't)

SELECT * FROM accounts WHERE last=m.lcFilter OR phone=m.lcFilter INTO CURSOR curFilteredAccounts

That would also optimize the query with indexes on last and phone field and can make use of both indexes.

Bye, Olaf.

 

[GriffMG]

have you tried incrementing nDx

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

 

[foxprox558]

Thanks Again, Guys!