Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

sorting a DBCombo & prob with Datareports

Status
Not open for further replies.

rajlobban

Programmer
Jul 12, 2000
6
CA
hi all,<br>&nbsp;&nbsp;&nbsp;i have recently just started learning Visual Basic 6 without any prior knowledge of basic.<br>my problem is this.. <br>1. i have combobox and had used it to list items from an unsorted foxpro database file or (using a field from (*.mdb), which ever you can help with) but now i would like to sort the list in the Dropdown combo boxalphabetically... is there any easy way of doing this or any way at all....please give me different methods if any.<br>and.<br>2.<br>when using a datareport form i cannot get the data to be refreshed without restarting the program.. i have tried the solutions given on this forum and the dont even work, in most cases it tell me that it does not even support this method. the idea is that the datareport gets it data from a command in the Dataenviroment that is linked to a MSAcess database, where i retrieve the recordset. during the run time i add - update then view the datareport with the&nbsp;&nbsp;new data -then i delete this data. the first time i run the query it works perfectly, however, any other time during the execution of the program it shows me the first data that i had requested as if getting it from a &quot;cache&quot; or something. after it deletes the records i close the program and view the table and as it should be the data is gone ,please can some one advice.here is an example of the codes used:<br>Private Sub Command1_Click()<br><br>Dim custName As String<br>Dim stuff1 As String&nbsp;&nbsp;&nbsp;&nbsp;<br>Dim stuff1x As String&nbsp;&nbsp;&nbsp;<br>'Variables used to print on report<br>Dim codes As String<br>'Initialize variables<br>cdes = &quot;&nbsp;&nbsp;&nbsp;&quot;<br><br>Dim try As Boolean<br>Dim try2 As Boolean<br><br>'Initialize local variables<br>stuff1 = &quot;NULL&quot;<br>try = True<br>try2 = False<br>'******************************<br>'On Error GoTo ErrorHandler 'Error Handling<br><br>custName = Format(DBCombo1.Text, &quot;&gt;&quot;)&nbsp;&nbsp;'Gets the customer id or company name<br><br>transaction.Recordset.MoveFirst 'reset database to Begining of file<br><br>cusrep.Refresh<br><br>arcustd.Recordset.MoveFirst<br>Do Until arcustd.Recordset.EOF<br>&nbsp;&nbsp;If arcustd.Recordset![custno] = custName Then&nbsp;&nbsp;'Works with Input from user<br>&nbsp;&nbsp;&nbsp;&nbsp;stuff1x = arcustd.Recordset![company]<br>&nbsp;' MsgBox &quot;ID# Given&quot;<br>&nbsp;&nbsp;End If<br>&nbsp;&nbsp;If arcustd.Recordset![company] = custName Then&nbsp;&nbsp;'Works with List<br>&nbsp;&nbsp;&nbsp;&nbsp;stuff1x = arcustd.Recordset![company]<br>&nbsp;&nbsp;&nbsp;&nbsp;custName = arcustd.Recordset![custno]<br>&nbsp;&nbsp;'&nbsp;&nbsp;MsgBox &quot;Company name Given&quot;<br>&nbsp;&nbsp;End If<br>&nbsp;&nbsp;arcustd.Recordset.MoveNext<br>Loop<br><br>Do Until transaction.Recordset.EOF<br>&nbsp;&nbsp;If transaction.Recordset![cust_id] = custName Then<br>&nbsp;&nbsp;&nbsp;&nbsp;stuff1 = transaction.Recordset![cust_id]<br>&nbsp;&nbsp;&nbsp;&nbsp;cusrep.Recordset.AddNew<br>&nbsp;&nbsp;&nbsp;&nbsp;cusrep.Recordset![custName] = stuff1x<br>&nbsp;&nbsp;&nbsp;&nbsp;cusrep.Recordset![cust_id] = transaction.Recordset![cust_id]<br>&nbsp;&nbsp;&nbsp;&nbsp;cusrep.Recordset![points] = transaction.Recordset![points]<br>&nbsp;&nbsp;&nbsp;&nbsp;cusrep.Recordset![rdate] = transaction.Recordset![date_r]<br>&nbsp;&nbsp;&nbsp;&nbsp;try2 = True<br>&nbsp;&nbsp;&nbsp;&nbsp;If transaction.Recordset![redem_code] &lt;&gt; 0 Then&nbsp;&nbsp;&nbsp;'&nbsp;&nbsp;MsgBox &quot;Has redem code&quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Do Until redeem.Recordset![redem_id] = transaction.Recordset![redem_code]<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;redeem.Recordset.MoveNext<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Loop<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;cusrep.Recordset![Description] = redeem.Recordset![Description]<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;cusrep.Recordset.Update<br>&nbsp;&nbsp;&nbsp;&nbsp;Else<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;cusrep.Recordset![invno] = transaction.Recordset![invno]<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;cusrep.Recordset.Update<br>&nbsp;&nbsp;&nbsp;&nbsp;End If<br>&nbsp;&nbsp;End If<br>&nbsp;&nbsp;<br>&nbsp;&nbsp;redeem.Recordset.MoveFirst&nbsp;&nbsp;'reset redeem tables<br>&nbsp;&nbsp;transaction.Recordset.MoveNext<br>Loop<br><br>If stuff1 = &quot;NULL&quot; Then<br>&nbsp;&nbsp;&nbsp;MsgBox (&quot;Customer Record NOT Found&quot;)<br>Else<br>'&nbsp;&nbsp;DataReport2.Refresh<br>&nbsp;&nbsp;cusrep.Recordset.AbsolutePosition = 0<br>&nbsp;&nbsp;'Load frmout<br>&nbsp;&nbsp;'frmout.Show<br>&nbsp;&nbsp;'cusrep.Recordset.MoveFirst<br>&nbsp;&nbsp;'RAJ :&nbsp;&nbsp;&nbsp;*** cusrep.Recordset.Restartable<br>&nbsp;&nbsp;'RAJ :&nbsp;&nbsp;&nbsp;*** data1.rscust_st.Requery<br>&nbsp;&nbsp;<br>&nbsp;&nbsp;'RAJ:&nbsp;&nbsp;&nbsp;DataReport2.Refresh<br>&nbsp;&nbsp;'data1.rscust_st.Requery<br>&nbsp;&nbsp;'&&**&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;DataReport2.Show<br>&nbsp;&nbsp;Load frmout<br>&nbsp;&nbsp;frmout.Show<br>&nbsp;&nbsp;countp = 1<br>&nbsp;&nbsp;Do Until countp = 20<br>&nbsp;&nbsp;&nbsp;frmout.Print &quot; &quot;<br>&nbsp;&nbsp;&nbsp;countp = countp + 1<br>&nbsp;&nbsp;Loop<br>&nbsp;&nbsp;frmout.Print &quot;Customer ID&quot;; Space(10); &quot;Company&quot;; Space(30); &quot;Points&quot;; Space(10); &quot;Date&quot;; Space(15); &quot;Description&quot;<br>&nbsp;&nbsp;frmout.Print &quot; &quot;<br>&nbsp;&nbsp;frmout.Print &quot; &quot;<br>&nbsp;&nbsp;Do Until cusrep.Recordset.EOF<br>&nbsp;&nbsp;&nbsp;&nbsp;If cusrep.Recordset![Description] &lt;&gt; &quot;Null&quot; Then cdes = cusrep.Recordset![Description]<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;frmout.Print cusrep.Recordset![cust_id]; Space(15); cusrep.Recordset![custName]; Space(15); cusrep.Recordset![points]; Space(10); cusrep.Recordset![rdate]; Space(10); cdes<br>&nbsp;&nbsp;&nbsp;&nbsp;cusrep.Recordset.MoveNext<br>&nbsp;&nbsp;&nbsp;&nbsp;cdes = &quot;&nbsp;&nbsp;&nbsp;&quot;<br>&nbsp;&nbsp;Loop<br>&nbsp;&nbsp;'&nbsp;&nbsp;&nbsp;frmout.Print cusrep.Recordset![cust_id] '; Space(7); cusrep.Recordset![custName]<br>&nbsp;&nbsp;'RAJ: Load csform<br>&nbsp;&nbsp;'mshflexgrid1.Refresh<br>&nbsp;&nbsp;'RAJ: csform.Show<br>&nbsp;&nbsp;'MsgBox &quot;Wait Look&quot;<br>&nbsp;&nbsp;'Load frmcusrep<br>&nbsp;&nbsp;'frmcusrep.Show<br>&nbsp;&nbsp;'csform.mshflexgrid1.Refresh<br>&nbsp;&nbsp;'RAJ:&nbsp;&nbsp;DataReport2.DataMember = &quot;select * from cusrep where cusrep.recordset![cust_id] = &quot; & custName<br>&nbsp;&nbsp;'DataReport2.DataSource = data1<br>&nbsp;&nbsp;'RAJ:&nbsp;&nbsp;DataReport2.Show<br>&nbsp;&nbsp;'Loading Printing option<br>&nbsp;&nbsp;'Load printopt3<br>&nbsp;&nbsp;'printopt3.Show<br>&nbsp;&nbsp;'cusrep.Recordset.BOF<br>&nbsp;&nbsp;If try2 Then<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;cusrep.Recordset.MoveFirst<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Do Until cusrep.Recordset.EOF<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;cusrep.Recordset.Delete<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;cusrep.Recordset.MoveNext<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Loop<br>&nbsp;&nbsp;&nbsp;End If<br>End If<br><br>transaction.Recordset.MoveFirst<br>Exit Sub<br><br>ErrorHandler:<br>&nbsp;&nbsp;&nbsp;MsgBox &quot;No Records Found&quot;, vbInformation, &quot;Record Not Found&quot;<br><br>End Sub<br>also the reason i use a Datareport is because it is the only form i know that can print multiple pages, please advice.<br>i'm sorry that this is SOOOO Longggg&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br>
 
In regards to your first question:<br>&nbsp;&nbsp;&nbsp;when you populate the recordsource for the RECORDSET write an query that sorts the contents alphabetically.<br><br>For example:<br>data1.recordsource = &quot;SELECT * from [&quot; & tablename & &quot;] ORDER BY [&quot; & fieldname & &quot;]&quot;<br><br>NOTE: this is assuming you make 'tablename' and 'fieldname' variables<br><br>This should allow your list to be populated alphabetically (if you select a text field in the database)<br><br>I know that this will work with an access database (.mdb) however, i am not sure if it works with a foxpro database.
 
thanks leviathan07.....<br>it helped a bit... except that i didn't exactly know how to use the code..... but i have a little more understanding..<br>
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top