Forms - Call Data from Another Table

[Guest]

Hi

I'm used to using lookups and forms etc but need to do something that I
think links queries, VBA (completely new to) and forms:

I have a form for creating new applicants

I want to build a form that allows the user to search a contacts table on
FirstName, MiddleName, Surname; receive a list of matches and then either

1. Select one of the matches which would then populate the applicants table

or

2. Reject the matches and create a new record as normal

Can you anyone point me in the right direction?

Thanks

James

 

[Guest]

Sounds like you are asking how to do callbacks.

Open a sub form from a parent form and set up two way calling.

' In a module
Public Function IsOpen(strForm As String) As Boolean
IsOpen = (SysCmd(acSysCmdGetObjectState, acForm, strForm) > 0)
End Function

' In frm_Calling
Private frm_detail As Form_frm_detail

If IsOpen("frm_detail") And Not frm_detail Is Nothing Then
frm_detail.CallingName = strTargetKeyString
Else
DoCmd.OpenForm "frm_detail", _
DataMode:=acFormEdit, _
WindowMode:=acWindowNormal, _
OpenArgs:=Trim(CStr(lngRecordID)) & "^" & strTargetKeyString &
"^" & Me.Name
Set frm_detail = Application.Forms("frm_detail")
End If

' If the calling form is a subform then the above open must use the parent
form name
' Use Parent.Name rather than Me.Name

' ****In frm_detail*****

Private ParentForm As Form
Private ParentFormName As Variant
Private lngRecordID As Long

Property Set ParentObject(fValue As Form)
Set ParentForm = fValue
ParentFormName = ParentForm.Name
End Property

Property Let CallingName(vValue As Variant)
' put code in here to initialize your form
' run a parameterized query to retrieve the desired records
' set default values for controls
End Property

Private Sub Form_Load()
Dim lngOne As Long
Dim vTargetKey As Variant
Dim vCallingForm As Variant
Dim vInstanceID As Variant

If Not IsNull(Me.OpenArgs) Then

lngOne = 1
vInstanceID = Trim(Nz(Left(Me.OpenArgs, InStr(lngOne, Me.OpenArgs,
"^") - 1), ""))
lngOne = InStr(lngOne, Me.OpenArgs, "^") + 1
vTargetKey = Trim(Nz(Mid(Me.OpenArgs, lngOne, InStr(lngOne,
Me.OpenArgs, "^") - lngOne), ""))
lngOne = InStr(lngOne, Me.OpenArgs, "^")
vCallingForm = Trim(Nz(Right(Me.OpenArgs, Len(Me.OpenArgs) -
lngOne), ""))
Set ParentObject = Application.Forms(vCallingForm)
' If the calling form is a subform use the following
' Set ParentObject =
Application.Forms(vCallingForm).subformcontrolname.Form
lngRecordID = CLng(Nz(vInstanceID, "0"))
CallingName = vTargetKey ' A method to initialize the form

End If ' If Not IsNull(Me.OpenArgs) Then
End Sub

' Now each form can call methods in the other

 

[Guest]

Hi Steve

Thanks for this - I'm still very new to this. Would you mind providing a
walkthrough in a bit more detail?

Thanks

James