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.

 

[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.