Could someone help with my code

[mgbeye]

I am trying to basically take a copy of one table and add it to a pre-existing table. For some reason I can not get my loop to work right. It simply goes through and copies the same record for each line. It puts in the right number, but never changes value. I would appreciate input on what might be wrong.

Do Until rstProductionReporting.EOF = True
    IsNull (varCheck)
    varCheck = DLookup("[id]", "Production Records", "mvt = " & Me.Mvt & " and material = '" & Me.Material & "' and quantity = " & Me.Quantity)
    If Not IsNull(varCheck) Then
        If varCount = 1 Then
            varANS = Msgbox("This action will creat duplicate records. Continue?", vbYesNo)
        End If
        If varANS = True Then
            rstProductionRecords.AddNew
            rstProductionRecords!Mvt = Me.Mvt
            rstProductionRecords!Material = Me.Material
            rstProductionRecords![Old matl] = Me.[Old matl]
            rstProductionRecords![Material desc] = Me.[Material desc]
            rstProductionRecords![Post date] = Me.[Post date]
            rstProductionRecords!Quantity = Me.Quantity
            rstProductionRecords.Update

        End If
    Else
         rstProductionRecords.AddNew
         rstProductionRecords!Mvt = Me.Mvt
         rstProductionRecords!Material = Me.Material
         rstProductionRecords![Old matl] = Me.[Old matl]
         rstProductionRecords![Material desc] = Me.[Material desc]
         rstProductionRecords![Post date] = Me.[Post date]
         rstProductionRecords!Quantity = Me.Quantity
         rstProductionRecords.Update
    End If
    varCount = varCount + 1
    rstProductionReporting.MoveNext
    Loop

THANKS!

 

[TTThio]

Saw the recordset move next, but didn't see any code that assign me.field value to the next me.field value.
It's probably why. Check the use of varCount.

 

[mgbeye]

I'm not sure if I am understanding. I need to do a movenext for the recordset which I have done and then also do one for each field. "me.field.movenext" Is that right?

 

[TTThio]

Probably it's me who didn't get it right.

From what I understand,
rs!Field value (rec 1)= me.field value
then rs.movenext
rs!Field value (rec 2)= me.field value
What I mean, I see the recordset move, but I didn't see where the me.field value moves from rec 1 to rec 2.

It seems like there's only one me.field value, and it's assigned to all records in the recordset.

If this is not the case, then try to debug your Dlookup. Does it return different values or the same one all the time.

 

[MichaelRed]

Two issues here. TTThio has identified the detail problem. You are adding records to [rstProductionRecords] - but all of the VALUES come from the controls on the form (e.g. Me.[Material desc]) - where there is no indication that the control value/content (Me.[Material desc]) has been changed. So, the record is added as many times as you indicate - but they all must have the SAME value. since you add a record for each original record, you double the number of records each execution.

Almost as important is the question of why you think you need to do this in code? If the controls are bound, simply moving to the next record can be made to trigger the record addition. If these are unbound controls, you could just use an append query with the form controls as the source for the fields.

I strongly suspect you want to add only a single record to your db and the loop part is totally wrong. Adding a single record in this manner (NO LOOP) is a generally accepted approach to adding from an unbound form.

MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over