Adding data to a Form from a Form that's being closed

[sheetsumon via AccessMonster.com]

I have a 'MasterForm1' that uses a combo box to list a set number of users
from another table 'UserNames.' If the user who's entering data into
'MasterForm1' doesn't see his name in the combobox, there's an "ADD NEW USER"
button to open up a form (with Data Entry set to 'Yes') so a new person can
be added to the 'UserNames' table.

Once the user's name (text box) has been defined, I have a button named
'CloseUsersForm' that I want to use to close the 'UsersForm' and load the
added name into 'MasterForm1's user name combo box (but I still want all of
the other user names to be available in the first form's combobox).

I know this is possible, but I can't figure out how to do it.
Do I need a global variable to pass the userID since the second form
('UsersForm') is being closed? How can I get the combo box to automatically
select the newly added user when operation returns to 'MasterForm1'? I've
seen people using Me!cboNames = Me!cboNames.Column(0, 0) in the form Load
event, but I'm not sure if I can use this here.

The code I currently have for the 'CloseUsersForm' button is:

Private Sub CloseUsersForm_Click()
On Error GoTo Err_CloseUsersForm_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "MasterForm1"
stLinkCriteria = "userID=" & Me.userID.Value

Me.Refresh
DoCmd.Close
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_CloseUsersForm_Click:
Exit Sub

Err_CloseUsersForm_Click:
MsgBox Err.Description
Resume Exit_CloseUsersForm_Click

End Sub

Pretty simple. If I take out the DoCmd.Close, the button will pass the new
user into the combo box selections successfully. However, I'd like the new
user to be automatically selected in the combobox when the first form returns.

Are global variables always needed when a form is closed that's passing data
to another form? I find it much more logical for a form to close itself as
the next form is being opened anyway. Otherwise, you have a bunch of forms
floating around in the end!

Any advice?

 

[Graham R Seach]

I think you might get some benefit from subclassing the AddUser form. Take a
look at the technique shown at the following page:
http://www.pacificdb.com.au/MVP/Code/MyDialog.htm

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

 

[sheetsumon via AccessMonster.com]

I've been investigating methods to do this for awhile now and had already
found that site. I tried to code it as described, but I couldn't get it to
work for some reason. I'll give it another go and see what I can come up
with.

If anyone else has a comment on this though, please let me know.

I think you might get some benefit from subclassing the AddUser form. Take a
look at the technique shown at the following page:
http://www.pacificdb.com.au/MVP/Code/MyDialog.htm

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

I have a 'MasterForm1' that uses a combo box to list a set number of users
from another table 'UserNames.' If the user who's entering data into

[quoted text clipped - 58 lines]

Any advice?

 

[sheetsumon via AccessMonster.com]

Excellent. Just got it working.
Thanks for your help - the code works.

I've been investigating methods to do this for awhile now and had already
found that site. I tried to code it as described, but I couldn't get it to
work for some reason. I'll give it another go and see what I can come up
with.

If anyone else has a comment on this though, please let me know.

I think you might get some benefit from subclassing the AddUser form. Take a
look at the technique shown at the following page:

[quoted text clipped - 11 lines]

 

[Graham R Seach]

Glad to help.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

Excellent. Just got it working.
Thanks for your help - the code works.

I've been investigating methods to do this for awhile now and had already
found that site. I tried to code it as described, but I couldn't get it
to
work for some reason. I'll give it another go and see what I can come up
with.

If anyone else has a comment on this though, please let me know.

I think you might get some benefit from subclassing the AddUser form.
Take a
look at the technique shown at the following page:

[quoted text clipped - 11 lines]

Any advice?