Return to last record of previous form??

[Byrt]

I have a WorkOrder form with a combo box to select customer which fills in customer info in the Customer Subform. I also have a command button for Add New Customer that opens the full Customer Form. What I need is to return to the record the user started to add in WorkOrder, before they realized this was a New Customer, when they close the Customer Form. I'm sure this can be done with a command button but I'm not sure whether I should be using GoToRecord, Bookmark or what. I'm pretty new to this. Any help appreciated.

 

[KenReay]

Hi

You do not need to 'return' to the record, you never left it.

On your work Order form make a button, 'Add Customer', within the onclick event of this button, open the new customer form as a dialog (see synstax of Docmd.openform):

DoCmd.OpenForm formname[, view][, filtername][, wherecondition][, datamode][, windowmode][, openargs]

it is windowmode, which needs to be acDialog,

this will open your new customer form, and then pause the code until you exit the New Customer Form, having presumambly added a new customer.

I assume you are probably picking the Customer from a combo on the Order Form?, in which case after the DoCmd.OpenForm put a command to requery the combo box (cboCustomer.Requery), this will cause it to repeat the query which populates the combobox, picking up the cistomer you have just added to the list.

Hope all this is clear, sorry cannot give exact code examples since I do not know the naes of you valiables, forms etc, but hopoefully you will get the idea.

Regards

Ken Reay

 

[Byrt]

Thanks for the help but I think I need a little more.

My Forms are WorkOrder and Customer, with a subform, sbfCustomer within WorkOrder. My combo box to select the customer (cmbCust) has the following row source:

SELECT DISTINCT Customers.CustomerID, Customers.ContactLastName, Customers.ContactFirstName
FROM Customers;

The command button for Add New Customer has this code, to which I have added your suggestions (hopefully in the right place):

Private Sub CmdNewCust_Click()
On Error GoTo Err_CmdNewCust_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Customer"
DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormAdd, acDialog
CmbCust.Requery

Exit_CmdNewCust_Click:
Exit Sub

Err_CmdNewCust_Click:
MsgBox Err.Description
Resume Exit_CmdNewCust_Click

End Sub

When I click on the Add New Customer button I go to the customer form in add mode just fine. But when I close the customer form the workorder form remains blank. As soon as I click on the combo box to select the customer I just added, the WorkOrder form goes to the first record, or Work Order 1.

What am I doing wrong?

 

[KenReay]

Hi

Yes you have put code where I expected.

I do not follow why you need a sub form for Customer within the WorkOrder Form?

I assume you are trying to create a WorksOrder which will be for a Customer and you want to display the Customer name etc on the Form?

Also I assume you are using the same form to create a WorksOrder and to Edit a WorksOrder (ie it is not always an AddNew situation)?

I would base the WorkOrder Form on a query joining WorksOrder Table and Customer Table on CustomerId, you then have Customer Name etc available in the recordsource of WorkOrder Form and you do not need a subform for Customer.

I am not sure this is what is causing you problem, just trying to work through how I would do it.

Regards

Ken Reay
Freelance Developer
kenneth.reay@talk21.com

 

[Byrt]

Thanks again. It so much simpler when you design things right! I did as you suggested, basing the workorder form on a query that combines the workorder and customers tables. Works great and "returns" to where I left off! The only problem is - it still doesn't fill in the customer info when I return to the WorkOrder form after adding a new customer. I have the CmbCust.Requery in the cmdNewCust_Click as suggested. The CmbCust is the combo box to select a customer - source is the Customer table. cmdNewCust is the command button to open the customer form.

Any other ideas?

 

[KenReay]

Hi

Presumably you can call the Customer maint form from other places too?, what I would do is to put in the OnCLose event of the Customer Maint form some code which tested if the Works Order Form is open (ie present in the Forms collection), if yes, then put the Customer Id from the Customer form into the Combobox of the WorksOrder Form eg

Forms!WorkOrder!cmbCust = Me.CustomerId

Regards

Ken Reay
Freelance Developer
kenneth.reay@talk21.com