current community

your communities

more stack exchange communities

Stack Exchange
tour help
stack overflow careers
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 2 down vote favorite

I would like to write a macro that will run every time a record is entered into a certain table. What I want the macro to do is check to see if there is a related record in a different table, and if so, to change a field of data type Yes/No to Yes. If no related record is defined yet, I would do nothing, as the default for the Yes/No field is No.

The two tables are related in this way.

So when a record is entered in to the tblOrders table, I need to check if a related record exists in the tblRecipes table, and I would like to use a macro to do so if possible, as I will be repeating this action for every Order entered.

Is this possible, and if so, what would the macro language be please?

share|improve this question

1 Answer 1

up vote 2 down vote accepted

The usual thing would be to use forms for data entry and to check the Recipes table in the before update event for the control, for example. 

Private Sub CustomerPartNumber_BeforeUpdate(Cancel As Integer)
    If Not IsNull(DLookup( _
     "CustPartNum", "tblRecipes", "CustPartNum=" & Me.CustomerPartNumber)) Then
        Me.AYesNoField = True
    Else
        Me.AYesNoField = False
    End If
End Sub

However, in Access 2010, you can use data macros. This example will run whenever CustomerPartNumber is changed in Orders.

before change data macro

share|improve this answer
1  
Yes, if you are working within MS Access, use VBA, it will give you more control. I have shown the Before Update event in case you need to cancel the update, but if all you are doing is changing the YesNo field, After Update is just as good. –  Fionnuala Jan 21 '13 at 0:50
1  
For text, you need quotes: "CustPartNum", "tblRecipes", "CustPartNum='" & Me.CustomerPartNumber & "'" –  Fionnuala Jan 21 '13 at 15:54
1  
You should not store the name of the Company, it will lead to problems, you should store the ID, you cam always get the name from a query. You cam set up you combo with a row source select id, company from customers -- column widths 0;2 -- column count 2 -- bound column 1 bound column is the value that will be stored to the field set in control source –  Fionnuala Jan 21 '13 at 16:40
1  
Use a query as the basis of the report: select company, orderdetail from orders inner join customers on customers.id=orders.customerid, assuming only a customer can have an order, if orders do not always have an associated customer, you need a left join –  Fionnuala Jan 21 '13 at 17:59
1  
Like: SELECT tblOrders.SalesOrderNumber, tblOrders.Customer, tblOrders.PONumber, tblOrders.OrderDate, tblOrders.DueDate, tblOrders.CustomerPartNumber, tblOrders.FONumber, tblOrders.Alloy, tblOrders.Temper, tblOrders.Finish, tblRecipes.StepOne, tblRecipes.StepTwo, tblRecipes.StepThree FROM (tblRecipes INNER JOIN tblOrders ON tblRecipes.CustPartNum = tblOrders.CustomerPartNumber) INNER JOIN tblCustomers ON tblOrders.Customer = tblCustomers.ID WHERE tblOrders.FONumber=[FO Number?] Use the query design window to get it right. –  Fionnuala Jan 21 '13 at 19:28

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.