NotInList VB Code

[Little Penny]

I have a UserID field that is in a form Called Computer and Users. The UseID field is the primary key to the UserInfo Table The Users table and
Computers table are joined (one to Many} by the UserID are in a query which is the record set for the form. My UserID field in the form is set to
limit to list. The drop down show 3 columns (UserID First and Last name) only the UserID is bound to field. I want to
1. If user enter UserID the not in list a MsgBox letting the user know and giving option to add to list. If they say no it undoes the changes and
goes back the the UserID field . If yes open another form to add user as well as undo any changes. I tried to tweak this code I have below but I'm
stuck. Everything work except yes option. It opens the form but I get a msg saying The text you entered is not in list please select from list. Also
it does not undo chages to UserID field.

I know I will also need a code to re populate the query on the form. But I guess I have to get this going first.



Private Sub UserID_NotInList(NewData As String, Response As Integer)
On Error GoTo UserID_NotInList_Err
Dim intAnswer As Integer
Dim strSQL As String
intAnswer = MsgBox("The User ID " & Chr(34) & NewData & _
Chr(34) & " is not currently in the in User ID list." & vbCrLf & _
"Would you like to add a new User to the list now?" _
, vbQuestion + vbYesNo, "User ID ")
If intAnswer = vbYes Then
DoCmd.OpenForm "frm_UserInfo"

Else
MsgBox "Please choose a User ID from the list." _
, vbInformation, "User ID"
Response = acDataErrContinue
Me.UserID.Undo
End If
UserID_NotInList_Exit:
Exit Sub
UserID_NotInList_Err:
MsgBox Err.Description, vbCritical, "Error"
Resume UserID_NotInList_Exit


End Sub





Thanks

 

[Jeff Boyce]

Where is the

Response = acDataErrAdd

statement? Re-check Access HELP for syntax on NotInList code.

Regards

Jeff Boyce
<Office/Access MVP>

 

[Albert D.Kallal]

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 the use of acDialog to "halt" the code.

Note the use of acDataErrContinue (this tells ms-access to re-load, and
re-query the combo
box...again..this saves some code).

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.

 

[Little Penny]

WOW........................................


Lost for words



Thanks