Connecting names between forms (Access 2000 / SP2)

Topic

New Reply

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

Reply | Quote

Replies

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:

TITLETITLE

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.

Reply | Quote

Hi!

Just saw this post and tried it. I connected LastName/Business to Member/BusinessName while using the wizard and when the form opens I get an error message asking me for the parameter for the actual name of the person.

Thanks,
Leesha

Reply | Quote

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

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

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.

Reply | Quote

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

Reply | Quote

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)

Reply | Quote

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

Reply | Quote

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?

Reply | Quote

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

Reply | Quote

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?

Reply | Quote

Pat,

I tried changing the (.) to a (!) with no luck.

The reason member/businessname is a combo box is because each time a memberstatus is added, the user looks back the demographics table to be sure the member is listed. A member is only listed once in the member demographics table while they may have multiple status of the year, ie. New member, renewal, inactive etc.

Alicia

Reply | Quote

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

Reply | Quote

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:

• What is the Control Source of this combo box?
• What is the Number of Columns, and what are the Column Widths?
• What is the Bound Column?
  [/list]You can look up these in the Properties window.

Reply | Quote

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

Reply | Quote

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?

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

>>(which is the recommended way of doing that).

Yahoo! Finally I did something the recommended way all on my own.

OK, this is what I changed the code to:

DoCmd.OpenForm “frmMemberStatusAdd”, , , , acFormAdd, , Me.[ID] I still get the error message on the load code which remains as:

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

I wasn’t sure where you wanted me to take off the quotes.

Thanks,
Leesah

Reply | Quote

The Chr(34) are the quotes (34 is the ASCII code for a double quote ” ). These must be removed, so that the code in the OnLoad event of frmMemberStatusAdd becomes

Me.[Member/BusinessName].DefaultValue = Me.OpenArgs

Reply | Quote

Thanks Hans!! That works. I appreciate all help and the tips.

Leesha

Reply | Quote