Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2004
    Posts
    13

    Red face Unanswered: lookup value from one table and insert into another

    Fisrtly I am new to VBA and SQL, and have been given a job to do...

    so here goes...

    One table called tblPrices has a CustID (text), PartNo (text), Price (currency), CUR (text). Let me explain that the field called "CUR" is the currency of the part. eg GBP or EURO. Note that The same part numer can be used by different customers and have different prices, hence the

    sSQL = "SELECT DISTINCT price FROM tblPrices "
    sSQL = sSQL & "WHERE partno = '" & PartNo & "' AND CustID = '" & CustID & "';"

    There is another table called tblGoodIn which also has a fields called CustID, PartNo, PartDescription, Price & CUR. There is also a form based on this table.

    When the part number is entered into the form, the Part description & price is lookedup, I also now need the currency looked up,

    I have tried using the same code as below, but get errors, What is the best way to add the CUR field? Thanks is advance.

    Allen

    My code so far is;

    Private Sub PartNo_AfterUpdate()


    Dim sSQL As String
    Dim Rec As Recordset
    Dim db As Database


    ' Query to bring out the price, using variables in the form.
    sSQL = "SELECT DISTINCT price FROM tblPrices "
    sSQL = sSQL & "WHERE partno = '" & PartNo & "' AND CustID = '" & CustID & "';"


    Set db = CurrentDb
    Set Rec = db.OpenRecordset(sSQL)


    ' Check something is returned and set the price field to returned value.

    If Rec.RecordCount > 0 Then
    price = Rec.Fields("price")
    'MsgBox price
    Else
    price = "0"
    End If



    'Lookup part description

    Dim Rec2 As Recordset
    Dim db2 As Database
    Dim sDes As String


    ' Query to bring out the PartDescription, using variables in the form.
    sDes = "SELECT DISTINCT PartDescription FROM tblPartDes "
    sDes = sDes & "WHERE partno = '" & PartNo & "' "
    'AND PartDescription = '" & PartDescription & "';"

    Set db2 = CurrentDb
    Set Rec2 = db2.OpenRecordset(sDes)

    ' Check something is returned and set the price field to returned value.

    If Rec.RecordCount > 0 Then
    PartDescription = Rec2.Fields("PartDescription")
    'MsgBox PartDescription
    Else
    PartDescription = "not known"
    End If


    End Sub

  2. #2
    Join Date
    Aug 2003
    Location
    Argentina
    Posts
    465
    What errors give you...????
    Saludos
    Norberto

  3. #3
    Join Date
    Jan 2004
    Posts
    13
    Hi Thanks for replying...

    I get an error "Compile Error - Syntax error"

    on line

    currency = Rec3.Fields("Currency")

    code is as follows (ps. currencyID is the name of the field in the tblPrices table and currency is the name of the field in tblGoodIn table)

    thanks



    Private Sub PartNo_AfterUpdate()


    Dim sSQL As String
    Dim Rec As Recordset
    Dim db As Database


    ' Query to bring out the price, using variables in the form.
    sSQL = "SELECT DISTINCT price FROM tblPrices "
    sSQL = sSQL & "WHERE partno = '" & PartNo & "' AND CustID = '" & CustID & "';"


    Set db = CurrentDb
    Set Rec = db.OpenRecordset(sSQL)


    ' Check something is returned and set the price field to returned value.

    If Rec.RecordCount > 0 Then
    price = Rec.Fields("price")
    'MsgBox price
    Else
    price = "0"
    End If



    'Lookup part description

    Dim Rec2 As Recordset
    Dim db2 As Database
    Dim sDes As String


    ' Query to bring out the PartDescription, using variables in the form.
    sDes = "SELECT DISTINCT PartDescription FROM tblPartDes "
    sDes = sDes & "WHERE partno = '" & PartNo & "' "
    'AND PartDescription = '" & PartDescription & "';"

    Set db2 = CurrentDb
    Set Rec2 = db2.OpenRecordset(sDes)

    ' Check something is returned and set the Part description field to returned value.

    If Rec.RecordCount > 0 Then
    PartDescription = Rec2.Fields("PartDescription")
    'MsgBox PartDescription
    Else
    PartDescription = "not known"
    End If


    'Lookup Currency

    Dim Rec3 As Recordset
    Dim db3 As Database
    Dim sCur As String


    ' Query to bring out the Currency, using variables in the form.
    sCur = "SELECT DISTINCT CurrencyID FROM tblPrices "
    sCur = sCur & "WHERE partno = '" & PartNo & "' AND CustID = '" & CustID & "';"

    Set db3 = CurrentDb
    Set Rec3 = db3.OpenRecordset(sCur)

    ' Check something is returned and set the Currency field to returned value.

    If Rec3.RecordCount > 0 Then
    currency = Rec3.Fields("Currency")

    Else
    Currency = "not known"
    End If


    End Sub

  4. #4
    Join Date
    Aug 2003
    Location
    Argentina
    Posts
    465
    (ps. currencyID is the name of the field in the tblPrices table and currency is the name of the field in tblGoodIn table)

    currency = Rec3.Fields("currencyID")

    I thing this is the error.
    Saludos
    Norberto

  5. #5
    Join Date
    Jun 2003
    Location
    Belgium
    Posts
    133
    awedmondson,

    why don't you make a query combining your 3 tables? You could use that query to retrieve all the wanted data in 1 time

    As far as I can tell from your code, you should be able to join the 3 tables on the field PartNo...

  6. #6
    Join Date
    Aug 2003
    Location
    Argentina
    Posts
    465
    Herman have reason is more easy in that way.
    Saludos
    Norberto

  7. #7
    Join Date
    Jan 2004
    Posts
    13
    Originally posted by Norberto
    Herman have reason is more easy in that way.
    Hi Herman,

    I have the table as a subForm. If I make a form out of a query, I dont seem to be able to add records,

  8. #8
    Join Date
    Jun 2003
    Location
    Belgium
    Posts
    133
    is probably because you created a non-updateable query.

    Try reading something on "When can I update data from a query?" in the Help. You'll be amazed what interesting things you can find there....

Posting Permissions

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