ACCESS FEED: Re: [MS_AccessPros] help with form bound to query

Kamis, 11 September 2014

Re: [MS_AccessPros] help with form bound to query

Graham-

As of about A2007, you can manipulate the form's Recordset directly. Use With Me.Recordset, and you don't have to set the Bookmark. The one advantage to using RecordsetClone is you can detect more easily when the record is not found.

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

On Sep 12, 2014, at 2:47 AM, 'Graham Mandeno' graham@mandeno.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

Hello Sarah

First, your combo box must be unbound (i.e. its ControlSource property should be blank). Set its BoundColumn to 1; ColumnCount to 2; ColumnWidths to 0 (this hides the first column; and set the RowSource to a query which returns the ProductID and the ProductName – for example:

SELECT [product id], [product name] FROM [products] ORDER BY [product name];

Let's assume the name of the combo box is cboSelectProduct. Next, set its AfterUpdate property to [Event Procedure] and click in the builder button […]. Enter the following code:

Private Sub cboSelectProduct_AfterUpdate()
With Me.RecordsetClone
If Not IsNull(cboSelectProduct) Then
    .FindFirst "[product id]=" & cboSelectProduct
    If .NoMatch Then
      MsgBox "Can't find that product"
    Else
      Me.Bookmark = .Bookmark
    End If
End If
End With
End Sub

This code simply finds the first record in the form's Recordset with a [product id] matching the selection in the combo box, and navigates to it.

If your user can navigate between records by some other means (e.g. the navigation buttons, page up/down, etc) then you might like to ensure that the combo box always matches the current record. To do this, add the following line to the Form_Current event procedure:

cboSelectProduct = Me.[product id]

All the best!

Graham Mandeno [Access MVP since 1996]

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Friday, 12 September 2014 12:07
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] help with form bound to query

I feel like a total idiot. I thought this would be really easy, but I can not do it!!!

I have a table 'products' with my 'product id', descriptions etc. a lot of fields are in this table. every product has a unique product id.

I want to make a form, where the user can select the product id from a dropdown box and modify some of the fields associated with that product. They can not add or delete products, only modify some of fields.

I made a query to select the product id and some other fields from the products table. The query works fine.

The I made a from that is bound to the query. The form has a header with dropdown box to select the product id. The form has a detail section with some additional fields bound to the query.

The dropdown box works fine, but here is the problem: I thought that once the user selected a product id from the dropdown box, it would automatically populate the fields in the detail section with data from the query. It does not do this. The data in the detail section does not populate correctly. It populates with the data from the first entry in the query and when I select a different product id from the query, the data does not change.

What am I missing???

As always thanks for all your help in advance.

Sarah

__._,_.___

Posted by: John Viescas <johnv@msn.com>

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic