Command Button - Input New Order

[Red6]

Hi

I have searched through the forums and can't find exactly what it is I am trying to do.

Essentially, on my Customers form, I have a command button which 'on click' opens the Input Order form - so far so good - however, what I want it to do, is open the Input Order form so that it goes to the record being shown on the Customers form.

Not sure whether I have explained that well but:

Viewing for example record 5 on my Customers form, I want to be able to Input a new order for that customer, therefore on opening the Input Order form all the customer header detail is populated, and I have an empty Order Details sub form in which to enter the order.

Hope this makes sense

Ian

 

[MSAccessRookie]

Hi

I have searched through the forums and can't find exactly what it is I am trying to do.

Essentially, on my Customers form, I have a command button which 'on click' opens the Input Order form - so far so good - however, what I want it to do, is open the Input Order form so that it goes to the record being shown on the Customers form.

Not sure whether I have explained that well but:

Viewing for example record 5 on my Customers form, I want to be able to Input a new order for that customer, therefore on opening the Input Order form all the customer header detail is populated, and I have an empty Order Details sub form in which to enter the order.

Hope this makes sense

Ian

You can add a Where Condition to your OpenForm Statement. In VB it would look something like the following (make changes where it is RED):

DoCmd.OpenForm "[COLOR=red][B]YourFormName[/B][/COLOR]", acNormal, , "{[COLOR=red][B]FieldToTest[/B][/COLOR]} = {[COLOR=red][B]ValueToScreenFor[/B][/COLOR]}, acFormEdit, acWindowNormal

Note: If you are using Macros, there is a Where Field to add the Condition to.

 

[Red6]

Thanks but I have as good as zero knowledge of VB and as such am trying to do this using Macros.

This is what I have in the Where function of my macro on the Open Form action.

[CustomerID]=[Forms]![Customers]![CustomerID]

This returns no data in the target form.

 

[MSAccessRookie]

Thanks but I have as good as zero knowledge of VB and as such am trying to do this using Macros.

This is what I have in the Where function of my macro on the Open Form action.

[CustomerID]=[Forms]![Customers]![CustomerID]

This returns no data in the target form.

I am not sure where the problem is since the syntax looks correct to me. Of course I assume that your first [CustomerID] is intended to represent[Input Order]![Customers]![CustomerID]

 

[Red6]

Even having used the Command Button wizard, there is still something not right with the way the function of the Input New Oder button is operating.

The button correctly opens the Input Orders form, but will only populate the customer header details for records where an order has been previously entered.

For example:

Customer 1 has 3 orders and on clicking the Input New Order button on the Customers form, with customer 1 displaying, it correctly displays all the header data pertaining to the customer, in the top of the Input Orders form.

Customer 5 has placed no orders and with the customer selected in the Customers form, click on the Input New Order button and the form opens but displays no customer information (ie name, customer ID etc etc)

The only thing I can think of is that it is something to do with the relationship between the customer and it's orders.

Ian

 

[Red6]

I have just noticed that when the Orders form is opened via the command button, a filter is applied and shows just 3 records. Interestingly the customer to which it refers, has currently got 3 orders in my test data.

The code which controls the command button is shown below.

Private Sub New_Order_btn_Click()
On Error GoTo Err_New_Order_btn_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Input Orders"

stLinkCriteria = "[CustomerID]=" & "'" & Me![CustomerID] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_New_Order_btn_Click:
Exit Sub

Err_New_Order_btn_Click:
MsgBox Err.Description
Resume Exit_New_Order_btn_Click

End Sub


Is there a fault in this code which might be causing the problem I am having?

Thanks

Ian

 

[MSAccessRookie]

I have just noticed that when the Orders form is opened via the command button, a filter is applied and shows just 3 records. Interestingly the customer to which it refers, has currently got 3 orders in my test data.

The code which controls the command button is shown below.

Private Sub New_Order_btn_Click()
On Error GoTo Err_New_Order_btn_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Input Orders"

stLinkCriteria = "[CustomerID]=" & "'" & Me![CustomerID] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_New_Order_btn_Click:
Exit Sub

Err_New_Order_btn_Click:
MsgBox Err.Description
Resume Exit_New_Order_btn_Click

End Sub


Is there a fault in this code which might be causing the problem I am having?

Thanks

Ian

I usually define more of the OpenForm Parameters than you did, so my OpenForm Statement looks more like the following:

DoCmd.OpenForm stDocName, acNormal, , stLinkCriteria, acFormEdit, acWindowNormal

I am not too sure whether any of the differences matter, but you can give them a try.

 

[Red6]

I am getting really bogged down with this now - The attached just contains some test data and I would really appreciate it if someone could have a look at this for me.

If you open the Customers form and scroll until you get to customer Berry PCs then hit the Input New Order Button.

What will happen is that the Customer Reference number field is still populated from the first order placed, hence it is trying (I think) to add to the first order.

What I would like to happen is on clicking the Input New Order button, it does exactly that and allows a new order to be created against the selected customer.

Just about everything else works OK other than the Orders table is not being populated when the values are entered.

Perhaps I have got the structure of this DB wrong but I am only learning so be gentle with me.

Thanks

Ian

 

[Red6]

I guess that if this can't be resolved via this forum, then it can't be done.

Ian

 

[boblarson]

I'm taking a look at it and it may take me a while because lunch is over now. I'll see what I can do and get back to you tonight when I am home.

 

[boblarson]

Unless of course someone else handles it first

 

[Red6]

Thanks Bob - you're a star