Hi John
I already had the requery statement in the On Enter event of the shop field which I believe achieves the same as doing it in the AfterUpdate event of the company field.
The query provides the subset of shops for the company entered - not a specific shop ...... so if company is updated then there is a need to go into the shop field to select a shop from the new subset of shops based on the new company.
I put the requery statement for the shop field in the AfterUpdate event of the form but ......
I am still getting the same effect - it seems to work on the current record (ie the correct shop is displayed in the field) but when i move to another record the shop value disappears from any other record - in other words it is not stored in the main table record.
Properties for the shop field are
Control Source Shop
Row Source SELECT [Shops].Id, Location, [Companys].Name, [Shops].torc, [Shops].nickname
FROM [Shops query] ORDER BY [companys].name, [shops].torc;
Bound Column 1
Shops Query joins company and shops (one to many) and has all shops for the company that matches the criteria forms!mainform!company
Where am i going wrong?
cheers
Phil
________________________________
From: John Viescas <JohnV@msn.com>
To: MS_Access_Professionals@yahoogroups.com
Sent: Thursday, 10 May 2012, 21:37
Subject: RE: [MS_AccessPros] Table not being updated
Phil-
What's the Row Source of the Shop combo, the Bound Column, the Column Count, and the Column Widths? Perhaps you have the wrong column trying to set the value.
When you use a Parameter query that points to another control as the Row Source, you should Requery the control to make sure its Row Source is in sync with the new value. Ditto when you move to a new record – the Current event of the form fires to let you know you're on a new record, and you need another Requery to keep the second combo in sync. You can set this up by selecting the Customer combo box control in Design view and finding the After Update property. Click in the property and choose [Event Procedure] from the drop-down list. Click the Build button (…) next to the property to open the code window with a skeleton AfterUpdate event procedure built for you. Add the Requery to that code, and when you're done it should look something like:
Private Sub cmbCustomer_AfterUpdate()
Me.cmbShop.Requery
End Sub
That's assuming the name of the Customer combo box is cmbCustomer and the name of the Shop combo box is cmbShop.
To do the form's Current event, select the form, and in the Properties window find the After Update property. Follow the same steps as above to write the one line of code you need to Requery the Shop combo when the form moves to a new record.
John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)
-------------------------------------
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Phil Knowles
Sent: Thursday, May 10, 2012 8:53 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Table not being updated
Hi John
The control source is shop (ie the shop field in the main table)
I don't understand what you mean I'm afraid.
I fill in the company field and then tab to the shop field and drop down the options - the options displayed are the correct ones based on the value in the company field - so my question is what do you mean by 'you should requery in the after update event of the company combo'? What purpose does this serve? It seems that the query is running as it should so what is the value of querying? (I understand what you are suggesting |I do but why will this help?)
I don't know what 'the current event of the form' means. But if requerying at this point will solve my problem I will happily do that.
Sorry to be a bit thick on this - but if you could explain further I would appreciate it.
cheers
Phil
________________________________
From: John Viescas <JohnV@msn.com>
To: MS_Access_Professionals@yahoogroups.com
Sent: Thursday, 10 May 2012, 19:28
Subject: RE: [MS_AccessPros] Table not being updated
Phil-
What's the Control Source of the "Shop" combo? I bet it's blank. And you
should Requery the shop combo in the AfterUpdate event of the Company combo and
the Current event of the form to keep everything in sync.
John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)
------------------
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of pdk444444
Sent: Thursday, May 10, 2012 7:25 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Table not being updated
Hi
2 of my tables are called company and shop.
I have a table (I'll call it Main) which has many fields, 2 of which are called
company and shop and these 2 fields lookup from the 2 tables.
I know that you guys hate lookups but this is what I have !!!!
I then have a form based on the main table.
The field 'company' on this form uses the lookup defined in the table to drop
down a list of companies for the user to choose from.
What i then want is for the field 'shop' to drop down a list of shops which is a
subset of all shops based on the entry in field 'company'
To achieve this I have a query which uses forms!main!company as the criteria and
I use this in the rowsource property of the shops field.
When entering a 'main' record all seems fine and the correct shops for the
company entered are displayed in the drop down list and when the user selects a
shop it is displayed in the field.
However, the value for shop is not then stored in the main record and of course
I want it to be.
Can someone please tell me why and whether there is something I can do to
achieve this?
cheers
Phil
I have a form where one of the fields drops down a list which is based on a
query which is dependent on the value entered in another field.
The correct list
[Non-text portions of this message have been removed]
[Non-text portions of this message have been removed]
Tidak ada komentar:
Posting Komentar