Two Form Fields Reference The Same Table Field

[Wayne]

I have a search form where users can enter data to search for
projects. On this form is a field called “Opened By” and another
called “Assigned To.” Both fields are combo boxes where the First and
Last names are concatenated like [Contacts].[First Name] & “ “ &
[Contacts].[First Name] via a query. Apparently because I was
referencing the same table field my search results were ambiguous so I
created a second Contacts table called Contacts_2, which is an exact
copy of the Contacts table. I then linked the “Assigned To” field to
Contacts_2 and all my search results worked correctly. This becomes a
pain if I have to update the Contacts tables. Is there a way to
reference the same field in a table from two different textboxes on a
form using the same query? Thanks in advance for your help.

 

[DStegon via AccessMonster.com]

You should never have to create a duplicate table. Waster of space and is
counter productive.

You should be able to use the one contact table, just have two instances of
that one table in your query. I am assuming the there is a table that has
the opened by and assigned to fields with the contact id stored. If so, then
when creating the query, just add a second instance of the contact table and
join the "assigned to" to one and the "opened by" to the other. You may want
to make them "right or left" joins instead of INNER, because then only those
records that were both "opened" and "assigned" would show up.

I have a search form where users can enter data to search for
projects. On this form is a field called “Opened By†and another
called “Assigned To.†Both fields are combo boxes where the First and
Last names are concatenated like [Contacts].[First Name] & “ “ &
[Contacts].[First Name] via a query. Apparently because I was
referencing the same table field my search results were ambiguous so I
created a second Contacts table called Contacts_2, which is an exact
copy of the Contacts table. I then linked the “Assigned To†field to
Contacts_2 and all my search results worked correctly. This becomes a
pain if I have to update the Contacts tables. Is there a way to
reference the same field in a table from two different textboxes on a
form using the same query? Thanks in advance for your help.

 

[Jeff Boyce]

Wayne

Are you saying that you created a duplicate table? Why not create a second
query? Why even bother with a second query?!

If [OpenedBy] and [AssignedTo] could be the same individual, why not use the
same query for each combobox?

By the way, [FirstName] & " " & [FirstName] is probably not what you
meant/want.

Regards

Jeff Boyce
Microsoft Office/Access MVP


I have a search form where users can enter data to search for
projects. On this form is a field called “Opened By” and another
called “Assigned To.” Both fields are combo boxes where the First and
Last names are concatenated like [Contacts].[First Name] & “ “ &
[Contacts].[First Name] via a query. Apparently because I was
referencing the same table field my search results were ambiguous so I
created a second Contacts table called Contacts_2, which is an exact
copy of the Contacts table. I then linked the “Assigned To” field to
Contacts_2 and all my search results worked correctly. This becomes a
pain if I have to update the Contacts tables. Is there a way to
reference the same field in a table from two different textboxes on a
form using the same query? Thanks in advance for your help.

 

[Wayne]

Wayne

Are you saying that you created a duplicate table?  Why not create a second
query?  Why even bother with a second query?!

If [OpenedBy] and [AssignedTo] could be the same individual, why not use the
same query for each combobox?

By the way, [FirstName] & " " & [FirstName] is probably not what you
meant/want.

Regards

Jeff Boyce
Microsoft Office/Access MVP


I have a search form where users can enter data to search for
projects.  On this form is a field called Opened By and another
called Assigned To.  Both fields are combo boxes where the First and
Last names are concatenated like [Contacts].[First Name] & &
[Contacts].[First Name] via a query.  Apparently because I was
referencing the same table field my search results were ambiguous so I
created a second Contacts table called Contacts_2, which is an exact
copy of the Contacts table.  I then linked the Assigned To field to
Contacts_2 and all my search results worked correctly.  This becomes a
pain if I have to update the Contacts tables.  Is there a way to
reference the same field in a table from two different textboxes on a
form using the same query?  Thanks in advance for your help.

The search form fields are UNBOUND. The Where statement is created in
VBA (see below).

' If Assigned To
If Not IsNull(Me.AssignedTo) Then
'Create Predicate
strWhere = strWhere & " AND " & "tblProjects.[Assigned To] = " &
Me.AssignedTo & ""
End If

' If Opened By
If Not IsNull(Me.OpenedBy) Then
'Add the predicate
strWhere = strWhere & " AND " & "tblProjects.[Opened By] = " &
Me.OpenedBy & ""
End If

Creating a second instance of Contacts.ID (creates Contacts_1.ID in
the query) seems to work better. My search form subform displays the
correct data but I'm still having trouble with my report. I tried
using the same Where statement from the subform by stripping off the
"1=1 AND" part of the filter but it keeps asking me to Enter Parameter
Value when running by report command:

Me.Filter = stFilter
Me.FilterOn = True
DoCmd.OpenReport ReportName:=strDocName, View:=acPreview,
WhereCondition:=Me.Filter

I'd like to use my subform filter below with my report.

Me.frmBrowseProjects.Form.Filter = strWhere
Me.frmBrowseProjects.Form.FilterOn = True

As I said the frmBrowseProjects form works correctly.

What I'm doing now is creating a complete separate set of VBA code for
my report.

 

[Wayne]

Wayne

Are you saying that you created a duplicate table?  Why not create a second
query?  Why even bother with a second query?!

If [OpenedBy] and [AssignedTo] could be the same individual, why not use the
same query for each combobox?

By the way, [FirstName] & " " & [FirstName] is probably not what you
meant/want.

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Wayne" <[email protected]> wrote in message

I have a search form where users can enter data to search for
projects.  On this form is a field called Opened By and another
called Assigned To.  Both fields are combo boxes where the First and
Last names are concatenated like [Contacts].[First Name] & &
[Contacts].[First Name] via a query.  Apparently because I was
referencing the same table field my search results were ambiguous so I
created a second Contacts table called Contacts_2, which is an exact
copy of the Contacts table.  I then linked the Assigned To field to
Contacts_2 and all my search results worked correctly.  This becomes a
pain if I have to update the Contacts tables.  Is there a way to
reference the same field in a table from two different textboxes on a
form using the same query?  Thanks in advance for your help.

The search form fields are UNBOUND.  The Where statement is created in
VBA (see below).

' If Assigned To
If Not IsNull(Me.AssignedTo) Then
    'Create Predicate
    strWhere = strWhere & " AND " & "tblProjects.[Assigned To] = " &
Me.AssignedTo & ""
End If

' If Opened By
If Not IsNull(Me.OpenedBy) Then
    'Add the predicate
    strWhere = strWhere & " AND " & "tblProjects.[Opened By] = " &
Me.OpenedBy & ""
End If

Creating a second instance of Contacts.ID (creates Contacts_1.ID in
the query) seems to work better.  My search form subform displays the
correct data but I'm still having trouble with my report.  I tried
using the same Where statement from the subform by stripping off the
"1=1 AND" part of the filter but it keeps asking me to Enter Parameter
Value when running by report command:

Me.Filter = stFilter
Me.FilterOn = True
DoCmd.OpenReport ReportName:=strDocName, View:=acPreview,
WhereCondition:=Me.Filter

I'd like to use my subform filter below with my report.

Me.frmBrowseProjects.Form.Filter = strWhere
Me.frmBrowseProjects.Form.FilterOn = True

As I said the frmBrowseProjects form works correctly.

What I'm doing now is creating a complete separate set of VBA code for
my report.- Hide quoted text -

- Show quoted text -

It now works! I changed the data source on Reports to match the
Browse Form. Once this was done I could use the same code for each.