Beetle? Can you help me out?

[Opal]

You gave me a hand last week on post
entitled:

strQuery = UPDATE

Can you spare me some time, once again?

I am continuing on from where I left off last week.
Now that the employees are assigned to their
team, I now need to assign the job process
to the employee for each team.

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.

Can you help me out? Thanks.

 

[Opal]

I have:

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 item on the form.

How can I loop through the remaining employees on the
continuous form? Thanks!

 

[Beetle]

I don't know anything about the structure of your db, but it would
seem very unusual to store information about a process in a employees
table. The first question I would ask is what is the nature of the
relationship between an employee and a process. For example, if
an employee can be assigned more than one process (at a given time
or over the course of their employment), and any given process can be
assigned to more than one employee, then you have a many-to-many
relationship and you need a junction table to manage it. The information
about which employees are assigned which processes would be stored
in this junction table, not in the employee or the process table.

So my first inclination would be to suggest that you should not be storing
the ProcessID in the Employees table to begin with. However, having said that,
if you need to loop through the records in a form, one method is to use the
RecordsetClone property. For example;

Dim i As Integer

With Me.RecordsetClone
'get an accurate record count
.MoveLast
.MoveFirst
'loop through the records
Do While Not .EOF
For i = 1 To .Recordcount
strSQL = "Update SomeTable Set SomeField = Something;"
CurrentDb.Execute strSQL, dbFailOnError
.MoveNext
Next i
Loop
End With

 

[Opal]

Hi Sean,

I chose to organize the DB in this manner
because we run on two shifts and the processes
on each shift are identical. The old organization
form allowed the users too much freedom to
change processes and we ended up with too
many errors. Only one process per employee
so rather than duplicate the employee info
in another table, I chose to add a "ProcessID"
field to the EMPInfo table.

Now, I tried your suggestion, above and although
it does loop through the form, it only writes the
process ID to the first employee, nothing is written
to this field for the remaining employees on the form

......?

 

[Beetle]

Can you post your code as it is currently written?

 

[Opal]

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

 

[Opal]

So, I have been sitting here researching various forums
and groups trying to come up with a solution to this
problem. Since the data already exists in the form's
recordset, I thought I could reference a normal dataset:

Dim iProcessID As Integer
Dim iEMPNumber As Long
Dim strQuery As String
Dim rst As DAO.Recordset

Set rst = Me.RecordsetClone
Do Until rst.EOF
iProcessID = rst("ProcessID")
iEMPNumber = rst("EMPNumber")

strQuery = "UPDATE TMInfo " & _
"SET ProcessID = " & ProcessID & _
" WHERE EMPNumber = " & EMPNumber
CurrentDb.Execute strQuery, dbFailOnError
rst.MoveNext
Loop

But I still have a problem with this.....

"Item not found in this 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.

Now I am really stumped...... about ready to give up on this
and try something else..... :-S

 

[Marshall Barton]

So, I have been sitting here researching various forums
and groups trying to come up with a solution to this
problem. Since the data already exists in the form's
recordset, I thought I could reference a normal dataset:

Dim iProcessID As Integer
Dim iEMPNumber As Long
Dim strQuery As String
Dim rst As DAO.Recordset

Set rst = Me.RecordsetClone
Do Until rst.EOF
iProcessID = rst("ProcessID")
iEMPNumber = rst("EMPNumber")

strQuery = "UPDATE TMInfo " & _
"SET ProcessID = " & ProcessID & _
" WHERE EMPNumber = " & EMPNumber
CurrentDb.Execute strQuery, dbFailOnError
rst.MoveNext
Loop

But I still have a problem with this.....

"Item not found in this 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.

You removed the rst.MoveFirst line so you have no idea which
record in the recordset cloneis current. Other than that, I
would expect this latest version to work.

 

[Opal]

Hi Marsh,

I'm still getting the same error. I'm having trouble
getting my head around using recordsetclone. I
understand the concept behind it but the error would
tell me that "iProcessID" does not exist in my
recordset, which is true. Its "ProcessID". But then
how do I use recordsetclone without an alias?

 

[Marshall Barton]

I'm still getting the same error. I'm having trouble
getting my head around using recordsetclone. I
understand the concept behind it but the error would
tell me that "iProcessID" does not exist in my
recordset, which is true. Its "ProcessID". But then
how do I use recordsetclone without an alias?

How does it tell you that? As far as I can see, you never
use iProcessID in the query.

The query does use ProcessID and I assumed that was a text
box on the form that contained the desired ID. Maybe you
intended to use iProcessID:

strQuery = "UPDATE TMInfo " & _
"SET ProcessID = " & iProcessID & _
" WHERE EMPNumber = " & iEMPNumber

 

[Opal]

Thank you Marsh.... but, I just got an email from
the AM on the afternoon shift and he has changed
the direction of this project, so I need to put the
brakes on this activity as it will now change...

Thank you for help, though!

 

[Marshall Barton]

Thank you Marsh.... but, I just got an email from
the AM on the afternoon shift and he has changed
the direction of this project, so I need to put the
brakes on this activity as it will now change...

Such is life in the world of software development.