Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    May 2002
    Location
    Hawaii
    Posts
    69
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Open a form using a button (2000/SR-1)

    Again, something so simple yet so aggrevating.

    I have two related tables: tblContacts and tblMeetingData. I have set their relationship on ContactID, an Autonumber, primary key value in tblContacts. I have a foreign key, also called ContactID, in tblMeetingData. I have set them to provide referential integrity.

    I then created two forms, one linked to tblContacts and one linked to tblMeetingData. I placed a button on the Contacts form, using the wizard, and have it open the Meeting Data form. I have specified that only with records matching ContactID fields are to be shown.

    Everthing looks fine except when I try to add a record. I enter data into the Meeting Data form and receive the following message:

    "You cannot add or change a record because a related record is required in table tblContacts."

    Now, if I manually add a record in tblMeetingData not using the form, then open the Meeting Data form, it shows up just fine. It appears that tblMeetingData.ContactID is not being initialized with valid contant. If I remove referential integrity, tblMeetingData.ContactID gets set to 0.

    Here is the code I use to open the Meeting Data form:

    Private Sub cmdMeetings_Click()
    On Error GoTo Err_cmdMeetings_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "frmMeetingData"

    stLinkCriteria = "[ContactID]=" & Str(ContactID)
    DoCmd.OpenForm stDocName, , , stLinkCriteria

    Exit_cmdMeetings_Click:
    Exit Sub

    Err_cmdMeetings_Click:
    MsgBox Err.Description
    Resume Exit_cmdMeetings_Click

    End Sub

    When I check properties in the Meeting Data form after running this code, the Filter property is set to "ContactID = 11" (assuming I pressed the button while viewing record 11).

    Any idea what I'm doing wrong? Am I supposed to initialize ContactID in code? I thought this happened automatically when a new record was created. If not, where do I do this?

    Thanks.
    Will

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Open a form using a button (2000/SR-1)

    You need to add code to the BeforeInsert event of frmMeetingData to set the value of ContactID to the value selected in frmContacts. For example:

    Private Sub Form_BeforeInsert(Cancel As Integer)
    If IsFormLoaded("frmContacts") Then Me.ContactID = Forms!frmContacts!ContactID
    End Sub

    (substitute correct names if necessary)

    IsFormLoaded is a utility function that returns True if the specified form is open in Form or Datasheet mode. You can place this function in a standard module so that it is available for use in all code in your database:

    Function IsFormLoaded(ByVal strName As String) As Boolean
    If SysCmd(acSysCmdGetObjectState, acForm, strName) Then IsFormLoaded = Forms(strName).CurrentView
    End Function

  3. #3
    Star Lounger
    Join Date
    May 2002
    Location
    Hawaii
    Posts
    69
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open a form using a button (2000/SR-1)

    Oh, thank you, thank you. It's good to know you people are here. I'd be kicking myself and reconsidering if it was worth the effort otherwise.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •