Jumat, 11 Mei 2012

Re: [MS_AccessPros] Table not being updated

 

Hi John
 
OK I undertand what you say.
 
Thanks for all your help on this one
 
cheers
 
Phil
 
 
 

________________________________
From: John Viescas <JohnV@msn.com>
To: MS_Access_Professionals@yahoogroups.com
Sent: Friday, 11 May 2012, 14:30
Subject: RE: [MS_AccessPros] Table not being updated

 
Phil-

There's only one copy of the combo box in the form even though you can see one on each row. When you move to a new row, the filter gets applied for the current company, so all other rows that don't match that company will show a blank in Shop.

One way to avoid it is to make sure the Shop Name is in the record source. Add a text box bound to Shop Name and overlay it on top of the "box" portion of your combo box. In the Enter event of the text box, move the focus to the combo box behind so the user is typing in the combo, not the text box. You don't want the user changing the shop name, just the setting in the combo box.

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: Friday, May 11, 2012 3:01 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Table not being updated

Hi John

I think I am getting there!!!

Everything seems fine now in form view and when I look in the table I can see that the ids for the appropriate shops are being stored. All good news.

However, strange happenings in datasheet view !

there are thousands of records in this table with many occurances of different companies and for each of those companies many different shops

when I click on a record in datasheet view, I can see all the correct companies on every record but I can only see the shops for those records which have the same company as the one I am currently on - all the other records (for companies other than the one I am currently on) show a blank in the shops field.

If I click on a different record with a different company all the shops that were showing go blank and all the shops for the records that have the same company as the record I have just clicked on appear.

Can you explain to me what is happening? and whether there is a way of diplaying all the correct shops in all of the records all of the time regardless of which record I happen to have clicked on

cheers

Phil

________________________________
From: John Viescas <JohnV@msn.com>
To: MS_Access_Professionals@yahoogroups.com
Sent: Friday, 11 May 2012, 11:40
Subject: RE: [MS_AccessPros] Table not being updated

Phil-

You need a Requery in the Current event of the form so that the Shop control updates when you move to a new company. You don't need the Requery in the On Enter event – it should only change when you change the value in Company.

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: Friday, May 11, 2012 11:21 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Table not being updated

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]

[Non-text portions of this message have been removed]

[Non-text portions of this message have been removed]

__._,_.___
Recent Activity:
.

__,_._,___

Tidak ada komentar:

Posting Komentar