Results 1 to 6 of 6
  1. #1
    PinkLady50 is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2017
    Posts
    171

    Code is adding a blank record before the record with data

    My code is adding a blank record before it writes the record with the data to my Problem_Record tbl.

    What am I doing wrong?



    Code:
    Private Sub cmdAddRecord_Click()
    Dim R As Recordset
    Set R = CurrentDb.OpenRecordset("SELECT * FROM [Problem_Record]")
    R.AddNew
    R![TeamID] = Me.cboManager.Column(0)
    R![Manager] = Me.cboManager.Column(1)
    R![COD] = Me.cboCOD.Value
    R![Fiscal_Year] = Me.cboFiscal_Year.Value
    R![Quarter] = Me.cboQuarter.Value
    R![Date_Opened] = Me.txtDateOpened.Value
    R![Contract_Number] = Me.txtContractNumber.Value
    R![CategoryID] = Me.cboCategory.Column(0)
    R![Category] = Me.cboCategory.Column(1)
    R![ProblemID] = Me.cboProblem.Column(0)
    R![Problem] = Me.cboProblem.Column(1)
    R![StaffID] = Me.cboStaff.Column(0)
    R![Staff] = Me.cboStaff.Column(1)
    R![Description] = Me.txtDescription.Value
    R.Update
    R.Close
    Set R = Nothing
     'Clear fields
        Me.Manager = ""
        Me.COD = ""
        Me.Fiscal_Year = ""
        Me.Quarter = ""
        Me.Date_Opened = ""
        Me.Contract_Number = ""
        Me.Category = ""
        Me.Problem = ""
        Me.Staff = ""
        Me.Description = ""
     If Me.Dirty = True Then Me.Dirty = False
    End Sub

  2. #2
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    What form is the cmdAddRecord_Click on?
    Does the Recordsource for that form happen to be Problem_record or a query based on Problem_Record?
    If so, it probably shouldn't be as your code is updating that table, not by the process of bound fields.
    Also, you don't need to clear the fields. Those fields are in the table and are not available after the R.Close (should give an error the way you are using them).
    And, take out the if me.dirty...

  3. #3
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    I don't see anything in the code that will do that. But if your form has the table as its record source, then why are you using VBA to add the record? Entering data into the form accomplishes the same thing. The blank record is probably coming from the "Clear fields" part of your code; you clear all the fields (i.e. you are changing the current record), and then you save it with me.dirty = False.

  4. #4
    PinkLady50 is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2017
    Posts
    171
    I have a cmdAddRecord button on the form to write the record to the table.
    Record Source for the form is Problem_Record
    So should I take the form Record Source out?

    I deleted the clear fields and got rid of If me.dirty.

    Now it writes the record to the table but does not clear the fields on the form. It also is not writing the extra blank record. Yeah!

    How do I clear the form for another record to be input?

  5. #5
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Put this in where the previous clears were..
    Code:
    Me.cboManager = ""
    Me.cboCOD = ""
    Me.cboFiscal_Year = ""
    Me.cboQuarter = ""
    Me.txtDateOpened = ""
    Me.txtContractNumber = ""
    Me.cboCategory = ""
    Me.cboProblem = ""
    Me.cboStaff = ""
    Me.txtDescription = ""

  6. #6
    PinkLady50 is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2017
    Posts
    171
    Oh!!!!! how stupid of me!!!

    Thanks for the catch!!!

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

Similar Threads

  1. Replies: 1
    Last Post: 03-13-2013, 03:52 PM
  2. Replies: 5
    Last Post: 08-21-2012, 12:30 PM
  3. execute code on subform without adding a record
    By markjkubicki in forum Forms
    Replies: 5
    Last Post: 10-13-2011, 12:23 PM
  4. adding new blank record help needed
    By Mile129 in forum Forms
    Replies: 2
    Last Post: 09-22-2010, 08:49 AM
  5. Add data from previous record if blank
    By mbc321 in forum Queries
    Replies: 1
    Last Post: 08-08-2009, 05:15 PM

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