Results 1 to 9 of 9
  1. #1
    welshmpf is offline Novice
    Windows XP Access 2007
    Join Date
    Aug 2011
    Posts
    8

    Repeat Expressions

    Hi,
    Can anyone help me, I am running a macro and want to run until a field in a table is no longer null, i.e. I am updating the table and allocating eancodes to any record that is currently Null

    so i runmacro macroname

    repeat expression [form]![form name]![Field name] Is Null

    but when i run access complains the form is not open or does not exist in database , if obviously does.

    if i incluse an open form command in the macro , it just open form and stops ! , if i do the same with a reportt it does the same thing
    i cant get macro to run more than once unless i put in a number to repeat , in this case i use up eancodes unnecessarily

    any ideas

    Option Compare Database


    '------------------------------------------------------------
    ' Generate_BarCodes
    '
    '------------------------------------------------------------
    Function Generate_BarCodes()
    On Error GoTo Generate_BarCodes_Err
    DoCmd.RunMacro "Copy Of Macro3", , "[Forms]![LookatEAN]![EAN_Code] Is Null"

    Generate_BarCodes_Exit:
    Exit Function
    Generate_BarCodes_Err:
    MsgBox Error$
    Resume Generate_BarCodes_Exit
    End Function

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,827
    I wouldn't use a macro myself, particularly for what you're trying to do, but is the form open at the point the RunMacro line runs? It has to be. If when you open it nothing else happens, what does that code look like?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    welshmpf is offline Novice
    Windows XP Access 2007
    Join Date
    Aug 2011
    Posts
    8
    Hi thanks for reply,

    first line of macro just opens a form , macro runs once and updates table
    when expression says IS Null but then form just appears on the screen , i expected it to carry on until all records updated and Null became false

    Which code do you want to see

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,827
    What exactly does the macro do? A VBA recordset or SQL would likely be better alternatives. Unless you're changing the record displayed on the form, the macro would stop as soon as that record is changed.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    welshmpf is offline Novice
    Windows XP Access 2007
    Join Date
    Aug 2011
    Posts
    8
    Hi,

    I am running a macro that runs a number of quiries, the first couple retrieve information and then a query calculates a new barcode using a function, the next query updates the table with the newly calculated barcode. I then want to repeat the query until all records on the table have had a barcode allocated.

    The macro is stopping when record is changed , but if I use the repeat count it does exactly that , i.e. runs how ever many times i specify. I was expecting repeat expression to do the same

    Do you think the repeat expresion is still checking the record i am on and not the next and can i only use repeat expressions with forms or reports ?
    Thanks
    Mike

  6. #6
    welshmpf is offline Novice
    Windows XP Access 2007
    Join Date
    Aug 2011
    Posts
    8
    The first macro uses runmacro to start the following macro, query3 updates the barcode on the table

    Option Compare Database
    '------------------------------------------------------------
    ' Macro3
    '
    '------------------------------------------------------------
    Function Macro3()
    On Error GoTo Macro3_Err
    DoCmd.OpenQuery "Query1_Ean", acViewNormal, acReadOnly
    DoCmd.OpenQuery "Query1 Query Ean", acViewNormal, acReadOnly
    DoCmd.OpenQuery "checkdigit2", acViewNormal, acEdit
    DoCmd.OpenQuery "Query3", acViewNormal, acEdit
    DoCmd.Close acQuery, "Query1_Ean"

    Macro3_Exit:
    Exit Function
    Macro3_Err:
    MsgBox Error$
    Resume Macro3_Exit
    End Function

  7. #7
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,827
    Quote Originally Posted by welshmpf View Post
    Do you think the repeat expresion is still checking the record i am on and not the next
    Like I said, yes, unless you're changing the record with focus via GoToRecord or something like that. I would tend to work "behind the scenes", not on the form. This type of thing:

    Code:
      Dim strSQL  As String
      Dim db      As DAO.Database
      Dim rs      As DAO.Recordset
    
      Set db = CurrentDb()
      
      'this would be an SQL statement that retrieved the records that needed to be worked on
      strSQL = "SELECT * FROM TableName WHERE Whatever"
      Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
    
      Do While Not rs.EOF
        'your process here to modify those records, likely via the Edit method of the recordset
        rs.MoveNext
      Loop
    
      set rs = nothing
      set db = nothing
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  8. #8
    welshmpf is offline Novice
    Windows XP Access 2007
    Join Date
    Aug 2011
    Posts
    8
    Thanks for your help I will try to work without the macro

  9. #9
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,827
    No problem; post back if you get stuck.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. IIF Expression - No Repeat Please
    By turnbuk in forum Queries
    Replies: 2
    Last Post: 08-11-2011, 02:50 PM
  2. Enter repeat identical data
    By wdrspens in forum Forms
    Replies: 1
    Last Post: 03-16-2011, 06:08 AM
  3. Clearing old table for repeat import
    By jasonbarnes in forum Import/Export Data
    Replies: 4
    Last Post: 02-13-2011, 04:00 AM
  4. Problem with Trying to Repeat Records in VBA
    By P5C768 in forum Programming
    Replies: 9
    Last Post: 09-22-2010, 12:09 PM
  5. Conditions / Expressions
    By Mark344 in forum Access
    Replies: 1
    Last Post: 02-19-2010, 08:15 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums