Is it possible?

[Guest]

I'm new to Access and need to know if I can create a form that uses mulitple
combo boxes each based on a seperate table, to create a unique NEW record
that will be stored on yet another "record" table.

To explain the purpose of the form:

The form will be used to track employees (thousands of them) at each of
their various project sites (a couple hundred). Each employee may work at
multiple projects and each project will obviously have many different
employees. The form will be used to enter new assignment details, like length
at the assignment, managers name, etc. The idea is to prevent having to enter
all of the employee or project data into the database everytime there is a
new assignment. Instead you would select the employee Number and Project
Number and the details would auto populate.

How I want the form to work:

The form will have an employee number combo box associated with Last name,
First name, Middle Initial. You select the employees number and last, first,
middle will auto populate. Then there will be a project number combo box that
will auto populate with the city, state, etc.. Finally, there will be various
blank fields on the form that will be for the data entry person to fill out
with details specific to the selected employee working at the selectd project
site. The blank fields will be for Managers name, length at the assignment,
etc.. When the data entry person is done there should be a new record based
on the Employee Number, Project Number, misc details.

Sorry to be so long winded! Any ideas would help. I have created all the
tables and relationships, but the form has more issues than I care to go into.

Thanks,
Justin

 

[Guest]

Yes, you can do that. It will take some VBA. Do you know VBA?

 

[Guest]

It's been a while, but if I can get pointed in the right direction I should
be able to get there...eventually!

 

[Guest]

point you in the right direction....hhmmmm....okay, go North

Okay. I don't know if any of the combo boxes you are planning to use will
depend on values in other combo boxes. This is a technique called "Cascading
Combos". What that does is allow you to filter succeeding combos based on
the value of a previous combo. This will, if applicable, shorten the list
the user has to choose from .

Now to the meat. I will give high overviews of the technique first, and we
can refine it as we go. Let's start with the Employee combo as an example.
But first, lets start with the record that will be created from the form.
You will want to make the table or (preferrabley) a query based on the table
you will be updating the record source of the form.
You will want to create controls for each of the fields in the recordset and
bind the controls to the fields in the recordset.

Now, to populate the controls on the form from the combo, use the combo's
After Update event for that. Combo boxes have a Column property that is zero
based. That is, the first column is referred to as Column(0). Don't confuse
this with the Bound Column property of the combo, it is one based (Thank you
Microsoft).

So assuming you have Last, First, Middle in your combo columns, the Row
Source would look something like:
SELECT [LAST], [FIRST], [MI] FROM tblEmployee;

You would code something like this in the combo's after update event:

With Me
.txtLastName = .cboEmployees.Column(0)
.txtFirstName = .cboEmployees.Column(1)
.txtMiddle = .cboEmployees.Column(2)
End With

Okay, that should get you started. Post back when you hit bumps.

 

[Guest]

Dave
That was a great help. I now have the form setup how I want it.

Step 2: How do I take the information on the form and save it as a new
unique record? Currently I have a tblAssignmentDetails that has a field for
every control on my frmEntry. This is the table I would like the form to save
to, creating a new record. Right now the form does not appear to do anything
with the data that I enter, at least I can't find the record on any of my
tables after entry.


Also, I would like to have a save and clear button on the bottom of the form
so that Access will not save data as it's entered. If I only fill out part of
the form and move to the next record or exit, Access has already created a
new record that will only be partially filled out. With a Save button it will
allow the data entry person to review their form or clear it if necessary
before Access saves the record.

Justin

point you in the right direction....hhmmmm....okay, go North

Okay. I don't know if any of the combo boxes you are planning to use will
depend on values in other combo boxes. This is a technique called "Cascading
Combos". What that does is allow you to filter succeeding combos based on
the value of a previous combo. This will, if applicable, shorten the list
the user has to choose from .

Now to the meat. I will give high overviews of the technique first, and we
can refine it as we go. Let's start with the Employee combo as an example.
But first, lets start with the record that will be created from the form.
You will want to make the table or (preferrabley) a query based on the table
you will be updating the record source of the form.
You will want to create controls for each of the fields in the recordset and
bind the controls to the fields in the recordset.

Now, to populate the controls on the form from the combo, use the combo's
After Update event for that. Combo boxes have a Column property that is zero
based. That is, the first column is referred to as Column(0). Don't confuse
this with the Bound Column property of the combo, it is one based (Thank you
Microsoft).

So assuming you have Last, First, Middle in your combo columns, the Row
Source would look something like:
SELECT [LAST], [FIRST], [MI] FROM tblEmployee;

You would code something like this in the combo's after update event:

With Me
.txtLastName = .cboEmployees.Column(0)
.txtFirstName = .cboEmployees.Column(1)
.txtMiddle = .cboEmployees.Column(2)
End With

Okay, that should get you started. Post back when you hit bumps.


--
Dave Hargis, Microsoft Access MVP

It's been a while, but if I can get pointed in the right direction I should
be able to get there...eventually!

 

[Guest]

With a bound form, you don't have to do anything to update the table. When
you change to a different record, move from a form to a subform, make a
different form the active form, or close the form, the form's current record
will be updated.

Now, there is one thing important to know. When you create a new record,
you will see it in your form's recordset, but it is not yet in the table. To
cause the record you create in the form to be inserted in the table, you
either have to close the form or requery the form. So if you are adding a
record in the form the opening the table to see if it is there, you will not
see it.

But, if that is not the case, check a couple of things.
The form's record source is the table or a query based on the table
Each field in the table/query is bound to a control on the form.

Let's get this part working, then we can move on to validating the data.
--
Dave Hargis, Microsoft Access MVP

Dave
That was a great help. I now have the form setup how I want it.

Step 2: How do I take the information on the form and save it as a new
unique record? Currently I have a tblAssignmentDetails that has a field for
every control on my frmEntry. This is the table I would like the form to save
to, creating a new record. Right now the form does not appear to do anything
with the data that I enter, at least I can't find the record on any of my
tables after entry.


Also, I would like to have a save and clear button on the bottom of the form
so that Access will not save data as it's entered. If I only fill out part of
the form and move to the next record or exit, Access has already created a
new record that will only be partially filled out. With a Save button it will
allow the data entry person to review their form or clear it if necessary
before Access saves the record.

Justin

point you in the right direction....hhmmmm....okay, go North

Okay. I don't know if any of the combo boxes you are planning to use will
depend on values in other combo boxes. This is a technique called "Cascading
Combos". What that does is allow you to filter succeeding combos based on
the value of a previous combo. This will, if applicable, shorten the list
the user has to choose from .

Now to the meat. I will give high overviews of the technique first, and we
can refine it as we go. Let's start with the Employee combo as an example.
But first, lets start with the record that will be created from the form.
You will want to make the table or (preferrabley) a query based on the table
you will be updating the record source of the form.
You will want to create controls for each of the fields in the recordset and
bind the controls to the fields in the recordset.

Now, to populate the controls on the form from the combo, use the combo's
After Update event for that. Combo boxes have a Column property that is zero
based. That is, the first column is referred to as Column(0). Don't confuse
this with the Bound Column property of the combo, it is one based (Thank you
Microsoft).

So assuming you have Last, First, Middle in your combo columns, the Row
Source would look something like:
SELECT [LAST], [FIRST], [MI] FROM tblEmployee;

You would code something like this in the combo's after update event:

With Me
.txtLastName = .cboEmployees.Column(0)
.txtFirstName = .cboEmployees.Column(1)
.txtMiddle = .cboEmployees.Column(2)
End With

Okay, that should get you started. Post back when you hit bumps.


--
Dave Hargis, Microsoft Access MVP

It's been a while, but if I can get pointed in the right direction I should
be able to get there...eventually!

:

Yes, you can do that. It will take some VBA. Do you know VBA?
--
Dave Hargis, Microsoft Access MVP


:

I'm new to Access and need to know if I can create a form that uses mulitple
combo boxes each based on a seperate table, to create a unique NEW record
that will be stored on yet another "record" table.

To explain the purpose of the form:

The form will be used to track employees (thousands of them) at each of
their various project sites (a couple hundred). Each employee may work at
multiple projects and each project will obviously have many different
employees. The form will be used to enter new assignment details, like length
at the assignment, managers name, etc. The idea is to prevent having to enter
all of the employee or project data into the database everytime there is a
new assignment. Instead you would select the employee Number and Project
Number and the details would auto populate.

How I want the form to work:

The form will have an employee number combo box associated with Last name,
First name, Middle Initial. You select the employees number and last, first,
middle will auto populate. Then there will be a project number combo box that
will auto populate with the city, state, etc.. Finally, there will be various
blank fields on the form that will be for the data entry person to fill out
with details specific to the selected employee working at the selectd project
site. The blank fields will be for Managers name, length at the assignment,
etc.. When the data entry person is done there should be a new record based
on the Employee Number, Project Number, misc details.

Sorry to be so long winded! Any ideas would help. I have created all the
tables and relationships, but the form has more issues than I care to go into.

Thanks,
Justin

 

[Guest]

ok. I did not have the control source set properly. Now it is working and the
form is saving a new record. However one of my fields (projected end date of
project) on the entry form is coded to take take the start date field and the
length at project field and calculate the projected end date. (Startdate +
length (in months) = Projected end date.)

Here is the code;

=DateAdd("m",[length],[start_date])

this is in the control source for the projected end date field, so I can't
point it at one of the forms record source.

any work arounds??

Justin

With a bound form, you don't have to do anything to update the table. When
you change to a different record, move from a form to a subform, make a
different form the active form, or close the form, the form's current record
will be updated.

Now, there is one thing important to know. When you create a new record,
you will see it in your form's recordset, but it is not yet in the table. To
cause the record you create in the form to be inserted in the table, you
either have to close the form or requery the form. So if you are adding a
record in the form the opening the table to see if it is there, you will not
see it.

But, if that is not the case, check a couple of things.
The form's record source is the table or a query based on the table
Each field in the table/query is bound to a control on the form.

Let's get this part working, then we can move on to validating the data.
--
Dave Hargis, Microsoft Access MVP

Dave
That was a great help. I now have the form setup how I want it.

Step 2: How do I take the information on the form and save it as a new
unique record? Currently I have a tblAssignmentDetails that has a field for
every control on my frmEntry. This is the table I would like the form to save
to, creating a new record. Right now the form does not appear to do anything
with the data that I enter, at least I can't find the record on any of my
tables after entry.


Also, I would like to have a save and clear button on the bottom of the form
so that Access will not save data as it's entered. If I only fill out part of
the form and move to the next record or exit, Access has already created a
new record that will only be partially filled out. With a Save button it will
allow the data entry person to review their form or clear it if necessary
before Access saves the record.

Justin

point you in the right direction....hhmmmm....okay, go North

Okay. I don't know if any of the combo boxes you are planning to use will
depend on values in other combo boxes. This is a technique called "Cascading
Combos". What that does is allow you to filter succeeding combos based on
the value of a previous combo. This will, if applicable, shorten the list
the user has to choose from .

Now to the meat. I will give high overviews of the technique first, and we
can refine it as we go. Let's start with the Employee combo as an example.
But first, lets start with the record that will be created from the form.
You will want to make the table or (preferrabley) a query based on the table
you will be updating the record source of the form.
You will want to create controls for each of the fields in the recordset and
bind the controls to the fields in the recordset.

Now, to populate the controls on the form from the combo, use the combo's
After Update event for that. Combo boxes have a Column property that is zero
based. That is, the first column is referred to as Column(0). Don't confuse
this with the Bound Column property of the combo, it is one based (Thank you
Microsoft).

So assuming you have Last, First, Middle in your combo columns, the Row
Source would look something like:
SELECT [LAST], [FIRST], [MI] FROM tblEmployee;

You would code something like this in the combo's after update event:

With Me
.txtLastName = .cboEmployees.Column(0)
.txtFirstName = .cboEmployees.Column(1)
.txtMiddle = .cboEmployees.Column(2)
End With

Okay, that should get you started. Post back when you hit bumps.


--
Dave Hargis, Microsoft Access MVP


:

It's been a while, but if I can get pointed in the right direction I should
be able to get there...eventually!

:

Yes, you can do that. It will take some VBA. Do you know VBA?
--
Dave Hargis, Microsoft Access MVP


:

I'm new to Access and need to know if I can create a form that uses mulitple
combo boxes each based on a seperate table, to create a unique NEW record
that will be stored on yet another "record" table.

To explain the purpose of the form:

The form will be used to track employees (thousands of them) at each of
their various project sites (a couple hundred). Each employee may work at
multiple projects and each project will obviously have many different
employees. The form will be used to enter new assignment details, like length
at the assignment, managers name, etc. The idea is to prevent having to enter
all of the employee or project data into the database everytime there is a
new assignment. Instead you would select the employee Number and Project
Number and the details would auto populate.

How I want the form to work:

The form will have an employee number combo box associated with Last name,
First name, Middle Initial. You select the employees number and last, first,
middle will auto populate. Then there will be a project number combo box that
will auto populate with the city, state, etc.. Finally, there will be various
blank fields on the form that will be for the data entry person to fill out
with details specific to the selected employee working at the selectd project
site. The blank fields will be for Managers name, length at the assignment,
etc.. When the data entry person is done there should be a new record based
on the Employee Number, Project Number, misc details.

Sorry to be so long winded! Any ideas would help. I have created all the
tables and relationships, but the form has more issues than I care to go into.

Thanks,
Justin

 

[Guest]

Okay, we can fix that. Take it out of the control source and move it to a
private function. At the very top of your form module (the code attached to
the form), and just after all the Option statements, create this sub:

Private Sub CalcEndDate()
If Not IsNull(Me.[start_date]) And Not IsNull(Me.[length]
Me.txtProjectedEndDate = DateAdd("m",Me.[length],Me.[start_date])
End If
End Sub

Now, use the after update event for lenght and start_date to call the
function:

Private Sub start_date_AfterUpdate()
Call CalcEndDate
End Sub

Since you don't know in what order the user will enter data or whether they
will visit any control at all, the Sub above will only calculate the value
when both controls have a value.

A word about naming, before you go any further:
I am assuming the [length] and [start_date] are the names of controls on
your form. They may also be the names of fields in your table if you created
the form using a wizard. The wizard has a very bad habit. It names the
controls the same name as the field it is bound to. This can cause a lot of
confusion. They really should not be the same name.

User defined names should only contain letters, numbers, and the underscore.
Never use spaces or any special character other than the underscore in names.
Never use any Access reserved word (Date, Month, Name, etc.). They can
confuse Access. If you have a name that does not conform to the above rules,
enclose it in brackets to avoid problems. [Date]

Here is a link to a site that will give you some info on good naming
conventions:

http://www.mvps.org/access/general/gen0012.htm

Using standard naming conventions makes your life easier and is doing a big
favor to the person who has to come behind you to support your code when you
win the Lottery

Good work, We are making progress.

--
Dave Hargis, Microsoft Access MVP

ok. I did not have the control source set properly. Now it is working and the
form is saving a new record. However one of my fields (projected end date of
project) on the entry form is coded to take take the start date field and the
length at project field and calculate the projected end date. (Startdate +
length (in months) = Projected end date.)

Here is the code;

=DateAdd("m",[length],[start_date])

this is in the control source for the projected end date field, so I can't
point it at one of the forms record source.

any work arounds??

Justin

With a bound form, you don't have to do anything to update the table. When
you change to a different record, move from a form to a subform, make a
different form the active form, or close the form, the form's current record
will be updated.

Now, there is one thing important to know. When you create a new record,
you will see it in your form's recordset, but it is not yet in the table. To
cause the record you create in the form to be inserted in the table, you
either have to close the form or requery the form. So if you are adding a
record in the form the opening the table to see if it is there, you will not
see it.

But, if that is not the case, check a couple of things.
The form's record source is the table or a query based on the table
Each field in the table/query is bound to a control on the form.

Let's get this part working, then we can move on to validating the data.
--
Dave Hargis, Microsoft Access MVP

Dave
That was a great help. I now have the form setup how I want it.

Step 2: How do I take the information on the form and save it as a new
unique record? Currently I have a tblAssignmentDetails that has a field for
every control on my frmEntry. This is the table I would like the form to save
to, creating a new record. Right now the form does not appear to do anything
with the data that I enter, at least I can't find the record on any of my
tables after entry.


Also, I would like to have a save and clear button on the bottom of the form
so that Access will not save data as it's entered. If I only fill out part of
the form and move to the next record or exit, Access has already created a
new record that will only be partially filled out. With a Save button it will
allow the data entry person to review their form or clear it if necessary
before Access saves the record.

Justin

:

point you in the right direction....hhmmmm....okay, go North

Okay. I don't know if any of the combo boxes you are planning to use will
depend on values in other combo boxes. This is a technique called "Cascading
Combos". What that does is allow you to filter succeeding combos based on
the value of a previous combo. This will, if applicable, shorten the list
the user has to choose from .

Now to the meat. I will give high overviews of the technique first, and we
can refine it as we go. Let's start with the Employee combo as an example.
But first, lets start with the record that will be created from the form.
You will want to make the table or (preferrabley) a query based on the table
you will be updating the record source of the form.
You will want to create controls for each of the fields in the recordset and
bind the controls to the fields in the recordset.

Now, to populate the controls on the form from the combo, use the combo's
After Update event for that. Combo boxes have a Column property that is zero
based. That is, the first column is referred to as Column(0). Don't confuse
this with the Bound Column property of the combo, it is one based (Thank you
Microsoft).

So assuming you have Last, First, Middle in your combo columns, the Row
Source would look something like:
SELECT [LAST], [FIRST], [MI] FROM tblEmployee;

You would code something like this in the combo's after update event:

With Me
.txtLastName = .cboEmployees.Column(0)
.txtFirstName = .cboEmployees.Column(1)
.txtMiddle = .cboEmployees.Column(2)
End With

Okay, that should get you started. Post back when you hit bumps.


--
Dave Hargis, Microsoft Access MVP


:

It's been a while, but if I can get pointed in the right direction I should
be able to get there...eventually!

:

Yes, you can do that. It will take some VBA. Do you know VBA?
--
Dave Hargis, Microsoft Access MVP


:

I'm new to Access and need to know if I can create a form that uses mulitple
combo boxes each based on a seperate table, to create a unique NEW record
that will be stored on yet another "record" table.

To explain the purpose of the form:

The form will be used to track employees (thousands of them) at each of
their various project sites (a couple hundred). Each employee may work at
multiple projects and each project will obviously have many different
employees. The form will be used to enter new assignment details, like length
at the assignment, managers name, etc. The idea is to prevent having to enter
all of the employee or project data into the database everytime there is a
new assignment. Instead you would select the employee Number and Project
Number and the details would auto populate.

How I want the form to work:

The form will have an employee number combo box associated with Last name,
First name, Middle Initial. You select the employees number and last, first,
middle will auto populate. Then there will be a project number combo box that
will auto populate with the city, state, etc.. Finally, there will be various
blank fields on the form that will be for the data entry person to fill out
with details specific to the selected employee working at the selectd project
site. The blank fields will be for Managers name, length at the assignment,
etc.. When the data entry person is done there should be a new record based
on the Employee Number, Project Number, misc details.

Sorry to be so long winded! Any ideas would help. I have created all the
tables and relationships, but the form has more issues than I care to go into.

Thanks,
Justin

 

[Guest]

Phew! Ok.. All is working great! Although I'm afraid I did use the wizards
for my form creation and will need to rename some of my controls, etc.. to
make things more clear. I also like the new ProjectedEndDate coding. The
field was showing #Name or some other error until data was entered into
start_date and length, now it looks good. It stays blank until data is
entered. Your making me look good Dave! I think I'll owe you a coffee when
this is over, or a percentage of my lottery winnings, which ever you prefer.


Justin

Okay, we can fix that. Take it out of the control source and move it to a
private function. At the very top of your form module (the code attached to
the form), and just after all the Option statements, create this sub:

Private Sub CalcEndDate()
If Not IsNull(Me.[start_date]) And Not IsNull(Me.[length]
Me.txtProjectedEndDate = DateAdd("m",Me.[length],Me.[start_date])
End If
End Sub

Now, use the after update event for lenght and start_date to call the
function:

Private Sub start_date_AfterUpdate()
Call CalcEndDate
End Sub

Since you don't know in what order the user will enter data or whether they
will visit any control at all, the Sub above will only calculate the value
when both controls have a value.

A word about naming, before you go any further:
I am assuming the [length] and [start_date] are the names of controls on
your form. They may also be the names of fields in your table if you created
the form using a wizard. The wizard has a very bad habit. It names the
controls the same name as the field it is bound to. This can cause a lot of
confusion. They really should not be the same name.

User defined names should only contain letters, numbers, and the underscore.
Never use spaces or any special character other than the underscore in names.
Never use any Access reserved word (Date, Month, Name, etc.). They can
confuse Access. If you have a name that does not conform to the above rules,
enclose it in brackets to avoid problems. [Date]

Here is a link to a site that will give you some info on good naming
conventions:

http://www.mvps.org/access/general/gen0012.htm

Using standard naming conventions makes your life easier and is doing a big
favor to the person who has to come behind you to support your code when you
win the Lottery

Good work, We are making progress.

--
Dave Hargis, Microsoft Access MVP

ok. I did not have the control source set properly. Now it is working and the
form is saving a new record. However one of my fields (projected end date of
project) on the entry form is coded to take take the start date field and the
length at project field and calculate the projected end date. (Startdate +
length (in months) = Projected end date.)

Here is the code;

=DateAdd("m",[length],[start_date])

this is in the control source for the projected end date field, so I can't
point it at one of the forms record source.

any work arounds??

Justin

With a bound form, you don't have to do anything to update the table. When
you change to a different record, move from a form to a subform, make a
different form the active form, or close the form, the form's current record
will be updated.

Now, there is one thing important to know. When you create a new record,
you will see it in your form's recordset, but it is not yet in the table. To
cause the record you create in the form to be inserted in the table, you
either have to close the form or requery the form. So if you are adding a
record in the form the opening the table to see if it is there, you will not
see it.

But, if that is not the case, check a couple of things.
The form's record source is the table or a query based on the table
Each field in the table/query is bound to a control on the form.

Let's get this part working, then we can move on to validating the data.
--
Dave Hargis, Microsoft Access MVP


:

Dave
That was a great help. I now have the form setup how I want it.

Step 2: How do I take the information on the form and save it as a new
unique record? Currently I have a tblAssignmentDetails that has a field for
every control on my frmEntry. This is the table I would like the form to save
to, creating a new record. Right now the form does not appear to do anything
with the data that I enter, at least I can't find the record on any of my
tables after entry.


Also, I would like to have a save and clear button on the bottom of the form
so that Access will not save data as it's entered. If I only fill out part of
the form and move to the next record or exit, Access has already created a
new record that will only be partially filled out. With a Save button it will
allow the data entry person to review their form or clear it if necessary
before Access saves the record.

Justin

:

point you in the right direction....hhmmmm....okay, go North

Okay. I don't know if any of the combo boxes you are planning to use will
depend on values in other combo boxes. This is a technique called "Cascading
Combos". What that does is allow you to filter succeeding combos based on
the value of a previous combo. This will, if applicable, shorten the list
the user has to choose from .

Now to the meat. I will give high overviews of the technique first, and we
can refine it as we go. Let's start with the Employee combo as an example.
But first, lets start with the record that will be created from the form.
You will want to make the table or (preferrabley) a query based on the table
you will be updating the record source of the form.
You will want to create controls for each of the fields in the recordset and
bind the controls to the fields in the recordset.

Now, to populate the controls on the form from the combo, use the combo's
After Update event for that. Combo boxes have a Column property that is zero
based. That is, the first column is referred to as Column(0). Don't confuse
this with the Bound Column property of the combo, it is one based (Thank you
Microsoft).

So assuming you have Last, First, Middle in your combo columns, the Row
Source would look something like:
SELECT [LAST], [FIRST], [MI] FROM tblEmployee;

You would code something like this in the combo's after update event:

With Me
.txtLastName = .cboEmployees.Column(0)
.txtFirstName = .cboEmployees.Column(1)
.txtMiddle = .cboEmployees.Column(2)
End With

Okay, that should get you started. Post back when you hit bumps.


--
Dave Hargis, Microsoft Access MVP


:

It's been a while, but if I can get pointed in the right direction I should
be able to get there...eventually!

:

Yes, you can do that. It will take some VBA. Do you know VBA?
--
Dave Hargis, Microsoft Access MVP


:

I'm new to Access and need to know if I can create a form that uses mulitple
combo boxes each based on a seperate table, to create a unique NEW record
that will be stored on yet another "record" table.

To explain the purpose of the form:

The form will be used to track employees (thousands of them) at each of
their various project sites (a couple hundred). Each employee may work at
multiple projects and each project will obviously have many different
employees. The form will be used to enter new assignment details, like length
at the assignment, managers name, etc. The idea is to prevent having to enter
all of the employee or project data into the database everytime there is a
new assignment. Instead you would select the employee Number and Project
Number and the details would auto populate.

How I want the form to work:

The form will have an employee number combo box associated with Last name,
First name, Middle Initial. You select the employees number and last, first,
middle will auto populate. Then there will be a project number combo box that
will auto populate with the city, state, etc.. Finally, there will be various
blank fields on the form that will be for the data entry person to fill out
with details specific to the selected employee working at the selectd project
site. The blank fields will be for Managers name, length at the assignment,
etc.. When the data entry person is done there should be a new record based
on the Employee Number, Project Number, misc details.

Sorry to be so long winded! Any ideas would help. I have created all the
tables and relationships, but the form has more issues than I care to go into.

Thanks,
Justin

 

[Guest]

I'll take the coffee I know you will have that.

As you progress in your knowledge of Access, you will begin to forget that
wizards exist.
--
Dave Hargis, Microsoft Access MVP

Phew! Ok.. All is working great! Although I'm afraid I did use the wizards
for my form creation and will need to rename some of my controls, etc.. to
make things more clear. I also like the new ProjectedEndDate coding. The
field was showing #Name or some other error until data was entered into
start_date and length, now it looks good. It stays blank until data is
entered. Your making me look good Dave! I think I'll owe you a coffee when
this is over, or a percentage of my lottery winnings, which ever you prefer.


Justin

Okay, we can fix that. Take it out of the control source and move it to a
private function. At the very top of your form module (the code attached to
the form), and just after all the Option statements, create this sub:

Private Sub CalcEndDate()
If Not IsNull(Me.[start_date]) And Not IsNull(Me.[length]
Me.txtProjectedEndDate = DateAdd("m",Me.[length],Me.[start_date])
End If
End Sub

Now, use the after update event for lenght and start_date to call the
function:

Private Sub start_date_AfterUpdate()
Call CalcEndDate
End Sub

Since you don't know in what order the user will enter data or whether they
will visit any control at all, the Sub above will only calculate the value
when both controls have a value.

A word about naming, before you go any further:
I am assuming the [length] and [start_date] are the names of controls on
your form. They may also be the names of fields in your table if you created
the form using a wizard. The wizard has a very bad habit. It names the
controls the same name as the field it is bound to. This can cause a lot of
confusion. They really should not be the same name.

User defined names should only contain letters, numbers, and the underscore.
Never use spaces or any special character other than the underscore in names.
Never use any Access reserved word (Date, Month, Name, etc.). They can
confuse Access. If you have a name that does not conform to the above rules,
enclose it in brackets to avoid problems. [Date]

Here is a link to a site that will give you some info on good naming
conventions:

http://www.mvps.org/access/general/gen0012.htm

Using standard naming conventions makes your life easier and is doing a big
favor to the person who has to come behind you to support your code when you
win the Lottery

Good work, We are making progress.

--
Dave Hargis, Microsoft Access MVP

ok. I did not have the control source set properly. Now it is working and the
form is saving a new record. However one of my fields (projected end date of
project) on the entry form is coded to take take the start date field and the
length at project field and calculate the projected end date. (Startdate +
length (in months) = Projected end date.)

Here is the code;

=DateAdd("m",[length],[start_date])

this is in the control source for the projected end date field, so I can't
point it at one of the forms record source.

any work arounds??

Justin

:

With a bound form, you don't have to do anything to update the table. When
you change to a different record, move from a form to a subform, make a
different form the active form, or close the form, the form's current record
will be updated.

Now, there is one thing important to know. When you create a new record,
you will see it in your form's recordset, but it is not yet in the table. To
cause the record you create in the form to be inserted in the table, you
either have to close the form or requery the form. So if you are adding a
record in the form the opening the table to see if it is there, you will not
see it.

But, if that is not the case, check a couple of things.
The form's record source is the table or a query based on the table
Each field in the table/query is bound to a control on the form.

Let's get this part working, then we can move on to validating the data.
--
Dave Hargis, Microsoft Access MVP


:

Dave
That was a great help. I now have the form setup how I want it.

Step 2: How do I take the information on the form and save it as a new
unique record? Currently I have a tblAssignmentDetails that has a field for
every control on my frmEntry. This is the table I would like the form to save
to, creating a new record. Right now the form does not appear to do anything
with the data that I enter, at least I can't find the record on any of my
tables after entry.


Also, I would like to have a save and clear button on the bottom of the form
so that Access will not save data as it's entered. If I only fill out part of
the form and move to the next record or exit, Access has already created a
new record that will only be partially filled out. With a Save button it will
allow the data entry person to review their form or clear it if necessary
before Access saves the record.

Justin

:

point you in the right direction....hhmmmm....okay, go North

Okay. I don't know if any of the combo boxes you are planning to use will
depend on values in other combo boxes. This is a technique called "Cascading
Combos". What that does is allow you to filter succeeding combos based on
the value of a previous combo. This will, if applicable, shorten the list
the user has to choose from .

Now to the meat. I will give high overviews of the technique first, and we
can refine it as we go. Let's start with the Employee combo as an example.
But first, lets start with the record that will be created from the form.
You will want to make the table or (preferrabley) a query based on the table
you will be updating the record source of the form.
You will want to create controls for each of the fields in the recordset and
bind the controls to the fields in the recordset.

Now, to populate the controls on the form from the combo, use the combo's
After Update event for that. Combo boxes have a Column property that is zero
based. That is, the first column is referred to as Column(0). Don't confuse
this with the Bound Column property of the combo, it is one based (Thank you
Microsoft).

So assuming you have Last, First, Middle in your combo columns, the Row
Source would look something like:
SELECT [LAST], [FIRST], [MI] FROM tblEmployee;

You would code something like this in the combo's after update event:

With Me
.txtLastName = .cboEmployees.Column(0)
.txtFirstName = .cboEmployees.Column(1)
.txtMiddle = .cboEmployees.Column(2)
End With

Okay, that should get you started. Post back when you hit bumps.


--
Dave Hargis, Microsoft Access MVP


:

It's been a while, but if I can get pointed in the right direction I should
be able to get there...eventually!

:

Yes, you can do that. It will take some VBA. Do you know VBA?
--
Dave Hargis, Microsoft Access MVP


:

I'm new to Access and need to know if I can create a form that uses mulitple
combo boxes each based on a seperate table, to create a unique NEW record
that will be stored on yet another "record" table.

To explain the purpose of the form:

The form will be used to track employees (thousands of them) at each of
their various project sites (a couple hundred). Each employee may work at
multiple projects and each project will obviously have many different
employees. The form will be used to enter new assignment details, like length
at the assignment, managers name, etc. The idea is to prevent having to enter
all of the employee or project data into the database everytime there is a
new assignment. Instead you would select the employee Number and Project
Number and the details would auto populate.

How I want the form to work:

The form will have an employee number combo box associated with Last name,
First name, Middle Initial. You select the employees number and last, first,
middle will auto populate. Then there will be a project number combo box that
will auto populate with the city, state, etc.. Finally, there will be various
blank fields on the form that will be for the data entry person to fill out
with details specific to the selected employee working at the selectd project
site. The blank fields will be for Managers name, length at the assignment,
etc.. When the data entry person is done there should be a new record based
on the Employee Number, Project Number, misc details.

Sorry to be so long winded! Any ideas would help. I have created all the
tables and relationships, but the form has more issues than I care to go into.

Thanks,
Justin