Nov 27 2006, 05:56 PM
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?
Nov 27 2006, 06:02 PM
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?
Nov 27 2006, 06:05 PM
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
Nov 27 2006, 06:08 PM
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.
Nov 27 2006, 06:13 PM
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
Nov 27 2006, 06:16 PM
It doesn't work with after Update because OrderNumber is not being updated, it's just viewing all current records of OrderNumber
Nov 27 2006, 06:18 PM
Then just in the Current event of the form will be all you need.
Nov 27 2006, 06:23 PM
Try this in the On Current event -
If Not IsNull("[OrderNumber]", "TableNameHere", "[OrderNumber] = & " Me.OrderNumber Then
Me.SubformControlName.Visible = True
Me.SubformControlName.Visible = False
Nov 27 2006, 06:27 PM
WHat should I replace the "[OrderNumber]" with?
The OrderNumber in the form or the OrderNumber in the table it is looking for?
Nov 27 2006, 06:37 PM
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?
Nov 27 2006, 08:57 PM
I guess you are trying to code:
If Not IsNull(DLookup("[OrderNumber]", "TableNameHere", "[OrderNumber] = " & Me.OrderNumber)) Then
and the keyboard went haywire???
I tend to use DCount() for this like:
If DCount("[OrderNumber]", "TableNameHere", "[OrderNumber] = " & Me.OrderNumber) > 0 Then
Nov 27 2006, 09:07 PM
... because OrderNumber is not in the form for it to read yet ...
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 ...
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here