bookmark record not working

[h2mhd]

Hi

When a code is entered in the piece field i want to add a line automatically and requery the table to show the new items and bookmark the record i was working on after the requery.... but it didn't work so far.

if someone can help me it would be nice

heres the code i use


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

 

[RuralGuy]

Starting suggestions:
Disambiguate your libraries...
Dim RstPiece As DAO.Recordset, RstDetailFacture As DAO.Recordset
...your original Dim line would Dim RstPiece as a Variant
---
I do not see where you are bookmarking a record.

(RG for short) aka Allan Bunch MS Access MVP acXP, ac07 ac10 - winXP Pro, Win7 Pro
Please respond to this forum so all may benefit

 

[TheAceMan1]

How are ya h2mhd . . .
[ol][li]Specify the type of objects for your recordsets:

[blue]Dim RstPiece [purple][b]As DAO.Recordset[/b][/purple], RstDetailFacture As [purple][b]DAO.[/b][/purple]Recordset[/blue]

[/li]
[li][blue]Set Frm = Me!DetailFacture[/blue] refers to a control. If the form where the code is running is the active form then [blue]you don't need the form object[/blue] ... just use [purple]Me[/purple][/li][/ol]
Try the following corrected code:

[blue]   Dim Base As Database, RowID As Integer
   Dim RstPiece [purple][b]As DAO.Recordset[/b][/purple], RstDetailFacture As [purple][b]DAO.[/b][/purple]Recordset

   On Error Resume Next
   Set Base = CurrentDb
   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
   
   
   [purple][b]Me[/b][/purple].Form.Requery
   [purple][b]Me[/b][/purple].Recordset.FindFirst "[IdDetailFacturation]" = RowID
   
   RstPiece.Close
   Set Base = Nothing[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[TheAceMan1]

h2mhd . . .

Woops! ...

[blue][purple][b]Me[/b][/purple].Form.Requery
   [green]'should be[/green]
[purple][b]Me[/b][/purple].Requery[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]