Not In List Event Question

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Using WinXP and Access2K2

I have a main form that has a combo box for a list of "entities" (persons).
Selecting a name from that combo box brings up the record for that entity.

When an entity is not on the list, I'd like to be able to choose to cancel
the operation of locating that person, and open the form to add a new record.

I have some code, not written by me, that addresses a "not-in-list" event,
but I don't know how to convert it to "opening the form in add mode".

Thanks for help in advance.
 
Using WinXP and Access2K2

I have a main form that has a combo box for a list of "entities"
(persons). Selecting a name from that combo box brings up the
record for that entity.

When an entity is not on the list, I'd like to be able to choose
to cancel the operation of locating that person, and open the form
to add a new record.

I have some code, not written by me, that addresses a
"not-in-list" event, but I don't know how to convert it to
"opening the form in add mode".

Thanks for help in advance.
Click to expand...
Can't do much without seeing the code. Please post it.

Note that I do not think you need to "open" the form, it's already
open. You just need to go to the new record, unless you need to open
a different form - please clarify.
 
Jim, what does the 'not in list event' cause to happen now? If it displays a
message box, we can modifiy the code and buttons on the msgbox to do
whatever. Please post the code for that event.

UpRider
 
The easy way to do this is have ms-access do ALL of the work for you. So,
given that new data is the actually text you typed into the combo, then you
can do the following:

Private Sub Distributee_NotInList(NewData As String, Response As Integer)

if MsgBox("Do you want to add this value to the list?", _
vbYesNo) then
DoCmd.OpenForm "frmAddClient", , , , acFormAdd, acDialog, NewData
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If

End Sub

The above is ALL YOU need. You can see it is not much code.

Note that by setting response = acDataErrAdded, then ms-access does a
re-load, and a re-query of the comb box list for you. In other words, quite
a bit of stuff happens to ensue that the combo box is re-loaded, and re-set.


However, there
is one thing we should do, and that is that then the frmAddClient loads, we
should put in the NewData value into the correct field so the user does not
have to re-type it. (and it helps the user "see" things a lot better". So,in
our forms on-load event, we will take the value of NewData, and set the
correct field. The code for this is:

if isnull(me.OpenArgs) = false then

me.CompanyName = me.Openargs

endif

That is all you need. However, to make life a bit easier to your users,
there are a good number of things you should set in frmAddClent to make life
better.

They are:

Set the forms "cycle" property to current. This means the cursor when on the
bottom of the screen at the last field does not jump to another record. It
is horrible when a user bumps the tab key, and access goes to another
record, so, set the forms cycle property (other tab) to current. In fact,
this is a good setting for most forms, and NOT just this add example.

Since this is just a add form, then turn off the record navigation at the
bottom, again this serves only to confuse the user (since you have a
frmAddClient, I bet you already did this too). Why confuse the user!

Further, turn off the forms ability to "add new" records. Yes, you read this
100% correct. You want turn off the forms allow additions property. The
reason for this is that the above "open" form will OVER RIDE this setting.
This is ideal, since once again, hitting page/down key, or even the mouse
wheel will not cause the form to jump to another record, and confuse the
heck out of the user (this is along the same lines as the tab key setting
above as per "cycle" setting).

Note if you need this form to do "double duty" and want to use it in other
places in the application to allow the user will add MORE THEN ONE record at
a time, then put the follwing in the forms on open

if isnull(me.OpenArgs) = false then

me.Company = me.Openargs
me.AllowAdditions = false
Me.NavigationButtons = False

endif

That way, this form can be used else where in the program, but for our combo
box, the navigation buttons, and accidental adding of MORE THEN one record
will not occur.

All in all, you don't have to do any of the additional things I mention
above, but can *just* use the short sample code, but all of the above
together makes a real slick app.
 
Albert said:
The easy way to do this is have ms-access do ALL of the work for you. So,
given that new data is the actually text you typed into the combo, then you
can do the following:

Private Sub Distributee_NotInList(NewData As String, Response As Integer)

if MsgBox("Do you want to add this value to the list?", _
vbYesNo) then
DoCmd.OpenForm "frmAddClient", , , , acFormAdd, acDialog, NewData
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If

End Sub

The above is ALL YOU need. You can see it is not much code.

Note that by setting response = acDataErrAdded, then ms-access does a
re-load, and a re-query of the comb box list for you. In other words, quite
a bit of stuff happens to ensue that the combo box is re-loaded, and re-set.


However, there
is one thing we should do, and that is that then the frmAddClient loads, we
should put in the NewData value into the correct field so the user does not
have to re-type it. (and it helps the user "see" things a lot better". So,in
our forms on-load event, we will take the value of NewData, and set the
correct field. The code for this is:

if isnull(me.OpenArgs) = false then

me.CompanyName = me.Openargs

endif

That is all you need. However, to make life a bit easier to your users,
there are a good number of things you should set in frmAddClent to make life
better.

They are:

Set the forms "cycle" property to current. This means the cursor when on the
bottom of the screen at the last field does not jump to another record. It
is horrible when a user bumps the tab key, and access goes to another
record, so, set the forms cycle property (other tab) to current. In fact,
this is a good setting for most forms, and NOT just this add example.

Since this is just a add form, then turn off the record navigation at the
bottom, again this serves only to confuse the user (since you have a
frmAddClient, I bet you already did this too). Why confuse the user!

Further, turn off the forms ability to "add new" records. Yes, you read this
100% correct. You want turn off the forms allow additions property. The
reason for this is that the above "open" form will OVER RIDE this setting.
This is ideal, since once again, hitting page/down key, or even the mouse
wheel will not cause the form to jump to another record, and confuse the
heck out of the user (this is along the same lines as the tab key setting
above as per "cycle" setting).

Note if you need this form to do "double duty" and want to use it in other
places in the application to allow the user will add MORE THEN ONE record at
a time, then put the follwing in the forms on open

if isnull(me.OpenArgs) = false then

me.Company = me.Openargs
me.AllowAdditions = false
Me.NavigationButtons = False

endif

That way, this form can be used else where in the program, but for our combo
box, the navigation buttons, and accidental adding of MORE THEN one record
will not occur.

All in all, you don't have to do any of the additional things I mention
above, but can *just* use the short sample code, but all of the above
together makes a real slick app.
Click to expand...

Jim,

Albert's answer was quite slick and very simple. I decided to try to
see what it would take to do it without using DoCmd.FormOpen and
OpenArgs. For instance, you might have OpenArgs tied up for other
purposes. I don't use bound forms a lot, but since I went to the
trouble to play with this a little I'll post this anyway so you can see
how that might be done. His shutting off the navigation for the add was
a nice touch and the user "bump" issues are well worth consideration.

I created a form called frmPeople with an unbound combobox called cbxPerson.

tblPeople
PID AutoNumber
FirstName Text
LastName Text
PID FirstName LastName
1 Bob Smith
2 Janet Jones
3 Sue Walker
4 Frank Taylor
5 Eve McKay

frmPeople
RecordSource = SELECT * FROM tblPeople ORDER BY LastName, FirstName;

Then I added textboxes from the field list to frmPeople bound to PID,
FirstName and LastName.

cbxPerson
RowSource = SELECT tblPeople.PID, FirstName & " " & LastName AS FullName
FROM tblPeople ORDER BY LastName, FirstName;
ColumnCount = 2
ColumnWidths = 0";1.7"
BoundColumn = 1

'---Code behind frmPeople---
Option Compare Database
Option Explicit

Private Sub cbxPerson_AfterUpdate()
Me.RecordsetClone.FindFirst "[FirstName] & ' ' & [LastName] = " &
Chr(34) & cbxPerson.Column(1) & Chr(34)
Me.Bookmark = Me.RecordsetClone.Bookmark
End Sub

Private Sub cbxPerson_NotInList(NewData As String, Response As Integer)
Dim Ans As Variant
Dim strPrompt As String
Dim strTitle As String

If InStr(1, NewData, " ") <= 1 Or InStr(1, NewData, " ") = Len(NewData) Then
Response = acDataErrContinue
MsgBox ("The new person's name must contain a space between the first
name and the last name.")
Exit Sub
End If
strPrompt = "The person you entered is not in the list. Click OK to add."
strTitle = "Action Verification"
Ans = MsgBox(strPrompt, vbOKCancel, strTitle)
cbxPerson.Undo
If Ans = vbOK Then
Response = acDataErrAdded
Me.RecordsetClone.AddNew
Me.RecordsetClone("FirstName") = Left(NewData, InStr(1, NewData, " ")
- 1)
Me.RecordsetClone("LastName") = Right(NewData, Len(NewData) -
InStr(1, NewData, " "))
Me.RecordsetClone.Update
Me.Requery
End If
End Sub

Private Sub Form_Current()
cbxPerson.Value = Me.PID
End Sub
'---End of code behind frmPeople---

Then I opened frmPeople and selected 'Eve McKay.' I changed 'Eve' to
'Bill' in the combobox. I got the message:

"The person you entered is not in the list. Click OK to add."

The combobox then shows cbxPerson.Value = 'Bill McKay' inserted at the
record selector = 2 value where it belongs. Then I selected 'Bob Smith'
and typed in 'JaneSmith.' I got:

"The new person's name must contain a space between the first name and
the last name."

The cbxPerson.Value keeps the invalid value there so the user can change
it but the combobox dropdown is down (unintended). The
cbxPerson_AfterUpdate code is so that when a selection is made in
cbxPerson, the form moves to the corresponding record. The Form_Current
code is so that when the user uses the navigation arrows or changes the
navigation record value, cbxPerson changes to the corresponding PID.
The invalid name part could use a little work, such as dealing with a
name having two spaces, but the location seemed like a decent place to
validate (or to call a validation subroutine to validate) the NotInList
NewData value.

Prior to using the RecordSource I did for frmPeople I tried 'tblPeople'
as the RecordSource and discovered that the form sorted the names by PID
and placed the new person at the end. This example is not as elegant as
Albert's but may be of interest to someone.

James A. Fortune
(e-mail address removed)
 
James A. Fortune said:
For instance, you might have OpenArgs tied up for other
purposes.
Click to expand...

You can pass a value list (only as literal values, not of variables), or
named arguments (in which case variables can be used, so this is the more
usual method) via the OpenArgs mechanism using the following module developed
by Stuart McAll and myself:

''''module begins''''
Private Const OFFSET As Long = 127
Private Const ASSIGNOP As String = "=="

Function Arg(buffer, idx) As Variant

If IsNumeric(idx) Then
i& = InStr(1, buffer, Chr(idx + OFFSET - 1))
token$ = Chr(idx + OFFSET)
i& = InStr(i&, buffer, ASSIGNOP) + 2
Else
i& = InStr(1, buffer, idx) + Len(idx) + 2
token$ = Chr(Asc(Mid(buffer, InStr(1, buffer, idx) - 1, 1)) + 1)
End If
Arg = Mid(buffer, i&, InStr(i&, buffer, token$) - i&)

End Function

Function Argname(buffer, idx) As String

i& = InStr(1, buffer, Chr(idx + OFFSET - 1))
token$ = Chr(idx + OFFSET)
Argname = Mid(buffer, i& + 1, InStr(i&, buffer, ASSIGNOP) - (i& + 1))

End Function

Function ArgCount(buffer) As Long

ArgCount = Asc(Right(Chr(OFFSET) & buffer, 1)) - OFFSET

End Function

Sub AddArg(buffer, Argname, argval)

If Len(buffer & "") = 0 Then buffer = Chr(OFFSET)
If IsNumeric(Argname) Then Argname = ArgCount(buffer) + 1
buffer = buffer & Argname & ASSIGNOP & argval & Chr(Asc(Right(buffer,
1)) + 1)

End Sub

Sub AddArgList(buffer, ParamArray Tokens())

For i& = 0 To UBound(Tokens)
AddArg buffer, i& + 1, Tokens(i&)
Next

End Sub
''''module ends''''

Its used as follows:

Values can be passed individually as named arguments:

Dim args As String

' add some named arguments
AddArg args, "First", "Apples"
AddArg args, "Second", "Pears"
AddArg args, "Third", "Bananas"

Or as an arguments list:

AddArgList args, "Oranges", "Peaches", "Grapefruit"

The list is then passed to the form with:

DoCmd.OpenForm "frmMyForm", OpenArgs:=args

In the form's module the values can be extracted like so:

Dim args As String, i As Integer

args = Me.OpenArgs

' get some named named arguments
Debug.Print Arg(args, "First")
Debug.Print Arg(args, "Second")
Debug.Print Arg(args, "Third")
' get some arguments by ordinal position
Debug.Print Arg(args, 4)
Debug.Print Arg(args, 5)
Debug.Print Arg(args, 6)
' list all arguments
For i = 1 To ArgCount(args)
Debug.Print Argname(args, i), Arg(args, i)
Next i
' get count of arguments
Debug.Print ArgCount(args)

You can find a demo at:


http://community.netscape.com/n/pfx...yMessages&tsn=1&tid=24091&webtag=ws-msdevapps


Ken Sheridan
Stafford, England
 
Here's an example which adds a city to a Cities table:

Private Sub cboCities_NotInList(NewData As String, Response As Integer)

Dim ctrl As Control
Dim strMessage As String

Set ctrl = Me.ActiveControl
strMessage = "Add " & NewData & " to list?"

If MsgBox(strMessage, vbYesNo + vbQuestion) = vbYes Then
DoCmd.OpenForm "frmCities", _
DataMode:=acFormAdd, _
WindowMode:=acDialog, _
OpenArgs:=NewData
' ensure frmCities closed
DoCmd.Close acForm, "frmCities"
' ensure city has been added
If Not IsNull(DLookup("CityID", "Cities", "City = """ & _
NewData & """")) Then
Response = acDataErrAdded
Else
strMessage = NewData & " was not added to Cities table."
MsgBox strMessage, vbInformation, "Warning"
Response = acDataErrContinue
ctrl.Undo
End If
Else
Response = acDataErrContinue
ctrl.Undo
End If

End Sub

In the Cities form's open event is the following code to assign the new name
to the City control's DefaultValue property.

Private Sub Form_Open(Cancel As Integer)

If Not IsNull(Me.OpenArgs) Then
Me.City.DefaultValue = """" & Me.OpenArgs & """"
End If

End Sub

Ken Sheridan
Stafford, England
 
Albert,

Thank you for the quick response. I appreciate the simple code you sent. I
tried it; however, I may have not provided enough information or correct
information for my question.

My combo box SQL is this:

SELECT tblEntity.intEntityID, [strLName] & ", " & [strFName] AS Contact FROM
tblEntity ORDER BY tblEntity.strLName, tblEntity.strFName;

Viewing the combo box an example would be: "Ory, Jim" The person entering
data would type in the first few characters of a last name, they would see
all the names already entered; both last and first. To get out of that
situation where the name is not on the list, they could press ESC twice and
manually go to New Record. Not very intuitive.

Also, the combo box is in the open form that is used to Add/Edit the
records. Looking back at my question, I see that I requested to "open" a form
in Add Mode. That was incorrect as the form is already open. What I needed
was to go to a "New Record" and enter names, addresses, etc.

A Yes answer will close the combo box and go to a New Record; a No answer
will request that a name be selected from the list.

Sorry for not being clearer in my original question.
 
Ken said:
:




You can pass a value list (only as literal values, not of variables), or
named arguments (in which case variables can be used, so this is the more
usual method) via the OpenArgs mechanism using the following module developed
by Stuart McAll and myself:

''''module begins''''
Private Const OFFSET As Long = 127
Private Const ASSIGNOP As String = "=="

Function Arg(buffer, idx) As Variant

If IsNumeric(idx) Then
i& = InStr(1, buffer, Chr(idx + OFFSET - 1))
token$ = Chr(idx + OFFSET)
i& = InStr(i&, buffer, ASSIGNOP) + 2
Else
i& = InStr(1, buffer, idx) + Len(idx) + 2
token$ = Chr(Asc(Mid(buffer, InStr(1, buffer, idx) - 1, 1)) + 1)
End If
Arg = Mid(buffer, i&, InStr(i&, buffer, token$) - i&)

End Function

Function Argname(buffer, idx) As String

i& = InStr(1, buffer, Chr(idx + OFFSET - 1))
token$ = Chr(idx + OFFSET)
Argname = Mid(buffer, i& + 1, InStr(i&, buffer, ASSIGNOP) - (i& + 1))

End Function

Function ArgCount(buffer) As Long

ArgCount = Asc(Right(Chr(OFFSET) & buffer, 1)) - OFFSET

End Function

Sub AddArg(buffer, Argname, argval)

If Len(buffer & "") = 0 Then buffer = Chr(OFFSET)
If IsNumeric(Argname) Then Argname = ArgCount(buffer) + 1
buffer = buffer & Argname & ASSIGNOP & argval & Chr(Asc(Right(buffer,
1)) + 1)

End Sub

Sub AddArgList(buffer, ParamArray Tokens())

For i& = 0 To UBound(Tokens)
AddArg buffer, i& + 1, Tokens(i&)
Next

End Sub
''''module ends''''

Its used as follows:

Values can be passed individually as named arguments:

Dim args As String

' add some named arguments
AddArg args, "First", "Apples"
AddArg args, "Second", "Pears"
AddArg args, "Third", "Bananas"

Or as an arguments list:

AddArgList args, "Oranges", "Peaches", "Grapefruit"

The list is then passed to the form with:

DoCmd.OpenForm "frmMyForm", OpenArgs:=args

In the form's module the values can be extracted like so:

Dim args As String, i As Integer

args = Me.OpenArgs

' get some named named arguments
Debug.Print Arg(args, "First")
Debug.Print Arg(args, "Second")
Debug.Print Arg(args, "Third")
' get some arguments by ordinal position
Debug.Print Arg(args, 4)
Debug.Print Arg(args, 5)
Debug.Print Arg(args, 6)
' list all arguments
For i = 1 To ArgCount(args)
Debug.Print Argname(args, i), Arg(args, i)
Next i
' get count of arguments
Debug.Print ArgCount(args)

You can find a demo at:


http://community.netscape.com/n/pfx...yMessages&tsn=1&tid=24091&webtag=ws-msdevapps


Ken Sheridan
Stafford, England
Click to expand...


I agree that loading up the OpenArgs string is a valid alternative.
Thanks for posting the code.

James A. Fortune
(e-mail address removed)
 
"Jim Ory" <jim[at]oryfamilyhistory[dot][comma]> wrote in message

To get out of that
situation where the name is not on the list, they could press ESC twice
and
manually go to New Record. Not very intuitive.
Click to expand...

Why do you have to do the above? My simple 5 lines of code will *prompt* the
user if the name just entered is NOT in he list....

I don't see the need for all that ESC stuff you talk about?

The user can answer "yes" to that prompt.
 
note that if you have a timer on the form opening a Form in Dialog Mode
won't suspend the calling code
I Therefore Always add the following to make sure

Const FormName As String = "frmAddClient"
DoCmd.OpenForm FormName, , , , acFormAdd, acDialog, NewData
While FisLoaded(FormName) ' your favourite FIsLoaded Function
VBA.DoEvents
Wend

Pieter

Albert D. Kallal said:
"Jim Ory" <jim[at]oryfamilyhistory[dot][comma]> wrote in message

To get out of that
situation where the name is not on the list, they could press ESC twice
and
manually go to New Record. Not very intuitive.
Click to expand...

Why do you have to do the above? My simple 5 lines of code will *prompt*
the user if the name just entered is NOT in he list....

I don't see the need for all that ESC stuff you talk about?

The user can answer "yes" to that prompt.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Trying to avoid duplicate board position titles 3
How to setup database to make transactions (transfers) between two bank accounts 0
No In List Event 3
VBA problem (I think) to check for data entry 9
Locking data in a field 4
People/Entities....always add to people/entities table? 3
Forms made from multiple tables 2
NotInList Event does not return to original record 7

Back
Top