> I'm still a VBA novice, and would appreciate help.

> The context -
> I have a table of products available for sale (tblMaster).
> I have a form used to create an invoice, with a "detail" subform
> containing the individual items being purchased (data to be entered
> into table tblInvItem). In the subform I use a Combo box to select
> an item from tblMaster using the tblMaster key field (ProdID).
> Getting that value into the appropriate field in tblInvItem is,
> of course, just what the Combo box is designed to do.
> However, I ALSO want to copy a couple of other fields from tblMaster
> to tblInvItem, e.g., price (I don't want items in the invoice created
> today to change value when the price (in tblMaster) changes tomorrow).

> I'm using Access 2000. I am guessing I need to use an [Event Procedure]
> for the "After Update" event of the Combo box. I would appreciate any
> help in figuring out how to code this (or how to do this efficiently
> using a query or some other method). Performance matters - tblMaster
> contains over 30,000 records.

> TIA for any help!
> Sigurd Andersen

In the AfterUpdate event procedure of the control where you select
the product, grab the info you need from the table:
-----------------------------------------------
Dim strSQL as String
Dim rs As DAO.Recordset

If Not IsNull(Me.Product) Then
  strSQL = "SELECT Price, Descrip FROM tblMaster WHERE ProductID = " _
      & Me.Product & ";"
  Set rs = CurrentDb().OpenRecordset(strSQL)
  If rs.RecordCount > 0 Then
    Me.Price = rs!Price
    Me.Description = rs!Descrip
    'etc for other fields if desired.
  End If
  rs.Close
End If

Set rs = Nothing
-----------------------------------------------

> I'm still a VBA novice, and would appreciate help.

> The context -
> I have a table of products available for sale (tblMaster).
> I have a form used to create an invoice, with a "detail" subform
> containing the individual items being purchased (data to be entered
> into table tblInvItem). In the subform I use a Combo box to select
> an item from tblMaster using the tblMaster key field (ProdID).
> Getting that value into the appropriate field in tblInvItem is,
> of course, just what the Combo box is designed to do.
> However, I ALSO want to copy a couple of other fields from tblMaster
> to tblInvItem, e.g., price (I don't want items in the invoice created
> today to change value when the price (in tblMaster) changes tomorrow).

> I'm using Access 2000. I am guessing I need to use an [Event Procedure]
> for the "After Update" event of the Combo box. I would appreciate any
> help in figuring out how to code this (or how to do this efficiently
> using a query or some other method). Performance matters - tblMaster
> contains over 30,000 records.

> TIA for any help!
> Sigurd Andersen

--
Perth, Western Australia
Tips for MS Access users at:
        http://odyssey.apana.org.au/~abrowne
.

> I tried the following code, based on your suggestion:

> Private Sub cbxProdID_AfterUpdate()
>     Dim strSQL As String
>     Dim rs As DAO.Recordset

>     If Not IsNull(Me.ProdID) Then
>         strSQL = "SELECT Descrip, ListUnit, ListPr, " & _
>           "BPPlan, BNetPr, BConPr FROM tblMaster WHERE " & _
>           "ProdID = " & Me.ProdID & ";"
>         Set rs = CurrentDb().OpenRecordset(strSQL)
>         If rs.RecordCount > 0 Then
>             Me.Descrip = rs!Descrip
>             Me.Unit = rs!ListUnit
>             Me.Price = rs!ListPr ' May need to use BConPr?
>             Me.PP = rs!BPPlan
>             Me.Cost = rs!BNetPr
>         End If
>         rs.Close
>     End If
>     Set rs = Nothing

> End Sub

> and got an error on the
>     Dim rs As DAO.Recordset
> statement. I assume this has to do with DAO not being the
> default in Access 2000, so I tried using ADO instead, as
> in the following code :

> Private Sub cbxProdID_AfterUpdate()
>     Dim strSQL As String
>     Dim rs As ADODB.Recordset

>     If Not IsNull(Me.ProdID) Then
>         strSQL = "SELECT Descrip, ListUnit, ListPr, " & _
>           "BPPlan, BNetPr, BConPr FROM tblMaster WHERE " & _
>           "ProdID = " & Me.ProdID & ";"
>         Set rs = New ADODB.Recordset
>         rs.ActiveConnection = CurrentProject.Connection
>         rs.CursorType = adOpenStatic
>         rs.LockType = adLockOptimistic
>         rs.Open strSQL
>         If rs.RecordCount > 0 Then
>             Me.Descrip = rs!Descrip
>             Me.Unit = rs!ListUnit
>             Me.Price = rs!ListPr ' May need to use BConPr?
>             Me.PP = rs!BPPlan
>             Me.Cost = rs!BNetPr
>         End If
>         rs.Close
>     End If
>     Set rs = Nothing

> End Sub

> but got the error
> "No value given for one or more required parameters"
> on the rs.Open statement. Using the variation:

>         rs.Open "SELECT Descrip, ListUnit, ListPr, " & _
>           "BPPlan, BNetPr, BConPr FROM tblMaster WHERE " & _
>           "ProdID = " & Me.ProdID & ";"

> led to the same error. What "required parameter" am I missing?
> Or is my syntax messed up some other way?

> I've been using the book
>   Access 2000 Developer's Handbook (Ken Getz et.al.)
> to help me understand VBA and various other Access issues -
> the authors seem to recommend using ADO if you're starting
> from scratch (i.e., not using DAO because of familiarity or
> for consistency with past projects, etc.)

> -----Original Message-----
> In the AfterUpdate event procedure of the control where you select
> the product, grab the info you need from the table:
> -----------------------------------------------
> Dim strSQL as String
> Dim rs As DAO.Recordset

> If Not IsNull(Me.Product) Then
>   strSQL = "SELECT Price, Descrip FROM tblMaster WHERE ProductID = " _
>       & Me.Product & ";"
>   Set rs = CurrentDb().OpenRecordset(strSQL)
>   If rs.RecordCount > 0 Then
>     Me.Price = rs!Price
>     Me.Description = rs!Descrip
>     'etc for other fields if desired.
>   End If
>   rs.Close
> End If

> Set rs = Nothing
> -----------------------------------------------

> > I'm still a VBA novice, and would appreciate help.

> > The context -
> > I have a table of products available for sale (tblMaster).
> > I have a form used to create an invoice, with a "detail" subform
> > containing the individual items being purchased (data to be entered
> > into table tblInvItem). In the subform I use a Combo box to select
> > an item from tblMaster using the tblMaster key field (ProdID).
> > Getting that value into the appropriate field in tblInvItem is,
> > of course, just what the Combo box is designed to do.
> > However, I ALSO want to copy a couple of other fields from tblMaster
> > to tblInvItem, e.g., price (I don't want items in the invoice created
> > today to change value when the price (in tblMaster) changes tomorrow).

> > I'm using Access 2000. I am guessing I need to use an [Event Procedure]
> > for the "After Update" event of the Combo box. I would appreciate any
> > help in figuring out how to code this (or how to do this efficiently
> > using a query or some other method). Performance matters - tblMaster
> > contains over 30,000 records.

> > TIA for any help!
> > Sigurd Andersen

> --
> Perth, Western Australia
> Tips for MS Access users at:
>         http://odyssey.apana.org.au/~abrowne
> .

From any code window, choose References on the Tools menu.
Check the reference to the Microsoft DAO 3.6 Library.
The code should then be okay.

If ProdID is a field of type Text, you may need extra quotes.
Otherwise what you are doing makes perfect sense.

> I tried the following code, based on your suggestion:

> Private Sub cbxProdID_AfterUpdate()
>     Dim strSQL As String
>     Dim rs As DAO.Recordset

>     If Not IsNull(Me.ProdID) Then
>         strSQL = "SELECT Descrip, ListUnit, ListPr, " & _
>           "BPPlan, BNetPr, BConPr FROM tblMaster WHERE " & _
>           "ProdID = " & Me.ProdID & ";"
>         Set rs = CurrentDb().OpenRecordset(strSQL)
>         If rs.RecordCount > 0 Then
>             Me.Descrip = rs!Descrip
>             Me.Unit = rs!ListUnit
>             Me.Price = rs!ListPr ' May need to use BConPr?
>             Me.PP = rs!BPPlan
>             Me.Cost = rs!BNetPr
>         End If
>         rs.Close
>     End If
>     Set rs = Nothing

> End Sub

> and got an error on the
>     Dim rs As DAO.Recordset
> statement. I assume this has to do with DAO not being the
> default in Access 2000, so I tried using ADO instead, as
> in the following code :

> Private Sub cbxProdID_AfterUpdate()
>     Dim strSQL As String
>     Dim rs As ADODB.Recordset

>     If Not IsNull(Me.ProdID) Then
>         strSQL = "SELECT Descrip, ListUnit, ListPr, " & _
>           "BPPlan, BNetPr, BConPr FROM tblMaster WHERE " & _
>           "ProdID = " & Me.ProdID & ";"
>         Set rs = New ADODB.Recordset
>         rs.ActiveConnection = CurrentProject.Connection
>         rs.CursorType = adOpenStatic
>         rs.LockType = adLockOptimistic
>         rs.Open strSQL
>         If rs.RecordCount > 0 Then
>             Me.Descrip = rs!Descrip
>             Me.Unit = rs!ListUnit
>             Me.Price = rs!ListPr ' May need to use BConPr?
>             Me.PP = rs!BPPlan
>             Me.Cost = rs!BNetPr
>         End If
>         rs.Close
>     End If
>     Set rs = Nothing

> End Sub

> but got the error
> "No value given for one or more required parameters"
> on the rs.Open statement. Using the variation:

>         rs.Open "SELECT Descrip, ListUnit, ListPr, " & _
>           "BPPlan, BNetPr, BConPr FROM tblMaster WHERE " & _
>           "ProdID = " & Me.ProdID & ";"

> led to the same error. What "required parameter" am I missing?
> Or is my syntax messed up some other way?

> I've been using the book
>   Access 2000 Developer's Handbook (Ken Getz et.al.)
> to help me understand VBA and various other Access issues -
> the authors seem to recommend using ADO if you're starting
> from scratch (i.e., not using DAO because of familiarity or
> for consistency with past projects, etc.)

> -----Original Message-----
> In the AfterUpdate event procedure of the control where you select
> the product, grab the info you need from the table:
> -----------------------------------------------
> Dim strSQL as String
> Dim rs As DAO.Recordset

> If Not IsNull(Me.Product) Then
>   strSQL = "SELECT Price, Descrip FROM tblMaster WHERE ProductID = " _
>       & Me.Product & ";"
>   Set rs = CurrentDb().OpenRecordset(strSQL)
>   If rs.RecordCount > 0 Then
>     Me.Price = rs!Price
>     Me.Description = rs!Descrip
>     'etc for other fields if desired.
>   End If
>   rs.Close
> End If

> Set rs = Nothing
> -----------------------------------------------

> > I'm still a VBA novice, and would appreciate help.

> > The context -
> > I have a table of products available for sale (tblMaster).
> > I have a form used to create an invoice, with a "detail" subform
> > containing the individual items being purchased (data to be entered
> > into table tblInvItem). In the subform I use a Combo box to select
> > an item from tblMaster using the tblMaster key field (ProdID).
> > Getting that value into the appropriate field in tblInvItem is,
> > of course, just what the Combo box is designed to do.
> > However, I ALSO want to copy a couple of other fields from tblMaster
> > to tblInvItem, e.g., price (I don't want items in the invoice created
> > today to change value when the price (in tblMaster) changes tomorrow).

> > I'm using Access 2000. I am guessing I need to use an [Event Procedure]
> > for the "After Update" event of the Combo box. I would appreciate any
> > help in figuring out how to code this (or how to do this efficiently
> > using a query or some other method). Performance matters - tblMaster
> > contains over 30,000 records.

> > TIA for any help!
> > Sigurd Andersen

> --
> Perth, Western Australia
> Tips for MS Access users at:
>         http://odyssey.apana.org.au/~abrowne
> .

--
Perth, Western Australia
Tips for MS Access users at:
        http://odyssey.apana.org.au/~abrowne
.