469,338 Members | 8,430 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,338 developers. It's quick & easy.

How to create SQL Statement from Combobox Selections?

132 100+
Hi all!
I have 2-Combobox, one shows tablelist and another fieldlist of selected tables. the Second is activated only when the first is selected.
Currently it works fine.
I want to add a bit work around on the Combobox to let users Create their own SQL Statement so that it can be used to create a query.
How can this be achieved?
Nov 27 '10 #1

✓ answered by Mr Key

Well welwell!
Finally its answered!
I have set a permanent txtbox property to WHERE clause of SQL and hence a user will select only a table and one record which will triger the SQL with four Records and hence a query!
Should you need any further details, I will!
Thanks all for your contributions

12 7739
NeoPa
32,182 Expert Mod 16PB
That's a bit ambitious for someone that needs to ask.

What have you got so far?
Nov 27 '10 #2
Mr Key
132 100+
Thanks neo!
Nov 28 '10 #3
Mr Key
132 100+
Well neopa!
Am in position of explaining much better!
I have a Combobox (cbxTables)which selects tables in database with a ROWSOURCE as follows
Expand|Select|Wrap|Line Numbers
  1. SELECT [MsysObjects].[Name] AS ObjectName, IIf([type]=1 Or [type]=6,"Table","Query") AS ObjectType FROM MsysObjects WHERE (([MsysObjects].[Connect]<>Null) And ([MSysObjects].[Flags]=11534336)); 
This Combobox list the linkedtables of exeltypes as expected!
From this Combo I have Created another Combo (cbxFld0) to display a fieldlist of a table Selected from the firstCombo.
See the attachement for more clarifications!
Questions!
1. How to set cbxFld0 to display AllRecords and not a Fieldlist?
2. How to add a wilcard on the VBA to have SQL with only one Record Selected on the Combobox (cbxFld0)?
Attached Files
File Type: zip Mydbs1.zip (206.9 KB, 258 views)
Nov 28 '10 #4
Mr Key
132 100+
Yeap!
With time I came up with solution on how to select a Column as follows:
Let the tableNamesList at cbxCombo1 and Recordslist on cbxCombo2, then the following code will create the list on cbxCombo2:
Expand|Select|Wrap|Line Numbers
  1. Private Sub cbxCombo1_AfterUpdate()
  2. Dim strSQL As String
  3.     strSQL = "Select " & Me!cbxCombo1
  4.     strSQL = strSQL & " from MsysObjects"
  5.     Me!cbxCombo2.RowSourceType = "Table/Query"
  6.     Me!cbxCombo2.RowSource = cbxCombo1
  7.     Me!cbxCombo2.Requery
  8. End Sub
Question:
How to set this Two Combos Selection into SQL-Button of which ON-CLICK Event will triger the CreateSQL and hence a QUERY?
Nov 28 '10 #5
NeoPa
32,182 Expert Mod 16PB
You may prefer to use [MSysObjects].[Connect]>''. It gives a clearer indication of what you're checking for. Technically Null isn't a value and can only properly be checked using the Is Null construct or the IsNull() function. Your code should work fine of course, as it is automatically converted to the empty string for you. Anyway, that was a small point.

For the purposes of the explanation and being clear about your question, you should assume that I cannot see attachments. In truth I have little interest in opening attachments from untrusted sources and the thread makes no sense if the question is hidden within one. Remember, when we're done, this may be used by others with the same problems you have, unless they can't understand the question because it's hidden in an attachment. If I need you to attach anything I'll let you know. It's very rare.

Mr Key:
1. How to set cbxFld0 to display AllRecords and not a Fieldlist?
What is AllRecords? Is it a name I should know? Is it an extra value entry "AllRecords", that should be included as well as all the individual field names? I presume Fieldlist is meant to be a field list. This needs to be clearer if I'm to help.
Mr Key:
How to add a wilcard on the VBA to have SQL with only one Record Selected on the Combobox (cbxFld0)?
What does this mean? I have no idea what you're asking here.

This is a problem. I asked for clarification of what you'd tried to ask in the first post. Instead, you have added two more questions it seems, and neither makes a great deal of sense (I'm unable to answer as the question isn't there properly). I hope you can understand why this is a problem. If you ignore what I say - how can I help you. I'm sure you weren't trying to ignore me, but you haven't paid too much attention to what I said either. Let's start at the start and see if we can make the first question clear, then we can see what we can do from there.
Nov 28 '10 #6
NeoPa
32,182 Expert Mod 16PB
Mr Key:
How to set this Two Combos Selection into SQL-Button of which ON-CLICK Event will triger the CreateSQL and hence a QUERY?
It seems you have made a start, so I will respond to this. It also seems to be a clarification (of sorts) of your first post, so I think I have a better understanding of what you require.

It's not too simple, even to do, let alone explain. One thing I should explain before we start though, is that any SQL that is just a SELECT statement (display of data rather than action query) is not possible to run just as a SQL string. Query data only displays in a QueryDef object.

Are you trying to create SQL to display data, or to update data somehow?

If it is the former then you will need to play with QueryDefs. If the latter, then we can execute the SQL once created.

Why don't you give an example of the sort of SQL string you expect to see so that we know what we're dealing with to start with at least.

Some examples of playing with SQL strings in VBA code can be found at Cascaded Form Filtering and other articles linked to within there.
Nov 28 '10 #7
Mr Key
132 100+
Well!
It might be difficult to explain what exactly am I looking for but I will try.
First of all I have taken your sugestionsinto considerations and working on it!
Thanks!
I have several tables to create a Parameter Query! Most of them will be linked-Tables from excel(.xl*), the only choice I have is to have SQL create a query depending on user selections from Comboboxes! and not just updating data!
The situation is as follows!
1. The first combobox called cbxTables has a list of tables of excel types and the Second Combobox named cbxFld0 consist of Recordslist of which its RECORDSOURCE set to select from cbxTables as follows:
Expand|Select|Wrap|Line Numbers
  1. Private Sub cbxTables_AfterUpdate()
  2. Dim strSQL As String
  3.     strSQL = "Select " & Me!cbxTables
  4.     strSQL = strSQL & " from MsysObjects"
  5.     Me!cbxFld0.RowSourceType = "Table/Query"
  6.     Me!cbxFld0.RowSource = cbxTables
  7.     Me!cbxFld0.Requery
  8. End Sub
Or, Anything similar to that will be fine
This works fine! whatever the table selected from cbxTables will have the corresponding Records dispalyed on the cbxFld0.
These two combobox selections should be used to create SQL and hence a query.
For example two selections from both combos should be used to create the SQL e.g (SELECT * FROM cbxTables WHERE IDs=Forms!MyForm!cbxFld0)
Where IDs is example of FieldsName. Or the Like!
From that SQL I can try to create a Query.
As I have said I dont know exactly what am I looking for!
May we have another approach on this?!
Any hint will be appreciated.
Please help!!!
Nov 29 '10 #8
Mr Key
132 100+
Well!
I have knew Idea on this!!
Almost done but I miss something that needs your help!
I set three comboboxes,
1. cbxTables which display-linked Tables
2. cbxFld0 which display a fieldlist of cbxTables
3. cbxCombo2 which display the Recordlist of cbxFld0

Where:
1.cbxTables ROWSOURCE set to
Expand|Select|Wrap|Line Numbers
  1. SELECT [MsysObjects].[Name] AS ObjectName, IIf([type]=1 Or [type]=6,"Table","Query") AS ObjectType FROM MsysObjects WHERE (([MsysObjects].[Connect]<>Null) And ([MSysObjects].[Flags]=11534336)); 
2. Afterupdate Event of cbxTables set to designate the ROWSOURCE of cbxFLD0 as follows:
Expand|Select|Wrap|Line Numbers
  1. Private Sub cbxTables_AfterUpdate()
  2.  'Try and enable the next control only if the the Clear
  3.  'button has been clicked (ctl.Tag = vbNullString)
  4.  'Otherwise just requery the field's info
  5.  '
  6.    If Me.cbxTables.Tag = vbNullString Then Call fEnableNextInTab
  7.    Me.cbxFld0.Requery
  8.  
  9. End Sub
3. On-Exit-Event set to Rowsource of cbxCombo2 as follows:
Expand|Select|Wrap|Line Numbers
  1. Private Sub cbxTables_Exit(Cancel As Integer)
  2. Dim strSQL As String
  3.     strSQL = "Select " & Me!cbxTables
  4.     strSQL = strSQL & " from MsysObjects"
  5.     Me!cbxCombo2.RowSourceType = "Table/Query"
  6.     Me!cbxCombo2.RowSource = cbxTables
  7.     Me!cbxCombo2.Requery
  8. End Sub
They all works as expected!
to create SQL I have designed a text-SQL which can be used to create the the SQL, a ListBox called listSQL which can be used to display the resulted selections as a table and two Butons, one for building SQL and the other for Creating a query. In short the code is as follows:
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private Type OPENFILENAME
  5.   lStructSize As Long
  6.   hwnd As Long
  7.   hInstance As Long
  8.   lpstrFilter As String
  9.   lpstrCustomFilter As String
  10.   nMaxCustFilter As Long
  11.   nFilterIndex As Long
  12.   lpstrFile As String
  13.   nMaxFile As Long
  14.   lpstrFileTitle As String
  15.   nMaxFileTitle As Long
  16.   lpstrInitialDir As String
  17.   lpstrTitle As String
  18.   Flags As Long
  19.   nFileOffset As Integer
  20.   nFileExtension As Integer
  21.   lpstrDefExt As String
  22.   lCustData As Long
  23.   lpfnHook As Long
  24.   lpTemplateName As String
  25. End Type
  26.  
  27. Private Declare Function GetOpenFileName _
  28.     Lib "comdlg32.dll" Alias "GetOpenFileNameA" _
  29.     (pOpenfilename As OPENFILENAME) As Long
  30.  
  31. Private Declare Function GetSaveFileName Lib "comdlg32.dll" Alias "GetSaveFileNameA" (pOpenfilename As OPENFILENAME) As Long
  32.  
  33. Private Const OFN_FILEMUSTEXIST = &H1000
  34. Private Const OFN_HIDEREADONLY = &H4
  35. Private Const OFN_OVERWRITEPROMPT = &H2
  36. Private Const OFN_PATHMUSTEXIST = &H800
  37. Private Const OFN_SAVE = 0
  38. Private Const OFN_OPEN = 1
  39.  
  40.  
  41.  
  42. Private Type CTLInf
  43.   Name As String
  44.   Enabled As Boolean
  45. End Type
  46. Private Declare Function apiSortStringArray Lib "msaccess.exe" _
  47.    Alias "#81" _
  48.    (astrStringArray() As String) _
  49.    As Long
  50.  
  51. Private arrCtls() As CTLInf
  52. Private mvarOriginalFields  As Variant
  53. Private Const mconQ = """"
  54.  
  55. Private Sub cbxCombo2_AfterUpdate()
  56. Me.cmdBuildSQL.Requery
  57. Call sBuildSQL
  58. End Sub
  59.  
  60. Private Sub cbxTables_AfterUpdate()
  61.  'Try and enable the next control only if the the Clear
  62.  'button has been clicked (ctl.Tag = vbNullString)
  63.  'Otherwise just requery the field's info
  64.  '
  65.    If Me.cbxTables.Tag = vbNullString Then Call fEnableNextInTab
  66.    Me.cbxFld0.Requery
  67.  
  68. End Sub
  69. Function fEnableNextInTab()
  70. 'Enable and Setfocus to the next control
  71. 'in the form's TabIndex.
  72. Dim ctlNew As Control, intTab As Integer
  73. Dim ctlOld As Control, intNewTab As Integer
  74.  
  75.    On Error Resume Next
  76.    'Since we're calling this function from AfterUpdate,
  77.    'what's the current control's position in TabIndex
  78.    Set ctlOld = Screen.ActiveControl
  79.    'we want the next one
  80.    intNewTab = ctlOld.TabIndex + 1
  81.  
  82.    For Each ctlNew In Me.Controls
  83.       intTab = ctlNew.TabIndex
  84.       If Not Err And (intTab = intNewTab) Then
  85.          'if no error occurred and the tab index is same as
  86.          'what we're looking for, then enable it
  87.          With ctlNew
  88.             'Store the control's name for later use
  89.             'but exclude the listbox since the tag there
  90.             'contains the number of fields in the object select
  91.             If Not ctlOld.ControlType = acListBox Then _
  92.                ctlOld.Tag = .Name
  93.             Select Case .ControlType
  94.                Case acListBox:
  95.                Case acComboBox:
  96.                   'If the control found is a combo, fill it's rowsource
  97.                   Call sFillCombo
  98.                Case Else:
  99.  
  100.             End Select
  101.             .Enabled = True
  102.             .Locked = False
  103.             .BackColor = vbWhite
  104.             .SetFocus
  105.             Exit For
  106.          End With
  107.       End If
  108.    Next
  109.    Set ctlOld = Nothing
  110.    Set ctlNew = Nothing
  111.    'Build the SQL automatically only if the user specified so
  112.    Call sBuildSQL
  113. End Function
  114.  
  115. Private Sub sFillCombo()
  116. 'Fills the Rowsource for a combo
  117. '
  118. On Error GoTo ErrHandler
  119. Dim i As Long
  120. Dim j As Integer
  121. Dim strOut As String
  122. Dim ctlTarget As Control
  123.  
  124.    'Which one to fill?
  125.    Set ctlTarget = Me.cbxFld0
  126.    For i = LBound(mvarOriginalFields) To UBound(mvarOriginalFields)
  127.       strOut = strOut & mvarOriginalFields(i) & ";"
  128.    Next
  129.    With ctlTarget
  130.       .RowSourceType = "Value List"
  131.       .RowSource = strOut
  132.    End With
  133. ExitHere:
  134.    Set ctlTarget = Nothing
  135.    Exit Sub
  136. ErrHandler:
  137.    Resume ExitHere
  138. End Sub
  139. Sub sBuildSQL()
  140. '  Take what's currently selected on the form
  141. '  and create a dynamic SQL statement for the
  142. '  lstResults listbox.
  143. '
  144. On Error GoTo ErrHandler
  145. Dim strSQL As String
  146. Dim strWhere As String
  147. Dim strJoinType As String
  148. Dim i As Integer
  149. Dim j As Integer
  150. Dim db As DAO.Database
  151. Dim rs As DAO.Recordset
  152. Dim tdf As TableDef
  153. Dim qdf As DAO.QueryDef
  154. Dim rsQdf As DAO.Recordset
  155. Dim fld As DAO.Field
  156. Const conMAXCONTROLS = 1
  157.  
  158.    Set db = DBEngine(0)(0)
  159.    strSQL = "Select * "
  160.    'Right now we have five combo/textbox sets
  161.    'so set up the master loop to go through these controls
  162.    For i = 0 To conMAXCONTROLS - 1
  163.       strJoinType = vbNullString
  164.       'there might be some unused sets, so don't bother
  165.       'going through the disabled controls
  166.       If Me("cbxFld" & i).Enabled Then
  167.          'The Or/And set starts with the second combo/textbox set
  168.          'so if there's only one criteria specified, don't need to
  169.          'concatenate additional stuff.
  170.          If i > 0 Then
  171.             If Me("opgClauseType" & i) = 1 Then
  172.                strJoinType = " OR "
  173.             Else
  174.                strJoinType = " AND "
  175.             End If
  176.          End If
  177.          'Get the a reference to the field in the table/Query as
  178.          'we'll need it for BuildCriteria later on
  179.          If Me.cbxTables.Column(1) = "Table" Then
  180.             Set tdf = db.TableDefs(Me.cbxTables.Column(0))
  181.             Set fld = tdf.Fields(Me("cbxFld" & i))
  182.          Else
  183.             Set rsQdf = db.OpenRecordset( _
  184.                "Select * from [" & Me.cbxTables.Column(0) & "] Where 1=2", dbOpenSnapshot)
  185.             Set fld = rsQdf.Fields(Me("cbxFld" & i))
  186.          End If
  187.  
  188.          'Only build a criteria if something's typed in the textbox
  189.          'otherwise assume all records
  190.          If Not IsNull(Me("txtVal" & i)) Then
  191.             strWhere = strWhere & strJoinType & Application.BuildCriteria( _
  192.                                                 "[" & Me("cbxFld" & i) & "]", _
  193.                                              fld.Type, Me("txtVal" & i) & "")
  194.          Else
  195.             strWhere = strWhere & strJoinType & "[" & Me("cbxFld" & i) & "]  like '*'"
  196.          End If
  197.  
  198.       End If
  199.    Next
  200.    'The final all important SQL statement
  201.    strSQL = strSQL & " from [" & Me.cbxTables & "] Where " & strWhere
  202.  
  203.    'If the user has modified the SQL directly, take what they've typed in
  204.       '"save" it in the textbox
  205.       Me.txtSQL = strSQL
  206.  
  207.    With Me.lstResult
  208.       Set rs = db.OpenRecordset(Me.txtSQL)
  209.       'assign the SQL to the lstResult only if
  210.       ' (a) it's valid (Set rs will generate an error otherwise)
  211.       ' (b) if the recordset actually returned any records.
  212.       If rs.RecordCount > 0 Then
  213.          Me.cmdCreateQDF.Enabled = True
  214.          .RowSourceType = "Table/Query"
  215.          .RowSource = Me.txtSQL
  216.          .Enabled = True
  217.          'display * fields
  218.          .ColumnCount = CInt(Me.cbxTables.Tag)
  219.          .ColumnHeads = True
  220.          Else
  221.         'Thanks for trying, better luck next time!!
  222.          Me.cmdCreateQDF.Enabled = False
  223.          .ColumnCount = 1
  224.          .RowSourceType = "Value List"
  225.          .RowSource = "No records found."
  226.       End If
  227.    End With
  228. ExitHere:
  229.    Set rsQdf = Nothing
  230.    Set rs = Nothing
  231.    Set tdf = Nothing
  232.    Set db = Nothing
  233.    Exit Sub
  234. ErrHandler:
  235.    Select Case Err.Number
  236.       'we're trying to open a parameter query
  237.       Case 3061:
  238.          MsgBox "The " & mconQ & Me.cbxTables & mconQ & " query you've selected " _
  239.             & " is a Parameter Query." & vbCrLf & Err.Description, vbExclamation + vbOKOnly, _
  240.             "Missing parameters"
  241.       Case Else:
  242.          'Either invalid SQL or some other error
  243.    End Select
  244.    Me.cmdCreateQDF.Enabled = False
  245.    With Me.lstResult
  246.       .RowSourceType = "Value List"
  247.       .RowSource = "Invalid SQL statement."
  248.       .ColumnHeads = False
  249.       .ColumnCount = 1
  250.       .Enabled = False
  251.    End With
  252.    Resume ExitHere
  253. End Sub
  254.  
  255. Private Sub cbxTables_Exit(Cancel As Integer)
  256. Dim strSQL As String
  257.     strSQL = "Select " & Me!cbxTables
  258.     strSQL = strSQL & " from MsysObjects"
  259.     Me!cbxCombo2.RowSourceType = "Table/Query"
  260.     Me!cbxCombo2.RowSource = cbxTables
  261.     Me!cbxCombo2.Requery
  262. End Sub
  263.  
  264. Private Sub cmdBuildSQL_Click()
  265.  'build the SQL with what we have
  266.    Call sBuildSQL
  267. End Sub
  268.  
  269. Private Sub cmdCreateQDF_Click()
  270. On Error GoTo ErrHandler
  271. Dim db As DAO.Database
  272. Dim qdf As DAO.QueryDef
  273. Dim strName As String
  274.    'first get a unique name for the querydef object
  275.    strName = Application.Run("acwzmain.wlib_stUniquedocname", "Query1", acQuery)
  276.    strName = InputBox("Please specify a query name", "Save As", strName)
  277.    If Not strName = vbNullString Then
  278.       'only create the querydef if user really wants to.
  279.       Set db = CurrentDb
  280.       Set qdf = db.CreateQueryDef(strName, Me.txtSQL)
  281.       qdf.Close
  282.    Else
  283.       'ok, so they don't want to
  284.       MsgBox "The save operation was cancelled." & vbCrLf & _
  285.          "Please try again.", vbExclamation + vbOKOnly, "Cancelled"
  286.    End If
  287. ExitHere:
  288.    On Error Resume Next
  289.    qdf.Close
  290.    Set qdf = Nothing
  291.    db.QueryDefs.Refresh
  292.    Set db = Nothing
  293.    Exit Sub
  294. ErrHandler:
  295.    Resume ExitHere
  296. End Sub
  297.  
  298. Private Sub txtSQL_AfterUpdate()
  299.    'build the SQL with what we have
  300.    Call sBuildSQL
  301. End Sub
  302. Function fListFill(ctl As Control, varID As Variant, lngRow As Long, _
  303.                     lngCol As Long, intCode As Integer) As Variant
  304. 'The callback function for the first combo
  305. '  sFillCombo takes care of the rest of 'em.
  306. On Error GoTo ErrHandler
  307. Static sastrObjSource() As String
  308. Static sastrFields() As String
  309. Static slngCount As Long
  310. Static sdb As DAO.Database
  311. Dim i As Long
  312. Dim j As Long
  313. Dim tdf As TableDef
  314. Dim rsQdf As DAO.Recordset
  315. Dim fld As DAO.Field
  316. Dim varRet As Variant
  317. Dim strObjectType As String
  318. Dim varItem As Variant
  319.  
  320.    Select Case intCode
  321.       Case acLBInitialize
  322.          If sdb Is Nothing Then Set sdb = CurrentDb
  323.          With Me
  324.             ReDim sastrObjSource(0)
  325.             'Are we looking for a table or a query
  326.             sastrObjSource(0) = .cbxTables.Column(0)
  327.             strObjectType = .cbxTables.Column(1)
  328.             j = -1
  329.                  If strObjectType = "Table" Then
  330.                Set tdf = sdb.TableDefs(sastrObjSource(0))
  331.                Me.cbxTables.Tag = tdf.Fields.Count
  332.                'Get a list of all the fields
  333.                For Each fld In tdf.Fields
  334.                   j = j + 1
  335.                   ReDim Preserve sastrFields(j)
  336.                   sastrFields(j) = fld.Name
  337.                Next
  338.                j = UBound(sastrFields)
  339.             Else
  340.                'Since the fieldnames can be changed, safest way is to
  341.                'open a recordset and go through it's Fields collection
  342.                Set rsQdf = sdb.OpenRecordset( _
  343.                      "Select * from [" & sastrObjSource(0) & "] Where 1=2", _
  344.                      dbOpenSnapshot)
  345.                Me.cbxTables.Tag = rsQdf.Fields.Count
  346.                For Each fld In rsQdf.Fields
  347.                   j = j + 1
  348.                   ReDim Preserve sastrFields(j)
  349.                   sastrFields(j) = fld.Name
  350.                Next
  351.                j = UBound(sastrFields)
  352.             End If
  353.             'sort the string
  354.             Call apiSortStringArray(sastrFields)
  355.             slngCount = UBound(sastrFields) + 1
  356.             'create a module level variant array for other combos
  357.             mvarOriginalFields = sastrFields
  358.          End With
  359.         varRet = True
  360.  
  361.         Case acLBOpen
  362.             varRet = Timer
  363.  
  364.         Case acLBGetRowCount
  365.             varRet = slngCount
  366.  
  367.         Case acLBGetValue
  368.             varRet = sastrFields(lngRow)
  369.  
  370.         Case acLBEnd
  371.             Set rsQdf = Nothing
  372.             Set tdf = Nothing
  373.             Set sdb = Nothing
  374.             Erase sastrFields
  375.             Erase sastrObjSource
  376.     End Select
  377.     fListFill = varRet
  378. ExitHere:
  379.    Exit Function
  380. ErrHandler:
  381.    Resume ExitHere
  382. End Function
  383. Private Function fSetDocObjectProperty(strObjectName As String, _
  384.                             strObjectType As String, _
  385.                             strPropertyName As String, _
  386.                             varPropertyValue As Variant, _
  387.                             Optional varPropertyType As Variant = dbText) _
  388.                             As Boolean
  389. '?fSetDocObjectProperty("Module33","Modules","DateLastUpdated",Now)
  390. '
  391. On Error GoTo ErrHandler
  392.   Dim db As DAO.Database
  393.   Dim doc As Document
  394.   Dim ctr As Container
  395.   Dim prop As Property
  396.  
  397.   Set db = CurrentDb
  398.   Set ctr = db.Containers(strObjectType)
  399.   Set doc = ctr.Documents(strObjectName)
  400.  
  401.   doc.Properties(strPropertyName).Value = varPropertyValue
  402.   fSetDocObjectProperty = True
  403. ExitHere:
  404.   Set prop = Nothing
  405.   Set doc = Nothing
  406.   Set ctr = Nothing
  407.   Set db = Nothing
  408.   Exit Function
  409. ErrHandler:
  410.   Select Case Err.Number
  411.     Case 3270:
  412.       Set prop = doc.CreateProperty(strPropertyName, _
  413.                             varPropertyType, varPropertyValue)
  414.       doc.Properties.Append prop
  415.       Resume Next
  416.     Case Else:
  417.       fSetDocObjectProperty = False
  418.       Resume ExitHere
  419.   End Select
  420.   Resume ExitHere
  421. End Function
  422.  
They all works as expected!
Now I want to add the first three permanent records (Where ID<4)that has to be used to create SQL with user`s cbxCombo2 Selection.
Nov 29 '10 #9
Mr Key
132 100+
I can send the sample of this db if you wish
Nov 29 '10 #10
Mr Key
132 100+
Well welwell!
Finally its answered!
I have set a permanent txtbox property to WHERE clause of SQL and hence a user will select only a table and one record which will triger the SQL with four Records and hence a query!
Should you need any further details, I will!
Thanks all for your contributions
Dec 1 '10 #11
NeoPa
32,182 Expert Mod 16PB
Well Mr Key.

Firstly, let me congratulate you on your work and success, then explain that I wasn't ignoring you - I was just away for four days (this happens sometimes I'm afraid). It seems that in my absence you have come to understand the situation better for yourself, without any help other than the suggestion to explain the situation as clearly as you can. It doesn't all make sense to me (so I may have been unable to be of much more help anyway - at least not without some further rereading and trying to understand better - but that doesn't mean the work was wasted, as it seems to have helped you in your thinking process and overall understanding (for which I am very glad for you).

Congratulations on coming up with the solution. Good for you! But sorry for not being there to help you through the process more easily.
Dec 3 '10 #12
Mr Key
132 100+
Yeah!
It was a bit difficult to create a QueryDefs!!!
In your presence would have make it much easier than what I did.
I have learnt alot on the way through.
Thanks you all, for your positive challenges
Dec 4 '10 #13

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by jose.cortijo | last post: by
37 posts views Thread by Steven Bethard | last post: by
18 posts views Thread by Steven Bethard | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by Purva khokhar | last post: by
1 post views Thread by haryvincent176 | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.