Requery Fields with Combobox

[Elvis72]

I have a Main Form and a Subform.

The Subform has a Combobox called WorkerID, which shows 3 fields to select
the person you want to assign to that task.

Which upon selecting that person in the Combobox would auto-populate the
First Name and Last Name fields on the subform.

This works great until I go back to the table and make the WorkerID no
duplicates allowed.

Then it does not show the First Name and Last Name results until I close the
form and re-open.

So, I had the suggestion of doing a requery on the combobox, but I think the
requery should be on the First Name and Last Name fields?

I have no idea how to resolve the issue?

Your help is so greatly appreciated!~

 

[Graham Mandeno]

Hi Elvis

Welcome back

I'm having to read between the lines a bit here, because you left out a lot
of details, but this is the situation I am assuming:

You have a Workers table:
WorkerID (primary key)
FirstName
LastName

You have another table (Tasks?) which has a field for the WorkerID of the
worker assigned to that task.

Your subform is based on the Tasks table.

Your combo box is bound to the WorkerID field in the Tasks table.

The RowSource of your combo box is based on the Workers Table.

OK so far?

You don't need to store the name of the worker in your tasks table, only the
WorkerID. Your combo box should have properties something like this:
RowSource: Select WorkerID, [FirstName] & " " & [LastName] as WorkerName
from Workers order by [FirstName], [LastName];
BoundColumn: 1
ColumnCount: 2
ColumnWidths: 0

Then, what you will *see* in your combo box is the name (first and last) of
the worker, but what will be *stored* in your table is the WorkerID of the
worker.

 

[Elvis72]

Good Morning!~

OK, alot of your assumptions were correct, here is the actual breakdown:

TblResumes
WorkerID
Last Name
First Name

TblProjectInformation
ProjectID
WorkerID
TitleID
Job/ProposalNo

TblConstructionServices
Job/ProposalNo
Client
Title

The TblConstruction Services is the Main Form

The TblProjectInformation is the Subform

The Worker ID is linked to the TblResume WorkerID field.

This is the Original RowSource for WorkerID:

SELECT TblResumes.WorkerID, TblResumes.[First Name], TblResumes.[Last Name]
FROM TblResumes ORDER BY [Last Name];

Then to resolve the issue I created this on the AfterUpdate:

[Forms]![FrmConstructionServices].[Form]![Job/ProposalNo].Requery

BUT I really like your approach with the merged First and Last Name in the
WorkerID field better...saves redundant fields on the form.

This is what I have which works fine:

SELECT TblResumes.WorkerID, TblResumes.[Last Name] & ", " &
TblResumes.[First Name] FROM TblResumes ORDER BY [Last Name];

But it is telling me now that it is creating duplicate records now?

It did not do that before?

Hi Elvis

Welcome back

I'm having to read between the lines a bit here, because you left out a lot
of details, but this is the situation I am assuming:

You have a Workers table:
WorkerID (primary key)
FirstName
LastName

You have another table (Tasks?) which has a field for the WorkerID of the
worker assigned to that task.

Your subform is based on the Tasks table.

Your combo box is bound to the WorkerID field in the Tasks table.

The RowSource of your combo box is based on the Workers Table.

OK so far?

You don't need to store the name of the worker in your tasks table, only the
WorkerID. Your combo box should have properties something like this:
RowSource: Select WorkerID, [FirstName] & " " & [LastName] as WorkerName
from Workers order by [FirstName], [LastName];
BoundColumn: 1
ColumnCount: 2
ColumnWidths: 0

Then, what you will *see* in your combo box is the name (first and last) of
the worker, but what will be *stored* in your table is the WorkerID of the
worker.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I have a Main Form and a Subform.

The Subform has a Combobox called WorkerID, which shows 3 fields to select
the person you want to assign to that task.

Which upon selecting that person in the Combobox would auto-populate the
First Name and Last Name fields on the subform.

This works great until I go back to the table and make the WorkerID no
duplicates allowed.

Then it does not show the First Name and Last Name results until I close
the
form and re-open.

So, I had the suggestion of doing a requery on the combobox, but I think
the
requery should be on the First Name and Last Name fields?

I have no idea how to resolve the issue?

Your help is so greatly appreciated!~

 

[Graham Mandeno]

Hello again

When you said in your original post "make the WorkerID no duplicates
allowed", did you mean in TblProjectInformation? If you do that, then a
worker will never be able to be assigned to more than one project! That
will be why you are getting the duplicate index error.

Do you want to stop the same worker from being assigned to two different
projects with the same Job/ProposalNo? If so, then you should create a
composite index (one comprising multiple fields).

To do this, click View>Indexes in table design. Then, in the first blank
row, enter JobWorker as the Index Name and set the Unique property to "Yes".
Then select Job/ProposalNo in the Field Name column and in the *next* row,
choose WorkerID in the Field Name column.

I'm not sure why you think the Requery is required. The combo box should
not require any AfterUpdate event procedure at all.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Good Morning!~

OK, alot of your assumptions were correct, here is the actual breakdown:

TblResumes
WorkerID
Last Name
First Name

TblProjectInformation
ProjectID
WorkerID
TitleID
Job/ProposalNo

TblConstructionServices
Job/ProposalNo
Client
Title

The TblConstruction Services is the Main Form

The TblProjectInformation is the Subform

The Worker ID is linked to the TblResume WorkerID field.

This is the Original RowSource for WorkerID:

SELECT TblResumes.WorkerID, TblResumes.[First Name], TblResumes.[Last
Name]
FROM TblResumes ORDER BY [Last Name];

Then to resolve the issue I created this on the AfterUpdate:

[Forms]![FrmConstructionServices].[Form]![Job/ProposalNo].Requery

BUT I really like your approach with the merged First and Last Name in the
WorkerID field better...saves redundant fields on the form.

This is what I have which works fine:

SELECT TblResumes.WorkerID, TblResumes.[Last Name] & ", " &
TblResumes.[First Name] FROM TblResumes ORDER BY [Last Name];

But it is telling me now that it is creating duplicate records now?

It did not do that before?

Hi Elvis

Welcome back

I'm having to read between the lines a bit here, because you left out a
lot
of details, but this is the situation I am assuming:

You have a Workers table:
WorkerID (primary key)
FirstName
LastName

You have another table (Tasks?) which has a field for the WorkerID of the
worker assigned to that task.

Your subform is based on the Tasks table.

Your combo box is bound to the WorkerID field in the Tasks table.

The RowSource of your combo box is based on the Workers Table.

OK so far?

You don't need to store the name of the worker in your tasks table, only
the
WorkerID. Your combo box should have properties something like this:
RowSource: Select WorkerID, [FirstName] & " " & [LastName] as
WorkerName
from Workers order by [FirstName], [LastName];
BoundColumn: 1
ColumnCount: 2
ColumnWidths: 0

Then, what you will *see* in your combo box is the name (first and last)
of
the worker, but what will be *stored* in your table is the WorkerID of
the
worker.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I have a Main Form and a Subform.

The Subform has a Combobox called WorkerID, which shows 3 fields to
select
the person you want to assign to that task.

Which upon selecting that person in the Combobox would auto-populate
the
First Name and Last Name fields on the subform.

This works great until I go back to the table and make the WorkerID no
duplicates allowed.

Then it does not show the First Name and Last Name results until I
close
the
form and re-open.

So, I had the suggestion of doing a requery on the combobox, but I
think
the
requery should be on the First Name and Last Name fields?

I have no idea how to resolve the issue?

Your help is so greatly appreciated!~