The Forums on slxdeveloper.com are now retired. The forum archive will remain available for the time being. Thank you for your participation on slxdeveloper.com!
|
|
Add GetField result to a ComboBox
Posted: 06 Aug 08 6:55 PM
|
Hi,
I appologise in advance if this is a simple question - I just can't get my head around it!!
I am using the following to determine the Contract number that an Asset is associated with:
ContrID = GetField("CONTRACTID", "ContractItem", "ACCOUNTPRODUCTID='" & AccProdId & "'")
However an Asset can be associated with more than one Contract, so I decided to use a ComboBox (space is at a premium), but I cannot figure out how to add each of the Contracts the Asset is associated with to the COmboBox.
Perhaps GetField is not the correct command to use (as it seems to just return the first match), any suggestions?
|
|
|
|
Re: Add GetField result to a ComboBox
Posted: 07 Aug 08 3:35 AM
|
Hi Paul,
You could use GetFields to get the list of Contract Numbers.
You will then need to check GetFields returned something, GetFields fills the array you pass it so you will need to work out the length, step though the array and add the items to the combo.
Cheers,
Steve |
|
|
| |
|
Re: Add GetField result to a ComboBox
Posted: 07 Aug 08 7:22 AM
|
Steve,
Thanks for the info & suggestions, the trouble is that I'm okay with VB, but the database side of it is what i'm having trouble with!
I wasn't aware that GetFields returned an array of values, I thought it was just returning one.
If you could give me an example of the code to step through the array I'd appreciate it.
Thanks,
Paul. |
|
|
|
Re: Add GetField result to a ComboBox
Posted: 07 Aug 08 7:48 AM
|
Hi Paul,
There are 2 functions in the SLX Database Support script:
GetField(strField, strTable, strWhere)
GetFields(strField, strTable, strWhere, strValue)
GetField does return one record, GetFields returns the array but for one record so you will need use the record set approach.
Cheers,
Steve |
|
|
|
Re: Add GetField result to a ComboBox
Posted: 07 Aug 08 7:51 AM
|
Ah,
I hadn't eealised there was a GetField & GetFields, btu as you say neither will do what I am after.
Can you give me an example of using the Record Set approach?
Thanks,
Paul. |
|
|
|
Re: Add GetField result to a ComboBox
Posted: 07 Aug 08 8:13 AM
|
Hi Paul,
The following should help - this takes your AccProdId (I haven't validated it contains data) and populates a combo control called cboTest.
Dim objRS
Dim strSQL
Dim i
Set objRS = CreateObject("ADODB.Recordset")
Set objRS.ActiveConnection = Application.GetNewConnection
If ErrorCheck(Application.Translator.Localize("Error getting new connection.")) Then Exit Sub
objRS.CursorLocation = adUseClient
objRS.LockType = adLockBatchOptimistic
objRS.CursorType = adOpenStatic
strSQL = "SELECT CONTRACTID FROM CONTRACTITEM WHERE ACCOUNTPRODUCTID='" & AccProdId & "'"
objRS.Open strSQL
If Not (objRS.BOF and objRS.EOF) Then
objRS.MoveFirst
cboTest.Items.Clear
For i = 0 to objRS.RecordCount - 1
cboTest.Items.Add objRS.Fields("CONTRACTID").Value
objRS.MoveNext
Next
End If
objRS.Close
Set objRS = Nothing
<\Pre>
Hope this helps,
Steve
|
|
|
|
Re: Add GetField result to a ComboBox
Posted: 07 Aug 08 8:31 AM
|
Hi Paul,
Sorry spotted an error in clearing the combo:
Dim objRS
Dim strSQL
Dim i
Set objRS = CreateObject("ADODB.Recordset")
Set objRS.ActiveConnection = Application.GetNewConnection
If ErrorCheck(Application.Translator.Localize("Error getting new connection.")) Then Exit Sub
objRS.CursorLocation = adUseClient
objRS.LockType = adLockBatchOptimistic
objRS.CursorType = adOpenStatic
strSQL = "SELECT CONTRACTID FROM CONTRACTITEM WHERE ACCOUNTPRODUCTID='" & AccProdId & "'"
objRS.Open strSQL
If Not (objRS.BOF and objRS.EOF) Then
objRS.MoveFirst
If cboTest.Items.Count <> 0 Then
cboTest.Items.Clear
cboTest.Text = ""
End If
For i = 0 to objRS.RecordCount - 1
cboTest.Items.Add objRS.Fields("CONTRACTID").Value
objRS.MoveNext
Next
End If
objRS.Close
Set objRS = Nothing
Cheers,
Steve |
|
|
|
Re: Add GetField result to a ComboBox
Posted: 07 Aug 08 9:52 AM
|
Steve,
thanks for this, It's way more complicated than I had expected, so I very much appreciate the assistance.
I have got it working now, so on with the next challenge!
Regards,
Paul. |
|
|
|