• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 206
  • Last Modified:

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


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?
2 Solutions
you can use DCOUNT in VBA to check for the record. If you need help with the syntax post back.
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
'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.
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 ;)
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now