Combo List Values to Ignore Certains Records

[breakingme10]

I need some help

I have a several tables but the ones i'm working on now are: Company, LeaseSites, and Water Tickets.

I have several lease sites that go to Newfield that have the same name as sites for other companies but they are in different locations.

We have two separate people that do the billing. 1 gets all the Newfield 2 Gets all the other companies. I am working on how to best filter this for the two. The actual water tickets have all the information on them that the table "Water Tickets" has. 28 Fields. A lot of information. I have the drivers in their own table, the trucks in their own table (the same driver doesn't always get the same truck each time).. How can I tell it to query only newfield vs all other companies.

LeaseSites looks like this:




Company:

Water Tickets:

InvoiceNum

Newfield

TicketDate

Time1

Time2

Time3

Time4

BillTo

Lease

Sec

T

R

County

Origin

Destination

Product

StartGauge1

StopGauge1

StartGauge2

StopGauge2

StartGauge3

StopGauge3

Service

Remarks

Driver

TruckNum

Barrels

Hours

(YES/NO)

[ItsMe]

How are you populating the field "LeaseCompany" in your table "LeaseSites"? I see a name and a PK

[breakingme10]

SELECT [Company.CompanyName] & " " & [Company.Company_ID] AS LeaseCompany FROM Company;

[ItsMe]

So LeaseSites is a query? Include the PK as its own field in the query and use WHERE criteria in your combo's RowSource to select only the desired records. YOu can use the query name in the RowSource to make things simple.

Here is a WHERE clause example

"WHERE Company = " & Me.CompanyID.Value

So maybe
Me.Combo.Rowsource = "SELECT Company, LeaseCompany FROM LeaseSites WHERE Company = " & Me.CompanyID.Value

Give your combo properties Column count = 2
Column Width = 0, 2
and bound column = 1

Should get you started anyway



This assumes you have a form with a control named CompanyID that is bound to your company table.

[breakingme10]

No lease sites is a table. the field is from an embedded lookup query.

[ItsMe]

Consider storing PK values as foreign keys in other tables. You can use the query builder to help construct your SQL for your combobox. In the property sheet for your combobox click the eclipses (...) next to the rowsource to launch the query builder.

You can add the where clause to the end of the string. Right click the rowsource property and use the zoom feature.