Not In List, saving data

[Chris]

I am having trouble saving data in a record.

Using NotInList event for a client selector combo box I add a new record for
a client and open a data entry form to enter data for a new (previously
non-existent client).

Using the code below I add the record to my client table, but no data (other
than that key field) stays in the table.

Code attached to NotInList event:
Dim dbsMyDatabase As DAO.Database
Dim rstClients As DAO.Recordset
Dim intAnswer As Integer
Dim stdocName, stLinkCriteria As String

stdocName = "1frmDEClient"
stLinkCriteria = "[ClientID]=" & "'" & NewData & "'"

NewData = UCase(NewData)

On Error GoTo ErrorHandler

intAnswer = MsgBox("Add " & NewData & " to the list of Clients?", _
vbQuestion + vbYesNo)

If intAnswer = vbYes Then
' Add client stored in NewData argument to the client table.
Set dbsMyDatabase = CurrentDb
Set rstClients = dbsMyDatabase.OpenRecordset("tbl 1 Client")
rstClients.AddNew
rstClients!ClientID = NewData
rstClients.Update

Response = acDataErrAdded ' Requery the combo box list.
DoCmd.OpenForm stdocName, , , stLinkCriteria, acAdd, acDialog, NewData
Else
Response = acDataErrDisplay ' Require the user to select an
existing client.
Me.cboClient = Null
End If

Set rstClients = Nothing
Set dbsMyDatabase = Nothing
Exit Sub

ErrorHandler:
MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description
End Sub

Then, in the Load event of the data entry form:
Private Sub Form_Load()
If Not IsNull(Me.OpenArgs) Then
Me![ClientID] = Me.OpenArgs
End If

End Sub


This code does add the record, but I am unable to retain the data I enter on
the data entry form. Also, I will have subform(s) that I will need to be
adding data to (they will have their own table.)

What have I done wrong?
How do I get data in the client table?
Then, how do I get data into the suborm's tables?

Help is very much appreciated.

Chrissy

 

[Albert D. Kallal]

I suspect that your combo box has two collums, the first being the ID, and
the 2nd being the text.

The not in list event returns the TEXT VALUE that the user typed in, NOT the
ID value...

You could actually same some code by opening the form in add mode.


here is how:

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.

If wanted to get your example code working, I would pickup the id *just*
added

eg:

Dim rstClients As DAO.Recordset
Dim lngID As Long

NewData = UCase(NewData)
On Error GoTo ErrorHandler
If MsgBox("Add " & NewData & " to the list of Clients?", _
vbQuestion + vbYesNo) = vbYes Then
' Add client stored in NewData argument to the client table.
Set rstClients = CurrentDb.OpenRecordset("tbl 1 Client")
rstClients.AddNew
rstClients!ClientID = NewData
lngID = rstClients!ID
rstClients.Update
rstClients.Close
DoCmd.OpenForm stdocName, , , "id = " & lngID,, acDialog
Response = acDataErrAdded ' Requery the combo box list.
Else
Response = acDataErrDisplay ' Require the user to select an
End If

Exit Sub

ErrorHandler:
MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description


the above assume the record has a primary key of "id" (in your case, could
there be more then one clientID of the same????).

Also, you don't need any of your code in the forms on-load to set the values
because you ALREADY setup and loaded the values in he record...if you load
the form to the same record, then any fields you set in the above recordset
code will appear correctly when the form loads. Further DO NOT open the form
in add mode, as that will got to a NEW record..and we already created the
record in code (assuming your recordset example).

You can use either approach, but I might as well take the extra time to
point out why your example not working in addition to the other solution.

 

[Chris]

Albert, thanks so much. I used the short code and it works fine. Thanks
also for taking the time to explain my code as well. I guess what confuses
me (very much an amateur) is the usually simple way to accomplish a task. I
seem to always find the more complex way first!!!

To complete my current issue -- I think -- how would I code to return the
user to the newly created record on the main client form -- on closing the
new client add form?

 

[Albert D. Kallal]

To complete my current issue -- I think -- how would I code to return the

user to the newly created record on the main client form -- on closing the
new client add form?

Use the combo box after update event (it don't fire unless the value
changes).

I also did not realize that the combo box is based on the same table as the
form.

The problem here is we can NOT move the form to this new record, because the
new
records not been loaded into the forms record set yet.

This means we have to requery the form so the recordset is re-loaded, and
THEN
you can move to that record..

I should note that this approach can actually cause problems in a multi user
environment. I write a lot of tour and event management systems in MS
access.
If you just added a new client to the database, and then the person hangs up
the phone, and then about ten minutes later the person phones back, but a
**different** person in the office picks up the phone, then their form will
NOT be able to display or search or find this new record (the name
will NOT appear in the combo box either). In other words
this design does not work well in a multi-user environment to find new
records.

A simple solution to the above problem is to simply modify how you search
and find records.

I give some ideas on the searching idea here:

http://www.members.shaw.ca/AlbertKallal/Search/index.html

The main concept and idea in above is that you simply display a list of your
search values, then click on that particular valued to display an edit the
one record in the form. That way you're only ever loading one record into
the form, and the instant that any new record is added to the system, you'll
be able to search and find it.

Note that these kind of systems also encourage the users to search for the
record first, and THEN add the record AFTER they not found the record. If
you throw users right into a form, they tend to start adding records first,
and thus the design does not encourage or nudge your users in the direction
to search first, and *then* add the name. The above type search form does
encourage searching first.

To "move" the current form to the new record, you can use the combo box
after update:

me.RecordSource = "select * from yourTable where id = " & mycomboBox

(I am assuming that the 1st column of the combo box is the primary key..and
is a number type).

You could also consider doing a requery, and then a bookmark/find first type
of approach (but, a requery is not very bandwidth friendly in that your
forcing the form to re-load *all* of its records, and your user don't
need to see/view all records...they are JUST working on the ONE record.

I think it is a more clean to place a search form BEFORE you load the form.
And, your search form can have an add button they can use
when they not found what they are looking for. This design also is better
since your form ONLY ever loads up to one record, of and that makes your
application network friendly.

I do have a simple sample of a search form in my super easy word merge
sample here:
http://www.members.shaw.ca/AlbertKallal/msaccess/msaccess.html

There's quite a few benefits to using a search form in place in the combo
box.