Below, I’ve gathered some Miscellaneous Tips and Tricks for customizing SLX (some of which are borrowed from Stephen Redmond’s DevLogix books):
- When using a DateTimeEdit control, bind the DateTime property to the database, but use the Text property (<> “”) to check for whether a value has been entered
- You can only Enable Field-Level Security when you’re initially creating a table; there’s no supported way to turn it on later
- You can’t directly show a Memo field in a DataGrid … but if you assign the grid’s SQL in a string, in code, you can use the SQL CONVERT function on the field, to cause it to display in the grid, e.g.,
SELECT CONVERT(varchar(4000), MyMemoField) As A1.MyField, ...
- To prevent a Data Form from closing when its OK button is pressed, in the AXFormValidate event, set AXFormValidate = False
- To load a custom form for entering search criteria for a lookup, create the form (with data-bound fields) and assign it to the lookup’s View Name property
- To iterate through the selected records in a multi-select grid:
For i = 0 to DataGrid1.Selection.Count - 1 ... DataGrid1.Selection.Item(i) ... Next
- To show/hide columns in a grid, either format works:
dgContact.Columns(2).Visible = False dgContact.Columns("Workphone").Visible = True
- To execute the OTB “stored procedures” in SLX, pass them to the CommandText property of an ADODB.Command object:
Set cn = Application.GetNewConnection Set cmd = CreateObject("ADODB.Command") cmd.ActiveConnection = cn cmd.CommandText = "slx_DBIDs('ACCOUNT', 10)" cmd.CommandType = adCmdText Set rs = cmd.Execute ...
- To use dates in SQL statements:
strSQL = "SELECT Field1, Field2 FROM MyTable WHERE StartDate > '" & Application.DateToISO(Now() - 30) & "'"
- Redmond: “It is good practice to add a hidden edit box containing the key field that joins the data table [e.g., the grid on a tab] to the main view…. Without this value on the form, the record is not created automatically when you save the Add/Edit form…. Some people have suggested that this field needs to be the first control created (you can control creation order, if you add the field later, by right-clicking on the form and select ‘Creation Order…’ from the menu).”
- To set (and show) the current ID for a Main View:
' Loads default Main View for Contact table: Application.MainViews.GetViewForRecord strContactID, "CONTACT" Application.MainViews.AddEx "System:Contact Details", _ 1, True, 1, ContactID, ""
Set MV = Application.MainViews.Add("System:Account Details", 1, True) MV.CurrentID = strAccountID MV.Show
- To get the ID:
strID = Application.MainViews.ActiveView.CurrentID