Data Entry form showing Inactive and Active data - Should only have choice of Active 1

[monkeysee]

I have the following tables:
Tables:
1) Table name: Fee
FeeID
Description
Amount
Inactive yes/no

2) Table name: Invoice
InvoiceID
CustomerID
BillingDate

3) Table name: InvoiceDetails
InvoiceID
FeeID
TotalBilled

My data entry form is based on a query with the above tables. The criteria in the query in the inactive field: =False. However when using the form, the combobox of the description field is still pulling the inactive fee in addition to the active fee. I would like only the active fees available for the user to avoid any errors in billing. What am I missing? Thanks is advance!

 

[lameid]

Hard to know without your actual SQL statement but in general I find it better to yes/no and not true/false as keywords in queries.... VBA is the other way use True/False and not Yes/No.

 

[monkeysee]

Here is the sql statement:

SELECT Visit.VisitID, Visit.ClientID, Visit.EmployeeID, Visit.VisitDate, Service.Description, Service.Fee, VisitDetails.Memo, Service.Inactive
FROM (Visit INNER JOIN VisitDetails ON Visit.VisitID = VisitDetails.VisitID) INNER JOIN Service ON VisitDetails.ServiceID = Service.ServiceTypeID
WHERE (((Service.Inactive)=False));

 

[PHV]

And what is the SQL code othe combo's RowSource property?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[lameid]

Seems like you are using inactive from the wrong table... one you did not list in your original post.

WHERE ((([red]Fee[/red].Inactive)=[red]No[/red]));

 

[monkeysee]

SELECT Service.ServiceTypeID, Service.Description
FROM Service
ORDER BY Service.Description;

 

[monkeysee]

Actually, Table #1's name is Service not Fee

 

[lameid]

I read over your combobox not working... Just add the same criteria to the row source and it should work.

 

[monkeysee]

That worked!!! Thanks!