Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    lburmz is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2019
    Posts
    8

    Using a variable to designate which constant to use

    I'm hoping this is simply a case of I don't know the correct syntax to use.



    I have a number of defined public constants:
    Code:
    Public Const sample_stage_0                 As String = "Sample Arrived"
    Public Const sample_stage_1                 As String = "Ready for Extraction"
    etc.

    Then I also have a table (current_status_tbl) in which I list the same constants:
    ID sample_stage Number
    1 sample_stage_0 #
    2 sample_stage_1 #

    What I'm trying to do is go through the table one record at a time and use the sample_stage to define which value to look for in a different query.

    The section of code I'm having trouble with is this:

    Code:
    Function run_stats()
    
    Dim current_stage As String
    Dim count_stage As Long
    
    strSql = "SELECT * from current_status_tbl"
    
    Set rst = CurrentDb.OpenRecordset(strSql)
    rst.MoveFirst
    
    
    Do Until rst.EOF
        
        current_stage = rst![sample_stage]
        'Count all official sample stages
        If rst![sample_stage] Like "sample_stage*" Then
            current_stage = (rst![sample_stage])
            StrSql2 = "SELECT * from animals_samples_current_qury where [sample_current_stage]='" & current_stage & "';"
        End If
           Debug.Print StrSql2
    The SQL statement I'm trying to generate should be:
    SELECT * from animals_samples_current_qury where [sample_current_stage]='Sample Arrived';
    Instead all I get is:
    SELECT * from animals_samples_current_qury where [sample_current_stage]='sample_stage_0';

    Is what I'm trying to do possible (surely?), and what am I doing wrong?

    Thank you in advance

  2. #2
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    Assuming your showing the full code, wheres your rs.movenext and loop?

  3. #3
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    If you have the table,then you really don't need the constants.
    but to use the constants:

    ....where [field]='" & kMyConstant & "'"

  4. #4
    lburmz is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2019
    Posts
    8
    Quote Originally Posted by moke123 View Post
    Assuming your showing the full code, wheres your rs.movenext and loop?
    That was just the beginning of my code where the problem is. rst.movenext and loop are way further down.

  5. #5
    lburmz is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2019
    Posts
    8
    Quote Originally Posted by ranman256 View Post
    If you have the table,then you really don't need the constants.
    but to use the constants:

    ....where [field]='" & kMyConstant & "'"
    I want the constants to be hard coded, as they are used in many other modules.

    I think that is exactly what I have, but it doesn't give me the SQL statement I need.

  6. #6
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Modify like this:
    Code:
    Function run_stats()
    Dim current_stage As String
    Dim count_stage As Long
    strSql = "SELECT * from current_status_tbl"
    Set rst = CurrentDb.OpenRecordset(strSql)
    rst.MoveFirst
    
    Do Until rst.EOF
        
        'Count all official sample stages
      Select Case rst!sample_stage
      Case "Sample_Stage_0"
       Currnet_Stage = Sample_Stage_0
      Case "Sample_Stage_1"
       Current_Stage = Sample_Stage_1 
     End Select        
     StrSql2 = "SELECT * from animals_samples_current_qury where [sample_current_stage]='" & current_stage & "';"
        Debug.Print StrSql2

  7. #7
    lburmz is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2019
    Posts
    8
    Quote Originally Posted by davegri View Post
    Modify like this:
    Code:
    Function run_stats()
    Dim current_stage As String
    Dim count_stage As Long
    strSql = "SELECT * from current_status_tbl"
    Set rst = CurrentDb.OpenRecordset(strSql)
    rst.MoveFirst
    
    Do Until rst.EOF
        
        'Count all official sample stages
      Select Case rst!sample_stage
      Case "Sample_Stage_0"
       Currnet_Stage = Sample_Stage_0
      Case "Sample_Stage_1"
       Current_Stage = Sample_Stage_1 
     End Select        
     StrSql2 = "SELECT * from animals_samples_current_qury where [sample_current_stage]='" & current_stage & "';"
        Debug.Print StrSql2
    I was hoping to avoid having to specify "sample_stage_1", "sample_stage_2" etc, as there may be more than 20 such stages.

  8. #8
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    I was hoping to avoid having to specify "sample_stage_1", "sample_stage_2" etc, as there may be more than 20 such stages.
    Then instead of using constants, make a lookup table.
    The VBA would do a DLookup to find the correct string to use as the criteria.

  9. #9
    lburmz is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2019
    Posts
    8
    Quote Originally Posted by davegri View Post
    Then instead of using constants, make a lookup table.
    The VBA would do a DLookup to find the correct string to use as the criteria.
    I can do that, but it would require changing a load of existing code already using the constants.
    So is there really not just a simple syntax problem in:
    StrSql2 = "SELECT * from animals_samples_current_qury where [sample_current_stage]='" & current_stage & "';"

  10. #10
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Maybe this:
    Code:
    Do Until rst.EOF
        
        current_stage = rst![sample_stage]
        'Count all official sample stages
        If rst![sample_stage] Like "sample_stage*" Then
            current_stage = Eval((rst![sample_stage]))
            StrSql2 = "SELECT * from animals_samples_current_qury where [sample_current_stage]='" & current_stage & "';"
        End If
           Debug.Print StrSql2

  11. #11
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Post#10 won't work, so I devised this. It involves a public user defined type that will have universal scope.
    You'll have to set up your 20 stages in fcnSetType, but only once. It doesn't use the public constants at all.
    I wasn't able to test completely as I don't have the tables.

    Put below in a module. The values will be available anywhere in your project
    Code:
    Option Compare Database
    Option Explicit
    
    Public Type usrComparator
        Tmatch As String
        Tvalue As String
    End Type
    
    Public myComp(20) As usrComparator
    
    ' execute below sub ONCE, via autoexec, or when opening the first form at BOJ
    Public Sub subSetType()
        myComp(0).Tmatch = "sample_stage_0"
        myComp(0).Tvalue = "Sample Arrived"
        myComp(1).Tmatch = "sample_stage_1"
        myComp(1).Tvalue = "Ready for Extraction"
        ' and so on for all pairs
    End Sub
    
    Below is the code for the forms
    Code:
    'This will be the code behind any from needing it
    Public Sub FindMatch()
        Dim strSQL As String
        Dim strSQL2 As String
        Dim i As Integer
        Dim rst As DAO.Recordset
        Dim current_stage As String
        strSQL = "SELECT * from current_status_tbl"
        Set rst = CurrentDb.OpenRecordset(strSQL)
        rst.MoveFirst
        
        Do Until rst.EOF
        'Count all official sample stages
        For i = 0 To 19
            If rst![sample_stage] = myComp(i).Tmatch Then
                current_stage = myComp(i).Tvalue
                Exit For
            End If
            Next i
            strSQL2 = "SELECT * from animals_samples_current_qury where [sample_current_stage]='" & current_stage & "';"
            Debug.Print strSQL2
            rst.MoveNext
        Loop
    End Sub
    Last edited by davegri; 11-01-2019 at 04:46 PM. Reason: clarif

  12. #12
    lburmz is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2019
    Posts
    8
    Thank you davegri, that is a much nicer way of doing what I want to do.

  13. #13
    lburmz is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2019
    Posts
    8
    Quote Originally Posted by davegri View Post
    Post#10 won't work, so I devised this. It involves a public user defined type that will have universal scope.
    You'll have to set up your 20 stages in fcnSetType, but only once. It doesn't use the public constants at all.
    I wasn't able to test completely as I don't have the tables.

    Put below in a module. The values will be available anywhere in your project
    Code:
    Option Compare Database
    Option Explicit
    
    Public Type usrComparator
        Tmatch As String
        Tvalue As String
    End Type
    
    Public myComp(20) As usrComparator
    
    ' execute below sub ONCE, via autoexec, or when opening the first form at BOJ
    Public Sub subSetType()
        myComp(0).Tmatch = "sample_stage_0"
        myComp(0).Tvalue = "Sample Arrived"
        myComp(1).Tmatch = "sample_stage_1"
        myComp(1).Tvalue = "Ready for Extraction"
        ' and so on for all pairs
    End Sub
    
    Below is the code for the forms
    Code:
    'This will be the code behind any from needing it
    Public Sub FindMatch()
        Dim strSQL As String
        Dim strSQL2 As String
        Dim i As Integer
        Dim rst As DAO.Recordset
        Dim current_stage As String
        strSQL = "SELECT * from current_status_tbl"
        Set rst = CurrentDb.OpenRecordset(strSQL)
        rst.MoveFirst
        
        Do Until rst.EOF
        'Count all official sample stages
        For i = 0 To 19
            If rst![sample_stage] = myComp(i).Tmatch Then
                current_stage = myComp(i).Tvalue
                Exit For
            End If
            Next i
            strSQL2 = "SELECT * from animals_samples_current_qury where [sample_current_stage]='" & current_stage & "';"
            Debug.Print strSQL2
            rst.MoveNext
        Loop
    End Sub
    So, in a number of other modules I want to use the values assigned to a particular stage. When I had them defined as constants I used
    Code:
        rst.Edit
    rst!current_stage = sample_stage_1
    rst.Update
    Now, since they are no longer constants, this code produces a "Variable not defined" error.

    I need to read up on Public Types and how they work, but in the mean time I would really appreciate some guidance on how to assign the values associated with each stage please?

    Thank you for any assistance!

  14. #14
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    You didn't indicate what determined which constant to use, so I used 1 for the index, matching the constant suffix.
    Code:
    rst.edit rst!current_stage = myComp(1).Tvalue rst.update

  15. #15
    lburmz is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2019
    Posts
    8
    Quote Originally Posted by davegri View Post
    You didn't indicate what determined which constant to use, so I used 1 for the index, matching the constant suffix.
    Code:
    rst.edit rst!current_stage = myComp(1).Tvalue rst.update
    Of course it is that simple
    Thank you very much!

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

Similar Threads

  1. Replies: 3
    Last Post: 11-25-2014, 12:53 PM
  2. Replies: 6
    Last Post: 09-17-2014, 05:12 PM
  3. Replies: 3
    Last Post: 06-10-2014, 03:36 PM
  4. Declaring a Variable Constant (Sort Of)
    By emmahope206 in forum Programming
    Replies: 1
    Last Post: 02-27-2013, 11:15 AM
  5. Replies: 1
    Last Post: 06-09-2010, 04:19 PM

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