How do I chain forms in Access? (pass values between them)

Question

Hello,

I'm using Access 2007 and have a data model like this...

Passenger - Bookings - Destinations

So 1 Passenger can have Many Bookings, each for 1 Destinations.

My problem...

I can create a form to allow the entry of Passenger details, but I then want to add a NEXT button to take me to a form to enter the details of the Booking (i.e. just a simple drop list of the Destinations). I've added the NEXT button and it has the events of

RunCommand SaveRecord
OpenForm   Destination_form

BUT, I cant work out how to pass accross to the new form the primary key of the passenger that was just entered (PassengerID).

I'd really like to have just one form, and that allow the entry of the Passenger details and the selection of a Destination, that then creates the entries in the 2 Tables (Passenger & Bookings), but I can't get that to work either.

Can anyone help me out please?

Thanks Jeff Porter

Answer 1

I would use the openargs method of the form. That way you can pass one piece of data to the new form from any other form. You can also expand of this by sending a delimited string of arguments and then splitting them out. For example I have a form for editing agent activity that is passed the date, the agents name, agents ID and team in the open args

DoCmd.OpenForm "frmEdit_agent_activity", , , , , acDialog, Item & "|" & Me.txtDate & "|" & Item.ListSubItems(1) & "|" & Item.ListSubItems(2)

The form then uses this to pre populate the controls

Private Sub Form_Load()
If IsMissing(Me.OpenArgs) = True Or IsNull(Me.OpenArgs) = True Then
    'no args, exit the form
    DoCmd.Close acForm, "frmEdit_agent_activity", acSaveNo
Else
    'this form has 4 open args
    '1 Staff ID
    '2 Date
    '3 Team_ID
    '4 Staff Name
    Me.txtStaff_ID = GetDelimitedField(1, Me.OpenArgs, "|")
    Me.txtDate = GetDelimitedField(2, Me.OpenArgs, "|")
    Me.txtTeam_ID = GetDelimitedField(3, Me.OpenArgs, "|")
    Me.txtStaff_name = GetDelimitedField(4, Me.OpenArgs, "|")
End If

End Sub

Ohh and here is the GetDelimitedField function

Function GetDelimitedField(FieldNum As Integer, DelimitedString As String, Delimiter As String) As String
Dim NewPos As Integer
Dim FieldCounter As Integer
Dim FieldData As String
Dim RightLength As Integer
Dim NextDelimiter As Integer
If (DelimitedString = "") Or (Delimiter = "") Or (FieldNum = 0) Then
    GetDelimitedField = ""
    Exit Function
End If
NewPos = 1
FieldCounter = 1
While (FieldCounter < FieldNum) And (NewPos <> 0)
    NewPos = InStr(NewPos, DelimitedString, Delimiter, vbTextCompare)
    If NewPos <> 0 Then
        FieldCounter = FieldCounter + 1
        NewPos = NewPos + 1
    End If
Wend
RightLength = Len(DelimitedString) - NewPos + 1
FieldData = Right$(DelimitedString, RightLength)
NextDelimiter = InStr(1, FieldData, Delimiter, vbTextCompare)
If NextDelimiter <> 0 Then
FieldData = Left$(FieldData, NextDelimiter - 1)
End If
GetDelimitedField = FieldData
End Function

Answer 2

Have you considered subforms? There are ideal for one to many relationships. The Link Child Field(s) will be automatically completed from the Link Master Field(s).

If you need an example of subforms in actions, the Northwind database ships with all versions of Access, or you can download which ever is relevant.

2007 http://office.microsoft.com/en-us/templates/TC012289971033.aspx?CategoryID=CT102115771033

2000 http://www.microsoft.com/downloads/details.aspx?familyid=c6661372-8dbe-422b-8676-c632d66c529c&displaylang=en

Answer 3

Actually the best suggestion I can give here is to not actually pass parameters. Simple in your form's on-open event, or even better is to use the later on-load event is to simply to pick up a reference in your code to the PREVIOUS calling form. The beauty of this approach is that if overtime you go from one parameter to 10 parameters then you don't have to modify the parameter parsing code, and you don't even have to modify the calling code. In fact there's no code to modify AT ALL if you decide to examine previous values from the calling form.

So, keep in mind using open args is only a one way affair. You can not use it to return values to the calling form. Furthermore all of the open args params will have to be strings. So, you lose any ability of dating typing such as real integers or even date and time formatting which can be quite problematic to parse out. And as you can see the example here the code to parse out strings can get a little bit messy anyway.

The simple solution is that in each form where you want values from the PREVIOUS from, simply declare a module level variable as follows

Dim frmPrevous         as form.

Then in your forms on load an event, simply pick up the name of the previous form as follows:

Set frmPrevious = screen.ActiveForm

That is it. We are done!

We only written one line of code here. (ok two if you include the declaration statement). At this point on words ANY place in your form's current code you can reference the events properties and any field or value in the previous form by doing the following

Msgbox "PK id of previous form = " & frmPrevious.ID

And let's say for some reason that you want the previous form to re-load records as in a continues form. Then we can go:

frmPrevious.Requery

Or, force a record save:

frmPrevious.Dirty = false

So, the above becomes almost as natural and handy as using "ME" in your current code. I find this so simple and easy I think this should have been part of access in the first place.

And as mentioned the uses are endless, I can inspect ANY column or value from the calling form. You can even declare variables and functions as public, and then they can be used.

And, note that this works BOTH WAYS. I can stuff and change values in the calling form. So I can update or change the value of any value/column/control from the calling form.

And there is absolutely no parsing required. Furthermore the above code step even works if the same existing form is called by different forms. In all cases the forms calling ID can be picked up without modifying your code.

And even in the case of that I have many different forms launching and calling this particular form, you can still pull out the ID column. And, in the case that columns could be different from different forms, you can simply declare public variables or public functions in the calling form of the SAME name. So, if I wanted to call a form that needs the DateCreate, but each form did NOT have a consistent column name of DateCreate (maybe invoiceDateCreate and inventory Date Create), then you simply declare a public function in the calling forms with a constant name. We then can go:

Msgbox "Date created of calling form record = " & frmPrevious.DateCreated

So, Date created can be a public variable or public function in the previous form that could be any conceivable column from the database.

So don't pass values between forms, simply pass a reference to the calling form, not only is this spectacularly more flexible than the other methods showing here, it's also an object oriented approach in which you're not limited to passing values.

You can ALSO return values in the calling form by simply setting the value of any control you want (frmPrevous.SomeContorlName).

And as mentioned, you not limited to just passing values, but have complete use of code, properties such as dirty and any other thing that exists in the calling form.

I have as a standard coding practice adopted the above for almost every form. I simply declare and set up the form previous reference. This results in a handy previous form reference as useful as "ME" reference when writing code.

With this coding standard I can also cut and paste the code between different forms with different names and as a general rule my code will continue to run without modification.

And as another example all of my forms have a public function called MyDelete which of course deletes the record in the form, therefore if I want to delete the record in the previous calling form for some reason, then it's a simple matter to do the following

frmPrevious.MyDelete

So I can save data in the previous form. I can requery the previous form, I can run code in the previous form, I can return values to the previous form, I can examine values and ALL columns all for the price of just ONE measly line of code that sets a reference to the calling form.

Answer 4

You can use OpenArgs.
But I would also suggest that you also consider using a tab control. That allows you to have different sets of controls on the same "screen real estate", using one single recordset, or using sub forms to show child recordsets.
In that case, you could use the "Next" button to just switch to the next page of your tab control.

Answer 5

I do this by defining properties in the form with Property Let and Property Get and passing values to those properties after opening the form, like this:

in the destination form:

Dim strCallingForm As String
Dim lngKey As Long

Public Property Get callingform() As String
    callingform = strCallingForm
End Property

Public Property Let callingform(NewValue As String)
    strCallingForm = NewValue
End Property

Public Property Let PrimaryKey(NewValue As Long)
    lngKey = NewValue
End Property

in the calling form:

Sub btnGo_Click()
    Const cform As String = "frmDestinationForm"
    DoCmd.OpenForm cform
    Forms(cform).callingform = Me.Name
    Forms(cform).PrimaryKey = Me.PrimaryKey
    Me.Visible = False
End Sub

(end)