'Problem with NotInList and unbound Form

[Volker Neurath]

Hi all,

I have a Problem with combobox-property "NotInList" and an unbound Form.

The situation:

On my main form i have three comboboxes for data-exchange (here: Names of
distributor, reseller and final customers[1], the whole database is made
for storing information about quotatations[2] - no, not for quoting itself)
ut the boxes actually may not contain all our distributors and reseller's
names (and final customers are not stored anywhere up to now) and
therefore, I want to give the users (me and a colleague ;) the possibility
to enter a customers name if during input it ist detected, that it isn't
stored.

For this purpose i use the "NotInList" property and i use it up to now with
a bound form - works good.
But now i wish to change to unbound form because i wish to pretend some
input-errors like leading/following spaces (for instance thew customer's
name is 'Multimedia Inc' but the user by mistake types
in '<space>Mutimed ia Inc'n - the next time another user types
in 'Multimedia Inc' it won't be found yet because of the leading space).

My problem:
I cannot get my code working.

This is my code for the combobox (here: final customer):
-------------

Private Sub cboFinalCustome r_NotInList(New Data As String, Response As
Integer)
*On Error GoTo fehler

* * Dim erg As Integer

* * Response = acDataErrAdded

* * 'MessageBox einblenden
* * erg = MsgBox("Der Eintrag '" & NewData & "' ist nicht in der Liste."
_
* * & "Möchten Sie den Eintrag erfassen?", vbYesNo + vbExclamation)

* * 'Wenn OK gedrückt, Erfassungsdialo g zeigen
* * If erg = vbYes Then
* * * * DoCmd.SetWarnin gs False
* * * * Response = acDataErrContin ue
* * * * DoCmd.OpenForm "frmFinalCustom erErfassung", DataMode:=acFor mAdd,
windowmode:=acD ialog, OpenArgs:=NewDa ta

* * * * 'Warnungen wieder einschalten
* * * * DoCmd.SetWarnin gs True

* * Else
* * *Response = acDataErrContin ue
* * *Me!cboFinalCus tomer.Undo
* * End If

ende:
* * Exit Sub

fehler:
* * Me!cboFinalCust omer.Value = Me!cboFinalCust omer.ItemData(0 )
* * Resume ende
End Sub
---------------------

And the following is my code for the unbound input form
----

Private Sub cmdCancel_Click ()
* * Dim erg As Long
* * 'Message-Box erzeugen und Anzeigen
* * erg = MsgBox("Eingabe Abbrechen?", Buttons:=vbYesN o + vbExclamation,
Title:="Final Customer Eingabe")

* * * * 'wenn Abbruch bestätigt
* * If erg = vbYes Then
* * * * 'dann Formular schliessen
* * * * DoCmd.Close
* * Else
* * * * 'Zurück zum letzten Steuerelement
* * * * Screen.Previous Control.SetFocu s
* * End If

End Sub
Private Sub cmdSave_Click()
* * 'Eigene speichern-Routine wg. Nachbehandlung der Eingaben
* * Dim db As DAO.Database
* * Dim rsFinalCustomer As DAO.Recordset

* * On Error GoTo err_cmdStore

* * Set db = CurrentDb()

* * 'Tabelle Final Customer öffnen
* * Set rsFinalCustomer = db.OpenRecordse t("tblFinalCust omer")

* * 'Speichern mit Nachbehandlung
* * With rsFinalCustomer
* * * * 'neuen Datensatz anfügen
* * * * .AddNew

* * * * 'Textfeldinhalt umkopieren
* * * * !strFinalName = Trim(txtFinalNa me.Value)

* * * * 'Datensatz schreiben
* * * * .Update

* * * * 'Recordset schliessen
* * * * .Close
* * End With

end_cmdSave
* * 'Datenbankobjek te zerstören
* * db.Close
* * Set rsFinalCustomer = Nothing
* * Set db = Nothing

* * Exit Sub

err_cmdSave
MsgBox Err.Description
Resume end_cmdSpeicher n
End Sub
Private Sub Form_Close()
* *
* * Forms!frmMaster Test!cboFinalCu stomer.Requery *
End Sub
Private Sub Form_Load()
*Me!txtFinalNam e.SetFocus
*Me!txtFinalNam e.Value = Me.OpenArgs

End Sub
--------

* here i get an Error "Item must be saved before requery"

I couldn't figure out where's my mistake/error in reasoning whole day.

Can anybody help me coming on the right way?

Volker

[1]Only names are stored not whole adresses. Just names and SAP number
[2]Our systems are limited in several functions so i try to get over
those limitations by using access - quotations themselves are
made with a web-tool based on SAP netweaver
--
Im übrigen bin ich der meinung, dass TCPA/TCG verhindert werden muss

[Larry Linson]

Perhaps there's a compelling reason, but I don't see why you do not validate
and edit the information in the Before Update and After Update properties of
the Controls and of the Form itself, and use a Bound Form. There are rare
times when it is needful to use an unbound form for handling data, but those
times are _rare indeed_. In any case, "unbound" or not, I would presume
that you are using a Table or Query as the Row Source on the Combo Box, so a
"standard solution" for that should work just fine.

That is, pop up a form to enter the missing Row Source information, save it,
and return. There are quite a number of examples... many can be found by
using http://groups.google.com to search the archives here; others can be
found in resources linked from my SharePoint page at
http://sp.ntpcug.org/accesssig/default.aspx (see the Resources list on that
same page).

Larry Linson
Microsof Office Access MVP
"Volker Neurath" <ne*******@gmx. dewrote in message
news:tm******** ****@ID-29596.user.indi vidual.de...

Hi all,

I have a Problem with combobox-property "NotInList" and an unbound Form.

The situation:

On my main form i have three comboboxes for data-exchange (here: Names of
distributor, reseller and final customers[1], the whole database is made
for storing information about quotatations[2] - no, not for quoting
itself)
ut the boxes actually may not contain all our distributors and reseller's
names (and final customers are not stored anywhere up to now) and
therefore, I want to give the users (me and a colleague ;) the possibility
to enter a customers name if during input it ist detected, that it isn't
stored.

For this purpose i use the "NotInList" property and i use it up to now
with
a bound form - works good.
But now i wish to change to unbound form because i wish to pretend some
input-errors like leading/following spaces (for instance thew customer's
name is 'Multimedia Inc' but the user by mistake types
in '<space>Mutimed ia Inc'n - the next time another user types
in 'Multimedia Inc' it won't be found yet because of the leading space).

My problem:
I cannot get my code working.

This is my code for the combobox (here: final customer):
-------------

Private Sub cboFinalCustome r_NotInList(New Data As String, Response As
Integer)
On Error GoTo fehler

Dim erg As Integer

Response = acDataErrAdded

'MessageBox einblenden
erg = MsgBox("Der Eintrag '" & NewData & "' ist nicht in der Liste."
_
& "Möchten Sie den Eintrag erfassen?", vbYesNo + vbExclamation)

'Wenn OK gedrückt, Erfassungsdialo g zeigen
If erg = vbYes Then
DoCmd.SetWarnin gs False
Response = acDataErrContin ue
DoCmd.OpenForm "frmFinalCustom erErfassung", DataMode:=acFor mAdd,
windowmode:=acD ialog, OpenArgs:=NewDa ta

'Warnungen wieder einschalten
DoCmd.SetWarnin gs True

Else
Response = acDataErrContin ue
Me!cboFinalCust omer.Undo
End If

ende:
Exit Sub

fehler:
Me!cboFinalCust omer.Value = Me!cboFinalCust omer.ItemData(0 )
Resume ende
End Sub
---------------------

And the following is my code for the unbound input form
----

Private Sub cmdCancel_Click ()
Dim erg As Long
'Message-Box erzeugen und Anzeigen
erg = MsgBox("Eingabe Abbrechen?", Buttons:=vbYesN o + vbExclamation,
Title:="Final Customer Eingabe")

'wenn Abbruch bestätigt
If erg = vbYes Then
'dann Formular schliessen
DoCmd.Close
Else
'Zurück zum letzten Steuerelement
Screen.Previous Control.SetFocu s
End If

End Sub
Private Sub cmdSave_Click()
'Eigene speichern-Routine wg. Nachbehandlung der Eingaben
Dim db As DAO.Database
Dim rsFinalCustomer As DAO.Recordset

On Error GoTo err_cmdStore

Set db = CurrentDb()

'Tabelle Final Customer öffnen
Set rsFinalCustomer = db.OpenRecordse t("tblFinalCust omer")

'Speichern mit Nachbehandlung
With rsFinalCustomer
'neuen Datensatz anfügen
.AddNew

'Textfeldinhalt umkopieren
!strFinalName = Trim(txtFinalNa me.Value)

'Datensatz schreiben
.Update

'Recordset schliessen
.Close
End With

end_cmdSave
'Datenbankobjek te zerstören
db.Close
Set rsFinalCustomer = Nothing
Set db = Nothing

Exit Sub

err_cmdSave
MsgBox Err.Description
Resume end_cmdSpeicher n
End Sub
Private Sub Form_Close()

Forms!frmMaster Test!cboFinalCu stomer.Requery *
End Sub
Private Sub Form_Load()
Me!txtFinalName .SetFocus
Me!txtFinalName .Value = Me.OpenArgs

End Sub
--------

* here i get an Error "Item must be saved before requery"

I couldn't figure out where's my mistake/error in reasoning whole day.

Can anybody help me coming on the right way?

Volker

[1]Only names are stored not whole adresses. Just names and SAP number
[2]Our systems are limited in several functions so i try to get over
those limitations by using access - quotations themselves are
made with a web-tool based on SAP netweaver
--
Im übrigen bin ich der meinung, dass TCPA/TCG verhindert werden muss

[Jens Schilling]

Hi, Larry

Larry Linson wrote:

There are quite a number of examples... many
can be found by using http://groups.google.com to search the archives
here; others can be found in resources linked from my SharePoint page
at http://sp.ntpcug.org/accesssig/default.aspx (see the Resources
list on that same page).

Right, and some links were already given in the german newsgroup.
There is a sample in Rogers Access Library :

http://www.rogersaccesslibrary.com/d...=NotInList.mdb

This sample is very close to the requested needs - even though there is no
unbound form used.

But some prefer to follow their own ideas...

Regards
Jens

[Volker Neurath]

Jens Schilling wrote:

But some prefer to follow their own ideas...

No - Im asking to either get help or other/new ideas.

The idea with using an unbound form came up because i didn't have any idea
how to catch and handle input-errors when using a bound form.
Now .. "before update" and "after update" of both, form and control, are the
keywords I didn't hit on all the time since yesterday.

Sorry for asking such simple questions but sometimes there are days you do
not see the wood for trees... and then you just need a helpful person who
puts you right back an your way by just giving the right keywords.

Therefore: thank you, Larry.

Volker
--
Im übrigen bin ich der meinung, dass TCPA/TCG verhindert werden muss

[Volker Neurath]

Larry Linson wrote:

Perhaps there's a compelling reason, but I don't see why you do not
validate and edit the information in the Before Update and After Update
properties of the Controls and of the Form itself, and use a Bound Form.

Outch! These arte the keywords i dind't hit on since yesterday.

Thank you for just putting me back on the right way.

and return. There are quite a number of examples... many can be found by
using http://groups.google.com to search the archives here;

but only by typing the right keywords - an that's what i was missing.

I fully ran out of ideas, just had a hole in my head.

found in resources linked from my SharePoint page at
http://sp.ntpcug.org/accesssig/default.aspx (see the Resources list on
that same page).

Thank you for that link. I'll check it out on monday, in the bureau.

I think i need these two days without having to do too much with Access ;)

Volker
--
Im übrigen bin ich der meinung, dass TCPA/TCG verhindert werden muss

[Jens Schilling]

Hi,

Volker Neurath wrote:

Jens Schilling wrote:

>But some prefer to follow their own ideas...

No - Im asking to either get help or other/new ideas.
The idea with using an unbound form came up because i didn't have any
idea how to catch and handle input-errors when using a bound form.

I'm a bit disappointed regarding your own efforts.
What more do you need than the examples labeled as Option 5 or Option 6 in
the sample of Rogers Access Library ?
There is a form opened that gives you the ability to edit the data you have
entered to the combo box.

So - what's your problem ???

Do you need more samples regarding the "NotInList-Event"?

OK, here we go:

http://home.comcast.net/~cccsolutions/accesstips.html
Not In List Combobox (Forms)

http://www.datadynamicsnw.com/seattl.../downloads.htm
Not In List - Detailed Instructions by Tom Wickerath

http://www.databasedev.co.uk/downloads.html
Using the Combo Box's NotInList event with multiple fields

http://www.geocities.com/pmpg98_pt/NotInList.html
NotInList

http://www.access-programmers.co.uk/...ad.php?t=97434
MultiNotInList. zip by ChrisO - post no. 14

And at last a german sample

http://www.access-home.de/accbsp2.htm
Ereignis 'NotInList' eines Kombinationsfel des

Regards
Jens

[Volker Neurath]

Jens Schilling wrote:

I'm a bit disappointed regarding your own efforts.

Why? I can assure you: there's no need for.

What more do you need than the examples labeled as Option 5 or Option 6 in
the sample of Rogers Access Library ?

Sorry, yesterday evening i didn't explore any of those links yet. As i said
in my post above: I felt a had a hole in my head an being empty and at the
same time too full to get anything in.

Having slept a night over it I'm now of the opinion, i shouldn't have asked
here too - not before having explored all the links given in m.p.d.a.

I today had some short looks on some of them and what i've seen there looks
very auspicious to me - I'm shure now I will solve my "problem".

I put the blame on the fact that i felt a kind of exhaustion or so.
Cannot really describe my situtation yesterday.
Today it seems to me that yesterday I didn't even really know what I was
looking for :(
You know how this feels (at least when you realize it)?

As I said in the german group yesterday: it was my off day
(including: brain.enabled=f alse) :->

Do you need more samples regarding the "NotInList-Event"?

OK, here we go:

http://home.comcast.net/~cccsolutions/accesstips.html
Not In List Combobox (Forms)

http://www.datadynamicsnw.com/seattl.../downloads.htm
Not In List - Detailed Instructions by Tom Wickerath

http://www.databasedev.co.uk/downloads.html
Using the Combo Box's NotInList event with multiple fields

http://www.geocities.com/pmpg98_pt/NotInList.html
NotInList

http://www.access-programmers.co.uk/...ad.php?t=97434
MultiNotInList. zip by ChrisO - post no. 14

Thank you once again.

And at last a german sample

http://www.access-home.de/accbsp2.htm
Ereignis 'NotInList' eines Kombinationsfel des

I've downloaded their two examples at the bureau yesterday - shortly before
i left it. I'll study them on monday, like all the other links.

Today and tomorry i use for relaxation - it's badly necessary

Volker
--
Im übrigen bin ich der meinung, dass TCPA/TCG verhindert werden muss