Results 1 to 6 of 6

Thread: storing values

  1. #1
    Join Date
    Mar 2004
    Posts
    13

    Unanswered: storing values

    I have some exelfiles with data from different projects.
    These are joined togheter in a query.
    On my forms i have combo-boxes where u should be able to select a start-week and a stop-week that is used in other queries.
    The interval for startweek and stopweek are calulated by selecting min(week) and max(week) from the joined exelfiles.
    The problem is that this takes quite some time.
    How can I store these values so that I don't have to run a query on joined tables every time a user clicks on a combobox?

    I was thinkin of updating a table with the min(week) and max(week) of every project on startup using ADO or DAO, but don't know exactly how to do it. Anyway i'm not sure this is the right approach.

    grateful for any help..

    //pete

  2. #2
    Join Date
    Feb 2004
    Location
    Dorset UK
    Posts
    147
    You can create 'global' variables.

    These will remain until one of two things happen, theres an error or you shut the database.

    I usually create a module and call it globals. You can call it what you like !!

    Then I simply add key words that I'll remember like:

    Global Sqlstr as string
    Global First_Date as Date

    and so on. Just like Dim, but use global instead.

    This means if I have a form open or module running, I can assign a value to Sqlstr and it will remain there even if the form/module is closed. If I open a new form and call this value Sqlstr the value will still be there. Great way f passing strings from Forms to Reports!

    But like I say I have had problems where error 'blank' this value.

    Does that help ??

    Ken

  3. #3
    Join Date
    Feb 2004
    Location
    Dorset UK
    Posts
    147
    ALSO !

    In your code, you could have public Your_Variable as String at the top of your code somewhere, this is almost the same as globals..

    Bit of code I'm working on now !


    Option Compare Database

    Public Firstdate As Date

    Private Sub Command_get_reg_data()
    Dim Chk as integer
    Chk = 0
    For A = 1 To Me.Current_List.ListCount - 1



    See where its placed.

    Ken

  4. #4
    Join Date
    Mar 2004
    Posts
    13
    Originally posted by Ken_Hart
    ALSO !

    In your code, you could have public Your_Variable as String at the top of your code somewhere, this is almost the same as globals..

    Bit of code I'm working on now !


    Option Compare Database

    Public Firstdate As Date

    Private Sub Command_get_reg_data()
    Dim Chk as integer
    Chk = 0
    For A = 1 To Me.Current_List.ListCount - 1



    See where its placed.

    Ken
    Thanks fofr the help Ken, but I don't think this is what I'm looking for. I have about 10 projects, and the list will get longer with time. Creating a startweek and stopweek variable would then require at least 20 queries.
    It would be better if I could just perform one query and then save the data I need in a table or something.

    br Pete

  5. #5
    Join Date
    Feb 2004
    Location
    Dorset UK
    Posts
    147
    Code to create a table, which means it gets overwritten so only 1 value is only ever present..

    Sub Create_Dates()

    Dim S_Date As Date, F_Date As Date

    S_Date = "1/march/2004"
    F_Date = "31/march/2004"

    SqlText = "SELECT #" & Format(S_Date, "dd/mmm/yyyy") & "# AS First_Date, #" & Format(F_Date, "dd/mmm/yyyy") & "# AS Last_Date INTO Tbl_Dates_KH;"

    DoCmd.SetWarnings False

    DoCmd.RunSQL SqlText

    DoCmd.SetWarnings True

    End Sub


    Does that help ?

    Ken

  6. #6
    Join Date
    Mar 2004
    Posts
    13
    Originally posted by Ken_Hart
    Code to create a table, which means it gets overwritten so only 1 value is only ever present..

    Sub Create_Dates()

    Dim S_Date As Date, F_Date As Date

    S_Date = "1/march/2004"
    F_Date = "31/march/2004"

    SqlText = "SELECT #" & Format(S_Date, "dd/mmm/yyyy") & "# AS First_Date, #" & Format(F_Date, "dd/mmm/yyyy") & "# AS Last_Date INTO Tbl_Dates_KH;"

    DoCmd.SetWarnings False

    DoCmd.RunSQL SqlText

    DoCmd.SetWarnings True

    End Sub


    Does that help ?

    Ken
    Hi Ken.
    Thanks a lot for all your help. I didn't know about the "SELECT INTO" statement. I should probably have used it.
    Anyways before reading your last post i solved the problem with this approach:

    Private Sub Form_Load()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strSQL As String

    strSQL = "SELECT Min(WeekConv.F2) AS StartWeek, Max(WeekConv.F2) AS StopWeek, Projects.ID FROM TRT, WeekConv, Projects WHERE TRT.week = WeekConv.F1 And TRT.Level1 = Projects.name GROUP BY Projects.ID;"
    Set dabs = CurrentDb
    Set rs = dabs.openrecordset(strSQL)
    CurrentDb.Execute "DROP TABLE ProjectWeek;"
    CurrentDb.Execute "CREATE TABLE ProjectWeek (ID integer, StartWeek varchar(10), StopWeek varchar(10));"
    rs.MoveFirst
    Do Until rs.EOF
    CurrentDb.Execute "INSERT INTO ProjectWeek (ID, StartWeek, StopWeek) VALUES (" & rs.Fields(2) & ",'" & rs.Fields(0) & "','" & rs.Fields(1) & "');"
    rs.MoveNext
    Loop

    Set rs = Nothing
    Set dabs = Nothing
    End Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •