Add new Record Button

Question

I have a parent form that contains information about organizations (from table Organization).  That is linked to a child subform that contains information about events with that organization (from table Events).  The two are linked on "OrganizationName" field which is the PK for Organization.  I added a button to the subform to create a new event.  The button works perfectly when an organization already has at least one event (opens a form with the organization information already filled out but with the event information blank).  However, for organizations that do not already have an event, I get the error message "You entered an expression that has no value."  I used the wizard to create the button and the VBA code is below:

Private Sub AddNewEventButton_Click()

On Error GoTo Err_AddNewEventButton_Click

    Dim stDocName As String

    Dim stLinkCriteria As String

    stDocName = "OrganizationEventsForm"

    stLinkCriteria = "[OrganizationName]=" & "'" & Me![OrganizationName] & "'"

    DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_AddNewEventButton_Click:

    Exit Sub

Err_AddNewEventButton_Click:

    MsgBox Err.Description

    Resume Exit_AddNewEventButton_Click

End Sub

Can someone tell me what I should change so I can add a new event even if one doesn't already exist?

Thanks!

Answer 1

This form has all the organization information and a subform with all the event information.

That's where you've gone wrong.  The dialogue form should be bound to the Event table and include controls bound to all columns in the table, so that you can insert the full data for the new row.  It should be in single form view and not have a subform.

In the subform in the original form which returns only the partial data I would use a command button rather than a combo box in this situation.  A combo box has no real advantage in this context as you have to type in the new event name somewhere.  Place the command button in the subform's header or footer.  Its Click event procedure's code would be:

' open events form in dialogue mode at a new record

DoCmd.OpenForm "frmEvents", _

DataMode:=acFormAdd, _

WindowMode:=acDialog, _

OpenArgs:=Me.Parent.OrganisationName

' requery subform to include new record

Me.Requery

Opening the frmEvents form in dialogue mode causes code execution to pause until frmEvents is closed, so the Requery method will not be called until code execution resumes after the new record has been inserted into the table

In the  frmEvents form's Open event procedure assign the value passed to it as its OpenArgs property to the DefaultValue property of the OrganisationName control, bound to the OrganisationName foreign key column, with:

If Not IsNull(Me.OpenArgs) Then

Me.OrganisationName.DefaultValue = """" & Me.OpenArgs & """"

End If

Note that the DefaultValue property is always a string expression regardless of the data type of the column in question, so the value is wrapped in literal quotes characters as above.

While the use of OrganisationName as the 'natural' key is perfectly permissible in the database relational model, I agree with Scott that a 'surrogate' numeric key would be better here.  There are situations in which the use of a natural key has advantages which outweigh the efficiency of a numeric key, but this is not one of them.

Answer 2

First, using Organizationname as a PK/FK is not the best design. Names can change and using a text datatype for a key field is less efficient. You should use an Autonumber as your PK and corresponding FKs.

Second, you should NOT need to have any organization info on the New Event form. Each Event record should have the OrganizationID (or name if you insist on using that as your keyfield) as the foreign key field. So your new Event form would have a combobox control bound to the FK field. 

If you want to open a new form to enter more info about an event then is shown in the subform, you have a couple of options. But the way I would do it is to enter the base info into the subform, then press a button to open a form filtered for the newly entered event:

Me.Dirty=False

DoCmd.OpenForm "formname",,,"[EventID] = " & Me.EventID

This will open the form to the newly added event where you can fill in whatever additional info you need. The OrganizationID and any info you entered into the subform will be automatically entered.

Answer 3

Thanks!  I'm making progress.  My situation is "(b) if the relationship type is one-to-many and the subform includes controls bound to only a subset of the columns (fields) from the Event table."  Each organization can have many events.  The subform is continuous and lists every event and a few fields (date, location) but it doesn't list everything.  That way users can quickly determine if they have already entered an event.  Ideally, if they haven't they click the "add new event" button (or just type the event name into the combo box like you suggested) and another form pops up (frmOrganizationEvents).  This form has all the organization information and a subform with all the event information.

Using your code, I was able to get part of the way: when I enter a new event name the dialogue box pops up and I can click "yes."  However, I then get a VBA error message: "Compile error: Method or data member not found" and Me.EventName is highlighted.  When playing around with some code I did get the frmOrganizationEvents to pop up but all fields were blank.

Thanks!

Answer 4

From your description, the relationship type between the Organisation and Event tables appears to be one-to-many, in which case there is no need to open a separate form to add a new event for the current organisation.  You simply need to navigate to an empty new record in the subform and insert the data for the new record.  If you want to do this via a button in the subform the code would be:

    DoCmd.GoToRecord Record:=acNewRec

The only situations in which you would need to open a separate form would be (a) if the relationship type between the Organisation and Event tables were many-to-many and modelled by a table which resolves it into two one-to-many relationship types, or (b) if the relationship type is one-to-many and the subform includes controls bound to only a subset of the columns (fields) from the Event table.  In either of these situations the control in the subform to select an event by name would normally be a combo box.  A new event would usually be added by typing the new event name into the combo box and having code in the combo box's NotInList event procedure which opens an events form at a new record, passing the value typed into the form via the OpenArgs mechanism.  Additional data relating to the event can then be entered into other controls in the form.  The code for the NotInList event procedure would be like this:

    Dim ctrl As Control

    Dim strMessage As String

    Set ctrl = Me.ActiveControl

    strMessage = "Add " & NewData & " to list?"

    If MsgBox(strMessage, vbYesNo + vbQuestion) = vbYes Then

        DoCmd.OpenForm "frmEvents", _

            DataMode:=acFormAdd, _

            WindowMode:=acDialog, _

            OpenArgs:=NewData

        ' ensure frmEvents closed

        DoCmd.Close acForm, "frmEvents"

        ' ensure Event has been added

        If Not IsNull(DLookup("EventName", "Event", "EventName = """ & _

            NewData & """")) Then

        Response = acDataErrAdded

        Else

            strMessage = NewData & " was not added to Events table."

            MsgBox strMessage, vbInformation, "Warning"

            Response = acDataErrContinue

            ctrl.Undo

        End If

    Else

        Response = acDataErrContinue

        ctrl.Undo

    End If

The following code would go in the frmEvents form's Open event procedure to assign the value passed to the form to the DefaultValue property of the EventName bound control in the form:

    If Not IsNull(Me.OpenArgs) Then

        Me.EventName.DefaultValue = """" & Me.OpenArgs & """"

    End If

Note that assigning a value to the DefaultValue property does not initiate a new record, so no record is actually created until further data is entered in other bound controls in the form.

Answer 5

Hi,

your problem is not there, post the Vba code in subform for the button to add.

Ciao Mimmo