MS Access - open a form taking a field value from a previous form.

Question

Hello IT folkzies,

I have a form in an MS Access database which lists all the landowners consulted with for a new electricity line. At the end of each row is a button which opens another form, showing the details of all consultation, offers made etc.

I am trying to use vb in MS Access to take the contactID and automatically put it in a field in the details form, so that landowner's consultation details will pop up automatically. I am not a vb programmer at all (I have a comp sci degree mostly in Java and I'm currently working as a GIS analyst but it's a small company so I've been asked to get an Access database working).

I want to say [detailsForm]![contactID] = [landownerlist]![ID] in a way that vb and access will be happy with. Then I can see if I'm on the right track and if it will actually work! What I have above does not actually work. It won't compile.

From Kaliana

Answer 1

You may want to look into the code that is behind these buttons. If you are using a docmd.openform you can set the 4th Setting to a where clause on openning the next form.

DoCmd.OpenForm "OpenFormName", acNormal, , "[contactID] = " & [detailsForm]![contactID] , acFormEdit, acWindowNormal

This assumes contact ID is numeric and doesn't require any quotes.

Answer 2

If you wish to open a form to a new record and to set the ID there, you can use Openargs, an argument of Openform:

DoCmd.OpenForm "FormName",,,,acFormAdd,,Me.ID

The opened form would also need some code:

If Me.Openargs<>vbNullstring Then
   Me.Id = Me.Openargs
End If

It is also possible to find:

Forms!LandownersList.Recordset.FindFirst "ID=" & Me.ID

or fill in a value:

Forms!LandownersList!Id = Me.ID

on the form being opened from the calling form.

Answer 3

As previously posted OpenArgs is great for this. One trick I have learned is that it is easy to pass in multiple parameters if required as a delimited string (comma for example), the target form can then access these values using the Split() function thus:

StringArrayVariable()= Split(me.OpenArgs,",")
Me.textbox= StringArrayVariable(0)
Me.textbox1= StringArrayVariable(1)

etc.

This is air code so check out the helpfile for Split().

It is also possible to pass objects in OpenArgs as well, it requires some manual memory pointer manipulation and I don't have the code to hand but I'm sure a Google search will find some examples. This technique can cause some random crashes though. Be Warned!

Answer 4

Using open args is the generally accepted solution, as alluded to by others. This just falls under the category of "For you edification":) One of the problems with using open args is that unless you are careful with your comments it's easy to forget what they were supposed to mean. Were you passing more than one? Which is which? How did I do it here? How did I do it there etc. For my own money, I standardized to this (below) so I can always pass more than one argument without fear, and when I review my code a year from now, I can still see what's what without a huge hassle:

Option Explicit

'Example use: DoCmd.OpenForm "Example", OpenArgs:="Some Filter|True"

Public Enum eForm1Args
    eFilter = 0
    eIsSpecial = 1
End Enum

Private m_strArgs() As String

Public Property Get Args(ByVal eForm1Args As eForm1Args) As String
    Args = m_strArgs(eForm1Args)
End Property

Private Sub Form_Open(Cancel As Integer)
    m_strArgs = Split(Nz(Me.OpenArgs, vbNullString), "|")
    If LenB(Me.Args(eFilter)) Then Me.Filter = Me.Args(eFilter)
End Sub

Private Sub Command1_Click()
    If LCase$(Me.Args(eIsSpecial)) = "true" Then
        'Do something special
    End If
End Sub