Not In List requery & error message on Cancel Add

[justme]

I was having a heck of a time getting my combo box to requery. I tried about
30 suggestions from different posts until I found this one thread from Brian
Bastl that works for me.

But there's one thing I can't figure out:

on my add form, I have a submit button and a cancel button.
If I click the cancel button, I get the "Item not in list" error, which I'm
pretty sure is due to the resuming of the parent form code : Response =
acDataErrAdded 'tell access to requery combo.

I need that code in order to avoid an error message if the user clicks the
submit button, but that code is messing me up if the user clicks cancel. Any
suggestions?





Private Sub cboColorway_NotInList(NewData As String, Response As Integer)
On Error GoTo Err_Form_Load

Dim strMsg As String

strMsg = cboColorway.text & " is not a known Colorway Code. Would you like
to add it?"
Beep
If MsgBox(strMsg, vbYesNo) = vbYes Then

'open form and pass NewData
DoCmd.OpenForm "fmpColorwayAdd", , , , acFormAdd, acDialog,
UCase(cboColorway.text)
'Response = acDataErrContinue
Response = acDataErrAdded 'tell access to requery combo

Else
'cancel event and undo typing
Response = acDataErrContinue
Me.Undo

End If

Exit_Form_Load:
Exit Sub

Err_Form_Load:
MsgBox "Error in Sub cboColorway_NotInList in " & Me.Name & " form." & _
vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & Err.Description
Err.Clear

End Sub






Private Sub btnCancel_Click()
'------------------------------------------------------------
' Cancel Submit Comment
' Created by Mari Berg 2006
'------------------------------------------------------------
On Error GoTo Err_btnCancel

Me.Undo
DoCmd.Close acForm, "fmpColorwayAdd"

Exit_btnCancel:
Exit Sub

Err_btnCancel:
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume Exit_btnCancel

End Sub




Thank you!

 

[strive4peace]

try it without 'Me.Undo'

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

 

[justme]

Hi Crystal,

Thank you for your reply!

I tried your suggestion but am still getting the error message. I think I
need to put an if somewhere to maybe stop the add form from going back to the
main form code & continuing?
Any other ideas?

 

[AccessVandal via AccessMonster.com]

Hi,

Look at this MS KB, method 2.

http://support.microsoft.com/kb/197526/en-us

 

[justme]

Hi,

thank you for your reply. I took a look at the link. It doesn't seem to
address my specific problem with my cancel button, so I'm not sure if I
should try the suggestions, since my requery is working now, but I will
definitely save this post. do you think changing my code to use the method on
the link would help me to solve my cancel button error problem?

Thank you.

 

[strive4peace]

NotInList Example
---

how about

Me.controlname.Undo

~~~

otherwise, here is working code for NotInList, storing IDs and showing text:

.... this example uses just one column for the text to display, but you
can use concatenated fields and parse them

Here is a complete process, from

making sure IDs are in your table structure
updating IDs is your tables are linked on text
setting up a combo on your form
adding data to a related table using NotInList event ...

~~~~~~~~~~
this example assumes:

MainTablename has SomeID as an Autonumber
RelatedTablename has SomeID as Long Integer

~~~~~~~~~~

if you are not linking tables on IDs...

firstly, use SomeID (or some other efficient key field -- AddrID,
TicketID, SaleID, etc, ...)

1. modify main table structure to include SomeID as an autonumber

2. modify related table structure to include SomeID as a number (Long
Integer, defaltValue = Null)

now you need to update the SomeIDs in your related tables with the
SomeID from the main table based on the text.

here are general instructions on how to do that:

QUERY TO UPDATE RELATED ID

you are storing a text field in a related table that relates to a text
field in the main table
now, you have added a Long Integer ID in the related table and want to
populate it with the IDs from the main table

make a new query based on the table you want to change
--> RelatedTablename

add the main table to your query
--> MainTablename

link the two tables on the common text field
--> SomeName

change the query type from a Select Query to an Update Query
from the menu bar --> Query, Update

on the grid:

field --> SomeID
table --> RelatedTablename
UpdateTo --> MainTablename.SomeID

then, RUN (!) your query

a quick way check to ensure that all related IDs were filled out
(without writing comparison query) is:
1. open related table
2. sort by common text field (SomeName)
3. visually scan ID field (SomeID) to make sure it is filled out for
every record where there was text
(you can also sort by SomeID and verify that wherever there is no value,
there is also no value in SomeName)

Naturally, if you have thousands of records, you can design a query to
show records where text Is Not Null and ID_field Is Null

-- then, add those unmatched text values to the main table
OR edit them so text text matches and run query again, and/or manually
fill IDs

Make sure that none of the SomeID values in the related table are 0 ...
these will not match to a SomeID created using Autonumber

Then, when you are satisfied that all data has been linked, delete the
text field from the related table
compact/repair database to regain the space it was using

naturally, back up your database before running any action queries on it

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

on your form, here is an example with the properties you need to set for
a combobox that stores the ID and displays the text

combobox control

Name --> SomeID
ControlSource --> SomeID
RowSource -->
SELECT
SomeID,
SomeName
FROM Tablename
ORDER BY SomeName

BoundColumn --> 1
ColumnCount --> 2

columnWidths --> 0;2
(etc for however many columns you have
-- the ID column will be hidden since its width is zero)

ListWidth --> 2
(should add up to the sum of the column widths)

SomeID will be stored in the form RecordSource while showing you
information from another table...

for the NotInList event of the combobox, here is code behind the form:

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Code:


Private Sub ControlName_NotInList( _
NewData As String, _
Response As Integer)

'assumption:
'the combobox controlname is
'RecordID_controlname
'and its first column (hidden)
'is the Autonumber record ID for the source table

Dim s As String _
, mRecordID As Long _
, mText As String

'if NewData needs to be parsed,
'seperate it and
'modify the SQL accordingly

'~~~~~~~~~~~~~~~~~~~~~~~~
'Choose ONE of these code blocks

'--------------------------------------------------------

'if you want to convert to ProperCase
'mText = StrConv(NewData, vbProperCase)

's = "INSERT INTO Tablename(SomeName) " _
& " SELECT '" & mText & "';"

'--------------------------------------------------------

s = "INSERT INTO Tablename(SomeName) " _
& " SELECT '" & NewData & "';"

'--------------------------------------------------------
'~~~~~~~~~~~~~~~~~~~~~~~~

'comment or remove next line after this works correctly
Debug.Print s

CurrentDb.Execute s

CurrentDb.TableDefs.Refresh
DoEvents

mRecordID = Nz(DMax("SomeID", "Tablename"))

If mRecordID > 0 Then
Response = acDataErrAdded

'assuming the first column of the listbox
'is the RecordID, SomeID and is a Long Integer

Me.SomeID_controlname = mRecordID
Else
Response = acDataErrContinue
End If

End Sub
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

 

[AccessVandal via AccessMonster.com]

It’s hard to tell without looking at your database.

When you say, “Cancelâ€, did you mean the button on the form “frmColorwayAddâ€?

When you cancel it, was the record added?

do you think changing my code to use the method on
the link would help me to solve my cancel button error problem?

Yes, the “Dlookup†function will search the record and return the result
after you have added the record in the form “frmColorwayAddâ€.

The IF Else statement,

If no records, change to Response = acDataErrContinue and return message box.

If false, change to Response = acDataErrAdded

Both of these will continue without error messages.

 

[justme]

I think I solved it.

If the user presses cancel on my add form, instead of closing the form, it
hides it.

Then in the parent form, the code continues with an if statement based on if
the add form is loaded. if it is, then the code goes to acDataErrContinue
and closes the add form.

see below:

Beep
If MsgBox(strMsg, vbYesNo) = vbYes Then

'open form and pass NewData
DoCmd.OpenForm "fmpCoOrAdd", , , , acFormAdd, acDialog, UCase(NewData)
'Response = acDataErrContinue

'this line redirects error message if cancel button has been pressed from
add form
If (CurrentProject.AllForms("fmpCoOrAdd").IsLoaded = False) Then
Response = acDataErrAdded 'tell access to requery combo
Else
'cancel event and undo typing
Response = acDataErrContinue
Me.Undo
DoCmd.Close acForm, "fmpCoOrAdd"
End If
End If


It works so far, anyway

 

[justme]

Hey, I just now saw your post, and I tried your suggestion and tried it, and,
what do you know, it worked!

It's funny, I had tried that dlookup method before from another post, but
couldn't get it to work.

I'm guessing that this solution would be cleaner than the solution I found
and posted just earlier, so I think I will go with the MS solution. Thanks
so much!
Have a great day!