do you wnat to save changes when updating rowsource

[wjlucas]

I have a form called LOGIN which has two combo boxes named FIELD1 and FIELD3, the values in FIELD3 are dependent on the selections made by the user in FIELD1. Everything works wonderfully well but when I select another form after updating FIELD1 it asks me if I want to save the form changes made to LOGIN. I have commented out all the code and found that setting the RowSource value in FIELD3 (Me.Field3.RowSource = SSOURCE) is what is causing the problem. Is there any way to have the form stop asking me to save it. Apparently it sees the change in RowSource and decides I just changed the form. Here is the FIELD1 "After Update" code.



Private Sub Field1_AfterUpdate()
Dim SSource As String
SSource = "SELECT DISTINCT [town name] FROM [nys swis codes] " & _
" WHERE countytowncode = '" & Me.Field1.Column(0) & "'"
Me.Field3.RowSource = SSource
Me.Field3 = Field3.ItemData(0)
DoCmd.Requery "Field3"

For the FIELD3 property sheet the default value sets to [Field3].[itemdata](0) and the RowSource resets to the correct query with the correct local predicate. If I select '5550' value in the for FIELD1 drop down list the RowSource in FIELD3 properties (on teh Data Tab) shows up as SELECT DISTINCT [town name] FROM [nys swis codes] WHERE countytowncode = '5550'; as it should, the value changes however when that happens ACCESS perceives that I have changed the form. Again it works well, I just don't want to be asked to save the form.

Thanks so much.

[RuralGuy]

It sounds like you are trying to close the Login form. Are you, and if so, what code are you using?

[pkstormy]

What is your code to open the LOGIN form? (ie. Are you opening the form in edit/design view versus just opening the form like: Docmd.openform "LOGIN"?)

The Afterupdate code you have for Field1 shouldn't prompt to save (I change the rowsource all the time for a combobox without ever getting a save prompt) so I'm wondering if something else isn't causing the prompt (such as again, your code is opening the LOGIN form in design view versus normal view)

(also note that there is a property for the form called: 'Allow Design Changes' which can be set to 'All Views' or 'Design View Only'. Maybe changing this to 'Design View Only' might help.)

Otherwise (although unlikely) you could try this:
Dim SSource As String
SSource = "SELECT DISTINCT [town name] FROM [nys swis codes] " & _
" WHERE countytowncode = '" & Me.Field1.Column(0) & "'"
Me.Field3.RowSource = SSource
Me.Field3.value = me.Field3.ItemData(0)
DoCmd.Requery me!Field3 (you really shouldn't need this command)

Also, you currently have the where clause for this:
SSource = "SELECT DISTINCT [town name] FROM [nys swis codes] " & _
" WHERE countytowncode = '" & Me.Field1.Column(0) & "'"

set to match against a text value. If Countytowncode is an integer type, I wonder if this would help (although it really shouldn't matter)...

SSource = "SELECT DISTINCT [town name] FROM [nys swis codes] " & _
" WHERE countytowncode = " & Me.Field1.Column(0) & "" (excluding the ' in the "'")

or you could also try hard-coding like below just to see if this would work (just to test)...

Me.Field3.RowSource = "SELECT DISTINCT [town name] FROM [nys swis codes] " & _
" WHERE countytowncode = '5550'"

Another thing I might try (out of curiosity) is to create a compiled mde from this mdb. An mde doesn't let you edit any forms and I wonder if trying your code on an mde would work without prompting to save or if it would give you an error (which would probably be something like 'you can't edit the LOGIN form'.)

Another thing (which again, probably won't make a difference), is you could try to reference the form itself versus using the me.
Ex:
SSource = "SELECT DISTINCT [town name] FROM [nys swis codes] " & _
" WHERE countytowncode = '" & Forms!LOGIN!Field1.Column(0) & "'"
Forms!LOGIN!Field3.RowSource = SSource
Forms!LOGIN!Field3 = Forms!LOGIN!Field3.ItemData(0)
DoCmd.Requery Forms!LOGIN!Field3 (again, perhaps try your code without this command)

The only last thing I can think of (which is unlikely the problem) is changing the form name from LOGIN to frmLOGIN just to see if perhaps something with the LOGIN name by itself might be an issue (although LOGIN isn't an MSAccess keyword that I know of). I'm also assuming that you've Debug/Compiled the code before running.

If all else fails, I might try re-creating the LOGIN form from scratch just to see if perhaps one of the properties of the form might have been changed which is causing the prompt when you run your code.

[wjlucas]

The SQL is fine. The values are strings and not numbers as some start with '0' and that is needed. Form LOGIN is started from a Navigation form in Access 10. So it runs as a subform. I am in FORM VIEW and running the application when this happens. There is no OnClick VBA code for FIELD1, only AFTER UPDATE code for FIELD1.

Question: If in VBA, I set a attribute field for FIELD3 that MS ACCESS has properties entries for, in this case ROWSOURCE, should that trigger MS ACCESS into thinking the form design has changed? It seems to in my app. If I comment out the line of code Me.Field3.RowSource = SSource, (although it does not work properly, :-)) there is no msg asking me if I want to save design changes to the form. Whether or not I change my selected value for FIELD1, everytime I select something, even if it is the same value I can't go to another subform without it asking me the same question. The specific msg I get when I go to another subform is "Do you want to save design changes to the form Login?"


Again this is the code.
Private Sub Field1_AfterUpdate()
Dim SSource As String
SSource = "SELECT DISTINCT [town name] FROM [nys swis codes] " & _
" WHERE countytowncode = '" & Me.Field1.Column(0) & "'"
Me.Field3.RowSource = SSource
Me.Field3 = Field3.ItemData(0)
DoCmd.Requery "Field3"
End Sub

[wjlucas]

'Allow Design Changes' which can be set to 'All Views' or 'Design View Only'.

Sorry, I didn't comment on this in your reply. I looked for it after reviewing another post and could not find it anyplace in the Properties. Where is it?

[wjlucas]

I compressed this yesterday and it continued with the same behavior, however, I just compressed the database again and went out of the application and back in and it is not doing it. Whatever it is, it is not consistent.

[RuralGuy]

I would try *importing* everything into a fresh, new db and see if the problem follows. http://www.btabdevelopment.com/ts/impnew

[pkstormy]

'Allow Design Changes' which can be set to 'All Views' or 'Design View Only'.

Sorry, I didn't comment on this in your reply. I looked for it after reviewing another post and could not find it anyplace in the Properties. Where is it?

In design view of the form, select/view the Form's Properties (ie. the property sheet which has all the tabs to change settings). Near the bottom is the 'Allow Design Changes' property.

[pkstormy]

I compressed this yesterday and it continued with the same behavior, however, I just compressed the database again and went out of the application and back in and it is not doing it. Whatever it is, it is not consistent.

I wonder what would happen if you added a button to the form and for the OnClick event of the button, you put in the code to JUST set the rowsource of Field3.

Can you zip and upload the mdb? I'd be curious on seeing what is actually happening. Maybe I can see something overlooked.

[wjlucas]

I don't wanna look stupid, but there is no "Allow Design Changes" property on my Access 2010 for a the Login Form. I read that in another forum earlier and looked for it. I just did again and can't find it.

The database file is 8.8MB zipped. I am uploading it but just got a security token problem with the uplaod. I think it only allows 2MB for zip files.

It is still working ok.... so maybe I just cross my fingers.

I appreciate everyone giving me ideas and support.

[pkstormy]

The SQL is fine. The values are strings and not numbers as some start with '0' and that is needed. Form LOGIN is started from a Navigation form in Access 10. So it runs as a subform. I am in FORM VIEW and running the application when this happens. There is no OnClick VBA code for FIELD1, only AFTER UPDATE code for FIELD1.

Question: If in VBA, I set a attribute field for FIELD3 that MS ACCESS has properties entries for, in this case ROWSOURCE, should that trigger MS ACCESS into thinking the form design has changed? It seems to in my app. If I comment out the line of code Me.Field3.RowSource = SSource, (although it does not work properly, :-)) there is no msg asking me if I want to save design changes to the form. Whether or not I change my selected value for FIELD1, everytime I select something, even if it is the same value I can't go to another subform without it asking me the same question. The specific msg I get when I go to another subform is "Do you want to save design changes to the form Login?"


Again this is the code.
Private Sub Field1_AfterUpdate()
Dim SSource As String
SSource = "SELECT DISTINCT [town name] FROM [nys swis codes] " & _
" WHERE countytowncode = '" & Me.Field1.Column(0) & "'"
Me.Field3.RowSource = SSource
Me.Field3 = Field3.ItemData(0)
DoCmd.Requery "Field3"
End Sub

Changing the rowsource for a combobox or listbox on a form or subform should not instigate a prompt to save changes (again, I do this all the time). I'm assuming there's no other events such as the LostFocus event on Field3 which could also be firing any other code. My guess is that there's something simple that's being overlooked. Did you try re-designing the form from scratch? Perhaps starting with a new fresh form might work. I'm thinking some property of the form got changed which is causing the prompt.

I'm not sure about 2010 but the Allow Design Changes property should be on the 'Form's' properties (near the bottom). I don't think 2010 did away with this property but I can't say for sure since I don't actively use it. Does hitting F1 for help show anything on this for 2010? 2010 still has a few kinks to work out in my opinion (my initial testing of it showed a few problems.) I don't have it installed on my home computer so I can't help much on specific 2010 problems or where the Allow Design Changes property might be for the form.

Still, I think re-designing the form from scratch might be best or as Allan recommended, even copying the objects to a new mdb.

[RuralGuy]

Paul,
The "Allow Design Changes" used to be at the bottom of the "Other" tab of the Form's Property sheet in acXP. I do not find it in ac07 or ac10.

[wjlucas]

Thanks, I appreciate all the help. I will rebuild the FORM from scratch as suggested.

[pkstormy]

Paul,
The "Allow Design Changes" used to be at the bottom of the "Other" tab of the Form's Property sheet in acXP. I do not find it in ac07 or ac10.

Thanks Allan!

I wonder why MS in their infinite wisdom decided to remove this? I'm guessing it's because 2007/2010 seems to default to show 'everything' (ie. the left-side ribbon) and just want to make it more miserable for developers to try and hide things users should not be seeing. I hate it that I had to go through all my 2003 projects and add code to hide the stupid ribbon for the 2007 users. We can't even go to 2010 because the Calendar control in previous versions won't upsize nicely (since they did away with the Calendar control completely in 2010). #@$% MS. (just me bitchin).

[RuralGuy]

One needs to vent once in a while or you can explode!