Results 1 to 10 of 10
  1. #1
    Join Date
    Mar 2004
    Posts
    21

    Unanswered: Create VB variable with SQL statements in Access

    I've posted this on the Microsoft Access forum, but no has responded. Maybe someone here can help.

    I'm kind of new to using SQL statements in Access. But I'm very familiar with MySQL and Access. I'm trying to SUM() two fields of a table based on the entered value of a field in the open form. Then perform a visual basic calculation with the two results to get my visual basic variable. Then I would use a SQL UPDATE to pass the variable value to another table. I believe I have found the proper syntax to perform the UPDATE, but I'm struggling with getting the two sums as visual basic variables. Also, I want this to occur when a button click is performed. This also is not a problem as I believe I know how to create the sub syntax. All tables reside in the same Access database.

    If this helps, here are the table names and field names and a brief description of how I want to use them:
    Table: Item_Receipts
    Field: Quantity
    Field: Invoice_Amt
    Table: Item_Listing
    Field: Avg_Each_Cost

    When items are entered into Item_Receipts table through form, user clicks on command button. On click, VB sums Quantity for that item in Item_Receipts table and sums Invoice_Amt for same item in same table. Sum of Invoice_Amt divided by Sum of Quantity is calculated to arrive at Avg_Each_Cost. Avg_Each_Cost is passed to Item_Listing table and updates Avg_Each_Cost field for entered item.

    Hope this makes sense and I appreciate any help with this.
    ---------------------
    Thanks
    David

  2. #2
    Join Date
    Jan 2004
    Posts
    184

    Re: Create VB variable with SQL statements in Access

    I can asnwer your question but the question is not very clear. Can you please restate it?
    In abundance of water only the fool is thirsty. Bob Marley.

  3. #3
    Join Date
    Mar 2004
    Posts
    21
    Thanks for the reply.

    A user opens a form for data entry to the Item_Receipts table. They enter the data in each of the fields; Item_Nbr, Date, Quantity and Invoice_Amt. When they click on an ENTER command button on the form, I would like a SQL statement to run in the background that pulls all other receipts for the item number they entered in the form from the Item_Recipts table. But I want the SQL to SUM() the Quantity field and SUM() the Invoice_Amt field. Then I want an average of the cost of the item by calculating total invoice amount divided by total quantity amount.

    Once the average cost of the item is determined, then I want to UPDATE the Avg_Each_Cost for that item in the Item_Listing table.

    I was thinking that to do this in VB I would need to have a variable to pass the result to the UPDATE table. This is my first time to work with VB, SQL and Access all together. So I'm really not sure where to begin, what syntax to use to establish a database connection (if necessary since the database will already be open), etc. I'm just real green at this. But I do have experience with MySQL syntax, VB syntax and Access. But just not all together. If you have a better suggestion to get this done, I'm open to it.

    Thanks for any help you can give and I hope this explains a little better.
    ---------------------
    Thanks
    David

  4. #4
    Join Date
    Jan 2004
    Posts
    184
    Yes it is clearer. I am assuming you will be using ADO through your Visual Basic Application to connect to Access (even if the database is open your VB app still needs to connect).

    (Remember to add the reference to Microsoft ActiveX data objects ADO in your VB project)

    dim cn as ADODB.connection
    dim rs as ADODB.recordset

    dim floatAverage as float

    set cn = new ADODB.connection

    cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source=C:\Test.mdb;Persist Security Info=False"
    cn.open 'Now you have an open connection to Access

    set rs = new ADODB.recordset

    rs.actiiveconnection = cn

    rs.open "SELECT SUM(Invoice_Amt)/SUM(Quantity) AS AverageInv FROM TABLE WHERE Item_Nbr=' " & textbox.text & " ' "

    floatAverage = rs.fields("AverageInv")

    rs.close 'I don't need the open recordset anymore

    cn.execute "UPDATE Item_Listing SET Avg_Each_Cost=' " & floatAverage & " ' FROM Item_Listing WHERE Item_Nbr=' " & textbox.text & " ' "

    cn.close

    set rs=nothing
    set cn = nothing

    You will have to modify the SQL queries for Access, I beleive they are slighly different then SQL otherwise that is the technique.

    You might also find one these controls useful:
    http://www.scirocco.ca/downloads.html
    In abundance of water only the fool is thirsty. Bob Marley.

  5. #5
    Join Date
    Mar 2004
    Posts
    21
    Here's my code:
    Private Sub Outcharge_Receipts_Enter_Button_Click()
    On Error GoTo myError

    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset

    'Dim floatAverage As float
    Dim dblAverage As Double
    Dim sPath As String

    Set cn = New ADODB.Connection
    'Added current directory variable for path to file
    sPath = CurDir
    If Right(sPath, 1) = "\" Then
    'do nothing
    Else
    sPath = sPath & "\"
    End If

    cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source=" & sPath & "StoreEquipment.mdb;Persist Security Info=False"
    cn.Open 'Now you have an open connection to Access

    Set rs = New ADODB.Recordset

    rs.ActiveConnection = cn

    rs.Open "SELECT SUM(Invoice_Amt)/SUM(Quantity) AS AverageInv FROM Outcharge_Receipts WHERE Item_Nbr=' " & Item_Nbr.Text & " ';"

    'floatAverage = rs.Fields("AverageInv")
    dblAverage = rs.Fields("AverageInv")

    rs.Close 'I don't need the open recordset anymore

    cn.Execute "UPDATE Outcharge_Item_List SET Avg_Each_Cost=' " & floatAverage & " ' FROM Outcharge_Item_List WHERE Item_Nbr=' " & Item_Nbr.Text & " ';"

    cn.Close

    Set rs = Nothing
    Set cn = Nothing

    SendKeys "{TAB}"

    GoTo endSub

    myError:
    MsgBox "An error in SQL occured." & vbCrLf & "Error Number: " & Err.Number & vbCrLf & "Error Desc: " & Err.Description, vbOKOnly, "SQL Error"
    GoTo endSub

    endSub:
    End Sub


    And attached is the error I received, Err # 2185.
    Attached Thumbnails Attached Thumbnails err2185.jpg  
    ---------------------
    Thanks
    David

  6. #6
    Join Date
    Feb 2004
    Posts
    199
    use Item_Nbr instead of Item_Nbr.Text
    MDB, ADP <-> MS SQL + VBA, ADO & RDO, .NET, Oracle, Java/Jsp.

  7. #7
    Join Date
    Jan 2004
    Posts
    184
    Did you try stepping through the code (by pressing F8) to see where the error occurs?

    Also make sure your SQL statements will work in access. Put breakpoints in the code take your SQL statement with quotations, and put that in the immediate window wiyh a quetion mark i.e.

    In the immediate window after the code reached the first rs.open take the statement

    "SELECT SUM(Invoice_Amt)/SUM(Quantity) AS AverageInv FROM Outcharge_Receipts WHERE Item_Nbr=' " & Item_Nbr.Text & " ';"

    Put it in the immediate window preceded by a question mark as follows

    ? "SELECT SUM(Invoice_Amt)/SUM(Quantity) AS AverageInv FROM Outcharge_Receipts WHERE Item_Nbr=' " & Item_Nbr.Text & " ';"

    Press Enter

    This will give you the statement that will be sent to access

    SELECT SUM(Invoice_Amt)/SUM(Quantity) AS AverageInv FROM Outcharge_Receipts WHERE Item_Nbr=' 3 ';

    Make sure it returns the result you are looking for (often times we make small syntax mistakes when writing SQL statements in VB)
    In abundance of water only the fool is thirsty. Bob Marley.

  8. #8
    Join Date
    Mar 2004
    Posts
    21
    The error below is the error I get when I changed Item_Nbr.Text to Item_Nbr.

    Also, I tried inserting the SQL statements in the immediate window with a ? at the beginning and nothing happened. And, when I get the errors, I'm not sent to the debug of the code, so I cannot step through it.

    I'll try the break points as suggested. I blieve your saying to break up the SQL statement lines such as:

    rs.Open "SELECT SUM(Invoice_Amt)/SUM(Quantity) AS AverageInv " & _
    FROM Outcharge_Receipts " & _
    WHERE Item_Nbr='" & Item_Nbr & "';"

    Let me know if this is not what you're referring to.
    Attached Thumbnails Attached Thumbnails err2147217904.jpg  
    ---------------------
    Thanks
    David

  9. #9
    Join Date
    Mar 2004
    Posts
    21
    I keep getting all types of errors with no chance of stepping into the code. Is there any other way I can test this? I finally figured out how to use the Immediate window. Each SQL syntax is returning the syntax and the variables undefined. Shouldn't I have the Avg_Each_Cost value if it's pulling the data correctly?

    Anyway, the code above is not working. I continue to get the error 2185 and others.
    ---------------------
    Thanks
    David

  10. #10
    Join Date
    Mar 2004
    Posts
    21

    Talking

    Wow!! I got what I wanted, but in a totally different way. After some extensive research, I found the DSum() syntax. This is how I wrote the code. Thanks a bunch for all the help you guys provided. I did learn something today.

    Code:
    Private Sub Outcharge_Receipts_Enter_Button_Click()
    On Error GoTo myError

    Dim SumTotalInv As Double
    Dim SumQuantity As Integer
    Dim AvgEachCost As Double
    Dim SQL As String

    SumTotalInv = DSum("[Invoice_Amount]", "Outcharge_Receipts", "[Item_Nbr]='" & Item_Nbr & "'")
    SumQuantity = DSum("[Quantity]", "Outcharge_REceipts", "[Item_Nbr]='" & Item_Nbr & "'")

    AvgEachCost = (SumTotalInv + Invoice_Amt) / (SumQuantity + Quantity)

    SQL = "UPDATE Outcharge_Item_List " & _
    "SET Outcharge_Item_List.Avg_Each_Cost=" & AvgEachCost & " " & _
    "WHERE Outcharge_Item_List.Item_Nbr='" & Item_Nbr & "';"

    DoCmd.RunSQL SQL

    SendKeys "{TAB}"

    GoTo endSub

    myError:
    MsgBox "Error Number: " & Err.Number & vbCrLf & "Error Desc: " & Err.Description, vbOKOnly, "Error"
    GoTo endSub

    endSub:
    End Sub

    Again, I really appreciate the help today.
    ---------------------
    Thanks
    David

Posting Permissions

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