Update record1 in record2 in the same table- CHALLENGE- Error SQL, DAO parameters

[julienmy5757]

Good to know thank you. I think that I will put a criteria only for QUOTE NUMBER and COMPPN in strOP. I will try it tomorrow I let you know if it works. You're very helpful

[nico5038]

OK, so all fields with a trailing "CONT1" are continuations, can there be fields with a trailing "CONT2" ?
And what happens when COMPPN gets another value ?

Nic;o)

[julienmy5757]

Cont2 doesn't exist and COMPPN is a part of a QUOTE NUMBER, so you can have differents COMPPN to choose in your form

[julienmy5757]

.MoveNext doesn't exist for rstOP..
.EOF too
(I tried also tried to put a .MoveFirst )

It is not a table so I don't know how to proceed

[Seth Schrock]

That means that there aren't any records in rstOP and you need to make a change to your WHERE clause in strOP.

One other thing that I just thought of. You need to add DoCmd.RunComman d acCmdSaveRecord in line 9 so that your changes are saved to the recordset. Otherwise, you will be updating it to the old value.

[julienmy5757]

i added docmd

When I try debug print I have

strOP = "SELECT * FROM tblRoutingMain " & _
        "WHERE [tblRoutingMain].[QUOTE NUMBER] = '" & [Forms]![frmAssemblyGeneralInfo]![QUOTE NUMBER] & "' AND " & _
        "[tblRoutingMain].COMPPN='" & [Forms]![frmAssemblyGeneralInfo]![frmSubAssemblyEnterPriceNew].[Form]![COMPPN] & "'" 
Debug.Print strOP
SELECT * FROM tblRoutingMain WHERE [tblRoutingMain].[QUOTE NUMBER] = 'OPP003903' AND [tblRoutingMain].COMPPN='A02H6007-1'

And It exists in tblRoutingMain

When i try debug print for rstOP I have error 424 object required

[nico5038]

For the OPCONT1 you have exact the same data as for the OP ?

Nic;o)

[julienmy5757]

I can have run time or delivery times who can change

[Seth Schrock]

You don't want that WHERE clause in your recordset. In this case you want all non CONT1 records to be returned in strOP's query. Matching the quote number, comppn, and routing come in the Update query. That way it can loop through all of them and update their CONT1 records.

Also, if you are getting an error message, please provide the exact error message along with the number and the line it highlights when you click debug.

[julienmy5757]

The error message is for Debug.Print rstOP with the immediate command.

When I am running the code, I have "compile error: method or data member not found" for .EOF in the beginning of the loop.

Do I have do remove the WHERE clause in strOP?

[nico5038]

So the OPCONT1 is a (multirecord) copy of the OP records with (optionally) different run time and/or delivery time.
For this you could just add "CONT1" fields in the original "OP" record like:
[QUOTE NUMBER CONT1]
[RUN TIME CONT1]
[DELIVERY TIME CONT1]
Now the duplication of the records can be "dropped" and when you want the records as in your sample table (e.g. for printing purposes) you use:

SELECT [QUOTE NUMBER], [SEQ], [COMPPN], [RUN TIME], [DELIVERY TIME] FROM tblRoutingMain
UNION
SELECT [QUOTE NUMBER CONT1], [SEQ], [COMPPN], [RUN TIME CONT1], [DELIVERY CONT1] TIME FROM tblRoutingMain

Maintaining the OP and OPCONT1 data can be done in the same record, instead of having to INSERT a set of records and UPDATing the SEQ field.

This is a (not normalized) way to avoid updating the SEQ field and saving diskspace (as there are no extra records for CONT1 situations)

Nic;o)

[Seth Schrock]

You can't print a recordset, which is why you are getting the error on Debug.Print rstOP. You will need to remove the WHERE clause that you have from strOP, but you need to make sure that you are only getting non CONT1 record returned so you might still need a WHERE clause.

Can you provide the whole code that you are using now? There is an error somewhere as .EOF is certainly a good method (I use it all the time).

[julienmy5757]

Nico, I don't understand this way

If I put an union query I have to change also the row source of my subform?

I have to use differents records for QUOTE NUMBER becase my progam is based on that

[julienmy5757]

This is the code now :

Dim db As DAO.Database
Dim strOP As String
Dim rstOP As DAO.Database
Dim seqNo As Integer
Dim quoteNo As String
Dim cPN As String
Dim routingStr As String
Dim strUpdate As String

DoCmd.RunCommand acCmdSaveRecord

Set db = CurrentDb
  
strOP = "SELECT * FROM tblRoutingMain " & _
        "WHERE [tblRoutingMain].[QUOTE NUMBER] = '" & [Forms]![frmAssemblyGeneralInfo]![QUOTE NUMBER] & "' AND " & _
        "[tblRoutingMain].COMPPN='" & [Forms]![frmAssemblyGeneralInfo]![frmSubAssemblyEnterPriceNew].[Form]![COMPPN] & "'"
Set rstOP = db.OpenRecordset(strOP, dbOpenDynaset)
  
With rstOP
     '.MoveFirst
    Do While Not .EOF
        seqNo = !Seq
        quoteNo = ![QUOTE NUMBER] & "CONT1"
        cPN = !COMPPN
        routingStr = !ROUTING
  
        strUpdate = "UPDATE tblRoutingMain SET SEQ = " & seqNo & _
                    " WHERE [QUOTE NUMBER] = '" & quoteNo & "' AND " & _
                    "COMPPN = '" & cPN & "' AND Routing = '" & routingStr & "'"
  
        DoCmd.SetWarnings = False
        db.Execute strUpdate, dbFailOnError
        DoCmd.SetWarnings = True
        .MoveNext
    Loop
End With

[Seth Schrock]

Oops. That I was my mistake. Change line 4 to Dim rstOP As DAO.Recordset. You will still need to remove the WHERE clause though.