Open record in another form 1

[TrekBiker]

I'm using a Customers form with an 'Allocated To' field for the staff member responsible. The form has a subform for the Events the customer has attended, and this contains an Actions subform for actions relating to each event.

A 'My Actions' button opens a second Actions form in datasheet view, showing all actions for the person currently in 'Allocated To'.

My client now wants to be able to select an action in this second form and use it to trigger going back to the relevant customer's record in the Customers form.

How can I close the Customer form when clicking My Actions but still retain the customer name in Allocated To since this is needed for the query source of the Actions form? I could then reopen the Customer form to the appropriate record.

Or is there another way?

 

[MacroAlan]

You might try setting a table to hold the Allocated To parameter and go get it when needed. In this example, the columns are KeyNumber, KeyValue, KeyName and KeyUpd. I use one to hold export paths rather than hard-coding into my VBA.

In the Customer Form use the =KeyVal to go get the Customer that was set by the Call SetKeyValue

'Get value from RegisterFile table

Public Function KeyVal(KeyName As String) As String
On Error GoTo KV_Error
 	If IsNull(DLookup("[keynumber]", "RegisterFile", "[keyname]= '" + KeyName + "'")) Then
		KeyVal = ""
	Else
		KeyVal = DLookup("[keyvalue]", "RegisterFile", "[keyname]='" + KeyName + "'")
	End If
Exit Function
KV_Error:
	KeyVal = ""
End Function

'Set value in RegisterFile table

Public Sub SetKeyValue(KeyName As String, Kval As String)
	Dim MyTable As Recordset
	Set MyTable = CurrentDb.OpenRecordset("RegisterFile", dbOpenDynaset)
	MyTable.MoveFirst
	MyTable.FindFirst ("[keyname] = '" + KeyName + "'")
	If MyTable.NoMatch = False Then
		With MyTable
			.Edit
			![KeyValue] = Kval
			![KeyUpd] = Now()
			.Update
		End With
	End If
		MyTable.Close
End Sub

To put something into the file, call it from VBA like

Call SetKeyValue("CurrentID", Me.ID.Value)

and pull it out to use it in your query with

=KeyVal("CurrentID")

Alan

 

[TrekBiker]

Thanks Alan, that's really helpful.

 

[MajP]

Saving in a table is nice since it will persist if you error. But you can do it also with a public variable in a standard module.

Public customerName as string

Public Function() getCustomerName as string 
  getCustomerName = customerName
end function

so when the form closes. Set the public variable.
CustomerName = me.AllocatedTo

Then in a query you can use the function

 Select .... where CustomerName = getCustomerName()

Another simple way to do this if you want to "close" the form but still reference it, is to not really close it. Put a close button on the form and put the code
me.visible = false.
The form appears closed but simply invisible. You can now reference values on the form. When done with it you can then close it.