I would like to open a form to display the contents of a table. The table is like this

PK = DivisionID

DivisionID: DivisionName

1000 General Conditions
2000 Site Work
3000 Concrete

I would like to be able to display the 3 records individually so they can be selected by a check box (instead of having them all in a drop down combo box) or not if they are not being used. So I would like the form to look like this

Select a Division

General Conditions [] check box
Site Work [] check box
Concrete [] check box

The Divisions selected will them be put into another table, relating to Job Information. Is this possible or can it not be done from the same table? Do I have to create a table for each individual Division Name?

What are you then wanting to do with this information?

Jim

The user will be selecting these Divisions if they are included in a Job i.e. if a job is remodeling a kitchen then they may want to include General Conditions and Site Work, but not Concrete. When they select the Division I would like to insert them into a table relating to a Job, so we know what Divisions are included in the Job. The PK of the Job table is JobNumber. I have the following tables:

tblJobInfo

JobNumber (PK)
JobName
JobAddress

tblDivisionInfo

DivisionID (PK)
DivisionName

tblJobDivisionInfo

JobNumber (FK tblJobInfo)
DivisionID (FK tblDivisionInfo)

The more I look at it what I really need is to be able to have each DivisionID displayed individually, have a text box beside it that when it is checked and the user clicks a button to say something like 'Include Divisions in Job', that the JobNumber and DivisionID will go into tblJobDivisionInfo. Then the user will go back to FormJobInfo and the information in tblJobDivisionInfo will be displayed in a subform. I will need to pass JobNumber from the FormJobInfo where the user will have entered a JobNumber, JobName and JobAddress and these go into tblJobInfo.

Make sense?

Hi

You want something like the attached which has a SubForm to record each of the Divisions required for the Job

Hope this helps?

Mike

I appreciate your taking the time to do that example out. I'm not seeing how you got the user to add the Divisions though? How they make the selection. Is that via a combo box? I was hoping to do it by having check boxes.

We may have a few design points here.

Job number should not be your primary key, it should be a generated number by yourself using the NZ(DMax approach on the database.

We also would not do what you are suggesting. These would be catgories the we would add the the job using a combo box approach, not the checkbox approach.

Jim

The JobNumber is determined by various things e.g. 8-424NY would be got by
8 (the year 2008)
-
4 (the month)
24 (day in the month)
NY (for New York office)

This is the date the job contract was bid by the company and the Office who bid the job. The whole point of not using the combo approach is because you can only chose 1 instance of a Division. With the combo box it would allow the user to select the same Division more than once which would lead to a mistake. The checkbox would only allow for 1 instance. I have got to the point of having the DivisionID and DivisionName to be selected by a checkbox by including DivisionSelected as an extra field. Then displaying the form as a Continuous form the user could see all the Division Names and select them one at a time, also in the future we could add divisions by using the NotInList function. Correct?

If I am correct in my thought process above could someone confirm this and I will explain where I am going after this. I don't want to create too much confusion for myself and everyone else right now.

Hi

As Jim has said the correct structure for this is the use of a ComboBox and NOT a CheckBox approach.

In my example you can see all of the Divisions selected - does this not suit?

With the checkbox approach you are creating Fields in a table with a Yes/No Field


Mike

I do have it functioning the way you showed in the sample, I had this done originally. I thought with the checkbox scenario I could say something like 'if checked then insert DivisionID into tblJobDivisionInfo' along with the JobNumber. I know this isn't VB or even SQL but you should see what I mean. I guess I'm flogging a dead horse trying to makeup a way of doing it. Let me continue my scenario further. After the user has selected a DivisionID, they will then select 1 or more (up to 3) subdivisions for this DivisionID e.g.

JobNumber: 8-424NY

DivisionID: 1000 (General Conditions)
SubDivisionID: 1010 (Labor)
SUbDivisionID: 1020 (Supervision)
SubDivisionID: 1090 (Office Staff Hours)

DivisionID: 3000 (Concrete)
SubDivisionID: 3010 (Forming)
SubDivisionID: 3050 (Pour Concrete)

To do this I need to base a combo box (ComboSubDIvisionID) on the results of the combo box ComboboxDivisionID. Yes? Then I need to say who the Contractors are who we are going to get to do the work. So for each SubDivisionID I will need to allocate a ContractorID.

This is a lot of repetitive combo box work, correct? Or is there a different way to attack this? I'm hoping your experience will show me a good format or way of doing this.

Thank you both though for getting me off the track of checkboxes. Any ideas to the rest of it? MOre combo boxes?

Hi

From the description you could do something like the example attached

Mike

Ok, now that is what I'm after. Believe it or not, that is pretty much where I was before I went with the checkbox scenario. Only thing I didn't have was the subdivisions in a second subform. Now, your example shows me how it looks for 1 Division. If I had another division, how would that work? Would it be another combobox under the first Division in the Divisions subform? If so, how do I add the subdivisions? How do I distinguish which SubDivision belongs to which Division? Will it only show the subdivisions for the Division selected in the Divisions subform? Are all the Subdivisions listed in the SubDivisions subform continuously? If you could give me an editable version that would be great, but I do understand you not wanting to do this. I only want to see what way it functions.

Hi

The Form is set as a Main Form with A SubForm linked to another Subform

You enter or select a JobName using the ComboBox in the Main Form

If you are entering another Division for Job that has already begun then you use the Add New Button this allows you to ener a New Division and also enter as many SubDivisions for the Division Chosen

Hope this makes Sence?

Mike

Hey,
Yeah, that makes sense and is what I thought it would do. Is there anyway I am able to group the Subdivisions to their related Division to show what Subdivisions are included in each Division as opposed to having am entire list of Divisions in one table and an entire list of Subdivisions is another? I know I'm getting complicated again, but its kind of the point of what I'm trying to acheive; making it easy to see what Subdivisions are included in each individual Division.
Thanks

Disregard last comment. I couldn't edit your example previous to that, now I can. I see what the sample does. Ok, thats 75% of what I want to do. LOL, am I being annoying or what? Is there anyway that I can view the entire List of Divisions and Subdivisions that have been selected at the same time on the Form? It would be easier for the user to see this and reduce error making if they ommited a Division because they couldn't see everything?
Could I have another subform that will show what the user has selected i.e. Division name, selected SubDivision, in a format that keeps them together? And have this being created and displayed everytime the user adds a new Division? I guess the Add Record/Division button would have to requery this new subform? Am I right?

Hi

The attached example gives you a list of Divisions & SubDivisions selected for a Job To Date

Is this what you need?

Mike

Yep. Thats pretty much it. Is there a way in which I can update the subform 'Divisions && SubDivisions Selected to Date' as the user adds new Divisions and SubDivisions to those subforms? Say something like when they begin to create a new record, the subform 'Divisions && SubDivisions Selected to Date' will refresh/requery or whatever the term is? Maybe click a button to do this?
I really appreciate your help with this, you've given me great assistance!

Hey, ok so now I'm cooking with gas. I would like to be able to do something else. I want to base the values shown in the SubDivision combobox on the value selected in the DivisionID combobox on the other subform. I can do this when they are on the same for with the AfterUpdate function but how do I refer to the comboboxes when they are on different forms?
Example - when they are on the same form I have this in the afterudate fuction of Division_ID

Private Sub Division_ID_AfterUpdate()
SubDivision_ID = Null
SubDivision_ID.Requery
SubDivision_ID = Me.SubDivision_ID.ItemData(0)
End Sub

Private Sub Form_Current()
SubDivision_ID.Requery
End Sub

Private Sub Form_Load()
If IsNull(Division_ID) Then
Division_ID = Me.Division_ID.ItemData(0)
Call Division_ID_AfterUpdate
End If
End Sub

then in the rowsource of SubDivision_ID I have this under Division_ID in the query

[Forms]![Job_Division_Info]![Division_ID]

I'm guessing its something like

Private Sub Division_ID_AfterUpdate()
SubDivision_ID = Null
SubDivision_ID.Requery
SubDivision_ID = Me.SubDivision_ID.ItemData(0) Change this to SubDivision_ID = Form.JobSubDivisionInfo subform.SubDivision_ID
End Sub

??? Close?

Ok, this is what I have, but I can't figure out how to get the contents of cmboSubDivision_ID in subform JobSubDivisionInfo subform to only show the values that relate to the selection in cmboDivision_ID on subform JobDivisionInfo subform

Hi

Is this what you need?

Come back with questions

Mike

Sorry, I thought you were off for the day.

Ok, I need to be able to show only the SubDivisions that relate to the Divisions i.e. when I select General Conditions from the Divisions combobox, I only want to be able to select the SubDivisions that are related to General Conditions. I have achieved this when the comboboxes are on the same form, but when they are on two subforms I can't do it.