Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Warrington, Cheshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Dynamic generation of numbers (2003)

    In the lottery database being produced, the lottery numbers are currently stored in a table. This seems inefficient. I want to generate these numbers dynamically; I've done this using a VBA function but can't get the array back into a query which I need because the query needs put on a criterion that it selects only those lottery numbers which are not in another set (the query to produce this set is already written and working)
    Is it possible to dynamically generate these numbers in a way which makes it possible to be the output of a query?
    Thanks
    Silverback
    Silverback

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Dynamic generation of numbers (2003)

    A query retrieves data from a table (or tables), so you'll need to write the numbers you generate to a table, even if only temporarily. You can always delete the records or even the entire table when you don't need them any more.

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Warrington, Cheshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dynamic generation of numbers (2003)

    Thanks, Hans. But it's the writing back bit I can't get going. Having created the array in VBA ( or should I be using some other method?), how do I get the results into records in a table, please.
    Also, I can see how to delete a table (DoCMD.DeleteObject) but how can I create a table, please.
    Thanks
    Silverback
    Silverback

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Dynamic generation of numbers (2003)

    In general, I'd prefer deleting the records from a table above deleting and creating the table.

    I don't know how you generate the numbers. so I don't know what would be the most efficient way to store them in a table. But you could do something like this. It requires a reference to the Microsoft DAO 3.6 Object Library.
    Assumptions:
    You have a one-dimensional array lngNumbers.
    You have a table tblData with a field LotteryNumber.
    <code>
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim strSQL As String
    dim i As Long

    Set dbs = CurrentDb
    ' Delete existing records from tblData
    strSQL = "DELETE * FROM tblData"
    dbs.Execute strSQL, dbFailOnError
    ' Open recordset
    Set rst = dbs.OpenRecordset("tblData", dbOpenDynaset)
    ' Add new records
    For i = LBound(lngNumbers) To UBound(lngNumbers)
    rst.AddNew
    rst!LotteryNumber = lngNumbers(i)
    rst.Update
    Next i
    rst.Close
    Set rst = Nothing
    Set dbs = Nothing
    </code>
    If you really want to create a new table, you could use the CreateTableDef method of the Database object, but you'd have to create the field(s) too, and the index(es). Much easier to create the table interactively and reuse it.

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Warrington, Cheshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dynamic generation of numbers (2003)

    Hans
    As usual, a very elegant solution.
    Thanks again for you help,
    Silverback
    Silverback

Posting Permissions

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