Is this even possible??

[RuralGuy]

Have you examined the InputBox() function?

 

[GregoryWest]

Sorry nope never used the "inputbox()" function. Looked up the syntax, so are you thinking of the button what opens the form and needs to prompt does an openform where number = inputbox(), and the other places openform where number = the_variable_already_known?

Something like that. And the embedded query has no where component to it at all.

 

[RuralGuy]

Yes, I'm thinking the embedded query has no where component. Your form with the three buttons could use the InputBox on that form to get the number you want and then open the next form with a WhereCondition argument.

 

[GregoryWest]

Ah sounds good, let me take a stab at this and see if I can get it working. Will make the end user happy not to have to type in the generated order number.

 

[GregoryWest]

I tried that out, I have copied a screen shot of the error I am getting on the OpenForm. The field Order_Number is correct and the only one in the query.

Hmmm the paste of the screen shot did not show up. I am getting an error 3079 The specified field 'Order_Number' could refer to more than one table listed in the from clause of your SQL statement.

The openform is:
DoCmd.OpenForm "PartsWorkOrderEdit", acNormal,,"Order_Number="&Work_Order_Number

 

[RuralGuy]

You've put two of the field on your query:

SELECT PartsWorkOrder.*, PartsWorkOrder.Order_Number
FROM PartsWorkOrder

Change it to...

SELECT PartsWorkOrder.*
FROM PartsWorkOrder

 

[GregoryWest]

I got rid of the extra field, not the form is callable from the VBA code. YEAH!!!
Then I try and call it from a Macro right off the button it still does not work. I get the: Function not available in expressions. Error. Not a great deal, I can just add a snippet of VBA code instead of a function.

Works great where the button that is supposed to ask the user for the order number does so, then opens the form.

I owe you all a HUGE thanks!! Been fighting with this for a long time time to make it work this smooth for the user.

 

[RuralGuy]

Just post back to this thread if it is still stubborn, otherwise go ahead and use the Thread Tools at the top of this thread to mark the thread as Solved.

 

[GregoryWest]

Done and added to both your reputations! Thanks again, would not have thought of solving the problem this way!!

 

[RuralGuy]

There are many features of Access that some never experience. It is really a pretty good Rapid Application Development (RAD) system.