How to automatically update data in form based on results of entry

[BLW]

I am creating an employee database. I have everything set up in a form with
that shows all their information including their employment status (Active,
Leave, Termed, etc...). I have details about their employment and
demographics in subforms.

I have it set up so that when information (like write-ups, absences, and
such) needs to be entered, you don't have to go to the main Employee form -
the info is entered into a basic "absence record" form and the subforms in
the main Employee Form are updated.

My question is how do I make a field in the Main Employee Form change when a
certain form is completed. I created a termination form with all the details
about a termination. What I want, is when this information is entered - the
employee's status is automatically updated to "Termed" in the Main Employee
form.

Any help would be great!

BLW

 

[Beetle]

Assuming the recordsource for your termination form includes a field
that is a foreign key to your employees table (like EmployeeID) then
you could use an update query. The code might look like;

Dim strSQL As String

strSQL = "Update tblEmployees Set tblEmployees.Status = 'Termed' Where
tblEmployees.EmployeeID = " & Me.EmployeeID & ";"

Currentdb.Execute strSQL, dbFailOnError

You could put the code in the Click event of a command button, or the After
Update event of some control, etc. You'll need to correct the line wrap, field
names and such.

 

[BLW]

Beetle,

Thanks for the quick response. I copy and pasted that; updates the field
names and line wrap..... my primary key in the Employees table is "ID" and
the primary key in the Termination Entry table is "TermID".

When you enter the ID in the Termination Entry table - it looks up the ID
from the Employees table. (don't know if this matters)

I keep getting the following error. Any ideas?

Syntax error(missing operator) in query expression
'tblEmployees.ID = TerminationEntry'

BLW

 

[Beetle]

Copy and paste the code as it is currently written, and just to clarify, what
is the name of the foreign key field in your Termination Entry table. Not
the primary key, but the FK field that is related to the Employees table.

 

[BLW]

strSQL = "Update tblEmployees Set tblEmployees.Active = 'Termed' Where
Employees.Name = " & Me.Name & ";"

I double checked all my primary keys:
The foreign key field in the termination entry is "Name"
The primary key in the Employees table is "Name"
The primary key in the Termination table is "Term ID"

BLW

 

[Beetle]

OK, there are a couple of things to address here. First, words like Name and
Date are reserved words in Access and should not be used as field
or control names. For a complete listing of reserved words, see this link;

http://allenbrowne.com/AppIssueBadWord.html

The word Name is a property of the form, so when you put Me.Name in
your code, Access/Jet thinks you are referring to the name of the form
itself. It then inserts that form name (TerminationEntry) into your SQL
statement. You can solve the problem temporarily by using Me![Name],
however you really should change the naming of those fields/controls
so as to avoid problems like this in the future.

The other issue, which isn't really related to your current problem, but
is an issue nonetheless, is with the structure of your employees table.
First, names should always be separated into FirstName and LastName
(at a minimum), not put into one field. Second, and more importantly,
a field such as a person's name should never be used as a primary
key because they are not unique. Eventually you will have two (or more)
people with the same name. You should have an EmployeeID field
as the PK. It would typically be an Autonumber data type.

 

[BLW]

Ok.

I have changed the field name to "Employee Name" and set the primary key in
the employees table to an autonumber field. I changed all related forms and
update the code in the termination entry form to this:

strSQL = "Update tblEmployees Set tblEmployees.Active = 'Termed' Where
Employees.[Employee Name] = " & Me.[Employee Name] & ";"

The error I am now getting is this

Syntax error (comma) in query expression 'Employees.[Employee Name] =
Abdullah, Kenneth R'.

Is this because of what you were saying before - I should separate first and
last names? Abdullah, Kenneth is the first name on the list.

BLW

OK, there are a couple of things to address here. First, words like Name and
Date are reserved words in Access and should not be used as field
or control names. For a complete listing of reserved words, see this link;

http://allenbrowne.com/AppIssueBadWord.html

The word Name is a property of the form, so when you put Me.Name in
your code, Access/Jet thinks you are referring to the name of the form
itself. It then inserts that form name (TerminationEntry) into your SQL
statement. You can solve the problem temporarily by using Me![Name],
however you really should change the naming of those fields/controls
so as to avoid problems like this in the future.

The other issue, which isn't really related to your current problem, but
is an issue nonetheless, is with the structure of your employees table.
First, names should always be separated into FirstName and LastName
(at a minimum), not put into one field. Second, and more importantly,
a field such as a person's name should never be used as a primary
key because they are not unique. Eventually you will have two (or more)
people with the same name. You should have an EmployeeID field
as the PK. It would typically be an Autonumber data type.

--
_________

Sean Bailey

strSQL = "Update tblEmployees Set tblEmployees.Active = 'Termed' Where
Employees.Name = " & Me.Name & ";"

I double checked all my primary keys:
The foreign key field in the termination entry is "Name"
The primary key in the Employees table is "Name"
The primary key in the Termination table is "Term ID"

BLW

 

[Beetle]

When I first posted the example SQL statement, my assumption was that
you would be using EmployeeID as criteria, and that it would be an integer
data type (like autonumber). If you are going to use EmployeeName, which
is a string data type, as criteria, then the statement needs to be modified
with additional qoutes like this;

strSQL = "Update tblEmployees Set tblEmployees.Active = 'Termed' Where
Employees.[Employee Name] = """ & Me.[Employee Name] & """;"

It would be better to use EmployeeID because, again, if you have more
than one "Smith, John" it is going to modify all records with that name.

--
_________

Sean Bailey

Ok.

I have changed the field name to "Employee Name" and set the primary key in
the employees table to an autonumber field. I changed all related forms and
update the code in the termination entry form to this:

strSQL = "Update tblEmployees Set tblEmployees.Active = 'Termed' Where
Employees.[Employee Name] = " & Me.[Employee Name] & ";"

The error I am now getting is this

Syntax error (comma) in query expression 'Employees.[Employee Name] =
Abdullah, Kenneth R'.

Is this because of what you were saying before - I should separate first and
last names? Abdullah, Kenneth is the first name on the list.

BLW

OK, there are a couple of things to address here. First, words like Name and
Date are reserved words in Access and should not be used as field
or control names. For a complete listing of reserved words, see this link;

http://allenbrowne.com/AppIssueBadWord.html

The word Name is a property of the form, so when you put Me.Name in
your code, Access/Jet thinks you are referring to the name of the form
itself. It then inserts that form name (TerminationEntry) into your SQL
statement. You can solve the problem temporarily by using Me![Name],
however you really should change the naming of those fields/controls
so as to avoid problems like this in the future.

The other issue, which isn't really related to your current problem, but
is an issue nonetheless, is with the structure of your employees table.
First, names should always be separated into FirstName and LastName
(at a minimum), not put into one field. Second, and more importantly,
a field such as a person's name should never be used as a primary
key because they are not unique. Eventually you will have two (or more)
people with the same name. You should have an EmployeeID field
as the PK. It would typically be an Autonumber data type.

--
_________

Sean Bailey

strSQL = "Update tblEmployees Set tblEmployees.Active = 'Termed' Where
Employees.Name = " & Me.Name & ";"

I double checked all my primary keys:
The foreign key field in the termination entry is "Name"
The primary key in the Employees table is "Name"
The primary key in the Termination table is "Term ID"

BLW

:

Copy and paste the code as it is currently written, and just to clarify, what
is the name of the foreign key field in your Termination Entry table. Not
the primary key, but the FK field that is related to the Employees table.
--
_________

Sean Bailey


:

Beetle,

Thanks for the quick response. I copy and pasted that; updates the field
names and line wrap..... my primary key in the Employees table is "ID" and
the primary key in the Termination Entry table is "TermID".

When you enter the ID in the Termination Entry table - it looks up the ID
from the Employees table. (don't know if this matters)

I keep getting the following error. Any ideas?

Syntax error(missing operator) in query expression
'tblEmployees.ID = TerminationEntry'

BLW



:

Assuming the recordsource for your termination form includes a field
that is a foreign key to your employees table (like EmployeeID) then
you could use an update query. The code might look like;

Dim strSQL As String

strSQL = "Update tblEmployees Set tblEmployees.Status = 'Termed' Where
tblEmployees.EmployeeID = " & Me.EmployeeID & ";"

Currentdb.Execute strSQL, dbFailOnError

You could put the code in the Click event of a command button, or the After
Update event of some control, etc. You'll need to correct the line wrap, field
names and such.
--
_________

Sean Bailey


:

I am creating an employee database. I have everything set up in a form with
that shows all their information including their employment status (Active,
Leave, Termed, etc...). I have details about their employment and
demographics in subforms.

I have it set up so that when information (like write-ups, absences, and
such) needs to be entered, you don't have to go to the main Employee form -
the info is entered into a basic "absence record" form and the subforms in
the main Employee Form are updated.

My question is how do I make a field in the Main Employee Form change when a
certain form is completed. I created a termination form with all the details
about a termination. What I want, is when this information is entered - the
employee's status is automatically updated to "Termed" in the Main Employee
form.

Any help would be great!

BLW

 

[BLW]

This is the latest error I am getting:

The Microsoft Jet database engine cannot find the input table or query
'tblEmployees'. make sure it exists and that its name is spelled correctly.

I double checked the spelling and it matches.

Any ideas? BTW Thank you so much for your help.

BLW

When I first posted the example SQL statement, my assumption was that
you would be using EmployeeID as criteria, and that it would be an integer
data type (like autonumber). If you are going to use EmployeeName, which
is a string data type, as criteria, then the statement needs to be modified
with additional qoutes like this;

strSQL = "Update tblEmployees Set tblEmployees.Active = 'Termed' Where
Employees.[Employee Name] = """ & Me.[Employee Name] & """;"

It would be better to use EmployeeID because, again, if you have more
than one "Smith, John" it is going to modify all records with that name.

--
_________

Sean Bailey

Ok.

I have changed the field name to "Employee Name" and set the primary key in
the employees table to an autonumber field. I changed all related forms and
update the code in the termination entry form to this:

strSQL = "Update tblEmployees Set tblEmployees.Active = 'Termed' Where
Employees.[Employee Name] = " & Me.[Employee Name] & ";"

The error I am now getting is this

Syntax error (comma) in query expression 'Employees.[Employee Name] =
Abdullah, Kenneth R'.

Is this because of what you were saying before - I should separate first and
last names? Abdullah, Kenneth is the first name on the list.

BLW

OK, there are a couple of things to address here. First, words like Name and
Date are reserved words in Access and should not be used as field
or control names. For a complete listing of reserved words, see this link;

http://allenbrowne.com/AppIssueBadWord.html

The word Name is a property of the form, so when you put Me.Name in
your code, Access/Jet thinks you are referring to the name of the form
itself. It then inserts that form name (TerminationEntry) into your SQL
statement. You can solve the problem temporarily by using Me![Name],
however you really should change the naming of those fields/controls
so as to avoid problems like this in the future.

The other issue, which isn't really related to your current problem, but
is an issue nonetheless, is with the structure of your employees table.
First, names should always be separated into FirstName and LastName
(at a minimum), not put into one field. Second, and more importantly,
a field such as a person's name should never be used as a primary
key because they are not unique. Eventually you will have two (or more)
people with the same name. You should have an EmployeeID field
as the PK. It would typically be an Autonumber data type.

--
_________

Sean Bailey


:

strSQL = "Update tblEmployees Set tblEmployees.Active = 'Termed' Where
Employees.Name = " & Me.Name & ";"

I double checked all my primary keys:
The foreign key field in the termination entry is "Name"
The primary key in the Employees table is "Name"
The primary key in the Termination table is "Term ID"

BLW

:

Copy and paste the code as it is currently written, and just to clarify, what
is the name of the foreign key field in your Termination Entry table. Not
the primary key, but the FK field that is related to the Employees table.
--
_________

Sean Bailey


:

Beetle,

Thanks for the quick response. I copy and pasted that; updates the field
names and line wrap..... my primary key in the Employees table is "ID" and
the primary key in the Termination Entry table is "TermID".

When you enter the ID in the Termination Entry table - it looks up the ID
from the Employees table. (don't know if this matters)

I keep getting the following error. Any ideas?

Syntax error(missing operator) in query expression
'tblEmployees.ID = TerminationEntry'

BLW



:

Assuming the recordsource for your termination form includes a field
that is a foreign key to your employees table (like EmployeeID) then
you could use an update query. The code might look like;

Dim strSQL As String

strSQL = "Update tblEmployees Set tblEmployees.Status = 'Termed' Where
tblEmployees.EmployeeID = " & Me.EmployeeID & ";"

Currentdb.Execute strSQL, dbFailOnError

You could put the code in the Click event of a command button, or the After
Update event of some control, etc. You'll need to correct the line wrap, field
names and such.
--
_________

Sean Bailey


:

I am creating an employee database. I have everything set up in a form with
that shows all their information including their employment status (Active,
Leave, Termed, etc...). I have details about their employment and
demographics in subforms.

I have it set up so that when information (like write-ups, absences, and
such) needs to be entered, you don't have to go to the main Employee form -
the info is entered into a basic "absence record" form and the subforms in
the main Employee Form are updated.

My question is how do I make a field in the Main Employee Form change when a
certain form is completed. I created a termination form with all the details
about a termination. What I want, is when this information is entered - the
employee's status is automatically updated to "Termed" in the Main Employee
form.

Any help would be great!

BLW

 

[Beetle]

Is it actually called tblEmployees (with the 'tbl' at the beginning)? Or is it
just called Employees?

The leading tbl that I used in the example is just part of a naming convention
that I, and many Access developers, use to keep track of what's what.
For example;

All my table names start with tbl
All my query names start with qry
Forms start with frm, subforms start with sfrm
etc., etc.
--
_________

Sean Bailey

This is the latest error I am getting:

The Microsoft Jet database engine cannot find the input table or query
'tblEmployees'. make sure it exists and that its name is spelled correctly.

I double checked the spelling and it matches.

Any ideas? BTW Thank you so much for your help.

BLW

When I first posted the example SQL statement, my assumption was that
you would be using EmployeeID as criteria, and that it would be an integer
data type (like autonumber). If you are going to use EmployeeName, which
is a string data type, as criteria, then the statement needs to be modified
with additional qoutes like this;

strSQL = "Update tblEmployees Set tblEmployees.Active = 'Termed' Where
Employees.[Employee Name] = """ & Me.[Employee Name] & """;"

It would be better to use EmployeeID because, again, if you have more
than one "Smith, John" it is going to modify all records with that name.

--
_________

Sean Bailey

Ok.

I have changed the field name to "Employee Name" and set the primary key in
the employees table to an autonumber field. I changed all related forms and
update the code in the termination entry form to this:

strSQL = "Update tblEmployees Set tblEmployees.Active = 'Termed' Where
Employees.[Employee Name] = " & Me.[Employee Name] & ";"

The error I am now getting is this

Syntax error (comma) in query expression 'Employees.[Employee Name] =
Abdullah, Kenneth R'.

Is this because of what you were saying before - I should separate first and
last names? Abdullah, Kenneth is the first name on the list.

BLW


:

OK, there are a couple of things to address here. First, words like Name and
Date are reserved words in Access and should not be used as field
or control names. For a complete listing of reserved words, see this link;

http://allenbrowne.com/AppIssueBadWord.html

The word Name is a property of the form, so when you put Me.Name in
your code, Access/Jet thinks you are referring to the name of the form
itself. It then inserts that form name (TerminationEntry) into your SQL
statement. You can solve the problem temporarily by using Me![Name],
however you really should change the naming of those fields/controls
so as to avoid problems like this in the future.

The other issue, which isn't really related to your current problem, but
is an issue nonetheless, is with the structure of your employees table.
First, names should always be separated into FirstName and LastName
(at a minimum), not put into one field. Second, and more importantly,
a field such as a person's name should never be used as a primary
key because they are not unique. Eventually you will have two (or more)
people with the same name. You should have an EmployeeID field
as the PK. It would typically be an Autonumber data type.

--
_________

Sean Bailey


:

strSQL = "Update tblEmployees Set tblEmployees.Active = 'Termed' Where
Employees.Name = " & Me.Name & ";"

I double checked all my primary keys:
The foreign key field in the termination entry is "Name"
The primary key in the Employees table is "Name"
The primary key in the Termination table is "Term ID"

BLW

:

Copy and paste the code as it is currently written, and just to clarify, what
is the name of the foreign key field in your Termination Entry table. Not
the primary key, but the FK field that is related to the Employees table.
--
_________

Sean Bailey


:

Beetle,

Thanks for the quick response. I copy and pasted that; updates the field
names and line wrap..... my primary key in the Employees table is "ID" and
the primary key in the Termination Entry table is "TermID".

When you enter the ID in the Termination Entry table - it looks up the ID
from the Employees table. (don't know if this matters)

I keep getting the following error. Any ideas?

Syntax error(missing operator) in query expression
'tblEmployees.ID = TerminationEntry'

BLW



:

Assuming the recordsource for your termination form includes a field
that is a foreign key to your employees table (like EmployeeID) then
you could use an update query. The code might look like;

Dim strSQL As String

strSQL = "Update tblEmployees Set tblEmployees.Status = 'Termed' Where
tblEmployees.EmployeeID = " & Me.EmployeeID & ";"

Currentdb.Execute strSQL, dbFailOnError

You could put the code in the Click event of a command button, or the After
Update event of some control, etc. You'll need to correct the line wrap, field
names and such.
--
_________

Sean Bailey


:

I am creating an employee database. I have everything set up in a form with
that shows all their information including their employment status (Active,
Leave, Termed, etc...). I have details about their employment and
demographics in subforms.

I have it set up so that when information (like write-ups, absences, and
such) needs to be entered, you don't have to go to the main Employee form -
the info is entered into a basic "absence record" form and the subforms in
the main Employee Form are updated.

My question is how do I make a field in the Main Employee Form change when a
certain form is completed. I created a termination form with all the details
about a termination. What I want, is when this information is entered - the
employee's status is automatically updated to "Termed" in the Main Employee
form.

Any help would be great!

BLW

 

[smudgedwhiteink]

Hi BLW, you have exactly what I am looking for. I am quite new at access,
and have been struggling with creating exactly what you described for awhile.
Do you have a template of any sort of what you described below that you
would be willing to give me... (e-mail address removed) is my email if
are willing to chat. Thanks,
Accessivley Confused