Results 1 to 3 of 3
  1. #1
    mrmims is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Jul 2013
    Posts
    53

    AddNew Function, but with Criteria?

    Hello,

    I have a module which works great, but now I need change a bit of the functioning and I have no idea how to proceed.

    Using AddNew, it adds records to a table (tbl_Prod_Log_Assoc) based on the number of records in another table (Table 2) using the ID field. The Table 2 has only 7 records with IDs 1 - 7, the AddNew function creates 7 new records in Table 1.



    Code:
    
    Function CreatePyroLog() 
    
    DoCmd.RunCommand acCmdSaveRecord 
    
    Dim DBSPyro As DAO.Database 
    Dim RDSProdAssoc As DAO.Recordset 
    Dim VAR_ShowLog As Integer 
    Dim VAR_Product_Total As Integer 
    Dim VAR_ProductID As Integer 
    
    Set DBSPyro = CurrentDb 
    Set RDSProdAssoc = DBSPyro.OpenRecordset("tbl_Prod_Log_Assoc") 
    VAR_ShowLog = Forms!frm_Show_Log.Show_Log_ID 
    VAR_Product_Total = DCount("*", "Product") 
    VAR_ProductID = 1         
    
    Do Until (VAR_ProductID = (VAR_Product_Total + 1))             
    
    RDSProdAssoc.AddNew             
    RDSProdAssoc!FK_Product_ID = VAR_ProductID             
    RDSProdAssoc!FK_Show_Log = VAR_ShowLog                        
    RDSProdAssoc.Update             
    VAR_ProductID = VAR_ProductID + 1         
    
    Loop Forms!frm_Show_Log.Refresh 
    
    
    End Function
    
    But now I need to create an Active Yes/No field in Table 1, and then run AddNew based on the criteria that the record is Active. Does this make sense?

    Thank you for any help and nudge in the right direciton.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    why do you have all this code, when a single update query will do?

  3. #3
    mrmims is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Jul 2013
    Posts
    53
    Hmmmmm ok ... How would that work? I will play right now.
    thank you for the suggestion

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

Similar Threads

  1. Recordset readonly on rs.addnew
    By Ruegen in forum Programming
    Replies: 5
    Last Post: 02-04-2015, 08:29 PM
  2. Error on AddNew to an empty recordset
    By kowalski in forum Programming
    Replies: 7
    Last Post: 05-22-2013, 06:17 PM
  3. Replies: 1
    Last Post: 11-13-2012, 07:25 PM
  4. Using Recordset rst.AddNew
    By dandoescode in forum Programming
    Replies: 1
    Last Post: 06-06-2012, 10:10 AM
  5. How to code in AddNew Button In Access DBA
    By ganeshvenkatram in forum Access
    Replies: 0
    Last Post: 07-07-2011, 02:50 AM

Tags for this Thread

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