Results 1 to 1 of 1
  1. #1
    Join Date
    Jun 2002

    Unanswered: Multi-table queries and Inserting New Records

    Hello all,

    I need to find a way to be able to insert a record into a query... or something like that... I have a query which joins 3 tables together, but I really only want to update and insert into one table. I however want to display parts of the other tables within the same subform as the table I want to update... maybe it's easier if I explain with my example...

    I have a master form which displays project information 'tblProject'
    and a listbox which lists the claims that the project has which is found
    from a query to 'tblClaims' with the 'tblProject' primary key as the constraint.

    When one selects an individual claim to edit a new form opens 'frmClaim'
    In 'frmClaim' it displays all the information about this individual claim that one can edit at will. There is a subform that displays all the tender items in this individual claim.

    This subform is where my problem lies. I tried creating it with a query that joined 'tblTenderItems', 'tblPricing'(a master price list), and 'tblLineItem'(a master list of line items that we have). I wanted to show 'Quantity', 'Lineitem_No' from 'tblTenderItems', 'Price' of each item taken from 'tblPricing' and the 'Unit_Label'(or Unit of measurement for the line item) from the 'tblLineItem'.

    Quantity / Lineitem_No / Unit_Label / Price
    34000 / 12 / c.m. / $1.83

    When you have a one table query it gives you an additional new line to be able to insert new tenderitems as needed, but with a multi-table query you may only edit the data that is already inputted. Since Unit_Label and Price are already set I would only be inserting new rows into 'tblTenderItems' and none of the other tables.

    I tried to make a subform with only the one table... and then create unbound disabled textboxes to show the Unit and price... but when I used VBA to populate the textbox... the value just propagates through instead of say showing the price and unit of each of the tender items... it just shows the first price and unit as the price and unit for all of them.

    so instead of

    Quantity / Lineitem_No / Unit_Label / Price
    34000 / 12 / c.m. / $1.83
    8 / 14 / ha. / $2000.65
    5400 / 23 / v.m. / $5.95

    we get
    Quantity / Lineitem_No / Unit_Label / Price
    34000 / 12 / c.m. / $1.83
    8 / 14 / c.m. / $1.83
    5400 / 23 / c.m. / $1.83

    I've tried putting the Code in almost every event trigger because I was not sure which one would work... but none of them did.

    Here is the code:
    Dim strSQL As String
    Dim Conn As ADODB.Connection
    Dim RS As ADODB.Recordset

    Set Conn = New ADODB.Connection

    Conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=wherever your database is"

    strSQL = "SELECT [Unit_Label] FROM tblLineitem WHERE Lineitem_No=" & [LineItem_No]
    Set RS = Conn.Execute(strSQL)

    If Not RS.EOF Then
    MsgBox RS("Unit_Label").Value
    Me!txtUnit.Value = RS("Unit_Label").Value
    End If

    I put a msgbox to see how many times this code executed... and it was only once.

    Anyways just wondering if there was another work around or if there is a way to insert into a multitable query...

    Thanks for your time


    PS - included is my current preliminary ERD in Word format.
    Attached Files Attached Files

Posting Permissions

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