MS Access Forum / Forms Programming / February 2007
Is it possible?
|
|
Thread rating:  |
Justin83716 - 30 Jan 2007 22:24 GMT 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
Klatuu - 30 Jan 2007 22:38 GMT Yes, you can do that. It will take some VBA. Do you know VBA?
 Signature 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 [quoted text clipped - 28 lines] > Thanks, > Justin Justin83716 - 30 Jan 2007 22:43 GMT 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? > [quoted text clipped - 30 lines] > > Thanks, > > Justin Klatuu - 30 Jan 2007 22:57 GMT 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.
 Signature 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! [quoted text clipped - 33 lines] > > > Thanks, > > > Justin Justin83716 - 31 Jan 2007 18:00 GMT 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 :) > [quoted text clipped - 69 lines] > > > > Thanks, > > > > Justin Klatuu - 31 Jan 2007 18:11 GMT 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.
 Signature Dave Hargis, Microsoft Access MVP
> Dave > That was a great help. I now have the form setup how I want it. [quoted text clipped - 88 lines] > > > > > Thanks, > > > > > Justin Justin83716 - 31 Jan 2007 18:32 GMT 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 [quoted text clipped - 106 lines] > > > > > > Thanks, > > > > > > Justin Klatuu - 31 Jan 2007 18:52 GMT 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.
 Signature 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 [quoted text clipped - 123 lines] > > > > > > > Thanks, > > > > > > > Justin Justin83716 - 31 Jan 2007 20:08 GMT 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 [quoted text clipped - 168 lines] > > > > > > > > Thanks, > > > > > > > > Justin Klatuu - 01 Feb 2007 13:28 GMT 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.
 Signature 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 [quoted text clipped - 179 lines] > > > > > > > > > Thanks, > > > > > > > > > Justin
|
|
|