Using Filter on a form on 4 tables

M

MChau

I am trying to build a form that will allow the user to query records
depending on the vendor id / vendor account. Here are the tables:

tblVendor ( column Vendor_ID, Vendor_Name)
tblVendor_Acct (columns Vendor_ID, Vendor_Acct_ID, Vendor_Acct_Name)
tblAllocation (columns Vendor_ID, Vendor_Acct_ID, Employee_ID,
Allocation_Costs)
tblEmployee_ID (columns Employee_ID, Employee_Name, Employee_Area)

on the top of the form, I have 2 combo boxes - 1 for Vendor ID, the other
for Vendor Acct ID). Then I have a command box that uses a filter on the
form which brings me all the records in tblAllocation for that Vendor ID &
Vendor Acct ID but cannot bring back the correct value from tblEmployee_ID
(the form displays the same Employee Name & Area in the first record as in
all subsequent records). Any help would be appreciated.
 
M

MChau

Hi, thanks for the response. I got it to work now, but now I have another
problem. here is my SQL:

SELECT tblVendor.Vendor_ID, tblVendor_Acct.Vendor_Acct_ID,
tblAllocation.No_of_Users, tblEmployee_Dept.Employee_Dept_Name,
tblEmployee_Dept.Work_Area, tblAllocation.Total_No_of_Users,
tblAllocation.Total_Allocation, tblAllocation.Total_Allocation_GST FROM
((tblAllocation INNER JOIN tblEmployee_Dept ON tblAllocation.Employee_ID =
tblEmployee_Dept.Employee_ID) INNER JOIN tblVendor ON tblAllocation.Vendor_ID
= tblVendor.Vendor_ID) INNER JOIN tblVendor_Acct ON (tblVendor.Vendor_ID =
tblVendor_Acct.Vendor_ID) AND (tblAllocation.Vendor_Acct_ID =
tblVendor_Acct.Vendor_Acct_ID);

I only want to bring up the records that the user chooses in the combo boxes
- Vendor Name / Vendor Acct. I am trying to use the filter function but
cannot get it to work. Here is my combo boxes:

cboVendor_Name:
Row/Source : Table/Query
Row Source : SELECT [tblVendor].[Vendor_ID], [tblVendor].[Vendor_Name] FROM
[tblVendor] ORDER BY [Vendor_Name];
Column Count: 2
Column Widths: 0";1"

cboVendor_Acct:
Row/Source : Table/Query
Row Source : SELECT tblVendor_Acct.Vendor_ID, tblVendor_Acct.Vendor_Acct,
tblVendor_Acct.Vendor_Acct_ID, tblVendor_Acct.Vendor_Other_ID FROM
tblVendor_Acct ORDER BY tblVendor_Acct.Vendor_Acct;
Column Count: 4
Column Widths: 0";1";0";0"

I have a command box that puts the values of the combo boxes in the filter:

Me.Filter = "([Vendor_ID] = 1 AND [Vendor_Acct_ID] = 1)"
Me.FilterOn = True

I am getting no data displayed but there is 8 records in the table for this
vendor_ID/Vendor_Acct_ID).

Thanks in advance! sorry for being so wordy...
 
K

KARL DEWEY

You need to AND the combo boxes in the query referencing them like this --
[Forms]![YourFormName]![cboVendor_Name]
and
[Forms]![YourFormName]![cboVendor_Acct]

--
KARL DEWEY
Build a little - Test a little


MChau said:
Hi, thanks for the response. I got it to work now, but now I have another
problem. here is my SQL:

SELECT tblVendor.Vendor_ID, tblVendor_Acct.Vendor_Acct_ID,
tblAllocation.No_of_Users, tblEmployee_Dept.Employee_Dept_Name,
tblEmployee_Dept.Work_Area, tblAllocation.Total_No_of_Users,
tblAllocation.Total_Allocation, tblAllocation.Total_Allocation_GST FROM
((tblAllocation INNER JOIN tblEmployee_Dept ON tblAllocation.Employee_ID =
tblEmployee_Dept.Employee_ID) INNER JOIN tblVendor ON tblAllocation.Vendor_ID
= tblVendor.Vendor_ID) INNER JOIN tblVendor_Acct ON (tblVendor.Vendor_ID =
tblVendor_Acct.Vendor_ID) AND (tblAllocation.Vendor_Acct_ID =
tblVendor_Acct.Vendor_Acct_ID);

I only want to bring up the records that the user chooses in the combo boxes
- Vendor Name / Vendor Acct. I am trying to use the filter function but
cannot get it to work. Here is my combo boxes:

cboVendor_Name:
Row/Source : Table/Query
Row Source : SELECT [tblVendor].[Vendor_ID], [tblVendor].[Vendor_Name] FROM
[tblVendor] ORDER BY [Vendor_Name];
Column Count: 2
Column Widths: 0";1"

cboVendor_Acct:
Row/Source : Table/Query
Row Source : SELECT tblVendor_Acct.Vendor_ID, tblVendor_Acct.Vendor_Acct,
tblVendor_Acct.Vendor_Acct_ID, tblVendor_Acct.Vendor_Other_ID FROM
tblVendor_Acct ORDER BY tblVendor_Acct.Vendor_Acct;
Column Count: 4
Column Widths: 0";1";0";0"

I have a command box that puts the values of the combo boxes in the filter:

Me.Filter = "([Vendor_ID] = 1 AND [Vendor_Acct_ID] = 1)"
Me.FilterOn = True

I am getting no data displayed but there is 8 records in the table for this
vendor_ID/Vendor_Acct_ID).

Thanks in advance! sorry for being so wordy...

KARL DEWEY said:
Post the SQL of the query you are using for the form source.
Click to expand...
Click to expand...
 
M

MChau

Thank you, Karl! I thought I tried that before, but because I tried so many
other ways, I got confused. Much appreciated!

KARL DEWEY said:
You need to AND the combo boxes in the query referencing them like this --
[Forms]![YourFormName]![cboVendor_Name]
and
[Forms]![YourFormName]![cboVendor_Acct]

--
KARL DEWEY
Build a little - Test a little


MChau said:
Hi, thanks for the response. I got it to work now, but now I have another
problem. here is my SQL:

SELECT tblVendor.Vendor_ID, tblVendor_Acct.Vendor_Acct_ID,
tblAllocation.No_of_Users, tblEmployee_Dept.Employee_Dept_Name,
tblEmployee_Dept.Work_Area, tblAllocation.Total_No_of_Users,
tblAllocation.Total_Allocation, tblAllocation.Total_Allocation_GST FROM
((tblAllocation INNER JOIN tblEmployee_Dept ON tblAllocation.Employee_ID =
tblEmployee_Dept.Employee_ID) INNER JOIN tblVendor ON tblAllocation.Vendor_ID
= tblVendor.Vendor_ID) INNER JOIN tblVendor_Acct ON (tblVendor.Vendor_ID =
tblVendor_Acct.Vendor_ID) AND (tblAllocation.Vendor_Acct_ID =
tblVendor_Acct.Vendor_Acct_ID);

I only want to bring up the records that the user chooses in the combo boxes
- Vendor Name / Vendor Acct. I am trying to use the filter function but
cannot get it to work. Here is my combo boxes:

cboVendor_Name:
Row/Source : Table/Query
Row Source : SELECT [tblVendor].[Vendor_ID], [tblVendor].[Vendor_Name] FROM
[tblVendor] ORDER BY [Vendor_Name];
Column Count: 2
Column Widths: 0";1"

cboVendor_Acct:
Row/Source : Table/Query
Row Source : SELECT tblVendor_Acct.Vendor_ID, tblVendor_Acct.Vendor_Acct,
tblVendor_Acct.Vendor_Acct_ID, tblVendor_Acct.Vendor_Other_ID FROM
tblVendor_Acct ORDER BY tblVendor_Acct.Vendor_Acct;
Column Count: 4
Column Widths: 0";1";0";0"

I have a command box that puts the values of the combo boxes in the filter:

Me.Filter = "([Vendor_ID] = 1 AND [Vendor_Acct_ID] = 1)"
Me.FilterOn = True

I am getting no data displayed but there is 8 records in the table for this
vendor_ID/Vendor_Acct_ID).

Thanks in advance! sorry for being so wordy...

KARL DEWEY said:
Post the SQL of the query you are using for the form source.
--
KARL DEWEY
Build a little - Test a little


:

I am trying to build a form that will allow the user to query records
depending on the vendor id / vendor account. Here are the tables:

tblVendor ( column Vendor_ID, Vendor_Name)
tblVendor_Acct (columns Vendor_ID, Vendor_Acct_ID, Vendor_Acct_Name)
tblAllocation (columns Vendor_ID, Vendor_Acct_ID, Employee_ID,
Allocation_Costs)
tblEmployee_ID (columns Employee_ID, Employee_Name, Employee_Area)

on the top of the form, I have 2 combo boxes - 1 for Vendor ID, the other
for Vendor Acct ID). Then I have a command box that uses a filter on the
form which brings me all the records in tblAllocation for that Vendor ID &
Vendor Acct ID but cannot bring back the correct value from tblEmployee_ID
(the form displays the same Employee Name & Area in the first record as in
all subsequent records). Any help would be appreciated.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

trying to get a sum in access 2003 1
Requery subform Field based on different Field's value 0
Need help to Simplify a Complex Calculation on a Form 0
Add new records into source table on Form 8
button on form 3
Build search function in to a form 5
using combo boxes to filter records in a subform 10
filtering a form based on a filter on an open form 2

Top