Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820

    Unanswered: what the F**k is going on

    i've got 2 odbc table joined in a query called RawData

    now i try to query RawData with
    SELECT RawData.*,
    FROM RawData
    WHERE (((RawData.SHIP_TO)=[name]));

    and get ODBC -- call failed

    yet

    SELECT RawData.*
    FROM RawData
    WHERE (((RawData.SHIP_TO)="DEW005"));

    works perfectly what is it playing at
    win 2000, access 2000

    also

    SELECT RawData.*,
    FROM RawData
    WHERE (((RawData.SHIP_TO)=[forms]![PerCustomer]![name]));
    does the same as the first
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  2. #2
    Join Date
    Nov 2002
    Posts
    272
    Could it be the comma (,) at the end of the line that starts with 'SELECT'?
    The first and third queries have that, the second one (correctly) doesn't.

  3. #3
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    there is no comma that was a typo

    it should of read

    SELECT RawData.*
    FROM RawData
    WHERE (((RawData.SHIP_TO)=[name]));

    SELECT RawData.*
    FROM RawData
    WHERE (((RawData.SHIP_TO)=[forms]![PerCustomer]![name]));
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  4. #4
    Join Date
    Oct 2002
    Location
    Rochester, NY
    Posts
    84
    Originally posted by m.timoney
    there is no comma that was a typo

    it should of read

    SELECT RawData.*
    FROM RawData
    WHERE (((RawData.SHIP_TO)=[name]));

    SELECT RawData.*
    FROM RawData
    WHERE (((RawData.SHIP_TO)=[forms]![PerCustomer]![name]));
    You're going to have problems using a field name or form control to create your query. I'd suggest you save the parameter for your query as a variable and use the variable to create the SQL string. You'll need to do this in VBA (not QBE). Remember that you're having ODBC perform a translation of Access SQL to Oracle SQL as a pass-through. More than likely, ODBC can't figure out how to perform the tarnslation of [name] to something Oracle likes.

    Try something that looks like the following:

    Dim strName as String
    Dim strSQL as String

    strName = [forms]![PerCustomer]![name]

    strSQL = "SELECT RawData.* "
    "FROM RawData "
    "WHERE RawData.SHIP_TO = '" & strSQL & "'"


    You should then be able to use strSQL as the source for a querydef or recordset source.

    Give it a try and let me know how you make out.
    Regards,
    Terry

  5. #5
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    i've not tried generating a select query in VB before, the above were done in the drag and drop query builder, so how do i go about making the query selecteble by the form or linking the form to VB so it can get the data that way?

    ie how to make VB the forms data source or make the VB generated query visble to access
    Last edited by m.timoney; 11-27-02 at 10:17.
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  6. #6
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    latest attempt

    Code:
    Private Sub Customer_AfterUpdate()
        Dim dbs As DAO.Database, qry As QueryDef
        Set dbs = CurrentDb
        dbs.QueryDefs.Delete "PerCust"
        Set qry = dbs.CreateQueryDef("PerCust", "SELECT RawData.* FROM
     RawData WHERE RawData.SHIP_TO = '" & Customer.Value & "';")
        DoCmd.Requery
    End Sub
    this works but to get the new details to show you have to close down the form and reopen
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  7. #7
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    anyone know a better way of coding it? or another method to get the form to show the correct results?
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  8. #8
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580

    Suggestion

    Can you design a dialogue form to enable the user to select the [name] they are interested in?

    say frmNameSelection

    on this form have a control, perhaps a txtbox as a drop-down with all the [name] values say txtChosenName

    also add a button with a command to open the existing form

    base the existing form on a query which selects all the records where [name] = forms!frmNameSelection!txtChosenName

    If you don't know how to do this let me know and i'll knock up an example

    GD

  9. #9
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820

    Re: what the F**k is going on

    this all stems from the fact that, that don't work on ODBC tables

    the orginal post reads
    Originally posted by m.timoney
    i've got 2 odbc table joined in a query called RawData

    now i try to query RawData with
    SELECT RawData.*,
    FROM RawData
    WHERE (((RawData.SHIP_TO)=[forms]![PerCustomer]![name]));
    does the same as the first
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  10. #10
    Join Date
    Sep 2002
    Location
    Land of OZ
    Posts
    173

    Re: what the F**k is going on

    I had a similar problem, I wrote some code to create a temp table (say TempRawData) based on the query (in your case RAWDATA) then wrote the code to do the following ....

    SELECT TempRawData.*,
    FROM RawData
    WHERE (((RawData.SHIP_TO)=[name]));

    Give this option a try.

    I found an article on the MS site but I can't seem to refind it at the moment ... but it is there ... somewhere

    Originally posted by m.timoney
    i've got 2 odbc table joined in a query called RawData

    now i try to query RawData with
    SELECT RawData.*,
    FROM RawData
    WHERE (((RawData.SHIP_TO)=[name]));

    and get ODBC -- call failed

    yet

    SELECT RawData.*
    FROM RawData
    WHERE (((RawData.SHIP_TO)="DEW005"));

    works perfectly what is it playing at
    win 2000, access 2000

    also

    SELECT RawData.*,
    FROM RawData
    WHERE (((RawData.SHIP_TO)=[forms]![PerCustomer]![name]));
    does the same as the first

  11. #11
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    how can you select from a table that doesn't excist in the query? and i don't see how it would work any way as Raw data isn't a ODBC table but a query that combines data from to odbc tables ie

    Code:
    SELECT Administrators_INVHIST_DER.*, Administrators_INVHIST_NF.ITEMNBR, 
    Administrators_INVHIST_NF.SHIPQTY, Administrators_INVHIST_NF.WHSE
    FROM Administrators_INVHIST_NF INNER JOIN Administrators_INVHIST_DER 
    ON Administrators_INVHIST_NF.ID = Administrators_INVHIST_DER.ID;
    so temping the table wont make any difference as far as i can see, personaly i can't see why access passes the buck to odbc anyway as i've linked the data into access so why the hell doesn't access perform the SQL on teh data i've linked in instead of wasting time passing access command that ODBC doesn't recognise to ODBC
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  12. #12
    Join Date
    Oct 2002
    Location
    Greensboro, NC
    Posts
    50
    Originally posted by m.timoney
    latest attempt

    Code:
    Private Sub Customer_AfterUpdate()
        Dim dbs As DAO.Database, qry As QueryDef
        Set dbs = CurrentDb
        dbs.QueryDefs.Delete "PerCust"
        Set qry = dbs.CreateQueryDef("PerCust", "SELECT RawData.* FROM
     RawData WHERE RawData.SHIP_TO = '" & Customer.Value & "';")
        DoCmd.Requery
    End Sub
    this works but to get the new details to show you have to close down the form and reopen
    Hi,
    you should not have to close down the form and reopen to show the new details.
    try this
    form.requery
    form.refresh
    form.repaint

    tjacobs

  13. #13
    Join Date
    Oct 2002
    Location
    Greensboro, NC
    Posts
    50
    Originally posted by m.timoney
    latest attempt

    Code:
    Private Sub Customer_AfterUpdate()
        Dim dbs As DAO.Database, qry As QueryDef
        Set dbs = CurrentDb
        dbs.QueryDefs.Delete "PerCust"
        Set qry = dbs.CreateQueryDef("PerCust", "SELECT RawData.* FROM
     RawData WHERE RawData.SHIP_TO = '" & Customer.Value & "';")
        DoCmd.Requery
    End Sub
    this works but to get the new details to show you have to close down the form and reopen
    Forgot one!

    docmd.save acform,"FormName"
    form.requery
    form.refresh
    form.repaint

    tjacobs

  14. #14
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    no affraid not form, still show results of the query before it was changed

    docmd.save "PerCustomer"
    Form_PerCustomer.Requery
    Form_PerCustomer.Repaint
    Form_PerCustomer.Refresh
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  15. #15
    Join Date
    Oct 2002
    Location
    Greensboro, NC
    Posts
    50
    Originally posted by m.timoney
    no affraid not form, still show results of the query before it was changed

    docmd.save "PerCustomer"
    Form_PerCustomer.Requery
    Form_PerCustomer.Repaint
    Form_PerCustomer.Refresh
    what do you mean not form? why do you have Form_PerCustomer if your form name if "PerCustomer"?

    PerCustomer.requery
    PerCustomer.refresh
    PerCustomer.repaint

Posting Permissions

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