Combo Box & Report Questions

[ADB_Seeker]

1) I have a combo box that I want to use to create the content for a report
and then have the combo box open the report. My strWhere code is returning a
compile error: Variable Not Defined. What needs changing to make this work?

2) Can I open the report directly from the combo box instead of adding
another command to open the report after the combo box content is selected?

3) How do I get the combo box to automatically remove the previous
selection when I return to the form?

4) Will this work on a bound form?

Following is my code (The table name is ASSIGNEES):

Private Sub cboSpecEObyAssignee_Change()
On Error GoTo Err_cboSpecEObyAssignee_Change
Dim stDocName As String

stDocName = "rptSpecEObyAssignee"
strWhere = "[ASSIGNEE.ID]=" & Me![ASSIGNEE-NAMES]

DoCmd.OpenReport stDocName, acViewPreview, , strWhere

Exit_cboSpecEObyAssignee_Change:
Exit Sub

Err_cboSpecEObyAssignee_Change:
MsgBox Err.Description
Resume Exit_cboSpecEObyAssignee_Change

End Sub

 

[Douglas J. Steele]

You haven't declared strWhere.

Private Sub cboSpecEObyAssignee_Change()
On Error GoTo Err_cboSpecEObyAssignee_Change
Dim stDocName As String
Dim strWhere As String ' <=== Missing

stDocName = "rptSpecEObyAssignee"
strWhere = "[ASSIGNEE.ID]=" & Me![ASSIGNEE-NAMES]

DoCmd.OpenReport stDocName, acViewPreview, , strWhere

Exit_cboSpecEObyAssignee_Change:
Exit Sub

Err_cboSpecEObyAssignee_Change:
MsgBox Err.Description
Resume Exit_cboSpecEObyAssignee_Change

End Sub

 

[Dirk Goldgar]

1) I have a combo box that I want to use to create the content for a
report
and then have the combo box open the report. My strWhere code is returning
a
compile error: Variable Not Defined. What needs changing to make this
work?

Um, define the variable. Specifically, your code doesn't declare the
variable strWhere. Add this line after the "Dim strDocName" line:

Dim strWhere As String

2) Can I open the report directly from the combo box instead of adding
another command to open the report after the combo box content is
selected?

Yes. Your current code is already doing that.

However, you should not use the combo box's Change event for this. If the
user were to type into the combo instead of using the mouse to select an
item from the list, the Change event would fire for every keystroke -- not
what you want. Use the control's AfterUpdate event instead.

3) How do I get the combo box to automatically remove the previous
selection when I return to the form?

After the "DoCmd.OpenReport" line, add the line:

Me.cboSpecEObyAssignee = Null

4) Will this work on a bound form?

Whether the form is bound or unbound is irrelevant, except that, if the form
is bound and contains a record that should be shown on the report, you need
to ensure that the current record is saved before opening the report. If
that is the case, you can add this line before the "DoCmd.OpenReport" line:

If Me.Dirty Then Me.Dirty = False

Note that you would not use this line if the form's current record is
irrelevant to the report.

Following is my code (The table name is ASSIGNEES): [snip]
strWhere = "[ASSIGNEE.ID]=" & Me![ASSIGNEE-NAMES]

The above line has at least one error.

1. "ASSIGNEE.ID" cannot be the name of a field, as no field name can contain
the "." character. I suspect that the above is incorrect bracketing, and
you meant to write:

strWhere = "[ASSIGNEE].[ID]=" & Me![ASSIGNEE-NAMES]

2. I'm confused here. The combo box is apparently named
"cboSpecEObyAssignee", but the above line is using a field called
"ASSIGNEE-NAMES" to build the where-condition. From what you said, I would
expect the line to be:

strWhere = "[ASSIGNEE].[ID]=" & Me!cboSpecEObyAssignee

But I could be missing information that would explain why it's different
from my expectation.

 

[ADB_Seeker]

Thank you for your help. I am learning VBA code. The combo box now opens the
report, but the report has no records.
The record source for my report is a query that pulls information from my
tables. I entered the criteria [Forms]![FRONT PAGE]![cboSpecEObyAssignee]
for the Assignee field, thinking it would pull the selection from the form's
combo box and populate the report with records based on that selection, but
it isn't working.
Thank you in advance for your help.

1) I have a combo box that I want to use to create the content for a
report
and then have the combo box open the report. My strWhere code is returning
a
compile error: Variable Not Defined. What needs changing to make this
work?

Um, define the variable. Specifically, your code doesn't declare the
variable strWhere. Add this line after the "Dim strDocName" line:

Dim strWhere As String

2) Can I open the report directly from the combo box instead of adding
another command to open the report after the combo box content is
selected?

Yes. Your current code is already doing that.

However, you should not use the combo box's Change event for this. If the
user were to type into the combo instead of using the mouse to select an
item from the list, the Change event would fire for every keystroke -- not
what you want. Use the control's AfterUpdate event instead.

3) How do I get the combo box to automatically remove the previous
selection when I return to the form?

After the "DoCmd.OpenReport" line, add the line:

Me.cboSpecEObyAssignee = Null

4) Will this work on a bound form?

Whether the form is bound or unbound is irrelevant, except that, if the form
is bound and contains a record that should be shown on the report, you need
to ensure that the current record is saved before opening the report. If
that is the case, you can add this line before the "DoCmd.OpenReport" line:

If Me.Dirty Then Me.Dirty = False

Note that you would not use this line if the form's current record is
irrelevant to the report.

Following is my code (The table name is ASSIGNEES): [snip]
strWhere = "[ASSIGNEE.ID]=" & Me![ASSIGNEE-NAMES]

The above line has at least one error.

1. "ASSIGNEE.ID" cannot be the name of a field, as no field name can contain
the "." character. I suspect that the above is incorrect bracketing, and
you meant to write:

strWhere = "[ASSIGNEE].[ID]=" & Me![ASSIGNEE-NAMES]

2. I'm confused here. The combo box is apparently named
"cboSpecEObyAssignee", but the above line is using a field called
"ASSIGNEE-NAMES" to build the where-condition. From what you said, I would
expect the line to be:

strWhere = "[ASSIGNEE].[ID]=" & Me!cboSpecEObyAssignee

But I could be missing information that would explain why it's different
from my expectation.

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)

 

[Dirk Goldgar]

Thank you for your help. I am learning VBA code. The combo box now opens
the
report, but the report has no records.
The record source for my report is a query that pulls information from my
tables. I entered the criteria [Forms]![FRONT PAGE]![cboSpecEObyAssignee]
for the Assignee field, thinking it would pull the selection from the
form's
combo box and populate the report with records based on that selection,
but
it isn't working.

What is your code now? If you made all the changes I suggested, you
shouldn't need the criterion in the report's recordsource query, and that
criterion may be causing the report to come up blank because the code is
clearing the combo box immediately after opening the report.

 

[ADB_Seeker]

Following is my current code:

Private Sub cboSpecEObyAssignee_AfterUpdate()
On Error GoTo Err_cboSpecEObyAssignee_AfterUpdate
Dim stDocName As String
Dim strWhere As String

stDocName = "rptSpecEObyAssignee"
strWhere = Me!cboSpecEObyAssignee

If Me.Dirty Then Me.Dirty = False
DoCmd.OpenReport stDocName, acViewPreview, , strWhere
Me.cboSpecEObyAssignee = Null

Exit_cboSpecEObyAssignee_AfterUpdate:
Exit Sub

Err_cboSpecEObyAssignee_AfterUpdate:
MsgBox Err.Description
Resume Exit_cboSpecEObyAssignee_AfterUpdate

End Sub

Thank you for your help. I am learning VBA code. The combo box now opens
the
report, but the report has no records.
The record source for my report is a query that pulls information from my
tables. I entered the criteria [Forms]![FRONT PAGE]![cboSpecEObyAssignee]
for the Assignee field, thinking it would pull the selection from the
form's
combo box and populate the report with records based on that selection,
but
it isn't working.

What is your code now? If you made all the changes I suggested, you
shouldn't need the criterion in the report's recordsource query, and that
criterion may be causing the report to come up blank because the code is
clearing the combo box immediately after opening the report.

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)

 

[Dirk Goldgar]

Following is my current code:

Private Sub cboSpecEObyAssignee_AfterUpdate()
On Error GoTo Err_cboSpecEObyAssignee_AfterUpdate
Dim stDocName As String
Dim strWhere As String

stDocName = "rptSpecEObyAssignee"
strWhere = Me!cboSpecEObyAssignee

If Me.Dirty Then Me.Dirty = False
DoCmd.OpenReport stDocName, acViewPreview, , strWhere
Me.cboSpecEObyAssignee = Null

Exit_cboSpecEObyAssignee_AfterUpdate:
Exit Sub

Err_cboSpecEObyAssignee_AfterUpdate:
MsgBox Err.Description
Resume Exit_cboSpecEObyAssignee_AfterUpdate

End Sub

That's not right. Instead of this line:

strWhere = Me!cboSpecEObyAssignee

.... you should probably have this, as I posted before:

strWhere = "[ASSIGNEE].[ID]=" & Me!cboSpecEObyAssignee

*And* you should remove the criterion from the report's recordsource query.

 

[ADB_Seeker]

Hi, Dirk
Thank you for your assistance! I replaced the strWhere line. When I click
on the combo box and select a name a pop-up box appears, wanting me to enter
the Assignee.Id. When I enter the ID for assignee, the report opens with all
data (not filtered for that specific assignee).

Private Sub cboSpecEObyAssignee_AfterUpdate()
On Error GoTo Err_cboSpecEObyAssignee_AfterUpdate
Dim stDocName As String
Dim strWhere As String

stDocName = "rptSpecEObyAssignee"
strWhere = "[ASSIGNEE].[ID]=" & Me!cboSpecEObyAssignee

If Me.Dirty Then Me.Dirty = False
DoCmd.OpenReport stDocName, acViewPreview, , strWhere
Me.cboSpecEObyAssignee = Null

Exit_cboSpecEObyAssignee_AfterUpdate:
Exit Sub

Err_cboSpecEObyAssignee_AfterUpdate:
MsgBox Err.Description
Resume Exit_cboSpecEObyAssignee_AfterUpdate

End Sub

Following is my current code:

Private Sub cboSpecEObyAssignee_AfterUpdate()
On Error GoTo Err_cboSpecEObyAssignee_AfterUpdate
Dim stDocName As String
Dim strWhere As String

stDocName = "rptSpecEObyAssignee"
strWhere = Me!cboSpecEObyAssignee

If Me.Dirty Then Me.Dirty = False
DoCmd.OpenReport stDocName, acViewPreview, , strWhere
Me.cboSpecEObyAssignee = Null

Exit_cboSpecEObyAssignee_AfterUpdate:
Exit Sub

Err_cboSpecEObyAssignee_AfterUpdate:
MsgBox Err.Description
Resume Exit_cboSpecEObyAssignee_AfterUpdate

End Sub

That's not right. Instead of this line:

strWhere = Me!cboSpecEObyAssignee

... you should probably have this, as I posted before:

strWhere = "[ASSIGNEE].[ID]=" & Me!cboSpecEObyAssignee

*And* you should remove the criterion from the report's recordsource query.

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)

 

[ADB_Seeker]

I also removed the criterion from the report query. To make sure I have this
correct, I set my record source for the combo box to: SELECT ASSIGNEES.ID,
ASSIGNEES.[ASSIGN-NAMES] FROM ASSIGNEES;
Assignees is my table.
Thank you.

Following is my current code:

Private Sub cboSpecEObyAssignee_AfterUpdate()
On Error GoTo Err_cboSpecEObyAssignee_AfterUpdate
Dim stDocName As String
Dim strWhere As String

stDocName = "rptSpecEObyAssignee"
strWhere = Me!cboSpecEObyAssignee

If Me.Dirty Then Me.Dirty = False
DoCmd.OpenReport stDocName, acViewPreview, , strWhere
Me.cboSpecEObyAssignee = Null

Exit_cboSpecEObyAssignee_AfterUpdate:
Exit Sub

Err_cboSpecEObyAssignee_AfterUpdate:
MsgBox Err.Description
Resume Exit_cboSpecEObyAssignee_AfterUpdate

End Sub

That's not right. Instead of this line:

strWhere = Me!cboSpecEObyAssignee

... you should probably have this, as I posted before:

strWhere = "[ASSIGNEE].[ID]=" & Me!cboSpecEObyAssignee

*And* you should remove the criterion from the report's recordsource query.

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)

 

[Dirk Goldgar]

Hi, Dirk
Thank you for your assistance! I replaced the strWhere line. When I click
on the combo box and select a name a pop-up box appears, wanting me to
enter
the Assignee.Id. When I enter the ID for assignee, the report opens with
all
data (not filtered for that specific assignee).

Private Sub cboSpecEObyAssignee_AfterUpdate()
On Error GoTo Err_cboSpecEObyAssignee_AfterUpdate
Dim stDocName As String
Dim strWhere As String

stDocName = "rptSpecEObyAssignee"
strWhere = "[ASSIGNEE].[ID]=" & Me!cboSpecEObyAssignee

If Me.Dirty Then Me.Dirty = False
DoCmd.OpenReport stDocName, acViewPreview, , strWhere
Me.cboSpecEObyAssignee = Null

Exit_cboSpecEObyAssignee_AfterUpdate:
Exit Sub

Err_cboSpecEObyAssignee_AfterUpdate:
MsgBox Err.Description
Resume Exit_cboSpecEObyAssignee_AfterUpdate

End Sub

If, as you said in your other message, "ASSIGNEES" is the name of your
table, then you need to change the strWhere clause to:

strWhere = "[ASSIGNEES].[ID]=" & Me!cboSpecEObyAssignee

The table qualifier may not be necessary, depending on your report's
recordsource query, but I don't want to assume that.

 

[ADB_Seeker]

I changed the strWhere line to include the correct table name. It still wants
the Parameter Value Assignees.ID

Also, I removed the recordsource and got nothing in the drop-down for the
combo box so I put it back in. My report query contains two tables: EO TABLE
& ER TABLE. When I add ASSIGNEES to the query , I get "type mismatch in
expression" when I try to run the report.

I have the report sorting/group set for EO Number from EO TABLE.

Thank you.

Hi, Dirk
Thank you for your assistance! I replaced the strWhere line. When I click
on the combo box and select a name a pop-up box appears, wanting me to
enter
the Assignee.Id. When I enter the ID for assignee, the report opens with
all
data (not filtered for that specific assignee).

Private Sub cboSpecEObyAssignee_AfterUpdate()
On Error GoTo Err_cboSpecEObyAssignee_AfterUpdate
Dim stDocName As String
Dim strWhere As String

stDocName = "rptSpecEObyAssignee"
strWhere = "[ASSIGNEE].[ID]=" & Me!cboSpecEObyAssignee

If Me.Dirty Then Me.Dirty = False
DoCmd.OpenReport stDocName, acViewPreview, , strWhere
Me.cboSpecEObyAssignee = Null

Exit_cboSpecEObyAssignee_AfterUpdate:
Exit Sub

Err_cboSpecEObyAssignee_AfterUpdate:
MsgBox Err.Description
Resume Exit_cboSpecEObyAssignee_AfterUpdate

End Sub

If, as you said in your other message, "ASSIGNEES" is the name of your
table, then you need to change the strWhere clause to:

strWhere = "[ASSIGNEES].[ID]=" & Me!cboSpecEObyAssignee

The table qualifier may not be necessary, depending on your report's
recordsource query, but I don't want to assume that.

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)

 

[Dirk Goldgar]

I changed the strWhere line to include the correct table name. It still
wants
the Parameter Value Assignees.ID

Also, I removed the recordsource and got nothing in the drop-down for the
combo box so I put it back in. My report query contains two tables: EO
TABLE
& ER TABLE. When I add ASSIGNEES to the query , I get "type mismatch in
expression" when I try to run the report.

I have the report sorting/group set for EO Number from EO TABLE.

Please post the SQL of the report's recordsource query. If it doesn't
contain the ASSIGNEES table, then your where-condition can't filter on
ASSIGNEES.ID. What is the name of the field in the recordsource query that
you want to filter?

I assume that the query contains some field from [EO TABLE] or [ER TABLE]
that should hold an ID value that corresponds to one of the ID values in the
ASSIGNEES table. Presumably, that is the field you want to filter on.

 

[ADB_Seeker]

Thank you, Dirk. I am quite new to coding and appreciate your patience.
The ORIGINATOR from the EO table is taken from the ASSIGN-NAMES in the
Assignees table. Following is the SQL from the report's recordsource:
SELECT [EO TABLE].[EO NUMBER], [EO TABLE].ORIGINATOR, [EO TABLE].DATE, [EO
TABLE].[PART NUMBER], [EO TABLE].[DRAWING NUMBER], [EO TABLE].REVISION, [EO
TABLE].[PROJECT NUMBER], [EO TABLE].[OUTSTANDING EOs], [EO TABLE].[MINOR
CHANGE], [EO TABLE].[STRUCTURAL REQUIREMENT], [EO TABLE].[INTERFERENCE
CONDITION], [EO TABLE].[INCOMPLETE DATA], [EO TABLE].[PROCESS IMPROVEMENT],
[EO TABLE].[ALL ISSUED REVISIONS], [EO TABLE].[SPECIFIED REVISION], [EO
TABLE].[SPECIFIED REV], [EO TABLE].[NEW REVISION ONLY], [EO
TABLE].[DESCRIPTION OF ISSUE], [EO TABLE].[ROOT CAUSE], [EO
TABLE].DISPOSITION, [EO TABLE].[INCORPORATED REVISION], [EO TABLE].[EST
CHANGE IN PRODUCTION], [EO TABLE].[EST ENGINEERING HOURS], [EO
TABLE].APPROVAL, [EO TABLE].[DATE APPROVED], [EO TABLE].[MANUFACTURING
ACKNOWLEDGMENT], [EO TABLE].[DATE OF MANUFACTURING ACKNOWLEDGMENT], [EO
TABLE].[QA ACKNOWLEDGMENT], [EO TABLE].[DATE OF QA ACKNOWLEDGMENT], [EO
TABLE].[ER NUMBER], [EO TABLE].[ER APPROVAL], [EO TABLE].[ER APPROVAL DATE],
[ER TABLE].HIGH, [ER TABLE].MEDIUM, [ER TABLE].LOW, [ER TABLE].[REASON FOR
REQUEST], [ER TABLE].[ACTION TAKEN 1], [ER TABLE].[ACTION TAKEN 2], [ER
TABLE].[ACTION TAKEN 3], [ER TABLE].[ACTION TAKEN 4], [ER TABLE].[BY 1], [ER
TABLE].[BY 2], [ER TABLE].[BY 4], [ER TABLE].[BY 3], [ER TABLE].[ACTION DATE
1], [ER TABLE].[ACTION DATE 2], [ER TABLE].[ACTION DATE 3], [ER
TABLE].[ACTION DATE 4], [ER TABLE].REFERENCES
FROM [ER TABLE] INNER JOIN [EO TABLE] ON [ER TABLE].[ER NUMBER] = [EO
TABLE].[ER NUMBER];

I changed the strWhere line to include the correct table name. It still
wants
the Parameter Value Assignees.ID

Also, I removed the recordsource and got nothing in the drop-down for the
combo box so I put it back in. My report query contains two tables: EO
TABLE
& ER TABLE. When I add ASSIGNEES to the query , I get "type mismatch in
expression" when I try to run the report.

I have the report sorting/group set for EO Number from EO TABLE.

Please post the SQL of the report's recordsource query. If it doesn't
contain the ASSIGNEES table, then your where-condition can't filter on
ASSIGNEES.ID. What is the name of the field in the recordsource query that
you want to filter?

I assume that the query contains some field from [EO TABLE] or [ER TABLE]
that should hold an ID value that corresponds to one of the ID values in the
ASSIGNEES table. Presumably, that is the field you want to filter on.

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)

.

 

[Dirk Goldgar]

Thank you, Dirk. I am quite new to coding and appreciate your patience.
The ORIGINATOR from the EO table is taken from the ASSIGN-NAMES in the
Assignees table. Following is the SQL from the report's recordsource:
SELECT [EO TABLE].[EO NUMBER], [EO TABLE].ORIGINATOR, [EO TABLE].DATE, [EO
TABLE].[PART NUMBER], [EO TABLE].[DRAWING NUMBER], [EO TABLE].REVISION,
[EO
TABLE].[PROJECT NUMBER], [EO TABLE].[OUTSTANDING EOs], [EO TABLE].[MINOR
CHANGE], [EO TABLE].[STRUCTURAL REQUIREMENT], [EO TABLE].[INTERFERENCE
CONDITION], [EO TABLE].[INCOMPLETE DATA], [EO TABLE].[PROCESS
IMPROVEMENT],
[EO TABLE].[ALL ISSUED REVISIONS], [EO TABLE].[SPECIFIED REVISION], [EO
TABLE].[SPECIFIED REV], [EO TABLE].[NEW REVISION ONLY], [EO
TABLE].[DESCRIPTION OF ISSUE], [EO TABLE].[ROOT CAUSE], [EO
TABLE].DISPOSITION, [EO TABLE].[INCORPORATED REVISION], [EO TABLE].[EST
CHANGE IN PRODUCTION], [EO TABLE].[EST ENGINEERING HOURS], [EO
TABLE].APPROVAL, [EO TABLE].[DATE APPROVED], [EO TABLE].[MANUFACTURING
ACKNOWLEDGMENT], [EO TABLE].[DATE OF MANUFACTURING ACKNOWLEDGMENT], [EO
TABLE].[QA ACKNOWLEDGMENT], [EO TABLE].[DATE OF QA ACKNOWLEDGMENT], [EO
TABLE].[ER NUMBER], [EO TABLE].[ER APPROVAL], [EO TABLE].[ER APPROVAL
DATE],
[ER TABLE].HIGH, [ER TABLE].MEDIUM, [ER TABLE].LOW, [ER TABLE].[REASON FOR
REQUEST], [ER TABLE].[ACTION TAKEN 1], [ER TABLE].[ACTION TAKEN 2], [ER
TABLE].[ACTION TAKEN 3], [ER TABLE].[ACTION TAKEN 4], [ER TABLE].[BY 1],
[ER
TABLE].[BY 2], [ER TABLE].[BY 4], [ER TABLE].[BY 3], [ER TABLE].[ACTION
DATE
1], [ER TABLE].[ACTION DATE 2], [ER TABLE].[ACTION DATE 3], [ER
TABLE].[ACTION DATE 4], [ER TABLE].REFERENCES
FROM [ER TABLE] INNER JOIN [EO TABLE] ON [ER TABLE].[ER NUMBER] = [EO
TABLE].[ER NUMBER];

Is [EO TABLE].ORIGINATOR a text field, then?

What is the structure of the Assignees table? What fields are in it, of
what types?

You may have already posted this, but I don't see it: what is the rowsource
of the combo box, cboSpecEObyAssignee? What are its Column Count and Bound
Column properties?

I have a feeling there is an error in your table design that we may either
have to correct or work around.

 

[ADB_Seeker]

[EO TABLE].ORIGINATOR is a text field
Assignees table structure:
ID: Auto Fill; Field Size-Long Integer; New Values-Increment; Indexed-Yes

ASSIGN-NAMES: Text; Field Size-50; Required-No; Allow Zero Length-Yes;
Indexed-No; Unicode Compression-Yes; IME Mode-No Control; IME Sentence
Mode-None; Lookup Display Control-Text Box

EMAILADDRESS: Text (all other attributes are sames as ASSIGN-NAMES)

Rowsource for cboSpecEObyAssignee is: Table/Query;
SELECT ASSIGNEES.ID, ASSIGNEES.[ASSIGN-NAMES] FROM ASSIGNEES;
Column Count: 2; Column Widths: 0";1"; Bound Column: 1

Thank you.

Thank you, Dirk. I am quite new to coding and appreciate your patience.
The ORIGINATOR from the EO table is taken from the ASSIGN-NAMES in the
Assignees table. Following is the SQL from the report's recordsource:
SELECT [EO TABLE].[EO NUMBER], [EO TABLE].ORIGINATOR, [EO TABLE].DATE, [EO
TABLE].[PART NUMBER], [EO TABLE].[DRAWING NUMBER], [EO TABLE].REVISION,
[EO
TABLE].[PROJECT NUMBER], [EO TABLE].[OUTSTANDING EOs], [EO TABLE].[MINOR
CHANGE], [EO TABLE].[STRUCTURAL REQUIREMENT], [EO TABLE].[INTERFERENCE
CONDITION], [EO TABLE].[INCOMPLETE DATA], [EO TABLE].[PROCESS
IMPROVEMENT],
[EO TABLE].[ALL ISSUED REVISIONS], [EO TABLE].[SPECIFIED REVISION], [EO
TABLE].[SPECIFIED REV], [EO TABLE].[NEW REVISION ONLY], [EO
TABLE].[DESCRIPTION OF ISSUE], [EO TABLE].[ROOT CAUSE], [EO
TABLE].DISPOSITION, [EO TABLE].[INCORPORATED REVISION], [EO TABLE].[EST
CHANGE IN PRODUCTION], [EO TABLE].[EST ENGINEERING HOURS], [EO
TABLE].APPROVAL, [EO TABLE].[DATE APPROVED], [EO TABLE].[MANUFACTURING
ACKNOWLEDGMENT], [EO TABLE].[DATE OF MANUFACTURING ACKNOWLEDGMENT], [EO
TABLE].[QA ACKNOWLEDGMENT], [EO TABLE].[DATE OF QA ACKNOWLEDGMENT], [EO
TABLE].[ER NUMBER], [EO TABLE].[ER APPROVAL], [EO TABLE].[ER APPROVAL
DATE],
[ER TABLE].HIGH, [ER TABLE].MEDIUM, [ER TABLE].LOW, [ER TABLE].[REASON FOR
REQUEST], [ER TABLE].[ACTION TAKEN 1], [ER TABLE].[ACTION TAKEN 2], [ER
TABLE].[ACTION TAKEN 3], [ER TABLE].[ACTION TAKEN 4], [ER TABLE].[BY 1],
[ER
TABLE].[BY 2], [ER TABLE].[BY 4], [ER TABLE].[BY 3], [ER TABLE].[ACTION
DATE
1], [ER TABLE].[ACTION DATE 2], [ER TABLE].[ACTION DATE 3], [ER
TABLE].[ACTION DATE 4], [ER TABLE].REFERENCES
FROM [ER TABLE] INNER JOIN [EO TABLE] ON [ER TABLE].[ER NUMBER] = [EO
TABLE].[ER NUMBER];

Is [EO TABLE].ORIGINATOR a text field, then?

What is the structure of the Assignees table? What fields are in it, of
what types?

You may have already posted this, but I don't see it: what is the rowsource
of the combo box, cboSpecEObyAssignee? What are its Column Count and Bound
Column properties?

I have a feeling there is an error in your table design that we may either
have to correct or work around.

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)

 

[Dirk Goldgar]

[EO TABLE].ORIGINATOR is a text field
Assignees table structure:
ID: Auto Fill; Field Size-Long Integer; New Values-Increment; Indexed-Yes

ASSIGN-NAMES: Text; Field Size-50; Required-No; Allow Zero Length-Yes;
Indexed-No; Unicode Compression-Yes; IME Mode-No Control; IME Sentence
Mode-None; Lookup Display Control-Text Box

EMAILADDRESS: Text (all other attributes are sames as ASSIGN-NAMES)

Rowsource for cboSpecEObyAssignee is: Table/Query;
SELECT ASSIGNEES.ID, ASSIGNEES.[ASSIGN-NAMES] FROM ASSIGNEES;
Column Count: 2; Column Widths: 0";1"; Bound Column: 1

There's a design issue here that complicates matters. In a correct table
design, the ORIGINATOR field in [EO TABLE] would not be a Text field storing
a value taken from ASSIGNEES.[ASSIGN-NAMES]; instead, it would be a
Number/Long Integer field storing a value taken from ASSIGNEES.ID. There is
nothing logically that prevents two ASSIGNEES records from having the same
value for ASSIGN-NAMES. The foreign key in [EO TABLE] should link to the
primary key in ASSIGNEES, thus ensuring that a given [EO TABLE] record can
match one ASSIGNEES record.

In any place where you need to show the name of the originator, you can
either use a query to look up the matching ASSIGNEES record and return the
name. For example, your report's recordsource query would also link in the
ASSIGNEES table and include the [ASSIGN-NAMES] field in the fields returned,
so that the name can be shown on the report in place of the ID.

While it is possible to make your existing form and report work as you have
it set up now -- so long as you never have two assignees with the same
name -- I would recommend that you correct the error in the data design, and
then the rest of the job will be easy. I understand, though, that you may
have a lot of existing data to correct, and other forms, queries, or reports
to change. If you really want to proceed with the existing, uncorrected
design, let me know and I'll continue on that basis.

 

[ADB_Seeker]

Thank you, Dirk.
I inherited this DB and while it will take time to make the corrections, I
want the DB to be correctly designed. I added the ASSIGNEES table to my query
and linked it to the EO Table as you recommended. I also have a table (ER
Table) that is used in the query. It does not have any fields that need to
take information from the Assignees table. Do I need to link the Assignees to
it also?

[EO TABLE].ORIGINATOR is a text field
Assignees table structure:
ID: Auto Fill; Field Size-Long Integer; New Values-Increment; Indexed-Yes

ASSIGN-NAMES: Text; Field Size-50; Required-No; Allow Zero Length-Yes;
Indexed-No; Unicode Compression-Yes; IME Mode-No Control; IME Sentence
Mode-None; Lookup Display Control-Text Box

EMAILADDRESS: Text (all other attributes are sames as ASSIGN-NAMES)

Rowsource for cboSpecEObyAssignee is: Table/Query;
SELECT ASSIGNEES.ID, ASSIGNEES.[ASSIGN-NAMES] FROM ASSIGNEES;
Column Count: 2; Column Widths: 0";1"; Bound Column: 1

There's a design issue here that complicates matters. In a correct table
design, the ORIGINATOR field in [EO TABLE] would not be a Text field storing
a value taken from ASSIGNEES.[ASSIGN-NAMES]; instead, it would be a
Number/Long Integer field storing a value taken from ASSIGNEES.ID. There is
nothing logically that prevents two ASSIGNEES records from having the same
value for ASSIGN-NAMES. The foreign key in [EO TABLE] should link to the
primary key in ASSIGNEES, thus ensuring that a given [EO TABLE] record can
match one ASSIGNEES record.

In any place where you need to show the name of the originator, you can
either use a query to look up the matching ASSIGNEES record and return the
name. For example, your report's recordsource query would also link in the
ASSIGNEES table and include the [ASSIGN-NAMES] field in the fields returned,
so that the name can be shown on the report in place of the ID.

While it is possible to make your existing form and report work as you have
it set up now -- so long as you never have two assignees with the same
name -- I would recommend that you correct the error in the data design, and
then the rest of the job will be easy. I understand, though, that you may
have a lot of existing data to correct, and other forms, queries, or reports
to change. If you really want to proceed with the existing, uncorrected
design, let me know and I'll continue on that basis.

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)

 

[ADB_Seeker]

Dirk,
When I change the data type in the EO TABLE for ORIGINATOR field, to
Number/Long Integer, all content in that field is deleted because it is text
(first initial<space>last name). Also, Access doesn't allow me to re-enter
the names because the field is numeric.
Any ideas on a way around this?

Thank you.

Thank you, Dirk.
I inherited this DB and while it will take time to make the corrections, I
want the DB to be correctly designed. I added the ASSIGNEES table to my query
and linked it to the EO Table as you recommended. I also have a table (ER
Table) that is used in the query. It does not have any fields that need to
take information from the Assignees table. Do I need to link the Assignees to
it also?

[EO TABLE].ORIGINATOR is a text field
Assignees table structure:
ID: Auto Fill; Field Size-Long Integer; New Values-Increment; Indexed-Yes

ASSIGN-NAMES: Text; Field Size-50; Required-No; Allow Zero Length-Yes;
Indexed-No; Unicode Compression-Yes; IME Mode-No Control; IME Sentence
Mode-None; Lookup Display Control-Text Box

EMAILADDRESS: Text (all other attributes are sames as ASSIGN-NAMES)

Rowsource for cboSpecEObyAssignee is: Table/Query;
SELECT ASSIGNEES.ID, ASSIGNEES.[ASSIGN-NAMES] FROM ASSIGNEES;
Column Count: 2; Column Widths: 0";1"; Bound Column: 1

There's a design issue here that complicates matters. In a correct table
design, the ORIGINATOR field in [EO TABLE] would not be a Text field storing
a value taken from ASSIGNEES.[ASSIGN-NAMES]; instead, it would be a
Number/Long Integer field storing a value taken from ASSIGNEES.ID. There is
nothing logically that prevents two ASSIGNEES records from having the same
value for ASSIGN-NAMES. The foreign key in [EO TABLE] should link to the
primary key in ASSIGNEES, thus ensuring that a given [EO TABLE] record can
match one ASSIGNEES record.

In any place where you need to show the name of the originator, you can
either use a query to look up the matching ASSIGNEES record and return the
name. For example, your report's recordsource query would also link in the
ASSIGNEES table and include the [ASSIGN-NAMES] field in the fields returned,
so that the name can be shown on the report in place of the ID.

While it is possible to make your existing form and report work as you have
it set up now -- so long as you never have two assignees with the same
name -- I would recommend that you correct the error in the data design, and
then the rest of the job will be easy. I understand, though, that you may
have a lot of existing data to correct, and other forms, queries, or reports
to change. If you really want to proceed with the existing, uncorrected
design, let me know and I'll continue on that basis.

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)

 

[Dirk Goldgar]

Dirk,
When I change the data type in the EO TABLE for ORIGINATOR field, to
Number/Long Integer, all content in that field is deleted because it is
text
(first initial<space>last name). Also, Access doesn't allow me to re-enter
the names because the field is numeric.
Any ideas on a way around this?

You don't want to lose the relationship information that is already stored
in the ORIGINATOR field, so you need to first capture the relationship in a
new numeric field, then replace the old field with the new one. Here's how:

1. Start with the original database before you made the change you described
above.

2. Make a backup copy of the [EO TABLE] table, and backup the database in
case something serious goes wrong.

3. Add a new field to the table. Name the field "OriginatorID", and make it
a Number/Long Integer field. If it comes up with a Default Value property
of 0, remove that default value. Set the field's Indexed property to "Yes
(Duplicates OK)".

4. Execute the following query:

UPDATE [EO TABLE] INNER JOIN ASSIGNEES
ON [EO TABLE].ORIGINATOR = ASSIGNEES.[ASSIGN-NAMES]
SET [EO TABLE].OriginatorID = [ASSIGNEES].[ID];

That should get the numeric ID for every originator into the new
OriginatorID field.

5. Open the table in datasheet view and inspect the OriginatorID field to
make sure every record has a value in the field. If there are any records
that don't, these records must have ORIGINATOR values that are either blank
(Null) or are not in the ASSIGNEES table at all. If you want, you can run a
query to find such records, like this:

SELECT * FROM [EO TABLE] WHERE OriginatorID Is Null;

You must decide how to deal with these -- correct typos or add new records
to the ASSIGNEES table, then rerun the update query from step 4.

6. When you are satisfied that you have assigned an OriginatorID to every
record in [EO TABLE], or at least assigned one wherever it is possible, then
....

6a. Open [EO TABLE] in design view.
6b. Delete the ORIGINATOR field.
6c. Rename the OriginatorID field to "ORIGINATOR".
6d. Save the table design changes and close the table.

At this point, the table should have a numeric ORIGINATOR field that
correctly links to the ID field in the ASSIGNEES table.

No, that table has no direct relationship to the ASSIGNEES table, so there
is no need to link them in the query, and no fields on which to link them,
anyway.

 

[ADB_Seeker]

Your help is much appreciated!
All updates/changes are complete. The Originator field is now numeric/long
integer and links to the ID field in ASSIGNEES table.
I need help with the next steps, please.

Dirk,
When I change the data type in the EO TABLE for ORIGINATOR field, to
Number/Long Integer, all content in that field is deleted because it is
text
(first initial<space>last name). Also, Access doesn't allow me to re-enter
the names because the field is numeric.
Any ideas on a way around this?

You don't want to lose the relationship information that is already stored
in the ORIGINATOR field, so you need to first capture the relationship in a
new numeric field, then replace the old field with the new one. Here's how:

1. Start with the original database before you made the change you described
above.

2. Make a backup copy of the [EO TABLE] table, and backup the database in
case something serious goes wrong.

3. Add a new field to the table. Name the field "OriginatorID", and make it
a Number/Long Integer field. If it comes up with a Default Value property
of 0, remove that default value. Set the field's Indexed property to "Yes
(Duplicates OK)".

4. Execute the following query:

UPDATE [EO TABLE] INNER JOIN ASSIGNEES
ON [EO TABLE].ORIGINATOR = ASSIGNEES.[ASSIGN-NAMES]
SET [EO TABLE].OriginatorID = [ASSIGNEES].[ID];

That should get the numeric ID for every originator into the new
OriginatorID field.

5. Open the table in datasheet view and inspect the OriginatorID field to
make sure every record has a value in the field. If there are any records
that don't, these records must have ORIGINATOR values that are either blank
(Null) or are not in the ASSIGNEES table at all. If you want, you can run a
query to find such records, like this:

SELECT * FROM [EO TABLE] WHERE OriginatorID Is Null;

You must decide how to deal with these -- correct typos or add new records
to the ASSIGNEES table, then rerun the update query from step 4.

6. When you are satisfied that you have assigned an OriginatorID to every
record in [EO TABLE], or at least assigned one wherever it is possible, then
...

6a. Open [EO TABLE] in design view.
6b. Delete the ORIGINATOR field.
6c. Rename the OriginatorID field to "ORIGINATOR".
6d. Save the table design changes and close the table.

At this point, the table should have a numeric ORIGINATOR field that
correctly links to the ID field in the ASSIGNEES table.

No, that table has no direct relationship to the ASSIGNEES table, so there
is no need to link them in the query, and no fields on which to link them,
anyway.

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)