Embedded Macro Disappears from On Dirty Event After Closing and Reopening Form

[Neversickatsea]

The On Enter code is a separate matter - it works fine. I had just copied the entire code for the previous post for completeness sake.
You're absolutely right about Control #1 going from something to blank, and in that case Control #2 goes blank. I had previously achieved that in the then separate After Update macro, but deleted it while trying to figure out why the On Dirty macro wouldn't stay put, and forgot to return it. My bad.
I have no idea what Option Explicit does, so it's hard for me to say something about your suggestion. If it's something I gotta do to make this work, then I'm all for it.


I'll look forward to your news whenever you have the time.
Many thanks for your help!

[Micron]

something not adding up here

Form control #1 contains unique text (selectable options from a drop-down combo box). Blank by default.

If it is blank when the form opens, it's probably not bound - or if it is, its list source (combo rowsource property) just happens to have a null or empty string (zls) which just happens to be the first in the sort order. Are you sure the combo has a *controlsource property? If not, I can't use the OldValue property and will have to do something a bit more complicated.

If you're using the combo to filter records, it is normally not bound to a field (it can still have a rowsource though, which is what populates the drop down list). Binding a search combo means you will change the value stored in the record when you think you're just playing around and filtering records.

* = corrected

EDIT

From something to something in #1, don't do anything in #2.

Just realized that "do nothing" could mean leave a value if it's there. It could also mean nothing should be in #2. So,
if combo value is A and A is selected again, leave #2 as is regardless of whether it contains the date or not, and
if combo value is A and B is chosen, same - leave #2 as is regardless.
Or something else?

There's more to this than first meets the eye.

[Micron]

Will post what I have to allow you to test at your convenience. Your code should look like this

Option Compare Database
Option Explicit
Dim varOldValue As Variant

Private Sub cmbOrderID_AfterUpdate()
varOldValue = Me.cmbOrderID

End Sub

Private Sub cmbOrderID_BeforeUpdate(Cancel As Integer)

'dropped down but no change
If varOldValue = Me.cmbOrderID Then Exit Sub

'going from nothing to something
If IsNull(varOldValue) And Not IsNull(Me.cmbOrderID) Then Me.txtAssigned = Date

'going from something to nothing
If Not IsNull(varOldValue) And IsNull(Me.cmbOrderID) Then Me.txtAssigned = Null

'going from something to something (thus there is a date unless user is allowed to remove it)
If Not IsNull(varOldValue) And Not IsNull(Me.cmbOrderID) And Not IsNull(Me.txtAssigned) Then Exit Sub '??

End Sub

Private Sub Form_Load()
varOldValue = Me.cmbOrderID

End Sub

Your date textbox should be disabled (see property sheet Data tab) or else user removing date may play havoc with the code.

See code editor Tools>Options General tab, 'require variable declaration' to turn on Option Explicit for future code modules. You have to manually add it to existing ones. This forces you to not only declare variables, it prevents most spelling errors when you code. You need to compile your code to catch these errors (Debug menu).

Note, create (begin) your procedures from the property sheet and paste what I have between the Sub/End Sub lines. You need those 3 procedures. If you simply paste in the whole procedure including Sub/End Sub (or Function/End function lines as the case may be), the code may not 'connect' to the event that's supposed to trigger it. Save right after pasting then compile to check for errors.

I preface all my object names with their type, so change cmbOrderID to OrderID if the latter is the name of your code. Good habit to get into. When you are 100 lines down in code you don't have to look back at the beginning to see what type the variable or control is. varOldValue tells me that the variable is a Variant. It also helps to see that txt is a textbox so you don't forget and try to use methods or properties that don't apply.

[Neversickatsea]

Are you sure the combo has a *controlsource property?

Yes. Both controls are bound. The fields in the *underlying table are not set to Required because when the records are first entered these fields normally stay blank *(Null). But are filled in later on. To clarify, the table is a list of railcars (more specifically, railcar trips) that I'm monitoring as part of a product supply process. The railcars are first added to the table by one person, then are assigned an order number (containing specifics of the product that the railcar will subsequently carry) by another.

I have renamed both controls in my form to include the 3-letter prefix as per your recommendation, then copied your code and successfully compiled it (no notifications popped up after the compilation and the compilation button was then greyed out). However, it did not perform the required task upon testing. Changing the OrderID from blank to something did not prompt the Assigned control to change. Neither did any other types of actions with the OrderID control (from something to blank with date filled in; from something to blank with date null; from something to something with date null; etc.)

No error messages or anything like that. Just as if it wasn't there.

Here's what the complete control code looks like now:

Option Compare Database
Option Explicit
Dim varOldValue As Variant


Private Sub cmbOrderID_AfterUpdate()
varOldValue = Me.cmbOrderID


End Sub


Private Sub cmbOrderID_BeforeUpdate(Cancel As Integer)


'dropped down but no change
If varOldValue = Me.cmbOrderID Then Exit Sub


'going from nothing to something
If IsNull(varOldValue) And Not IsNull(Me.cmbOrderID) Then Me.txtAssigned = Date


'going from something to nothing
If Not IsNull(varOldValue) And IsNull(Me.cmbOrderID) Then Me.txtAssigned = Null


'going from something to something (thus there is a date unless user is allowed to remove it)
If Not IsNull(varOldValue) And Not IsNull(Me.cmbOrderID) And Not IsNull(Me.txtAssigned) Then Exit Sub '??


End Sub


Private Sub Form_Load()
varOldValue = Me.cmbOrderID


End Sub


Private Sub OrderID_Enter()
   Forms![Supply Order Control]![OrderID].Requery
End Sub

Any idea why the code seemingly draws a blank? *Have I copied the code incorrectly?

P.S.

...if combo value is A and A is selected again, leave #2 as is regardless of whether it contains the date or not, and if combo value is A and B is chosen, same - leave #2 as is regardless.

Precisely.

P.P.S.
I have switched on the 'require variable declaration' option as per your suggestion, too.

[Micron]

You created these events from the property sheet and copied/pasted what's between the Sub/End Sub lines as directed? If not, that could cause it. Fully tested by me before posting and code runs, so it has to be something you did/have. That requery is on your date control that we're trying to set the date in? Comment that out and re-test as it's probably being called by the programmatic change of the textbox contents (when the date is inserted). FWIW I don't see a need for it here. Can you explain the reasoning?

[Neversickatsea]

Eureka!
The issue seems to have been that I am using a split form, and I was trying to change the OrderID from the datasheet view. When making the change from the form part, it works as it should. Thank you very much!
By the way, the reason for the requery is that I need the OrderID to show me a list of available options filtered to match another field in the same form. Dependent combo box, I think is the name. For example, if the railcars are going to station A (which is another field on the same form), the available list of options for OrderID must show only orders 1, 2 and 3. If they are going to station B - only orders 4, 5 and 6. It works with the requery, and it doesn't work without it.

[Neversickatsea]

Spoke too soon, I'm afraid.
The code works, but only under a certain condition...
If I open the form, go to a record that has a blank OrderID and select a value for that form control, nothing happens.
However, if I save that value, then leave the record, then come back to the record, then REMOVE that value, then leave from the record again, then come back to it again, and then AGAIN select a value - the code works.
It's as if it doesn't record or recognize the 'original' blank value when the form is just opened, and only recognizes it as such when you've intentionally made it blank in the form after opening it.
Any idea why that might be?

[Micron]

I think you will have to copy, compact/repair and zip your db copy and post it in order to get a solution. You could remove whatever is not required to replicate the issue and/or randomize any data

[Neversickatsea]

Dear Micron,
I don't think there's anything exceptionally sensitive in the database, my only question is... do you happen to read Russian? Some of the label names in queries and forms are in that language, among some other things. Might not be a problem, but if you'd rather I make a translation in a copy of the DB, I can do that.

[Micron]

Sorry, I don't. Not sure what the effect would be when my Windows regional settings are that far off. Maybe someone who regularly helps out here will chime in and say they they will be able to read it. Or

It's as if it doesn't record or recognize the 'original' blank value when the form is just opened, and only recognizes it as such when you've intentionally made it blank in the form after opening it.

perhaps records contain zls (empty strings) which could cause that behaviour. First try a new query with a couple of fields and only put criteria on that field. Use a known value. If that returns records, change the criteria to "" (2 double quote marks, no spaces). If you get any records, the field contains zls in the records so those records are not Null, which is what the code is testing for. The actions you describe would change a zls field to null, which would explain why the code would then work.

Or you could create an English version if you wish. But could that 'undo' whatever is causing your problem?

[ssanfu]

I would be interested in seeing the dB (in Russian)...
(Я немного понимаю русский)


You did change the control names in the code or the control names on the form?

From Post #12

<snip>
Form control #1 contains unique text (selectable options from a drop-down combo box). Blank by default.
Form control #2 contains dates. Blank by default.

and
Post #14

Both controls are bound to fields in a table. It is the same table.
Control #1 is [OrderID]
Control #2 is [Assigned]

The code uses
"cmbOrderID" and
"txtAssigned".

[Neversickatsea]

Dear Gentlemen,
Thank you all for your immeasurable help, but I've managed to crack the issue using a macro, which I hadn't known how to do before.
This is what I've used:
- On Enter Event in the combo box where you select the OrderID: SetTempVar (Varname, [cmbOrderID])
- After Update Event in the same combo box: If [TempVars]![Varname] is Null and cmbOrderID is not Null, SetValue of txtAssigned to Date()
Etc. (Else IFs)
As I understand it's the same principle as the VBA code Mr. Micron had kindly shared earlier, but due to my lack of knowledge I didn't know how to edit/change it to make it work, so I attempted the macro approach, which worked.
Kind regards,
Neversickatsea