how to look for a value in a table using vba/sql

hi,

i am wanting a button to search through a table and see if a record already exists.

basically i have an order table, service table, serviceordertable (weak entity) and a serviceowner table (to link services to customers which uses the customer table). when i add an order, i want to add a service, for instace, customer A wants to order a new telephone from us, i want to go into the order form add customer details etc...then on the serviceorder subform i want to select an item, in this case "telephone" i want some code based on the change event of the combo box in the subform to look through the serviceowner table, and see if the current customer has already got a record containing both the serviceID of the service and the CustomerID....is this possible?
mrBrightsideAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

GcartyCommented:
you can use DCOUNT in VBA to check for the record. If you need help with the syntax post back.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jefftwilleyCommented:
Gather your values like below, making sure they aren't null or blank. If they are, then the code will fail. You may consider this prior to running it.

if the event you want this triggered from is on the subform, then you'll need to reference the customerID from the main form


Dim CustID as long
Dim ServID as long  

CustID = me.Parent.CustomerID   'This references the main or parent form and the control where you have the ID
ServID = me.ServiceID    ' This references the subform that you're on and the control where you have the ID

If DCount("*","YourTableName","[ServiceID]= " & ServID & " AND [CustomerID] = " & CustID) > 0 then
'the record exists
else
'the record doesn't exist
end if


That's the easiest way. You could also use a recordset but that's a little bit longer.

Hope this helps.
J
0
mrBrightsideAuthor Commented:
amazing, exaclty what i am after, you are lovely people.....i will even the points out, even though jefftwilley gave me the syntax, im sure gcarty knew it ;)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.