I have a piece of code which when a form is opens

Reads in OrderNumber from the form data
Puts OrderNumber into a query
Returns a new value from the query.

I would like this to happen everytime the form is opened and when a new record is selected. Obviously I cannot put it in Form_Open() because OrderNumber is not in the form for it to read yet. However I want it to happen as soon as OrderNumber has been recognised by the form, and everytime the record is changed i.e OrderNumber is changed.

How do I do this? Which event should it go under?

Thanks
Forg

What is the purpose of the code that you want to run? Why do you need it to get a number from the record that opens when the form opens? Are you trying to create your own OrderNumbers programmatically?

Jack

I'm running a query which sees if the OrderNumber exists in a table, if it does then I want a certain subform to become visible, or, if it's not, i.e the query is empty then the subform stays not visible

Forg

It is not real clear to me what your query accomplishes. Returns a new value for what? Is it needed for *every* existing record? Where does the value go? Does the user key in the OrderNumber by hand?

Edit: Took too long to ask my questions! Put your code in the AfterUpdate event of the control that displays the OrderNumber and similar code in the Current event of the form.

Edited by: RuralGuy on Mon Nov 27 18:11:38 EST 2006.

OrderNumber is an autonumber.

This autonumber then goes into a query which basically says, take this OrderNumber, tell me if it exists in table x. If it does the query will have something in, if it doesn't the query will be empty. I want this to run everytime OrderNumber changes

It doesn't work with after Update because OrderNumber is not being updated, it's just viewing all current records of OrderNumber

Forg

Then just in the Current event of the form will be all you need.

Try this in the On Current event -

If Not IsNull("[OrderNumber]", "TableNameHere", "[OrderNumber] = & " Me.OrderNumber Then
Me.SubformControlName.Visible = True
Else
Me.SubformControlName.Visible = False
End if

Jack

WHat should I replace the "[OrderNumber]" with?

The OrderNumber in the form or the OrderNumber in the table it is looking for?

Forg

You are running a query to see if a number exists in a table. The code I gave you looks to see if the number exists without the use of a query. I have no idea where you are running your current query from. All I know is that you want a subform to be visible or not visible depending on if a record appears in a table. The code I gave you can tell you that, but since haven't a clue as to where you are running your code I can't give you the specifics of the control names to use in the code. Does that make sense? In other words, if you have a form and on that form is a control with a number and you want to see if that number exists in another table then the code I suggested will do that but you will have to fill in the table name and a couple of other things in the code...

Does this help?

Jack

Jack

I guess you are trying to code:



and the keyboard went haywire???

I tend to use DCount() for this like:

Where did you get this???

AFAIK, the Field values of the soon-to-be Current Record are available in the Open Event of the bound Form. The eventual Current Record may be different later since VBA code in Open / Load ... Event may force a new Current Record ...

You can check this by placing some Debug statements in the Form_Open Event Procedure like:



However, I agree with Jack the the Current Event is more appropriate in this case ...