Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Connecting names between forms (Access 2000 / SP2)

    I have a form that enters data into a member demographic table. This table is linked to a member status table by member name. The user enters the demographic data into a form called "frm database". After the demographic data has been entered they then open a member status form called "frm member status add". Presently they choose the member name from a combo box that is linked to the member name in the demographic table. Instead, they would like "frm member status add" to open with the name already selected (and corresponding to the one on "frm database". I need help with the code to get this to happen.

    The text box on "frm database" is called "Last / Business Name" and the text box on "frm member status add" is called "member / business name".

    Thanks!

    Leesha

  2. #2
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Connecting names between forms (Access 2000 / SP2)

    You can use a command button wizard and select open form.
    In the process of the wizard, it will ask you if you want to open on a specific record dependent on field name ie:

    TITLE<>TITLE

    Using the button this way will depend whether you have data ie "TITLE the same in each table.
    There's another way to do this which is a little more complicated, by checking the Status table first to see if an entry "TITLE" is there in the first place, if not create it and then open the status form with the data "TITLE" added.

    Post back with the option you need, hopefully the Button wizard will be suffice.

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Connecting names between forms (Access 2000 / SP2)

    The DoCmd.OpenForm statement includes a WhereCondition string. When you open "frm member status add" (and I highly recommend that you rename your forms to eliminate the spaces. frmMemberStatusAdd is more readable and won't cause you the problems that the spaces may), just pass an argument like this

    <pre>DoCmd.OpenForm "frm member status add", , , "[LastName] = '" & Me.[Last/Business Name] & "'" </pre>


    Note that the WhereCondition applies to the fields in the called form's recordset, not to its controls, so unless they have the same names (not a good idea), you need to use the field name, not the control name. I have assumed here that the matching field in the member form's recordset is "LastName", but you would have to substitute the actual field name. The value being passed from 'frm database" ( Me.[Last/Business Name] ) can be concatenated from a control value. Since the name value is likely to be a string, don't forget the needed string delimiter. In this example, I concatenated single quotes around the value for that purpose.
    Charlotte

  4. #4
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Connecting names between forms (Access 2000 / SP2)

    Hi Charlotte,

    When I ran the code I got an error message that says the openform action was canceled.

    This is the code I used -

    DoCmd.OpenForm "frmMemberStatusAdd", , , "[Member/BusinessName] = '" & Me.[LastName/Business] & "'"

    I took out the spaces as you mentioned.

    Thanks for the help!

    Leesha

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

    Re: Connecting names between forms (Access 2000 / SP2)

    When you open frmMemberStatusAdd, does a record for the member exist already, or is the purpose of opening the form to create a new record?

    If the former, Charlottes code should work, so if the opening of the form is canceled, there must be some kind of other problem.

    If the latter, setting the Where-condition in OpenForm won't help, because there is no record for that person yet that can be selected. You would have to pass the name in the OpenArgs argument of OpenForm, and use this in the OnLoad event of frmMemberStatusAdd to set the default value for Member/BusinessName.

    To open the form:

    DoCmd.OpenForm "frmMemberStatusAdd", , , , acFormAdd, , Me.[LastName/Business]

    and in the code for frmMemberStatusAdd:

    Private Sub Form_Load()
    Me.[Member/BusinessName].DefaultValue = Chr(34) & Me.OpenArgs & Chr(34)
    End Sub

    (I used Chr(34) here to enclose the default value in quotes, to demonstrate another method of doing this)

  6. #6
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Connecting names between forms (Access 2000 / SP2)

    Hi Hans,

    No the record for the member does not already exist. The form opens with the pupose of adding a new membership record for the name chosen from the demographics table/form.

    I entered this code behind the cmd button that is used to open the form:

    DoCmd.OpenForm "frmMemberStatusAdd", , , , acFormAdd, , Me.[LASTNAME/BUSINESS]

    I entered the following code on the frmMemberStatusAdd:

    Me.[Member/BusinessName].DefaultValue = Chr(34) & Me.OpenArgs & Chr(34)

    When the run the cmd. button on the demographics form to open frmMemberStatusAdd I get the following error:

    Object doesn't support this property or method and when I debug it it is the code one the form load property that is in yellow.

    Alicia

  7. #7
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Connecting names between forms (Access 2000 / SP2)

    I wonder if an exclamation mark (!) should be used here rather than a fullstop (.)?

    Also is Member/BusinessName the name of the control on the form frmMemberStatusAdd?

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

    Re: Connecting names between forms (Access 2000 / SP2)

    Hi Alicia,

    It's becoming hard to visualize what is going on precisely. If I understand correctly, the problem arises with the [Member/BusinessName] combo box. Can you tell us:
    <UL><LI>What is the Control Source of this combo box?
    <LI>What is the Number of Columns, and what are the Column Widths?
    <LI>What is the Bound Column?[/list]You can look up these in the Properties window.

  9. #9
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Connecting names between forms (Access 2000 / SP2)

    Uhm, I "think" that is where the problem is arising as the code gets past the point of opening the form but seems to blow up on the form load code. To answer your questions:

    Control source = member/businessname
    Number of columns = 4
    Column widths = 0";1";1;1"
    Bound column = 1

    Thanks so much!

    Alicia

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

    Re: Connecting names between forms (Access 2000 / SP2)

    Thanks for the info. This means that the combo box doesn't display the actual field it's bound to, since the first column width is 0. Is member/businessname a numeric field?

  11. #11
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Connecting names between forms (Access 2000 / SP2)

    Hi Pat,

    I'm not sure where you want me to try to (!).

    Yes, Member/BusinessName is the name of the control on the frmMemberStatusAdd.

    Thanks,
    Leesha

  12. #12
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Connecting names between forms (Access 2000 / SP2)

    OK, bear with me. I used the lookup wizard to look back to the demographics table for the name. The fields I chose were to show in combo box were LastName/Business,FirstName,MiddleInitial. The ID is the one that is = 0 and isn't seen. When I checked the data type on the table it does state its a number vs text.

    Leesha

  13. #13
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Connecting names between forms (Access 2000 / SP2)

    I had meant
    Me.[Member/BusinessName].DefaultValue = Chr(34) & Me.OpenArgs & Chr(34)
    to be
    Me![Member/BusinessName].DefaultValue = Chr(34) & Me.OpenArgs & Chr(34)
    but I'm not too sure that this makes any difference. I'm sure Hans will clarify this.

    Why is the Member/BusinessName control a combobox? I would have thought you would have this as a text box. Isn't this used for a new record?

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

    Re: Connecting names between forms (Access 2000 / SP2)

    So the combo box is actually bound to a numeric ID field (which is the recommended way of doing that). But that means that you have to pass a numeric ID as OpenArgs in DoCmd.OpenForm too, and that you should not put quotes around the DefaultValue.

  15. #15
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Connecting names between forms (Access 2000 / SP2)

    The only place you absolutely have to use a bang (!) is when referencing fields in a recordset.
    Charlotte

Page 1 of 2 12 LastLast

Posting Permissions

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