Prev: Australian dates in access
Next: Can not get size of list width
From: Velo on 6 Jun 2010 13:18
Hello, I have an issue opening a form to an ID of the record I am currently
on. If I am on frmProjects and am adding a typing in a new EndUser from the
dropdown in cboEndUser I want to be able to do two things:
1) Add this new EndUser name in the frmEndUserDetails in control source
EndUserID (named cboEndUser) once I tab out of it
2) Have this new EndUser nae appear in the frmProjects once I finish adding
details about this new End User in frmEndUser.

So what happpens is that if I need to enter a new EndUser name in
cboEndUser, once i tab out of cboEndUser the frmEndUserDetails opens to that
new EndUser I just entered and is based on EndUserID.

My EndUserID on tblProjects is a combobox with row source query
SELECT tblEndUser.[EndUserID], [tblEndUser].[End User] FROM tblEndUser ORDER
BY [tblEndUser].[End User];

It is kinda like add new record if not there and if it is there then go to
it. If it is there then I just double click the field cboEndUserID and it
does go to the frmEndUser based on that id so I am good if it already exists.
Problem is getting it added if not exists.

thank you...

From: Marshall Barton on 6 Jun 2010 13:42
Velo wrote:
>Hello, I have an issue opening a form to an ID of the record I am currently
>on. If I am on frmProjects and am adding a typing in a new EndUser from the
>dropdown in cboEndUser I want to be able to do two things:
>1) Add this new EndUser name in the frmEndUserDetails in control source
>EndUserID (named cboEndUser) once I tab out of it
>2) Have this new EndUser nae appear in the frmProjects once I finish adding
>details about this new End User in frmEndUser.
>
>So what happpens is that if I need to enter a new EndUser name in
>cboEndUser, once i tab out of cboEndUser the frmEndUserDetails opens to that
>new EndUser I just entered and is based on EndUserID.
>
>My EndUserID on tblProjects is a combobox with row source query
>SELECT tblEndUser.[EndUserID], [tblEndUser].[End User] FROM tblEndUser ORDER
>BY [tblEndUser].[End User];
>
>It is kinda like add new record if not there and if it is there then go to
>it. If it is there then I just double click the field cboEndUserID and it
>does go to the frmEndUser based on that id so I am good if it already exists.
> Problem is getting it added if not exists.
>

The usual way to do that is to set the combo box's
LimitToList property to Yes. Then add code to the combo
box's NotInList event procedure to open the end user form:

DoCmd.OpenForm "frmEndUserDetails", _
DataMode:= acFormAdd, _
WindowMode:=acDialog, _
OpenArgs:= NewData
Response = acDataErrAdded

the Data<ode argument open the form with only a new, blank
record. WindowMode tells access to suspend the code until
the new user form is closed or made invisible. Setting
Response to acDataErrAdded tell the combo box to requery the
combo box's list to find the new user record that was just
added and then proceed normally.

To plug the new user into the new record, add a little code
to frmEndUserDetails' Load event procedure:

If Not IsNull(Me.OpenArgs) Then
Me.EndUserID = Me.OpenArgs
End If

--
Marsh
MVP [MS Access]
 | 
Pages: 1
Prev: Australian dates in access
Next: Can not get size of list width