Hi John
Actually A2003 IIRC J
I still prefer to use RecordsetClone though, for the reason you state – namely that it makes things much easier to handle if the record is non-existent.
Cheers
Graham
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Friday, 12 September 2014 17:45
To: MS_Access_Professionals@yahoogroups.com
Subject: 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
(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: "Graham Mandeno" <graham@mandeno.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (4) |
Tidak ada komentar:
Posting Komentar