How do i use this code in a loop/update method?

[papic1972]

Hi All,

I have a form "frmCandM" where i would like to click a command button & update all of the rates in the opened recordset in subform "subfrmAdbriMasonry".

I need to abide by the following criteria:


If Me.Qty < 3 And Me.PricePeriod = 1 Then

Forms!frmCandM!subfrmAdbriMasonry!JDRate = DLookup("[JDRate]", "qryContractRatesAdbri1", "

=Forms!frmCandM!subfrmAdbriMasonry!Code")
Forms!frmCandM!subfrmAdbriMasonry!SubyRate = DLookup("[SubyRate]", "qryContractRatesAdbri1", "[Code]=Forms!frmCandM!subfrmAdbriMasonry!Code")
Forms!frmCandM!subfrmAdbriMasonry!MinQty = 3

Else

If Me.Qty >= 3 And Me.PricePeriod = 1 Then

Forms!frmCandM!subfrmAdbriMasonry!JDRate = DLookup("[JDRate]", "qryContractRatesAdbri1", "[Code]=Forms!frmCandM!subfrmAdbriMasonry!Code")
Forms!frmCandM!subfrmAdbriMasonry!SubyRate = DLookup("[SubyRate]", "qryContractRatesAdbri1", "[Code]=Forms!frmCandM!subfrmAdbriMasonry!Code")
Forms!frmCandM!subfrmAdbriMasonry!MinQty = Me.Qty


End If
End If


Can anyone point me in the right direction on how to incorporate this into a Do/While/Loop method to update all of the records on my subform "subfrmAdbriMasonry"? OR, am i barking up the wrong tree & should i be using an UPDATE statement?:confused::eek:

 

[gemma-the-husky]

it can be done with a loop, but it can probably also be done by a stored query

sort of this idea

update price to price + somevalue where pricetype is somevalue


depends exactly what you are trying to do in this example

 

[papic1972]

I've tried having a go at creating a loop. For the time being i've eliminated the IF statement & i'm trying to get the loop to Dlookup the value of the JDRate in the query.

I keep getting a Run-time error '3061' "Too few parameters. Expected 1" error. Here is my attempt:

Dim rst As DAO.Recordset
Dim lngLoadLineID As Long



Set rst = CurrentDb.OpenRecordset("SELECT tblLocal.LoadLineID, tblLocal.JDRate, " _
& "tblLocal.SubyRate, tblLocal.JDPayment, tblLocal.Qty, tblLocal.PricePeriod FROM tblLocal " _
& "WHERE (((tblLocal.LoadlineID)=" & Forms!frmCandM!subfrmAdbriMasonry!LoadLineID & "));", dbOpenDynaset, dbSeeChanges)

With rst
.MoveFirst

Do While Not .EOF
.Edit
lngLoadLineID = ![LoadLineID]
![jdrate] = DLookup("[JDRate]", "qryContractRatesAdbri1", "

=Forms!frmCandM!subfrmAdbriMasonry!Code")
            .Update
            
            .MoveNext
            
            If .EOF Then
                Exit Do
            
            End If
        
        Loop
    
    End With


Can anyone suggest where i'm going wrong?

 

[boblarson]

You need to have the form reference outside of the quotes and you have to use the correct subform syntax. You need to refer to the control on the main form which houses the subform, not the name of the subform itself:


DLookup("[JDRate]", "qryContractRatesAdbri1", "

=Forms!frmCandM!subfrmAdbriMasonry!Code")
 
would actually be:
 
DLookup("[JDRate]", "qryContractRatesAdbri1", "[code]= " & Forms!frmCandM!subfrmAdbriMasonry.Form.Code)
 
And if Code is text: 
 
DLookup("[JDRate]", "qryContractRatesAdbri1", "[code]= " & Chr(34) & Forms!frmCandM!subfrmAdbriMasonry.Form.Code & Chr(34))
 
And if code is text you would use

 

[papic1972]

I tried that Bob, but i'm still getting the same error. Would i be getting the error because i haven't listed all the fields from tblLocal in my SELECT statment?

 

[boblarson]

Is LoadLineID text or numeric?

 

[papic1972]

LoadLineID is numeric

 

[boblarson]

Okay, and are you referring to the subform control here and not the name of the subform itself (unless they share the exact same name):

Forms!frmCandM!subfrmAdbriMasonry!LoadLineID

Normally if the control name is subfrmAdbriMasonry then you would use:

Forms!frmCandM!subfrmAdbriMasonry.Form.LoadLineID

 

[papic1972]

Yes, i'm referring to the subform control.

 

[boblarson]

Yes, i'm referring to the subform control.

Need the .Form. part too (which I added for you)

 

[papic1972]

I just tried listing all the fields in my table tblLocal in the SELECT statement & now am getting a different error.

I'm getting a syntax error (missing operator) in query expression '

= MOORNSyd'... (where MOORNSyd is the text in the Code field)

Does this error make sense?

 

[boblarson]

I just tried listing all the fields in my table tblLocal in the SELECT statement & now am getting a different error.

I'm getting a syntax error (missing operator) in query expression '

= MOORNSyd'... (where MOORNSyd is the text in the Code field)
 
Does this error make sense?[/quote]
 
Yes, look back at what I said earlier:
 
[quote=boblarson]And if Code is text: 
 
DLookup("[JDRate]", "qryContractRatesAdbri1", "[code]= " [COLOR=red][B]& Chr(34)[/B][/COLOR] & Forms!frmCandM!subfrmAdbriMasonry[COLOR=red][B].Form.[/B][/COLOR]Code [COLOR=red][B]& Chr(34)[/B][/COLOR])
[/quote]

 

[papic1972]

Bob! You are legendary!!! Thank you so much, that works!

 

[boblarson]

Glad we got that out of the way

 

[papic1972]

My loop only works for the current record, but not for my whole recordset.

Is my loop not working because of this?

Set rst = CurrentDb.OpenRecordset("SELECT tblLocal.LoadLineID, tblLocal.JDRate, " _
& "tblLocal.SubyRate, tblLocal.JDPayment, tblLocal.Qty, tblLocal.PricePeriod FROM tblLocal " _
& "WHERE (((tblLocal.LoadlineID)=" & Forms!frmCandM!subfrmAdbriMasonry!LoadLineID & "));", dbOpenDynaset, dbSeeChanges)

 

[boblarson]

My loop only works for the current record, but not for my whole recordset.

Is my loop not working because of this?

Set rst = CurrentDb.OpenRecordset("SELECT tblLocal.LoadLineID, tblLocal.JDRate, " _
& "tblLocal.SubyRate, tblLocal.JDPayment, tblLocal.Qty, tblLocal.PricePeriod FROM tblLocal " _
& "WHERE (((tblLocal.LoadlineID)=" & Forms!frmCandM!subfrmAdbriMasonry!LoadLineID & "));", dbOpenDynaset, dbSeeChanges)

I'd like to know where the heck you got that because I sure didn't give it to you. I've already given you what you SHOULD use but apparently that has gone unheeded.

If you look back you might find this:

Forms!frmCandM!subfrmAdbriMasonry.Form.LoadLineID

 

[papic1972]

My apologies Bob, it was a long and disastrous day yesterday. Your advice definitely did not go unheeded.

Thank you for your help.

 

[boblarson]

Hopefully today is better.

 

[papic1972]

Hopefully! Thanks again!