Results 1 to 15 of 15

Thread: SQL Question

  1. #1
    Join Date
    May 2004
    Posts
    159

    Unanswered: SQL Question

    I have an SQL statement that I want to run and assign it to a string value.
    here is the sql statement
    strSQL = " SELECT tblOHCIFacIDNumber.FAC_ID_NUMBER FROM tblOHCIFacIDNumber INNER JOIN dbo_LOCATION_MSTR ON tblOHCIFacIDNumber.LOCATION_CD = dbo_LOCATION_MSTR.LOCATION_CD;"

    I am not able to get it to give me a value although the query works fine.
    What do I need in order to show the value of the statement and use that value ? str = (strsql) just repeats the wording not the value.
    Thanks

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    bind it to a textbox. Set the RowSource to your SQL statement ...
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    May 2004
    Posts
    159
    OK but don't you mean a listbox if you use rowsource? Then I just assign my string to the value of the listbox?

  4. #4
    Join Date
    May 2004
    Posts
    159
    I put in a listbox and set it to the sql query
    It shows the value I want which is great but the code in the module still does not see the value strFacility = Forms!frmmain.txtFacID.Value
    hmmm.. I also see that someone else has posted about not being able to read the listbox value from code - they get null value as do I. Why is that a problem?
    Is there a way to run an SQL statement of a query and then pull the result into a string??? It would really help me to get the syntax straight. As it is when I try to insert the value for the SQL it just inserts the statement not the value.
    Last edited by WilliamS; 02-22-05 at 09:49.

  5. #5
    Join Date
    May 2004
    Posts
    159
    Can anybody help ?

  6. #6
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Here's some sample ADO code that you can get an idea of how to do it from:
    Code:
    Function RetrieveTheCustomer() As Integer
        On Error GoTo Err_RTCust
        
    '    SQLString = "SELECT * FROM " & CompanyTbl & " WHERE (RecNum=" & CompanyRecNumber & ");"
        SQLString = "SELECT * FROM Customers WHERE (CustomerID=" & CurrentCompanyID & ");"
        MyRecSet.Open SQLString, MyConnect
        If MyRecSet.BOF = False Then
            MyRecSet.MoveFirst
            ' Company ID
    '        CurrentCompanyID = MyRecSet.Fields(0).Value
            ' Company Name
            CompanyNameTxt.Value = MyRecSet.Fields(1).Value & ""
            ' Address
            AddressTxt.Value = MyRecSet.Fields(2).Value & ""
            ' 2nd Address
            Address2Txt.Value = MyRecSet.Fields(3).Value & ""
            ' City
            CityTxt.Value = MyRecSet.Fields(4).Value & ""
            ' State/Province
            StateTxt.Value = MyRecSet.Fields(5).Value & ""
            ' Zip/Postal Code
            ZipTxt.Value = MyRecSet.Fields(6).Value & ""
            ' Phone #
            PhoneTxt.Value = MyRecSet.Fields(7).Value & ""
            ' Fax #
    '        FaxTxt.Value = MyRecSet.Fields(8).Value & ""
            ' Website
            WebsiteTxt.Value = MyRecSet.Fields(8).Value & ""
            ' External Customer
            ExternalCheckBox.Value = MyRecSet.Fields(9).Value
            ' Rate Schedule
            ScheduleCombobox.Value = 1
            If MyRecSet.Fields(10).Value & "" <> "" Then ScheduleCombobox.Value = MyRecSet.Fields(10).Value
        End If
        MyRecSet.Close
        
        If WhereCalledFrom = 0 Then ' From Customer Review
            RetrieveTheContacts
    '        DoCmd.GoToRecord acDataForm, "Customer Review", acGoTo, CompanyRecNumber
        End If
        
        RetrieveTheCustomer = 1
    Exit_RTCust:
        Exit Function
        
    Err_RTCust:
        
        MsgBox Err.Number & ": " & Err.Description
        RetrieveTheCustomer = 0
        Resume Exit_RTCust
    End Function
    Back to Access ... ADO is not the way to go for speed ...

  7. #7
    Join Date
    Nov 2003
    Location
    LONDON
    Posts
    238
    The best way to handle the results of a query is to open the recordset using ADO. You assign the recordset to a recordset variable and then you can move through it as you want to. Look in the help file to see the full syntax for using ADO

  8. #8
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by justin_tighe
    The best way to handle the results of a query is to open the recordset using ADO. You assign the recordset to a recordset variable and then you can move through it as you want to. Look in the help file to see the full syntax for using ADO
    Or check my example ...

    Oops! ... Here's ADO Connection constuction code:
    Code:
    Function EstablishDBConnection() As Integer
        On Error GoTo Err_EDBC
        
        Set MyConnect = New ADODB.Connection
        Set MyRecSet = New ADODB.Recordset
        
        MyConnect.CursorLocation = adUseClient
    '    MyConnect.Open "Provider=MicroSoft.Jet.OLEDB.4.0;Data Source=G:\Billing Data.mdb;User ID=admin;Password=;"
        MyConnect.Open "DSN=Billing System;" 'PWD=" & Mid(EE_TmpText, CleanUpPos, 10) & ";"
        
        MyRecSet.CursorType = adOpenDynamic
        MyRecSet.LockType = adLockOptimistic
        MyRecSet.CursorLocation = adUseClient
        
    EstablishDBConnection = 1
    Exit_EDBC:
        Exit Function
        
    Err_EDBC:
        
        MsgBox Err.Number & ": " & Err.Description
        EstablishDBConnection = 0
        Resume Exit_EDBC
    End Function
    Last edited by M Owen; 02-22-05 at 10:04.
    Back to Access ... ADO is not the way to go for speed ...

  9. #9
    Join Date
    May 2004
    Posts
    159
    But that is overkill for what I want as I only need one value based on what another value is in another table.
    I can put the value into a listbox and it shows on the form fine -the sql runs great as the recordsource for the listbox.
    Now I just want to get the value in that listbox into a string. If I try to access it in code I get a null value. What is a quick and easy way to do that?

  10. #10
    Join Date
    Nov 2003
    Location
    LONDON
    Posts
    238
    To get the value from the selected item in a combo box or the list box you use the following:

    strMyVariable = MyComboBoxOrlListBox.Value

    You could also use the column property to return a different value from the combo/list box. The column order starts with column 0 (which is the same as the value property) through to the number of columns less 1
    Justin

  11. #11
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by WilliamS
    But that is overkill for what I want as I only need one value based on what another value is in another table.
    I can put the value into a listbox and it shows on the form fine -the sql runs great as the recordsource for the listbox.
    Now I just want to get the value in that listbox into a string. If I try to access it in code I get a null value. What is a quick and easy way to do that?
    Dim SomeString as String

    SomeString=SomeListBox.Value

    -or-

    SomeString=SomeListBox.Column(XXX,YYY)
    Back to Access ... ADO is not the way to go for speed ...

  12. #12
    Join Date
    May 2004
    Posts
    159
    It would seem to be that simple but it isn't at least not for me.
    The listbox uses a sql query to select the value and that sql query is not available from the code side apparently. When I try to assign the value of the listbox to a string it gives a null value error. I know I am accessing the listbox because if I change the property in the LB to a default text value the code picks that up just fine.
    Neither of the below give other than a null error
    strFacility = Forms!frmMain!lstFacID.Column(1, 2)
    strFacility = Forms!frmMain!lstFacID.value

  13. #13
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by WilliamS
    It would seem to be that simple but it isn't at least not for me.
    The listbox uses a sql query to select the value and that sql query is not available from the code side apparently. When I try to assign the value of the listbox to a string it gives a null value error. I know I am accessing the listbox because if I change the property in the LB to a default text value the code picks that up just fine.
    Neither of the below give other than a null error
    strFacility = Forms!frmMain!lstFacID.Column(1, 2)
    strFacility = Forms!frmMain!lstFacID.value
    WHERE are you doing this assignment at???
    Back to Access ... ADO is not the way to go for speed ...

  14. #14
    Join Date
    Nov 2003
    Location
    LONDON
    Posts
    238
    You could set the variable in the List box after update event. When you first open the form the list box does not have a value until the user selects one or you select one using VBA

  15. #15
    Join Date
    May 2004
    Posts
    159
    I get it now!!
    Slaps head at blindingly obvious!!
    A listbox or textbox will have a null value untill there is a selection!!
    using an sql statement will populate the listbox with the desired field but it still will have a null value untill it is selected! So that is why the code choked. Perhaps adding a setfocus command would have populated the value field but in this case it is simpler to use the columm specifier which will hold the pointers to the data needed. BUT you have to know which ones have the data you need and remeber that in ACCESS it starts at 0 not 1!!

    This is what I needed in my case where the data is in the first row and column
    strFacility = Forms!frmMain!lstFacID.Column(0, 0)
    Got it now and thanks for helping me see the light!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •