Populate Dropdown and change values of the feilds in an instance

[Former Community Member]

Hello,<br /><br />Sorry for the long post, I hope this is as clear as possible. Any help or pointers to the right direction would be soo greatly appreciated. And sorry if there is another post with the answer, I didnt see it. Or it doesnt work for me.<br /><br />I have a SQL DB table with the following data:<br />ID | POWER | COST | CHARGE<br />1 | 100 | 2000 | 2500<br />2 | 150 | 2050 | 2600<br />3 | 200 | 3000 | 4000<br /><br />I have a repeatable subform <This is what I call an instance, not sure of terminology> (lineItem) with the following objects<br />A [subform] with a delete button. To delete the entire line, or instance.<br />POWER [dropdown]<br />COST [text]<br />CHARGE [text]<br /><br />My Data connection is: <br />Provider=SQLOLEDB.1;<br />Integrated Security=SSPI;<br />Persist Security Info=False;<br />Initial Catalog=pieces;<br />Data Source=PCSQL;<br />Use Procedure for Prepare=1;<br />Auto Translate=True;<br />Packet Size=4096;<br />Workstation ID=C0089;<br />Use Encryption for Data=False;<br />Tag with column collation when possible=True<br /><br />Record Source is: [Table]<br /><br />What I am trying to do if possible, is auto populate the POWER dropdown with the values, and then populate the rest of the line with the corresponding data.<br />So if I choose, 150, the other two fields would then refresh with 2050, and 2600<br />At the moment the drop down populates 100, 2000, and 2500 and I cannot get it to populate the drop down with any other values.<br /><br />Thank you for your help.

[Former Community Member]

So you need to make two calls to the DB. The 1st one will populate the dropdown with teh values 100, 150 and 200 and then when the user chooses an item in the dropdown you would do a second call to the db to get that specific record. Put your code on the exit event of the drop down. The SQL would be something like:



'Select * from Table where Power = "' + DropDownList.rawValue + '"'

[Former Community Member]

Thanks Paul,

two questions.



1- my drop down only populates the first value of power, and doesn't list the other 2.



2- 'Select * from Table where Power = "' + DropDownList.rawValue + '"'

DO I make this another Data connection, or use the previous one?

I am not sure where I make that call, and I assume its as formcalc right?



Thanks again.

[Former Community Member]

That is because the connection can only retrieve one record at a time. There is no recordSet object that you can store the retrieved data from and then manipulate it locally. You would have to get each record one by one, parse out the value you want and then populate the dropdown. Not very good if you have a lot of records.



I would suggest a second conenction for #2. I only gave you the SQL statement to retrive the record you want.