If Vinson's idea is not what you need, here is a simple way to do this. On
top of it, you will learn a lot about forms and macros. This is long and
detailed but should work.
The basics of this is that we will write a macro that will run when you
update the field you want the user to fill in. In this case, When you enter
the name, a macro will run that will open a form in "hidden mode". This form
will be from the table that has the name and churchID. It will be opened
where the name in the new form = the name in the form you are working with.
Next the macro will set the value of the ChurchID = to the ChurchID in the
new form. Next the hidden form will close.
First create an auto-form of the table with the ChurchID you want to populate
in the form you are working with. You have to at least have the name and
ChurchID field in the new form. Don't worry about formatting it, as you will
never see it. Save the form.
In the design view of the form you are tyring to automate the population on
the ChurchID, right click on the text box that will be populated by the user.
Go to properties and then to the event tab. I would then click in the "After
Update" row. A small box with three dots will be in the right hand side of
the row. Click on this. A small box will pop up. Select "macro builder". The
macro design view will open. You can name the macro whatever you like. Now,
in the first row at the top, type "open form". The bottom of the screen will
be able to select the name of the new form you just created. Window Mode
should be set to "hidden". The "Where condition" is where you will tell the
form to open where the name in the source table = the name in the original
form. Click on the box with the three dots. Double click on tables in the
forst column, and select the table that has the ChurchID information. In the
2nd column will be the field names. Double click on the name field. It will
appear in the small window with text like [Table1]![name1]. Put an "=" sign
after that. Now double click on forms, and then loaded forms. The name of the
original form you are working with should showup. Click on it, then in the
next column, click on field list, and then on the next column double click
on the Name field. the box in the expression builder will look something like
this. [Table1]![name1]=[Forms]![frm2]![name1]. Click on OK.
Now, in you macro, you will type "SetValue" in the second row at the top. The
bottom of the page will have two rows. One says "Item" and the other is
"expression". The item is what you want to populate in your original form.
Click on the small box on the right of the "item" row. A "expression builder"
box will pop up. Double click on "forms" and then on "loaded forms", and then
on the form name you are working in. In the next column click on field list
and the next double click on the ChurchID filed. Click OK. In the expression
line, follow the same procedure but selct the new form and the field ChurchID
and click ok. In the thrid row of the macro, type "close" and at the bottom
select object type "form" and then the name of the new form you created. Save
the macro, and then save your form. Try it out. It should work fine. You can
also set the data properties of the ChurchID field in your form to locked if
you do not want people to override it once it is populated. Sorry so long,
but I hope it helps.
Frank said:
Barret:
Since the software will be used by many churches of the same denomination,
so what I intended to do is to have the text control combo box in the
memberform, and not to let them change the chruchID that I gave to them.
Giwever since the same software willb e used to consolidate/combined all
members from all chruches in the Regional chruch office, so there is still
chance to see it in combo in the upper level office, but not in the chruch
level.
So I am interested to do the macro you said, but I do not know how to do it
Thanks in advance for your help.
If I understand correctly, you should be able to simply add a list box in
your form tied to the table ChuchID. The list box can have both fields listed
[quoted text clipped - 11 lines]
