Duplicated Data Issue

**justair07** · 05-07-2014, 12:30 PM

Hello everyone,

Attached is a sample of my project to help you see the issue yourself instead of me trying to explain. It's kind of confusing.

Once you open the db. open up the frmQuoteLog and the frmPDMontior. You will notice one record is displayed in the frmPDMonitor (Justin Ryan). This is correct.

Now to test out the issue leave open the frmPDMonitor and frmQuoteLog at the same time. Go to frmQuotLog, go to the first record, make the "Sales Coordinator" = "Chris April", then check the box that says "Display on Product Design Monitor", then press the "Save Changes" button.

Then look at the frmPDMonitor and you will notice the new record is now displayed on the form. This is correct as well.

Leaving open both forms, go to frmQuoteLog and uncheck the box you previous checked on the first record, then press "Save Changes".

Then look again at the frmPDMontior and notice that the previous record is gone but the remaining record is now duplicated.

Only when I close the frmPDMonitor and reopen it, does the duplication go away.

How can I avoid this duplication without closing and reopening the frmPDMonitor? The purpose of this form is to be ran 24/7.

Sorry for the long explanation but I wanted to make sure I explained everything for the best possible solution.

Completing this project is a top priority for me and could really use some the help.

As always thank you,

-Justin

**burrina** · 05-07-2014, 01:13 PM

Requery your data.

Me.Requery

HTH

**justair07** · 05-07-2014, 03:23 PM

Originally Posted by burrina

Requery your data.

HTH

Hi Dave,

Thank you for the reply. How do I requry the data. Sorry I'm still new to Access

**June7** · 05-07-2014, 03:35 PM

Well, Requery won't work because frmPDMonitor has unbound controls. Your code is populating unbound controls, not displaying records. Would need code to set controls back to null.

The form has 20 sets of unbound controls. This means limited to 20 sets of data.

Why is this not just one set of controls bound to fields?

**justair07** · 05-07-2014, 04:44 PM

Originally Posted by June7

Well, Requery won't work because frmPDMonitor has unbound controls. Your code is populating unbound controls, not displaying records. Would need code to set controls back to null.

The form has 20 sets of unbound controls. This means limited to 20 sets of data.

Why is this not just one set of controls bound to fields?

Hello June7,

Using one set of bound controls and requery on a Timer set at 1000 causes the form to flicker and is extremely distracting. The method I used was a work around to avoid the flickering.

How could I reset the controls back to null? I think I understand you correct. Are you saying that I need the code to reset the controls to Null and then reestablish what data needs to be repopulated?

Sorry I'm still learning.

**June7** · 05-07-2014, 04:48 PM

Code populates the controls so code must de-populate them. Yes, if you de-populate all then must run the code again to re-populate with revised data.

**justair07** · 05-07-2014, 04:53 PM

Originally Posted by June7

Well, Requery won't work because frmPDMonitor has unbound controls. Your code is populating unbound controls, not displaying records. Would need code to set controls back to null.

The form has 20 sets of unbound controls. This means limited to 20 sets of data.

Why is this not just one set of controls bound to fields?

Thanks again June 7,

Here is the code I used to populate the controls:

Code:

Public Sub LoadMyForm(frm As Access.Form)
On Error GoTo Error_Handler
'    Dim db              As DAO.Database
    Dim rs              As DAO.Recordset
    Dim x               As Integer

'    Set db = CurrentDb()
    Set rs = db.OpenRecordset("qryPDMonitor", dbOpenSnapshot)

    For x = 1 To 16
        If rs.EOF Then Exit For
        With frm
        frm.Controls("qn" & x) = rs!QuoteLogNumber
        frm.Controls("cust" & x) = rs!Customer
        frm.Controls("sales" & x) = rs![Sales COOrd]
        frm.Controls("prod" & x) = rs!ProductDesignInitials
        frm.Controls("submit" & x) = rs!dtmTimeSubmitted
        frm.Controls("total" & x) = rs!Expr1
        frm.Controls("c" & x) = rs!PartsCompleted
        frm.Controls("e" & x) = rs!ynExpedite
        End With
        rs.MoveNext
    Next

Error_Handler_Exit:
    On Error Resume Next
    rs.Close
    Set rs = Nothing
'    Set db = Nothing
    Exit Sub

Error_Handler:
    MsgBox "The following error has occured." & vbCrLf & vbCrLf & _
            "Error Number: " & Err.Number & vbCrLf & _
            "Error Source: LoadMyForm" & vbCrLf & _
            "Error Description: " & Err.Description, _
            vbCritical, "An Error has Occured!"
    Resume Error_Handler_Exit
End Sub

This code is called from a Timer Event on my forms Load Event.

Like I said I'm still learning. Could you take a look at the code and suggest a solution that de-populates the controls?

**June7** · 05-07-2014, 04:59 PM

It would be the same loop structure only instead of referencing recordset to pull values, just = Null.

**justair07** · 05-07-2014, 05:16 PM

Originally Posted by June7

It would be the same loop structure only instead of referencing recordset to pull values, just = Null.

Okay but if I use this in the same module that is called by a timer event triggered every 1 second. Wont that cause the screen to flicker blank every second?

**June7** · 05-07-2014, 05:19 PM

Yes, but maybe so fast hardly noticeable. Maybe not the 'jumpy' flicker caused by requery/refresh.

**justair07** · 05-07-2014, 05:22 PM

Originally Posted by June7

Yes, but maybe so fast hardly noticeable. Maybe not the 'jumpy' flicker caused by requery/refresh.

Here's what I put:

Code:

Public Sub LoadMyForm(frm As Access.Form)
On Error GoTo Error_Handler
'    Dim db              As DAO.Database
    Dim rs              As DAO.Recordset
    Dim x               As Integer

'    Set db = CurrentDb()
    Set rs = db.OpenRecordset("qryPDMonitor", dbOpenSnapshot)

    For x = 1 To 16
        If rs.EOF Then Exit For
        With frm
        frm.Controls("qn" & x) = Null
        frm.Controls("cust" & x) = Null
        frm.Controls("sales" & x) = Null
        frm.Controls("prod" & x) = Null
        frm.Controls("submit" & x) = Null
        frm.Controls("total" & x) = Null
        frm.Controls("c" & x) = Null
        frm.Controls("e" & x) = Null
        End With
        rs.MoveNext
    Next
    
    For x = 1 To 16
        If rs.EOF Then Exit For
        With frm
        frm.Controls("qn" & x) = rs!QuoteLogNumber
        frm.Controls("cust" & x) = rs!Customer
        frm.Controls("sales" & x) = rs![Sales COOrd]
        frm.Controls("prod" & x) = rs!ProductDesignInitials
        frm.Controls("submit" & x) = rs!dtmTimeSubmitted
        frm.Controls("total" & x) = rs!Expr1
        frm.Controls("c" & x) = rs!PartsCompleted
        frm.Controls("e" & x) = rs!ynExpedite
        End With
        rs.MoveNext
    Next

Error_Handler_Exit:
    On Error Resume Next
    rs.Close
    Set rs = Nothing
'    Set db = Nothing
    Exit Sub

Error_Handler:
    MsgBox "The following error has occured." & vbCrLf & vbCrLf & _
            "Error Number: " & Err.Number & vbCrLf & _
            "Error Source: LoadMyForm" & vbCrLf & _
            "Error Description: " & Err.Description, _
            vbCritical, "An Error has Occured!"
    Resume Error_Handler_Exit
End Sub

But I must have done something wrong since the controls only display blank now.

**June7** · 05-07-2014, 05:32 PM

Guess I should have mentioned not to cycle through the recordset when setting the fields to Null. The recordset is not relevant for that.

BTW, since you have With frm, no need to repeat frm on each line. That's the reason for using With, less typing.

**justair07** · 05-07-2014, 05:45 PM

Originally Posted by June7

Guess I should have mentioned not to cycle through the recordset when setting the fields to Null. The recordset is not relevant for that.

BTW, since you have With frm, no need to repeat frm on each line. That's the reason for using With, less typing.

Hi June7,

So which line/s do I need to remove?

**June7** · 05-07-2014, 06:25 PM

If rs.EOF Then Exit For

and

rs.MoveNext

**justair07** · 05-07-2014, 07:20 PM

June7,

Thank you very much. One error though I keep getting the following error:

Click image for larger version.

Name: Capture.JPG
Views: 17
Size: 21.9 KB
ID: 16380

I had to comment out the line that referenced control e jus tto get it to run without error, see below:

Code:

 For x = 1 To 16
        'If rs.EOF Then Exit For
        With frm
        frm.Controls("qn" & x) = Null
        frm.Controls("cust" & x) = Null
        frm.Controls("sales" & x) = Null
        frm.Controls("prod" & x) = Null
        frm.Controls("submit" & x) = Null
        frm.Controls("total" & x) = Null
        frm.Controls("c" & x) = Null
        'frm.Controls("e" & x) = Null
        End With
        'rs.MoveNext
    Next

Any idea why this is happening and why it's only an issue with this control?

Duplicated Data Issue

Thread Tools

Duplicated Data Issue

Similar Threads

Blank duplicated values

Combine duplicated data in a report

Duplicated values in query

Results from query are duplicated

Lines duplicated with Queries

Posting Permissions