Find Record Action

[Joker2]

Is there a way to temporarily "save" an entry in a combo box to use leter in the find record action?

I have a form open in add mode but if a reference number is selected from a combo box I would like the current form to close without saving any changes and then to re-open showing the record related to the selected reference in edit mode.

I can get the form to close without saving the changes but then it is unable to re-open as it doesn't know what reference it is looking for! Would it be possible to make it remember what was selected to enable to the find record action to run?

 

[SydneyGeek]

Hi Joker2, could you clarify what you need to do please? From what you have said so far, I reckon you could be trying to do one of:

1. Open a form, let the user search for a record and then go to that record on the same form

2. Open a form, select from a combo, and go to a related record on a different form

Or it could be something entirely different...

If you provide some more info on what you need to achieve, we can can point you in the right direction.

Denis

 

[Joker2]

I think option 1 is the one I need.

The user opens FormA in add mode. When the user goes through the motions of adding the required details to the new record SubFormA shows any results that match up. Therefore if the user tries to add a "duplicate" record they can see what details have already been recorded and see if they need amending.

If they need amending I would like the user to be able to select the reference number corresponding to the record that needs amending in a combo box (shown on SubFormA) and close down FormA in "add" mode and open it in "edit" mode for the record selected.

 

[SydneyGeek]

Normally you would do this by searching in FormA and then opening FormB. That way, you can pass the OrderNo (or whatever ID you are using).

It turns out that you can get sneaky, and open a filtered copy of the already open form. I got this example from a newsletter that I subscribe to, written by Garry Robinson. It uses the Orders form in the Northwind sample database, but could be adapted for any situation.

First, place this subroutine in the code module of the Orders (target) form:

Public Sub CopyOrder(Optional ShowOrderID As Variant)

'  This public subroutine will appear as a method
'  of form_orders.copyOrders  class

Static lastTop As Long, lastLeft As Long
Static frmOrders As Form_Orders
Dim orderReq As Variant

' If this function is called as a class method,
' the programmer will pass an order number to
' designate whch order to display

If IsMissing(ShowOrderID) Then
   orderReq = Me!OrderID
Else
  orderReq = ShowOrderID
End If

On Error Resume Next
   
'  Instantiate a new class of the form
' and position the form near the top
' of the available Access window area.
' Note that if the form is already open

Set frmOrders = New Form_Orders
   
With frmOrders
  .Visible = True
  .Filter = "orderId = " & orderReq
  .FilterOn = True
  .Caption = "Filter:  " & .Filter
  .Detail.BackColor = vbWhite
  lastTop = lastTop + 100
  lastLeft = lastLeft + 100
  DoCmd.MoveSize lastTop, lastLeft
  .cmdCopyOrder.Visible = False
  
End With
   

End Sub

Now you need to trigger this code, and pass it the order number that you will use for the filter. In the Click event of the first visible subform field, place some code like this:

With Form_Orders
  .CopyOrder Me!orderReq
End With

Where OrderReq is the matching OrderNo in the subform. All going well, you will have a duplicate of the form, filtered on the required OrderNo and offset to the top and left. The background of the Detail section will be white, to differentiate it. The duplicate will close when you click its Close button or close the original form.

Hope this helps.
Denis