Page 2 of 2 FirstFirst 12
Results 16 to 27 of 27
  1. #16
    Join Date
    Oct 2021
    Posts
    13
    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!

  2. #17
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    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.
    Last edited by Micron; 10-21-2021 at 03:58 PM. Reason: correction
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #18
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Will post what I have to allow you to test at your convenience. Your code should look like this
    Code:
    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.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #19
    Join Date
    Oct 2021
    Posts
    13
    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:

    Code:
    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.
    Last edited by Neversickatsea; 10-22-2021 at 03:38 AM. Reason: *Correction; PPS

  5. #20
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    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?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #21
    Join Date
    Oct 2021
    Posts
    13
    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.

  7. #22
    Join Date
    Oct 2021
    Posts
    13
    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?

  8. #23
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    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

    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  9. #24
    Join Date
    Oct 2021
    Posts
    13
    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.

  10. #25
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    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?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  11. #26
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    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
    Quote Originally Posted by Neversickatsea View Post
    <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
    Quote Originally Posted by Neversickatsea View Post
    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".

  12. #27
    Join Date
    Oct 2021
    Posts
    13
    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

Page 2 of 2 FirstFirst 12
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 03-03-2017, 08:17 AM
  2. Replies: 7
    Last Post: 08-07-2014, 06:07 PM
  3. Dirty event not activating on Access 2007 form
    By newbieX in forum Programming
    Replies: 4
    Last Post: 06-17-2014, 12:57 PM
  4. Embedded macro on form
    By trout in forum Macros
    Replies: 7
    Last Post: 07-09-2013, 01:13 PM
  5. Replies: 3
    Last Post: 06-02-2011, 07:40 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums