Results 1 to 6 of 6
  1. #1
    MDBURDEN1954 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Posts
    2

    Question Setting Temporary Varibles Using a combobox and command button

    I have a form (frmFindCategory) with 2 objects. Object 1 is a combo box (cboFoodCategory) and Object 2 is a Command Button (cmdFindRecord). The cboFoodCategory uses a Select Query (SELECT [Categories].[CategoryID], [Categories].[Category] FROM Categories ORDER BY [Category]; to get the values from which a user can choose. The cmdFindRecord should save that choice to a temporary variable CurrentCategory.



    In the event tab for cmdFindRecord I have a Macro built like this:

    Code:
        
    If Not IsNull([cboFoodCategory]) Then
        SetTempVar
              Name CurrentCategory
              Expression =[cboFoodCategory]
        MessageBox
              Message "Temporary Variable CurrentCategory is set to: " & [TempVars]![CurrentCategory] & "."
                    Beep Yes
                    Type Information
                    Title
    End IF
    
    When I select the Value in cboFoodCategory object and then click the Find Button, I get the Message box but instead of getting the selected category I get the message Temporary Variable CurrentCategory is set to: " & [TempVars]![CurrentCategory] & "." in the message box.

    Does that mean the Temporary Variable CurrentCategory was not set? Or do I have something wrong in my coding?

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    I don't use macros nor TempVars. I find both confusing and difficult to debug.

    I can get VBA Immediate Window to return the value of TempVars variable set in embedded macro:

    MsgBox TempVars!Test
    Last edited by June7; 02-23-2019 at 11:06 PM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Try below not so convoluted. The .value for the combobox may or may not be needed. You don't need to use set or add syntax to declare and set a tempvar value, you just do it in-line as needed.
    Code:
    If Not IsNull([cboFoodCategory]) Then
        TempVars!CurrentCategory=me.cbofoodcategory.value
    
        MessageBox "Temporary Variable CurrentCategory is set to: " & [TempVars]![CurrentCategory] & ".",vbInformation,"  T E M P V A R   V E R I F I E D   "
    End IF

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    @Dave, OP is using a macro, not VBA.

    Me. will not work in macro. The .Value is not needed.

    I cannot find any examples of macro Message Box referencing a TempVars. So, supported by our failures, I conclude it cannot recognize variable references.

    If you only want to confirm that the TempVars variable is set and assigned value, VBA MsgBox does that.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    June, Oh, now I see. That's the reason for the strange syntax. I don't get much exposure to macros either.

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Finally figured out all that is needed is an = sign in the MessageBox Message property.

    Message: ="Temporary Variable CurrentCategory is set to: " & [TempVars]![CurrentCategory] & "."
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 10-18-2016, 06:00 AM
  2. Running Macro from Combobox using Command Button
    By MTSPEER in forum Programming
    Replies: 6
    Last Post: 07-09-2015, 06:59 AM
  3. Replies: 3
    Last Post: 09-15-2011, 09:06 AM
  4. Setting Temporary Variable
    By KEVWB in forum Access
    Replies: 1
    Last Post: 02-07-2011, 01:41 PM
  5. Replies: 1
    Last Post: 07-27-2010, 02:27 PM

Tags for this Thread

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