Populate table with id of combo box selection (1 Viewer)

[P_DUBS]

Hello,

I have a table that lists processes (with process ID's)

I have a table that lists sub processes. Within my sub process table I have a process ID column whereby sub processes can be assigned to processes by inputing the relevant process ID.

I have a form linked to the process ID (user selects process and opens form to input data into various subforms - within tabs)

I have created my subform and it is linked by the process ID
I would like it to be a continuous form that shows all the sub processes selected for the process.

I am able to display this info by inserting a sub process text box.

I would like to change my text box to a combo box to allow users to add new sub process and select from the available list within my sub process table but I can't seem to get this to work!

Effectively I need the combo box selection to update the process ID column (within the sub process table) with the process ID.

 

[Pat Hartman]

Can a subprocess belong to more than one process? If so, then you need a third table to manage the many-to-many relationship.

Whether a subform is in single or continuous or datasheet view doesn't change how it works. It is still linked to the parent form with the master/child link properties. Access will automatically populate the foreign key (ProcessID in your case) so it should not appear in the subform at all.

 

[P_DUBS]

No, a sub process can only belong to one process.

I have manually assigned (via the sub process table) some sub processes to processes by inputing the process ID. This has allowed me to play about with the form and try and get it to work.

As previously mentioned text box's show all the sub processes I have manualy assigned. However when I try and change these to a combo box, on opening the sub form all the combo's show only the first sub process assigned to the parent process ID and when you click on there drop down it is blank.

 

[CyberLynx]

Is it possible for you to post a small sample DB? I just can't seem to picture this one....mind you, I am tired

.

 

[P_DUBS]

Not sure if I can due to it's current size. I can try and explain it a bit more...

User selects process, clicks 'data entry' to open a data entry form. Data entry form has a tab control with various sub forms.

The subform in question's record source is a table (sub processes) with columns including 'Sub Process' and 'Process ID'.

The subform is a continuous form with text boxes that display the sub process that have a process ID that matches the main form (data entry)

I would however like to change the text boxes to combo boxes that on openeing the subform will display all assigned subprocesses. When the user clicks the combo I would like the full list of available subprocesses to be displayed to enable them to be able change to a different sub process.

When the user adds a new record I would again like the combo to show all sub processes (as listed in the sub process table) and when selected for the process ID (picked up from the main form) to be added to the sub process table in the process ID column beside the selected sub process.

Hope that helps!

 

[CyberLynx]

Make a copy of the SubForm to test the following:

Change your Text Box to a Combo Box just as you wanted and set the following properties for it:

Column Count 2
Column Widths 0";1"
Control Source {to whatever the Control Source was for the TextBox}
Row Source Type Table/Query
Row Source "SELECT ProcessID, ProcessDescript FROM tblSubProcesses ORDER BY ProcessDescript;"
Bound Column 1
Limit To List Yes

You will need to obviously work on the Row Source SQL string to suit what you have.

.

 

[P_DUBS]

The usual reply - sorry!...

Row Source

"SELECT ProcessID, ProcessDescript FROM tblSubProcesses ORDER BY ProcessDescript;"

Can you help me with what bits I need to change.

ProcessID and tblSubProcesses I'm ok with, just unsure what the ProcessDescript bit should be changed to.

Many thanks for your help with this.

 

[CyberLynx]

I need to know...what are the field names contained within the tblSubProcesses Table?

.