pre-populate popup form after update to another form

[Johnmad86]

Hi,

I hope someone is able to help me on this complex issue.
I'm sure there is a way to do this with code, but my skills have left me tied up in knots.

I have two tables - Interviews & Placements.
these tables have multiple foreign keys which pull information from other tables (CandidateID,ClientID etc).

I have designed queries and forms (datasheet view) which display all the values that I need, For example:

The interview form shows the following fields:
CandidateName;Company;consultant;1stInterviewDate;2ndInterviewDate;Offer;Accepted

the placements form shows the following fields:
PlacedCandidate;Company;Consultant;PlacementDate;Fee;

This query "qry_Interviews" populates these forms using the foreign keys:
CandidateID from candidates table
CompanyID from companies table
consultant from consultants table

Ideally what I'd like to happen, is when the "accepted" field is updated on the interviews form, the placements form opens as a pop up and is auto populated with the values (CandidateName;Company & consultant).

So far I have tried setting the value directly, i.e on the "on Open" event of the Placements form I entered:

Me.Txtcontactname = Forms!ISISnavigationMain!navigationSubform.Form!DS.Name

This does not work.

Should I be populating the placements form with the actual Foreign key values rather than the resolved names? Ideally I'd want the pop up placements form to display the actual names rather than just ID numbers.

is this possible? And if so do I need another query to pull this info together?

I'm very lost and only just started with Access VBA so any help would be hugely appreciated.

many thanks
John

 

[CJ_London]

You should be able to use the openargs property on the openform command to pass values from your main form to the popup form.

If you pass several values say CandidateID and ClientID separate them with commas in the openargs string

Then in your popup form on current event put code to split these down again

e.g. Main form

docmd.OpenForm "frmPopUp",,,,,,me.ClientID & "," & me.CandidateID

and in you popup current event

Dim S() as String
 
S=Split(Openargs)
me.clientID=CLng(S(0))
me.candidateID=CLng(s(1))

 

[Johnmad86]

Many thanks. I'll try this tonight and let you know how I get on

John

 

[Johnmad86]

I managed to get it working. The code I used was:

On the first form's On Dirty event:

DoCmd.OpenForm "frm_EnterPlacements", , , , , , Me.ClientID & "|" & Me.CandidateID & "|" & Me.ConsultantID & "|" & Me.txtName & "|" & Me.Company & "|" & Me.ConsultantName

This passes the openArgs as "Me.ClientID|Me.CandidateID|Me.ConsultantID|Me.txtname|Me.Company|Me.ConsultantName"

Then this is split back again on the opening forms on load event:

Dim varArgs As Variant
Dim strClientID As String
Dim strCandidateID As String
Dim strConsultantID As String
Dim strCandidateName As String
Dim strCompany As String
Dim strConsultantName As String

varArgs = Me.OpenArgs

If Not IsNull(varArgs) Then
strClientID = Split(varArgs, "|")(0)
strCandidateID = Split(varArgs, "|")(1)
strConsultantID = Split(varArgs, "|")(2)
strCandidateName = Split(varArgs, "|")(3)
strCompany = Split(varArgs, "|")(4)
strConsultantName = Split(varArgs, "|")(5)

Me.[ClientID] = strClientID
Me.[CandidateID] = strCandidateID
Me.[ConsultantID] = strConsultantID
Me.[txtCandidateName] = strCandidateName
Me.[ClientName] = strCompany
Me.[ConsultantName] = strConsultantName


End If
End Sub

This works perfectly

 

[CJ_London]

Glad to help

You have made your code longer than you needed you could just have

Dim varArgs() as String
 
varArgs = split(Me.OpenArgs,"|")

if Ubound(varArgs)<>-1 then
    Me.[ClientID] = varArgs(0)
    Me.[CandidateID] = varArgs(1)
    Me.[ConsultantID] = varArgs(2)
    Me.[txtCandidateName] = varArgs(3)
    Me.[ClientName] = varArgs(4)
    Me.[ConsultantName] = varArgs(5)
Else
    msgbox "arguments not passed"
End if