Results 1 to 2 of 2

Thread: Vba/sql

  1. #1
    Join Date
    May 2014
    Posts
    16

    Unanswered: Vba/sql

    I have a form that has a header field named fBuyer (attachment Buyer). This form also get its data from a record source name Supplier_Alpha which is a query. In the SQL View of the query I have added the WHERE part.

    SELECT dbo_DB02001.SupplierID, dbo_DB02001.Supplier, dbo_DB02001.Status, dbo_DB02001.Buyer
    FROM dbo_DB02001
    WHERE (((dbo_DB02001.Buyer)=[Forms]![Supplier_Alpha]![me.fbuyer.value]));

    When execute the form I get the error:
    Invalid bracketing of name '[Forms]![Supplier_Alpha]![me.fbuyer.value]" What is wrong?
    Attached Files Attached Files

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Code:
    strSQL = "SELECT dbo_DB02001.SupplierID,"
    strSQL = strSQL & "  dbo_DB02001.Supplier,"
    strSQL = strSQL & "  dbo_DB02001.Status,"
    strSQL = strSQL & "  dbo_DB02001.Buyer"
    strSQL = strSQL & "FROM dbo_DB02001"
    strSQL = strSQL & "WHERE dbo_DB02001.Buyer =" &  [Forms]![Supplier_Alpha]![me.fbuyer.value]  & ";"
    you don't say how you are using this, but I expect the reason for the problem is that you are not toggling between VBA and SQL code. if you wnt to use a value for a control or variable you have to 'drop out' of SQL code and into VBA code. you cannot access controls and reports directly in SQL code

    on top of that if fbuyer is alphanumeric then could be whinging about not delimiting the value.
    Code:
    strSQL = "SELECT dbo_DB02001.SupplierID,"
    strSQL = strSQL & "  dbo_DB02001.Supplier,"
    strSQL = strSQL & "  dbo_DB02001.Status,"
    strSQL = strSQL & "  dbo_DB02001.Buyer"
    strSQL = strSQL & "FROM dbo_DB02001"
    strSQL = strSQL & "WHERE dbo_DB02001.Buyer ='" &  [Forms]![Supplier_Alpha]![me.fbuyer.value]  & "';"
    I'd rather be riding on the Tiger 800 or the Norton

Tags for this Thread

Posting Permissions

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