MS Access How to select record by using VBA

Question

Hey everyone,

i have some problem, i want to select some information by using vba because i want this logic when the user click on 'Ajouter au panier' the system have to select the product and add it on the list Box of 'Panier' 

this is the interface of the form_dialog to understand me 

Please can you give me the solution what is the code i can use it here ?

Answer 1

Why not just bind a subform on the page of the tab control to the TB_Article table.  You can then navigate to the selected record with the following in the txtListPrdct control's AfterUpdate event procedure:

    Const MESSAGE_TEXT = "No matching record"

    Dim ctrl As Control

    Set ctrl = Me.ActiveControl

    If Not IsNull(ctrl) Then

        With Me.RecordsetClone

            .FindFirst "IDProduit = " & ctrl

            If Not .NoMatch Then

                ' go to record by synchronizing bookmarks

                Me.Bookmark = .Bookmark

            Else

                MsgBox MESSAGE_TEXT, vbInformation, "Warning"

            End If

        End With

    End If

If you format the subform without record selectors, navigation buttons or scroll bars, and a BorderStyle property of None, it will appear exactly the same as your current form.  Set the Enabled property of each bound control to False (No) and their Locked property to True (Yes) to make them read-only.

Answer 2

but my problem is not on table its about the attachment field

when i selected a product in  combo box these problem display on the screen

because he after update other information but he not insert the picture of product because he is an attachment files

this is the code behind combo box

Private Sub txtListPrdct_AfterUpdate()

Dim db As Database

Dim rs As Recordset

Set db = CurrentDb

Set rs = db.OpenRecordset("SELECT * FROM TB_Article WHERE IDProduit=" & Me.txtListPrdct.Value, dbOpenDynaset)

If Not rs.EOF Then rs.MoveFirst

Me.txtNatrPrd = rs("nature_produit")

Me.txtPrxVente = rs("prix_vente")

Photo_produit.CurrentAttachment = rs("Pic_Prod")

Me.txtQtedemande.SetFocus

rs.Close

Set rs = Nothing

db.Close

Set db = Nothing

End Sub

can you give me a solution for this problem ??

Answer 3

I can't comment on how you'd assign an attachment to an unbound control as attachments are not something I would ever use.  The approach I have always adopted is to store the paths to external image files in a column of text data type, and load the image into an Image control at runtime.  With an unbound Image control this merely requires the control's Picture property to be set to the path.

The reason I'm suggesting a bound subform in your case is that it then becomes a simple task to show the data, including the image attachment, in bound controls.  In the code you simply need to navigate to the record in the way I described.

Answer 4

Ok i understand what you mean

but how i an do what you see practically in my case

because i have before a code vba in those unbound variant

Answer 5

Without knowing what your code is I can't be certain, but, apart from the code in the txtListPrdct control's AfterUpdate event procedure, I wouldn't expect any other code to be very different when using a bound rather than unbound form.