I've tried all sorts, the most promising being, (obviously SQL) I have an input box that allows me to enter the search parameter but, try as I might I cannot convince anything to take me to a specific record.
eg. If I enter "001a" I would hope to find the first record that has a reference to "001a" (especially in one particular field). The SQL statement
SELECT MA_SG_REF FROM MASTER WHERE MA_SG_REF = Data
would seem to be correct where Data is a variable that reflects what was parsed to the input box
Data = InputBox("Record Number",0,"1")
I get an error telling me that it cannot find MASTER - which is one of the Tables in the form.
The full sub is and it always fails at the SQL statement As far as I can establish the Database is recognised by the other parameters in the same sub:
Code: Select all
Sub Find2
Dim Data as Variant
Dim oForm 'Reference to the form containing the primary record.
Dim oSubForm 'Reference to the subForm
Dim i
Dim n
Dim sFormName 'Name of the SubForm
dim oDoc as object
dim oSearch as object
dim oCursor as object
dim dispatcher as object
Dim DatabaseContext As Object
Dim DataSource As Object
Dim Connection As Object
Dim InteractionHandler as Object
Dim Statement As Object
Dim ResultSet As Object
DatabaseContext = createUnoService("com.sun.star.sdb.DatabaseContext")
DataSource = DatabaseContext.getByName("StampMaster"
InteractionHandler = createUnoService("com.sun.star.sdb.InteractionHandler")
Connection = DataSource.ConnectWithCompletion(InteractionHandler)
Data = InputBox("SG number",0,"1")
If Data = 0 goto Fin
Statement = Connection.createStatement()
ResultSet = Statement.executeQuery("SELECT ""SM_SG_REF"" FROM ""MASTER ""WHERE"" SM_SG_REF = ""Data")
if isnull(ResultSet) then
msgbox "Not Found", 64, "Title"
else
oCursor = thisComponent.CurrentController.ViewCursor
oCursor.gotoRange(ResultSet, false)
msgbox "Found", 64, "Title"
end if
Fin:
End Sub