auto add item in table when a code is entered

[h2mhd]

Hi

i have a form invoice that contains some fields about the client, date etc.. and have a subform that contains the details of the invoice. So what i want to do is when the user enter a code like pn for tire it adds automatically a line in the table for the tire tax.

I am able to add this line automatically but when i requery my tables it goes to the first record of this table and the client have to schroll down to return to the line he was working on.

is there a way to add a line in the table and return to the exact record he was working on? in this case i want to return to the line pn on the field description.

heres the code i use:

Dim Base As Database
Dim RstPiece, RstDetailFacture As Recordset

On Error Resume Next
Set Base = CurrentDb

If Me.IdPiece.Column(1) = "PN" Then
Set RstDetailFacture = Base.OpenRecordset("SELECT * from detailfacture")
With RstDetailFacture
.AddNew
!IDFacture = Forms!facturation!IDFacture
!DateFacture = Forms!facturation!DateFacture
!IdPiece = 11 ' tire tax code
.Update
End With
End If

Set RstPiece = Base.OpenRecordset("select * from piece where idpiece = " & Me.IdPiece)

If Not RstPiece.EOF Then
'Me.DescItem = RstServices!DescServiceVente
Me.DescItem = RstPiece!DescPiece
Me.Vendant = RstPiece!PrixVendant
End If

Me.Requery

RstPiece.Close
Set Base = Nothing

thanks a lot and have a nice day

 

[seaport]

You need to manipulate the subform recordset to set the focus on the record that a user just entered. The code is like:

' after a invoice detailed record is inserted and the RowID for
' that record is obtained.

dim frm as Form
set frm=me!SubformDetails
frm.form.requery
frm.recordset.findfirst "RowID=" = rowID

Seaport

 

[h2mhd]

Hi seaport

thanks for your help!

i have made changes to my code to put the code you gave me but it finds the record with the rowid but it does'nt requery the form so the line that i add automatically is not showing.

heres the code changes i have made


Dim Base As Database
Dim RowID As Integer
Dim RstPiece, RstDetailFacture As Recordset
Dim Frm As Form

On Error Resume Next
Set Base = CurrentDb
Set Frm = Me!DetailFacture

RowID = Me.IdDetailFacturation

If Me.IdPiece.Column(1) = "PN" Then
Set RstDetailFacture = Base.OpenRecordset("SELECT * from detailfacture")
With RstDetailFacture
.AddNew
!IDFacture = Forms!facturation!IDFacture
!DateFacture = Forms!facturation!DateFacture
!IdPiece = 11 ' code de taxes de pneu
.Update
End With

End If

Set RstPiece = Base.OpenRecordset("select * from piece where idpiece = " & Me.IdPiece)

If Not RstPiece.EOF Then
'Me.DescItem = RstServices!DescServiceVente
Me.DescItem = RstPiece!DescPiece
Me.Vendant = RstPiece!PrixVendant
End If


Frm.Form.Requery
Frm.Recordset.FindFirst "IdDetailFacturation=" = RowID

RstPiece.Close
Set Base = Nothing



thanks for your help