navigate recordset

I have a table with too many fields to show in a ListBox. Made a Window with TextFields for each field in the table. Also in that window is SegmentControl with three sections for rs.movenext, rs.moveprevious, rs.movelast. All the code is in the Action event of the SegmentControl. When running the window opens with no data in any of the TextFields. Press first segment and the last record shows in the TextFields. The problem is it shows only one record and cannot navigate through the record set. Here is the code. What am i missing? Please help, I am new at this.

dim sql10 as text
dim rs10 as RecordSet

sql10=“Select account, company, fund, nbr, whose, ira, type, int, symbol, spend, payto from active”

while not rs10.eof
//if rs10<> nil then
TextField1.text = rs10.Field(“account”).StringValue
TextField2.text = rs10.Field(“company”).StringValue
TextField3.text = rs10.Field(“fund”).StringValue
TextField4.text = rs10.Field(“nbr”).StringValue
TextField7.text = rs10.Field(“symbol”).StringValue
TextField5.text = rs10.Field(“whose”).StringValue
ComboBox1.text = rs10.Field(“ira”).StringValue
ComboBox2.text = rs10.Field(“type”).StringValue
TextField6.text = rs10.Field(“int”).StringValue
ComboBox3.text = rs10.Field(“spend”).StringValue
ComboBox4.text = rs10.Field(“payto”).StringValue
//end if

Select case itemIndex
case 0
//MsgBox(“Next pressed”)
case 1
//MsgBox(“Prev pressed”)
case 2
//MsgBox “New Record pressed”
end select

The RecordSet must be defined as a property in the window:

Name: rs10 Type: RecordSet Default: (leave this empty) Scope: Private

In the Open event of the window add this:

dim sql10 as text sql10="Select account, company, fund, nbr, whose, ira, type, int, symbol, spend, payto from active" rs10=invest_test.SQLSelect(sql10)

Create a method in the window and name it UpdateUserInterface:

Sub UpdateUserInterface() TextField1.text = rs10.Field("account").StringValue TextField2.text = rs10.Field("company").StringValue TextField3.text = rs10.Field("fund").StringValue TextField4.text = rs10.Field("nbr").StringValue TextField7.text = rs10.Field("symbol").StringValue TextField5.text = rs10.Field("whose").StringValue ComboBox1.text = rs10.Field("ira").StringValue ComboBox2.text = rs10.Field("type").StringValue TextField6.text = rs10.Field("int").StringValue ComboBox3.text = rs10.Field("spend").StringValue ComboBox4.text = rs10.Field("payto").StringValue End

And this goes into the SegmentedControl’s Action event:

Select case itemIndex case 0 //MsgBox("Next pressed") rs10.MoveNext case 1 //MsgBox("Prev pressed") rs10.MovePrevious case 2 //MsgBox "New Record pressed" rs10.MoveLast end select UpdateUserInterface()

Another thing that will help you in the short to long term is to give a specific name to your TextFields like:

TF_Account.text = rs10.Field("account").StringValue TF_Company.text = rs10.Field("company").StringValue TF_Fund.text = rs10.Field("fund").StringValue TF_Nbr.text = rs10.Field("nbr").StringValue

and so on (for ComboBox too).

What type of database are you connected to ?
Not all databases support recordset.moveprevious

Thanks Eli, works great. Another question: If I navigate to last record then move next, I get a blank record. I have added a button to the form to save the data entered here to the database. I get no exceptions but this code does not save the record to the database (SQLite 3.0). What am I missing? Here is the code:

''GET VALUES FROM FORM ----------------------------------
dim sCT,sCO,sFN,sNO,sSY,sWO,sIR,sTY,sSP,sIN,sPT,sSO,sMI as string
sCT = Window6.TextField1.Text
sCO = Window6.TextField2.Text
sFN =Window6.TextField3.Text
sNO = Window6.TextField4.Text
sSY = Window6.TextField7.Text
sWO = Window6.TextField5.Text
sIR = Window6.ComboBox1.Text
sTY = Window6.ComboBox2.Text
sSP = Window6.ComboBox3.Text
sIN = Window6.TextField6.Text
sPT = Window6.TextField8.Text
sSO = “00”
sMI = “00”

//SAVE CURRENT RECORD TO DATABASE---------------------------------
invest_test.SQLExecute(“BEGIN TRANSACTION”)

dim nu9 as string = “INSERT INTO ““ACTIVE””(account,company,fund,nbr,whose,ira,type,int,source,mingle,symbol,spend,payto) VALUES (”+"’"+sCT+"’, ‘"+sCO+"’, ‘"+sFN+"’, ‘"+sNO+"’, ‘"+sWO+"’, ‘"+sIR+"’, ‘"+sTY+"’, ‘"+sIN+"’, ‘"+sSO+"’, ‘"+sMI+"’, ‘"+sSY+"’, ‘"+sSP+"’, ‘"+sPT+"’)"
//MsgBox(nu9) //This looks good



you must check recordset.eof for that.

Check invest_test.Error after invest_test.SQLExecute(nu9).

We’ll bring up prepared statements and SQL injection later :slight_smile: