Combo box requery; update underlying table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I've asked this ? b4 and was able to get it to work with your help, but now
can't seem get it right in a new db.

I have a combo box control name cboUserID.
This autofills after combo box [Originator] is selected. The display is
correct, but the value is not retained in the underlying table.

The form is based on a query.
Row Source: SELECT DISTINCT ProjectTeam.[PT UserID] FROM ProjectTeam;

I need this value retained in the table.

Thanks! Mary
 
Is the field you are trying to store the value in available in the query? If
so, open the form in design mode and go to the Properties for the combo box.
Set the Control Source of the combo box to the field you want the value
stored in and set the Bound Column to the column in the combo box that
contains that value. Judging by the Select statement you are using as a Row
Source, the Bound Column should be 1 since you only have one column in the
combo box.
 
Wayne, the field I'm trying to store is available in the query only after the
Originator field is filled in. I have the field [UserID] from my query as
the Control Source and the Bound Column is 1, but it's still not retaining
the value. Thanks, Mary

Wayne Morgan said:
Is the field you are trying to store the value in available in the query? If
so, open the form in design mode and go to the Properties for the combo box.
Set the Control Source of the combo box to the field you want the value
stored in and set the Bound Column to the column in the combo box that
contains that value. Judging by the Select statement you are using as a Row
Source, the Bound Column should be 1 since you only have one column in the
combo box.

--
Wayne Morgan
MS Access MVP


Mary said:
I've asked this ? b4 and was able to get it to work with your help, but
now
can't seem get it right in a new db.

I have a combo box control name cboUserID.
This autofills after combo box [Originator] is selected. The display is
correct, but the value is not retained in the underlying table.

The form is based on a query.
Row Source: SELECT DISTINCT ProjectTeam.[PT UserID] FROM ProjectTeam;

I need this value retained in the table.

Thanks! Mary
Click to expand...
Click to expand...
 
What is the query being used as the Record Source for the form? Will you
post the SQL view of this query?

Is the data type the form's record source the same for this field as the
data type in the lookup table being used for the combo box's Row Source?
 
Here's the SQL:

SELECT Tasks.[TaskNumber], Tasks.[TaskName], Tasks.[TaskDescription],
Tasks.[TaskType], Tasks.[TaskActivity], ProjectTeam.[PT UserID] AS UserID,
Tasks.[TaskAssignedTo], ProjectTeam.[PT UserID], Tasks.[TaskSponsor],
Tasks.[TaskCreationDate], Tasks.[TaskDueDate], Tasks.[TaskCompleteDate],
Tasks.[TaskNotes], Tasks.[TaskEstimatedHours], Tasks.[TaskSize],
Tasks.Priority, BurkholzTeam.[PT UserID] AS [TaskAssignedToUID],
Tasks.CurrentUserID, Tasks.EntryTime
FROM (Sponsors INNER JOIN Tasks ON Sponsors.[SP Name] = Tasks.[TaskSponsor])
INNER JOIN ProjectTeam ON Tasks.[TaskAssignedTo] = ProjectTeam.[PT Name]
WITH OWNERACCESS OPTION;

They're both text fields and both limited to 6 characters.

Thanks, Mary

Wayne Morgan said:
What is the query being used as the Record Source for the form? Will you
post the SQL view of this query?

Is the data type the form's record source the same for this field as the
data type in the lookup table being used for the combo box's Row Source?

--
Wayne Morgan
MS Access MVP


Mary said:
Wayne, the field I'm trying to store is available in the query only after
the
Originator field is filled in. I have the field [UserID] from my query as
the Control Source and the Bound Column is 1, but it's still not retaining
the value.
Click to expand...
Click to expand...
 
Why do you have the lookup table, ProjectTeam, in the form's Record Source?
The normal way would be to have it in the combo box's Row Source and you
would place it in an "equivalent" field in the form's Record Source, but not
the same field that you are drawing the data from.

If you need more than one field from the ProjectTeam table in the form's
RecordSource, add additional columns to the combo box. To get these
additional columns to show once a selection has been made in the combo box,
place textboxes on the form and set their Control Source to an equation that
refers to the equivalent column in the combo box.

Example:
=cboMyCombo.Column(1)

The column index is zero based, so 1 would be the second column.

--
Wayne Morgan
MS Access MVP


Mary said:
Here's the SQL:

SELECT Tasks.[TaskNumber], Tasks.[TaskName], Tasks.[TaskDescription],
Tasks.[TaskType], Tasks.[TaskActivity], ProjectTeam.[PT UserID] AS UserID,
Tasks.[TaskAssignedTo], ProjectTeam.[PT UserID], Tasks.[TaskSponsor],
Tasks.[TaskCreationDate], Tasks.[TaskDueDate], Tasks.[TaskCompleteDate],
Tasks.[TaskNotes], Tasks.[TaskEstimatedHours], Tasks.[TaskSize],
Tasks.Priority, BurkholzTeam.[PT UserID] AS [TaskAssignedToUID],
Tasks.CurrentUserID, Tasks.EntryTime
FROM (Sponsors INNER JOIN Tasks ON Sponsors.[SP Name] =
Tasks.[TaskSponsor])
INNER JOIN ProjectTeam ON Tasks.[TaskAssignedTo] = ProjectTeam.[PT Name]
WITH OWNERACCESS OPTION;

They're both text fields and both limited to 6 characters.

Thanks, Mary

Wayne Morgan said:
What is the query being used as the Record Source for the form? Will you
post the SQL view of this query?

Is the data type the form's record source the same for this field as the
data type in the lookup table being used for the combo box's Row Source?

--
Wayne Morgan
MS Access MVP


Mary said:
Wayne, the field I'm trying to store is available in the query only
after
the
Originator field is filled in. I have the field [UserID] from my query
as
the Control Source and the Bound Column is 1, but it's still not
retaining
the value.
Click to expand...
Click to expand...
Click to expand...
 
I made the following revisions and the UserID field is still not retained in
the Tasks table.

Here's the SQL:

SELECT Tasks.[TaskNumber], Tasks.[TaskName], Tasks.[TaskDescription],
Tasks.[TaskType], Tasks.[TaskActivity], Tasks.UserID, Tasks.[TaskAssignedTo],
Tasks.[TaskSponsor], Tasks.[TaskCreationDate], Tasks.[TaskDueDate],
Tasks.[TaskCompleteDate], Tasks.[TaskNotes], Tasks.[TaskEstimatedHours],
Tasks.[TaskSize], Tasks.Priority, Tasks.CurrentUserID, Tasks.EntryTime
FROM Tasks
WITH OWNERACCESS OPTION;

Combo Box (to autofill TaskAssignedTo field in Tasks Table)
Name: cboTaskAssignedTo
Control Source: Task Assigned To
Row Source: SELECT ProjectTeam.[PTName] AS [TaskAssignedTo],
ProjectTeam.[PTUserID] AS UserID FROM ProjectTeam;
Column Count: 2
Bound Column 1

Text Box (to autofill UserID field in Tasks Table)
Name: txtUserID
Control Source: =[cboTaskAssignedTo].[Column](1)
(Access added the brackets when I tabbed off the field)

The Tasks table is still only retaining the Name and not the ID.

Thanks again, Mary.
Wayne Morgan said:
Why do you have the lookup table, ProjectTeam, in the form's Record Source?
The normal way would be to have it in the combo box's Row Source and you
would place it in an "equivalent" field in the form's Record Source, but not
the same field that you are drawing the data from.

If you need more than one field from the ProjectTeam table in the form's
RecordSource, add additional columns to the combo box. To get these
additional columns to show once a selection has been made in the combo box,
place textboxes on the form and set their Control Source to an equation that
refers to the equivalent column in the combo box.

Example:
=cboMyCombo.Column(1)

The column index is zero based, so 1 would be the second column.

--
Wayne Morgan
MS Access MVP


Mary said:
Here's the SQL:

SELECT Tasks.[TaskNumber], Tasks.[TaskName], Tasks.[TaskDescription],
Tasks.[TaskType], Tasks.[TaskActivity], ProjectTeam.[PT UserID] AS UserID,
Tasks.[TaskAssignedTo], ProjectTeam.[PT UserID], Tasks.[TaskSponsor],
Tasks.[TaskCreationDate], Tasks.[TaskDueDate], Tasks.[TaskCompleteDate],
Tasks.[TaskNotes], Tasks.[TaskEstimatedHours], Tasks.[TaskSize],
Tasks.Priority, BurkholzTeam.[PT UserID] AS [TaskAssignedToUID],
Tasks.CurrentUserID, Tasks.EntryTime
FROM (Sponsors INNER JOIN Tasks ON Sponsors.[SP Name] =
Tasks.[TaskSponsor])
INNER JOIN ProjectTeam ON Tasks.[TaskAssignedTo] = ProjectTeam.[PT Name]
WITH OWNERACCESS OPTION;

They're both text fields and both limited to 6 characters.

Thanks, Mary

Wayne Morgan said:
What is the query being used as the Record Source for the form? Will you
post the SQL view of this query?

Is the data type the form's record source the same for this field as the
data type in the lookup table being used for the combo box's Row Source?

--
Wayne Morgan
MS Access MVP


Wayne, the field I'm trying to store is available in the query only
after
the
Originator field is filled in. I have the field [UserID] from my query
as
the Control Source and the Bound Column is 1, but it's still not
retaining
the value.
Click to expand...
Click to expand...
Click to expand...
 
Thanks, your explanation shows the problem.

First, the Bound Column should be the column that contains the unique field
from the lookup table (ProjectTeam). I would assume this to be your ID
field. Therefore the Bound Column should be 2 (yes, the Column property as
used in the textbox is zero based, but the bound Column property is 1
based). The Control Source for the combo box should be the field that the
bound column should be stored in, in this case UserID. The first visible
column, in this case PTName/TaksAssignedTo would be what shows in the combo
box after you make your selection.

You don't need to store both values in the destination table (Tasks), the
combo box will show the correct text value when it sees the corresponding ID
field in its Control Source. When ever you need to show the name at a later
time, such as in a report, you would include both tables in the query and
link them on the ID field then use the PTName field from ProjectTeam. The
link will make sure that the correct text field lines up with the ID stored
in Tasks.

The textbox isn't storing the value because it's not bound to a field in the
table, instead it has an equation as a Control Source.
 
Wayne, thank you SO much! That is exactly what I needed.

Mary

Wayne Morgan said:
Thanks, your explanation shows the problem.

First, the Bound Column should be the column that contains the unique field
from the lookup table (ProjectTeam). I would assume this to be your ID
field. Therefore the Bound Column should be 2 (yes, the Column property as
used in the textbox is zero based, but the bound Column property is 1
based). The Control Source for the combo box should be the field that the
bound column should be stored in, in this case UserID. The first visible
column, in this case PTName/TaksAssignedTo would be what shows in the combo
box after you make your selection.

You don't need to store both values in the destination table (Tasks), the
combo box will show the correct text value when it sees the corresponding ID
field in its Control Source. When ever you need to show the name at a later
time, such as in a report, you would include both tables in the query and
link them on the ID field then use the PTName field from ProjectTeam. The
link will make sure that the correct text field lines up with the ID stored
in Tasks.

The textbox isn't storing the value because it's not bound to a field in the
table, instead it has an equation as a Control Source.

--
Wayne Morgan
MS Access MVP


Mary said:
I made the following revisions and the UserID field is still not retained
in
the Tasks table.

Here's the SQL:

SELECT Tasks.[TaskNumber], Tasks.[TaskName], Tasks.[TaskDescription],
Tasks.[TaskType], Tasks.[TaskActivity], Tasks.UserID,
Tasks.[TaskAssignedTo],
Tasks.[TaskSponsor], Tasks.[TaskCreationDate], Tasks.[TaskDueDate],
Tasks.[TaskCompleteDate], Tasks.[TaskNotes], Tasks.[TaskEstimatedHours],
Tasks.[TaskSize], Tasks.Priority, Tasks.CurrentUserID, Tasks.EntryTime
FROM Tasks
WITH OWNERACCESS OPTION;

Combo Box (to autofill TaskAssignedTo field in Tasks Table)
Name: cboTaskAssignedTo
Control Source: Task Assigned To
Row Source: SELECT ProjectTeam.[PTName] AS [TaskAssignedTo],
ProjectTeam.[PTUserID] AS UserID FROM ProjectTeam;
Column Count: 2
Bound Column 1

Text Box (to autofill UserID field in Tasks Table)
Name: txtUserID
Control Source: =[cboTaskAssignedTo].[Column](1)
(Access added the brackets when I tabbed off the field)

The Tasks table is still only retaining the Name and not the ID.
Click to expand...
Click to expand...
 
Back
Top