Search for a Record Problem 2

[Nelz]

I'm trying to make a mini search form that opens my main form, Orders. I made a simple text box to enter the order ID . The problem is that the Orders Form expects to be opened from another source, so it is looking for the parameter CustomerID. I'm using this code on the button.
Private Sub Command4_Click()
On Error GoTo Err_Command4_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Orders"

stLinkCriteria = "[OrderID]=" & Me![OrderID]
DoCmd.Openform stDocName, , , stLinkCriteria

Exit_Command4_Click:
Exit Sub

Err_Command4_Click:
MsgBox Err.Description
Resume Exit_Command4_Click

End Sub


It gives an error
"Enter Parameter Value" Forms!OrdersbyCustomer!CustomerID

Is there a way to prevent that and pass the value from the mini search form?

 

[SyedAqif]

Hi

In the begining of ur code it might be worth while by putting the form filter to null.

Me![OrdersForm].Form.Filter = "IsNull(CustomerID)"

i dont know whats causing the problem but my main guess is that some filter is attached to it...

Cheers!
Aqif

 

[Nelz]

Well...The Orders form is actually opened FROM the Orders By Customer Form, which passes the Customer ID to it. The form then opens with the number of records being equal to the Orders for that Customer. Searching for a specific order within that set is no problem. What I want to do is pass an Order number to that form and have it open it REGARDLESS of the Customer ID.

 

[Databaseguy]

Assuming your form is bound to a query, add this code to your open button:

Dim DBS as database
Dim qdef as queryDef
Set DBS = Current DB
Set qdef = dbs."Query that has the paramater"
qdef![Forms].[OrdersbyCustomer].[CustomerID]= "Your Variable

You may have to go to the query and qualify the parameter also.
Tyrone Lumley
augerinn@gte.net

http://server105.hypermart.net/databaseguy

 

[Nelz]

Thanks for the help. It isnt really a parameter. What I want to do is type into a text box any order number, like 226......and have the Orders form open that Order. That particular form has 2 subforms on it, and somewhere along the line it needs to know the CustomerID, so it only looks in the Orders for that Customer. What I want is to have it bypass that parameter and just open the record based on ALL Customer ID's.

I put a search box on the form itself, and it works fine as long as the order number you enter in the search box is one that is for the particular customer that the form is open to.

If you put in an order number for a customer that it isnt open to already, it doesnt work.

Is there any way to do this? I've been scratching my head on this one since yesterday. Seems like it should be do-able.

Nelz

 

[Nelz]

OK I removed the Criteria in the filed for CustomerID in the underlying SQL statement for the Form Data. It now Works (Thanks to you all for the suggestions), although when there is no order yet placed for a Customer on the first form and you open this one, it defaults to the last open record for the customer until you hit the button to start a new order.

Sounds complicated and its not really a big deal. I can live with that....now that this works. Hopefully I wont find too many other little things that got screwed up as a result of making that change.

I think Tyrone had the right idea, but I was a little confused as to the exact language for what I'd have to do. I assume he was saying that instead of deleting the refence to the CustomerID in the criterea of the existing query, to make a new querey eliminating that refernce for the purpose of the search button. Therefore everything else would remain the same. If that IS what he meant, it's exactly what I wanted to do. Again...great tip for lots of people with a similar problem. I'm just not sure I can do it!