Passing customer ID between forms

[Thadeous]

Hi All

I have an order form. The first field is unbound and is used to write in the customer surname. on Key down event a second continuous form opens to the required search results through using "Docmd Openform where condition."

you then select a name the form closes transfering the CustomerID to the original order form using "Docmd Openform where condition." then away we go with rest of details for order.

The problem I have is if I want to change the customer it goes to the last record for the newly selected customer. If I manually change the customer ID it retains the order details and puts in new customer however if I select a new customer via the above key down event I loose the details of the order and it brings up the last order attributed to that customer.

Any help would be appreciated.

 

[Thadeous]

Hi All,

Not getting alot of replies to my question. Is this due to my poor attempt to explain the problem.

would appreciate some help.

T

 

[Norie]

Why is the field unbound?

Couldn't you use a bound field with a combobox for the customer name?

The combobox could actually be populated from a query that returns a unique list of customer IDs and names.

The ID could be the bound column and hidden if required.

Would that work for your set-up?

 

[Thadeous]

thnx Norie

Yes you can use the combo box selection method and it will work. However when you have a large amount of customer records to chose from it doesn't seem like the most efficient method.

The unbound field is a search field where the customers surname or part of surname is typed in and keydown event triggers search result in a separate continuous form.

From this result form a customer is selected then the CustomerID is passed back to the original form and the rest of the order details are filled out.

Everything works as it should when its a new order ie type in customer surname (onthe order form)then press enter to open the results form with the search results, chose the correct customer from results form. The customerID is passed back to order form and fill in rest of the details.

Problem arises when you try to edit an existing order and change the customer. It clears the order and won't simply change the customer name

Here's the code which passes the customerID from the Search results form back to the order form.

Private Sub SelectCustomer()
On Error GoTo Err_SelectCustomer

Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "frmCustomerMaster"

stLinkCriteria = "[CustomerId]=" & Me![CustomerId]

DoCmd.Close acForm, "frm_searchCustomerQury"

DoCmd.OpenForm stDocName, , , stLinkCriteria



Exit_SelectCustomer:
Exit Sub
Err_SelectCustomer:
MsgBox Err.Description
Resume Exit_SelectCustomer
End Sub


Regrds

T

 

[Norie]

I don't know why but that sounds sort of the wrong way round.

Why not search/filter the continuous form that has the customers?

Then once the user has located the customer of interest they hit a button that opens another form where they can enter the order details.

This 'order' form can be used to view/change existing orders or add new ones.

The customer ID would be used to open this form so it only displayed orders for that particular customer.

Perhaps that won't work for you, I don't really know what sort of 'orders' you are dealing with.

eg are there mutliple products in each order