Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2004
    Posts
    6

    Exclamation Unanswered: Automatically generate records

    Hi.

    Sorry if this question is a bit naive, but I have just started using VBA and to be honest I am a bit lost.....

    How would I go about programming VBA in order to generate a block of records in a table to correspond to certain time slots every day for a month.

    ie. there are 12 time slots each day and (obviously) 31 days for each month.

    I have been advised to use 'for' loops, nested 'for' loops and to be honest I am none the wiser.

    How would I relate the loop structure to the pre existing time slots in my database?

    Thanks for any help anyone can give

  2. #2
    Join Date
    Feb 2004
    Posts
    142

    Re: Automatically generate records

    Originally posted by Robbie_C
    Hi.

    Sorry if this question is a bit naive, but I have just started using VBA and to be honest I am a bit lost.....

    How would I go about programming VBA in order to generate a block of records in a table to correspond to certain time slots every day for a month.

    ie. there are 12 time slots each day and (obviously) 31 days for each month.

    I have been advised to use 'for' loops, nested 'for' loops and to be honest I am none the wiser.

    How would I relate the loop structure to the pre existing time slots in my database?

    Thanks for any help anyone can give
    First, the loop structure is used to create new records and has nothing to do with current records

    I can give you a generic overview of how this structure should look like.
    The reference to DaysInMonth is a holder for the actual days in the month and you will need to calculate or insert this value.

    Dim Day_Cnt as int
    Dim TimeSlot_Cnt as int

    for Day_Cnt = 1 to DaysInMonth step 1

    for TimeSlot_Cnt = 1 to 12 step 1

    docmd.runsql INSERT into tablename (Comma-delimited field list) Values(Value List)

    next TimeSlot_Cnt

    Next Day_Cnt
    KC

  3. #3
    Join Date
    Mar 2004
    Posts
    6

    Re: Automatically generate records

    Originally posted by AZ KC
    First, the loop structure is used to create new records and has nothing to do with current records

    I can give you a generic overview of how this structure should look like.
    The reference to DaysInMonth is a holder for the actual days in the month and you will need to calculate or insert this value.

    Dim Day_Cnt as int
    Dim TimeSlot_Cnt as int

    for Day_Cnt = 1 to DaysInMonth step 1

    for TimeSlot_Cnt = 1 to 12 step 1

    docmd.runsql INSERT into tablename (Comma-delimited field list) Values(Value List)

    next TimeSlot_Cnt

    Next Day_Cnt
    That's brilliant. thanks so much.

    one further question: Is there any way of referring the nested loop to values in a separate table as in:

    for each [value] in [table]
    [code]
    next bla

  4. #4
    Join Date
    Jan 2004
    Location
    Toronto, Canada
    Posts
    259
    u need to do some ADODB work here. ADO is Advanced Data Objects; its a way of referring to tables, fields and such.

    To do it here's how;

    Dim conn as adodb.connection 'initialize a connection to a database
    Dim record as adodb.recordset 'initialize a record object to refer to table

    set conn = currentproject.connection 'connects to your current db.
    set record = new adodb.recordset ' make new recordset

    record.Open "SELECT * FROM [tablename]", conn, adOpenStatic, adLockOptimistic 'just opening everything in your table

    for i = 1 to record.recordcount 'go through all the values
    'do stuff
    next

  5. #5
    Join Date
    Jan 2004
    Location
    Toronto, Canada
    Posts
    259
    In Tools->References
    make sure you have these checked off
    Microsoft ActiveX Data Objects 2.1 Library

  6. #6
    Join Date
    Mar 2004
    Posts
    6
    Originally posted by Jerrie
    In Tools->References
    make sure you have these checked off
    Microsoft ActiveX Data Objects 2.1 Library
    Jerrie, you are an absolute star. Do you know how much this problem has been bugging me.

    Thanks very much for your help.

    Rob

  7. #7
    Join Date
    Jan 2004
    Location
    Toronto, Canada
    Posts
    259
    np! if you have any questions...feel free to ask

  8. #8
    Join Date
    Dec 2003
    Posts
    268

    Clarification on DAO vs. ADO

    Jerrie,

    Why for this instance wuold you use ADO vs DAO? If this is all just rsiding in Acccess (Which I am assuming seeing as this is a new person) you can do it with some code that is a little easier to understand.

    With the DAO library referenced

    Dim db as database
    dim rs as recordset

    set db = currentdb
    set rs = db.openrecordset(selectd string here)

  9. #9
    Join Date
    Mar 2004
    Posts
    6

    Wink Re: Clarification on DAO vs. ADO

    Originally posted by mjweyland
    Jerrie,

    Why for this instance wuold you use ADO vs DAO? If this is all just rsiding in Acccess (Which I am assuming seeing as this is a new person) you can do it with some code that is a little easier to understand.

    With the DAO library referenced

    Dim db as database
    dim rs as recordset

    set db = currentdb
    set rs = db.openrecordset(selectd string here)
    Thanks for your reply.

    It is just going on an access form/command button that I am going to open then close almost immediately just so I can generate monthly sessions.

    What would the selected string be in the DB.openrecordset() command line?

Posting Permissions

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