Added new view from other database platform system using oledb connection w/Uselsx "*lsxlc" need to add this view to existing script
i currently have a script that connects to a different platform and retrieves list of topics and populates that list in a drop down field within my form. Currently today there is only 1 view being accessed from that other platofrm. Now there is another view that was created and the populated field on my form needs to use both views but 1 or the other depending upon a field value in my form. Here is the existing script:
Sub Postopen(Source As Notesuidocument)
Dim ses As New LCSession
ses.ClearStatus
Dim doc As NotesDocument
Set doc = Source.Document
Dim con As New LCConnection("oledb")
With con
.Provider = "SQLOLEDB"
.Server = "ENYRSRSQL"
.Database = "EPT"
.Metadata = "ActiveProjects"
.UserID = "3PTR3@d"
.Password = "3PTR3@d"
End With
On Error Resume Next
con.Connect
If Err = True Then
Messagebox "Unable to Connection failed with error " & Err & ": " & Error
Exit Sub
End If
Dim MyFldList As New LCFieldList
Dim MyFld As LCField
Dim SQL As String
Dim MyResult As String
SQL = "SELECT RTRIM(Project)+' - '+RTRIM(Title) FROM ActiveProjects ORDER BY Project DESC"
con.Execute SQL, MyFldList
Set MyFld = MyFldList.GetField(1)
While (con.Fetch(MyFldList) > 0)
MyResult = MyResult & MyFld.Text(0) & Chr(10)
Wend
Call Source.FieldSetText("EVDSq
'Messagebox MyResult
con.Disconnect
Set MyFldList = Nothing
Set MyFld = Nothing
Set con = Nothing
Set ses = Nothing
End Sub
the new view is called ActiveProjectsMEIPASS. The original view is called ActiveProjects as you can see in the script above. If my existing field EVDproject = MEIPASS value on the form then I want to use the new view on the other platform and retrieve projects only assosciated with MEIPASS other wise use the existing view ActiveProjects and bring in all non MEIPASS projects.
got bits and pieces but I can't the if structure.
Thank you !
Sub Postopen(Source As Notesuidocument)
Dim ses As New LCSession
ses.ClearStatus
Dim doc As NotesDocument
Set doc = Source.Document
Dim con As New LCConnection("oledb")
With con
.Provider = "SQLOLEDB"
.Server = "ENYRSRSQL"
.Database = "EPT"
.Metadata = "ActiveProjects"
.UserID = "3PTR3@d"
.Password = "3PTR3@d"
End With
On Error Resume Next
con.Connect
If Err = True Then
Messagebox "Unable to Connection failed with error " & Err & ": " & Error
Exit Sub
End If
Dim MyFldList As New LCFieldList
Dim MyFld As LCField
Dim SQL As String
Dim MyResult As String
SQL = "SELECT RTRIM(Project)+' - '+RTRIM(Title) FROM ActiveProjects ORDER BY Project DESC"
con.Execute SQL, MyFldList
Set MyFld = MyFldList.GetField(1)
While (con.Fetch(MyFldList) > 0)
MyResult = MyResult & MyFld.Text(0) & Chr(10)
Wend
Call Source.FieldSetText("EVDSq
'Messagebox MyResult
con.Disconnect
Set MyFldList = Nothing
Set MyFld = Nothing
Set con = Nothing
Set ses = Nothing
End Sub
the new view is called ActiveProjectsMEIPASS. The original view is called ActiveProjects as you can see in the script above. If my existing field EVDproject = MEIPASS value on the form then I want to use the new view on the other platform and retrieve projects only assosciated with MEIPASS other wise use the existing view ActiveProjects and bring in all non MEIPASS projects.
got bits and pieces but I can't the if structure.
Thank you !
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
:-) After all the projects you did and the questions we answered together I'm quite sure you could have done this by yourself.
Eh, or is this maybe a different user pratigan?
Eh, or is this maybe a different user pratigan?
ASKER
no it's the right one... lol That .metadata interface and the different platform were throwing me for a loop. I didn't figure it to be as easy as it was.
Thanks !
Thanks !
Study the With statement in the Help database, maybe that takes some of your confusion away.
ASKER
I'm thinking that I have to move the code from its current location. Currently it is in the Postopen event and therefore doesn't change the values in the list when I select MEIPASS.
If the field containing the condition can be changed dynamically, PostOpen isn't any good indeed. You have to put it in the QueryRecalc event, plus you have to select "Recalculate form when value changes" and for the field with the list you have to select "Refresh options when form is refreshed". Well, the options are named differently but you'll see soon enough what I mean. ;-)
ASKER
the post calc field is working from the start of the form only. When I select MEIPASS first thing and then go to title field, only MEIPASS projects are listed. Good. but If I go back and change the radio button from MEIPASS to Evolution and then select the title field, the MEIPASS projects still show listed. Also if Evolution first then tht list remains on radio button change.
the radio button is set to Refresh fields on keyword change
the title field(the one that gets generated from the different view in the script) is set to Refresh choices on document refresh.
aggravating ..... lol
the radio button is set to Refresh fields on keyword change
the title field(the one that gets generated from the different view in the script) is set to Refresh choices on document refresh.
aggravating ..... lol
ASKER
I'm thinking the best place for this script is in the Exiting event from the radio button field that drives the title lists further down in the form. this way the script would only be executed once. With the script in the POSTrecalc or queryrecalc the script gets executed everytime the cursor changes fields.
Your thoughts ?
Your thoughts ?
> the title field(the one that gets generated from the different view in the script)
What type of field is that? Is it a dialog list or other type of selection field? If it is not, why not?
What type of field is that? Is it a dialog list or other type of selection field? If it is not, why not?
ASKER
yes the title feld is a Dialog List field that gets generated using formula of choice and th selection in the formula field is EVDSqlTitle (coming from th script in the form postrecalc or queryrecalc)
Usually, Exiting is a place that doesn't excite me. It has a serious drawback: if the user, while in the field, types Ctrl-S and then Ctrl-F4, the Exiting event never fires. Both Recalc events do fire when the user tries to save the document.
Does the Title field have the option Refresh choices on document refresh set? (or similar text)
ASKER
yes it does.
I'm just thinking the exiting to have the script only execute once. as a recalc it executes everytime the cursor changes field. It's not that big of a form so it's not that big of overhead
I'm just thinking the exiting to have the script only execute once. as a recalc it executes everytime the cursor changes field. It's not that big of a form so it's not that big of overhead
> as a recalc it executes everytime the cursor changes field
I don't understand. You should not have the auto-recalc option of the form enabled, that's overkill. Just for the Title field, enable Refresh choices on document refresh.
I don't understand. You should not have the auto-recalc option of the form enabled, that's overkill. Just for the Title field, enable Refresh choices on document refresh.
ASKER
thats the problem. if Iremove automatically refresh fields from the form properties, then the title list is blank. I have the project field set to refresh fields on keyword change radio button containing 2 values, evolution or MEIPASS) the title button is set to refresh choices on document refresh.
The strange thing is, when I go a few fields past the title field and then go back ad change the radio button to MEIPASS or evolution, then go back to the title field, the values have changed to reflect the correct values for the project radio button seection. Meaning the project field is the first field inthe form. It defaults at Evolution.... two fields down the tile field reflects Evolution projects in the list. go 2 more fields down an then back up to chane the project field to MEIPASS. go down to the title field now ad the projects listed are MEIPASS titles. only if I go 1 or more fields past the title do they change to a correct list.
The strange thing is, when I go a few fields past the title field and then go back ad change the radio button to MEIPASS or evolution, then go back to the title field, the values have changed to reflect the correct values for the project radio button seection. Meaning the project field is the first field inthe form. It defaults at Evolution.... two fields down the tile field reflects Evolution projects in the list. go 2 more fields down an then back up to chane the project field to MEIPASS. go down to the title field now ad the projects listed are MEIPASS titles. only if I go 1 or more fields past the title do they change to a correct list.
There is also an Onchange event, but I'm not in favour of that one either, but you might try it. I assume it gets triggered immediately after changing the value of the field.
ASKER
it is workable as is. I will try the Onchange. I've used it before in other forms and it worked nicely.
ASKER
Than You for your help Sjef. very much appreciated.
If you can avoid the use of the automatic update, you should, and almost at all costs. It'll execute the code all the time. I'd say that in 99% of all cases you can do without it.
The fields are evaluated top to bottom and left to right. So if the list of choices for field2 depends on field1, field1 should be placed before field2 on the form. If choices are to be calculated using LotusScript, you may have to put that code in the QueryRecalc event on the form, and the resulting list of choices in a field that is NOT on the form. Why not? The form always fiddles with this type of field, plus I'd suggest to make that field Computed for Display. And since you cannot give a CfD field a value from LotusScript, you don't add it to the form. Instead, you use ReplaceItemValue to add it to the document, and then you set the SaveToDisk property of the field to False.
The fields are evaluated top to bottom and left to right. So if the list of choices for field2 depends on field1, field1 should be placed before field2 on the form. If choices are to be calculated using LotusScript, you may have to put that code in the QueryRecalc event on the form, and the resulting list of choices in a field that is NOT on the form. Why not? The form always fiddles with this type of field, plus I'd suggest to make that field Computed for Display. And since you cannot give a CfD field a value from LotusScript, you don't add it to the form. Instead, you use ReplaceItemValue to add it to the document, and then you set the SaveToDisk property of the field to False.
ASKER
I will take heed of this. Interesting layut for the computerd for display field. I wll try that as well.
Thank You Agan !!
I totally appreciate your help.
Thank You Agan !!
I totally appreciate your help.
ASKER
Thank you !