Continuous form + strQuery = "UPDATE help

[Indigo]

I'm working in Access 2003 trying to build something that will allow users to identify employees' processes within their groups.

I have a continuous form that shows the processes for each team. Beside each process I have a combo box for the user to select the employee name associated with the process.

The form is based on the process table, but I want to update the
values in employee info table.

Do While Not .EOF 
With Forms!subfrmZoneOrg1.txtEMPNumber      strQuery = "UPDATE EMPInfo " & _ 
                 "SET ProcessID= " & Forms!subformZoneOrg1.ProcessID & _
                   " WHERE EMPNumber = " & .Column(0, varItem) 


      CurrentDb.Execute strQuery, dbFailOnError 

End With 
Loop

I know that this code does not work, but I was hoping it would explain what I am trying to do and perhaps someone could point me in the right direction.....

 

[Indigo]

I have come up with:

Dim strQuery As String 
strQuery = "UPDATE EMPInfo " & _ 
         "SET ProcessID = " & Forms!subfrmZoneOrg1.ProcessID & _ 
          " WHERE EMPNumber = " & Forms!subfrmZoneOrg1.cboEMPNumber 
CurrentDb.Execute strQuery, dbFailOnError

But that only takes care of the first employee on the form.
How can I loop through the remaining employees on the
continuous form? Thanks!

 

[Indigo]

So I tried the following to loop through the form:

Dim i As Integer
Dim strQuery As String
 
With Me.RecordsetClone
    'get an accurate record count
    .MoveLast
    .MoveFirst
    'loop through the records
    Do While Not .EOF
        For i = 1 To .RecordCount
      strQuery = "UPDATE EMPInfo " & _
                    "SET ProcessID = " & Forms!subfrmZoneOrg1.ProcessID & _
                    " WHERE EMPNumber = " & Forms!subfrmZoneOrg1.txtName
      CurrentDb.Execute strQuery, dbFailOnError
        .MoveNext
        Next i
    Loop
End With

But it still only writes a process ID to the first employee, not the others on the form......

 

[namliam]

                    "SET ProcessID = " & Forms!subfrmZoneOrg1.ProcessID & _
                    " WHERE EMPNumber = " & Forms!subfrmZoneOrg1.txtName

Answer me this, what are these 2 variables refering too??

 

[Indigo]

ProcessID & EMPNumber are the fields in the table.

Forms!subfrmZoneOrg1.ProcessID and Forms!subfrmZoneOrg1.txtName
are the controls on the subform.

EMPNumber is the employee number and ProcessID is the corresponding primary key in the Process table.

 

[dcb]

OK

I have a continuous form that shows the processes for each team. Beside each process I have a combo box for the user to select the employee name associated with the process.

The form is based on the process table, but I want to update the
values in employee info table.

I assume that the process table includes ProcessID and EMPNumber Fields and you want to base this off the form so that you can keep your filters etc?

If the above is correct the updated data already exists in the forms Recordset

Thus instead of trying to use "With" just reference it as a normal dataset
Something Like this:

Dim iProcessID,iEMPNumber As Integer
  Dim strQuery As String
   
  Set rst = Me.RecordsetClone
      Do Until rst.EOF
          iProcessID = rst(“ProcessID”)
          iEMPNumber = rst(“EMPNumber”)
          
        strQuery = "UPDATE EMPInfo " & _
                      "SET ProcessID = " & iProcessID & _
                      " WHERE EMPNumber = " & iEMPNumber
        CurrentDb.Execute strQuery, dbFailOnError
          rst.MoveNext
         Loop

Am new to this forum so forgive me if I have missed anything...

 

[namliam]

ProcessID & EMPNumber are the fields in the table.

Forms!subfrmZoneOrg1.ProcessID and Forms!subfrmZoneOrg1.txtName
are the controls on the subform.

EMPNumber is the employee number and ProcessID is the corresponding primary key in the Process table.

Bolding by me...
Controls ON THE FORM... If you are reading from the form, this form will always have only 1 record selected, then how can the query update anything but 1 record??

You need to change that to be the recordset instead of the form, much like dcb did... to whom a harty welcome to AWF

 

[Indigo]

Thank you, so much, for your help and insight. It is starting to make sense to me now. Just a couple of items to iron out, however....

Set rst = Me.RecordsetClone

When I compile, I get an error that the variable is not defined.

So I tried:

Dim rst As Recordset

But then I get: "Type mismatch"

 

[namliam]

DAO vs ADO

Your version of access has ADO defaulted while the recordset of a form is DAO thus causing the conflict.

Dim rst as DAO.Recordset
Should resolve this conflict, though this in itself may well cause an error if you have not added Microsoft DAO (something or other) to your references. Search for the error or something like "ado vs dao" or dao reference or somethnig to find the solution.

Good luck!

 

[Indigo]

Thank you, that fixed that....now

"Item not found in collection"

Stepping through the code, this is the line with the error:

iProcessID = rst("ProcessID")

When I move my mouse over the line I get "iProcessID = 0

but there is a value there. I opened the immediate window and
entered

?Forms!subfrmZoneOrg1!ProcessID

and got a value of 32 which corresponds to the first value in the form
for the ProcessID field. Am I missing something?

 

[dcb]

I missed the subform ref - thanks

I assume then that you are using a button to trigger and this is on the main form? If thats the case you need to point the recordset to the subform not the main form.

Set rst = Me.subfrmZoneOrg1.Form.RecordsetClone



Good Luck

 

[namliam]

"Item not found in collection"

This error in itself means that the column you are referencing is not in your recordset.
Check where you are getting the recordset vs what you are expecting to get and change accoordingly.

 

[Indigo]

This is where I am getting confused about recordsetclone....

I have no column labelled "iProcessID" This is the name dcb gave it.

My recordset contains the value "ProcessID"

As for the button to trigger... its on the subform itself, not the main form.

And what happened to rst.MoveFirst ???

 

[dcb]

Can you upload the db with no data and the form that we are talking about? It would be a great help to just take a look.

 

[Indigo]

I wish I could. The DB is 5 years old and the information is not only proprietory but also confidential. Unfortunately, I don't think I have the time to create a "dummy" to just send the forms. But also, what happened to rst.MoveFirst?

Also, I put this code right on the subform and not in a module because I got an invalid use of "me" when I had it in a module for the line:

Set rst = Me.RecordsetClone

 

[dcb]

Understood
Where is your control ProcessID getting its data from? Is it in the recordset for the form or is it stand alone?

 

[namliam]

"Me" is only valid in forms as me points to the "current" form.

iProcessID is just a variable that temporarily stores the ProcessID for later use

 

[Indigo]

The subform is based on the following SQL:

SELECT qryZoneProcess.ZoneProcessID, qryZoneProcess.Process, qryZoneProcess.TLGroupID, qryZoneProcess.TMNumber
FROM qryZoneProcess
WHERE (((qryZoneProcess.TLGroupID)=1));

So the control source is ZoneProcessID

 

[dcb]

Hi Indigo,

This must be driving you insane - wish i could've replied earlier

Thus the code should be:

iProcessID = rst("ZoneProcessID")
​

as this is the name in the underlying dataset

However we are going to see the same issue with EMPNumber or rather the source of txtName - is this in the dataset ?

Can I ask a silly question here ... Why not use the after_update of the txtName control to solve this issue. As the user changes the value you could update the employees table. because you are on the correct record at the time your prev references would work, thus you would update one at a time.

 

[namliam]

iProcessID = rst("ZoneProcessID")

FYI
rst!ZoneProcessID
rst(1) (as in get the first column, might actually be rst(0) now that i think of it)

Both also work.