NotInList help requested

  • Thread starter Thread starter Charles W. Stricklin
  • Start date Start date
C

Charles W. Stricklin

I'm pretty sure this is a newbie question, and I have read similar things in
this group thus far, but if someone would have pity on me and help me out
I'd really appreciate it.

I have an Access 2000 database file named Installations.mdb. In it, there is
one table named installations. There is a text field called FIT_Name in that
table. In a form, I'd like to display the contents of that field in combo
box. If a user chooses an existing financial institution name that choice is
inserted into the record's FIT_Name field. If they type something different,
I'd like a dialog box asking them to confirm that they'd like to add that
value to the list, and then adding it if they choose yes, or not if they
choose no.

I'm familiar with setting the LimitToList property to yes, and I can cut and
paste EventProcedures with the best of them. However, writing code from
scratch is something I haven't done a lot of yet and I don't have time to
devote to learning right now.

Also, should I enable DAO? If so, how?

Thanks in advance for any help offered,

Charles
 
Thanks. But now, I get a Compile Error message "User-defined type not
defined."
 
Thanks. But now, I get a Compile Error message "User-defined type not
defined."
Click to expand...

If you haven't already, you will need to add a reference to the Microsoft DAO
3.6 Object Library.
 
Okay. I added the missing End If as well. Now I get Runtime error #3078: The
Microsoft Jet database engine cannot find the input table or query 'tblAE'.
Make sure it exists and that its name is spelled correctly.
 
Hi,
You have to modify the code to reflect *your* database.
Obviously you won't have a table called tblAE.
Substitute your table name.
 
Hi guys,

Here is the code thus far:

'************ Code Start **********
' This code was originally written by Dev Ashish.
' It is not to be altered or distributed,
' except as part of an application.
' You are free to use it in any application,
' provided the copyright notice is left unchanged.
'
' Code Courtesy of
' Dev Ashish
'
Private Sub FIT_Name_Combo_Box_NotInList(NewData As String, Response As
Integer)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strMsg As String

strMsg = "There is not currently a financial institution named '" &
NewData & "' available in this list. "
strMsg = strMsg & "Do you want to add '" & NewData & "' to the list?"
strMsg = strMsg & vbCrLf & vbCrLf & "Click Yes to link or No to re-type
it."

If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new name?") = vbNo Then
Response = acDataErrContinue
Else
Set db = CurrentDb
Set rs = db.OpenRecordset("installations", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!AEName = NewData
rs.Update

If Err Then
MsgBox "An error occurred. Please try again."
Response = acDataErrContinue
Else
Response = acDataErrAdded
End If
End If

rs.Close
Set rs = Nothing
Set db = Nothing
End Sub
'*********** Code End **************

Now, anytime I try to add an item, it always falls to the "An error
occurred. Please try again."
 
Hi guys,

Okay, I've modified Dev's code to add the value to the table, but it's still
not quite right.

Say, for example, my table 'installations' has 3 records where the
'FIT_Name's are "A", "B" and "C".
Step 1: I open up the form (displaying record #1) and type "D" in the
'FIT_Name' combo box.
Step 2: A dialog appears asking me if I'd like to add "D" to the combo box
and table.
Step 3: I click "yes".

I'd like for Access to create a 4th record, enter "D" into the 'FIT_Name'
field and change the display to reflect the newly created 4th record.
Currently, record #1's 'FIT_Name' field changes to "D", a 4th record is
created and "D" is entered into the 'FIT_Name' field, and the 2nd record is
displayed. Any help?

The code as is follows:

'************ Code Start **********
' This code was originally written by Dev Ashish.
' It is not to be altered or distributed,
' except as part of an application.
' You are free to use it in any application,
' provided the copyright notice is left unchanged.
'
' Code Courtesy of
' Dev Ashish
'
Private Sub FIT_Name_Combo_Box_NotInList(NewData As String, Response As
Integer)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strMsg As String

strMsg = "There is not currently a financial institution named '" &
NewData & "' available in this list. "
strMsg = strMsg & "Do you want to add '" & NewData & "' to the list?"
strMsg = strMsg & vbCrLf & vbCrLf & "Click Yes to link or No to re-type
it."

If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new name?") = vbNo Then
Response = acDataErrContinue
Else
Set db = CurrentDb
Set rs = db.OpenRecordset("installations", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!FIT_Name = NewData
rs.Update

If Err Then
MsgBox "An error occurred. Please try again."
Response = acDataErrContinue
Else
Response = acDataErrAdded
End If
End If

rs.Close
Set rs = Nothing
Set db = Nothing
End Sub
'*********** Code End **************
 
I'd like for Access to create a 4th record, enter "D" into the 'FIT_Name'
field and change the display to reflect the newly created 4th record.
Currently, record #1's 'FIT_Name' field changes to "D", a 4th record is
created and "D" is entered into the 'FIT_Name' field, and the 2nd record is
displayed. Any help?
Click to expand...

Is your combo box bound a "'FIT_Name" field in the same table to which you are
trying to add the new value?
 
I think I need to rethink what I'm trying to accomplish here. Rather than
add an entirely new record here, let's simplify things a bit and leave it to
the user to add records.

1. Let's say for example I have a table named 'tlblInstallations' where I
store all of my records, and I also have a table named 'tlblFITs' where all
I store is the name of financial institutions.
2. Let's also say that currently there are 3 records in each of the above
tables:

Record tlblInstallations.fldFIT_Name
tlblFITs.fldFIT_Name
1 "First National Bank of Podunk"
"First National Bank of Podunk"
2 "Podunk Federal Credit Union"
"Podunk Federal Credit Union"
3 "Podunk City Savings and Loan"
"Podunk City Savings and Loan"

3. The db user creates a new record for a new installation. A combo box
shows all the previously entered financial institution names. He or she
choses "Podunk Federal Credit Union" and continues entering data into that
new record. Now we should have:

Record tlblInstallations.fldFIT_Name
tlblFITs.fldFIT_Name
1 "First National Bank of Podunk"
"First National Bank of Podunk"
2 "Podunk Federal Credit Union"
"Podunk Federal Credit Union"
3 "Podunk City Savings and Loan"
"Podunk City Savings and Loan"
4 "Podunk Federal Credit Union"

4. Now, let's have the user create a new record and type in the name of a
financial institution that is not in the list, say, "Bank of Podunk". A
dialog appears informing him or her that "Bank of Podunk" is not a name that
is found on the list, and would he or she like to add it? He or she clicks
"Yes". This is what we should have:

Record tlblInstallations.fldFIT_Name
tlblFITs.fldFIT_Name
1 "First National Bank of Podunk"
"First National Bank of Podunk"
2 "Podunk Federal Credit Union"
"Podunk Federal Credit Union"
3 "Podunk City Savings and Loan"
"Podunk City Savings and Loan"
4 "Podunk Federal Credit Union"
"Bank of Podunk"
5 "Bank of Podunk"

Another question: If I plan to port this over to a MySQL/PHP combination
later, is it better to have the financial institution names in a table or
some sort of enumerated/value list?

BTW, thanks for all of the great help so far. I'm really starting to enjoy
this.

Charles
--
 
This is what we should have:
Record tlblInstallations.fldFIT_Name
tlblFITs.fldFIT_Name
1 "First National Bank of Podunk"
"First National Bank of Podunk"
2 "Podunk Federal Credit Union"
"Podunk Federal Credit Union"
3 "Podunk City Savings and Loan"
"Podunk City Savings and Loan"
4 "Podunk Federal Credit Union"
"Bank of Podunk"
5 "Bank of Podunk"
Click to expand...

You don't say whether you're getting what you "should have" or not. If not, what
is not happening that you need to happen? Keep in mind that the user entering a
record into the "tlblInstallations" table and your code entering a record into
the "tlblFITs" table are two separate operations. As long as the "NotInList"
event procedure code is successfully adding the new value to "tlblFITs" and
requerying the combo box (by setting "Response" to "acDataErrAdded") when the
user confirms that the value should be added, that problem is solved and any
additional problems should be addressed separately.
Another question: If I plan to port this over to a MySQL/PHP combination
later, is it better to have the financial institution names in a table or
some sort of enumerated/value list?
Click to expand...

If you want to dynamically add a value, as you are doing now, you will need to
store the values in a table.

:-)
 
Everything I have works perfectly except:

1. If I click "No" (I don't want to add the financial institution name to
the list) the alert "An error occurred. Please try again." appears.
2. If I click "Yes" (I do) the record currently being added increments. For
example, I create a new record number 6 (of 6), I type in something new and
click okay; whatever I type correctly appears in record number 6, but
suddenly I'm editing record 7 of 7.

Any suggestions?

Here's what I have currently:

'************ Code Start **********
' This code was originally written by Dev Ashish.
' It is not to be altered or distributed,
' except as part of an application.
' You are free to use it in any application,
' provided the copyright notice is left unchanged.
'
' Code Courtesy of
' Dev Ashish
'
Private Sub cboFIT_Name_NotInList(NewData As String, Response As Integer)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim rsSaved As DAO.Recordset
Dim strMsg As String

strMsg = "There is not currently a financial institution named '" &
vbCrLf & NewData & "' available in this list. "
strMsg = strMsg & "Do you want to add '" & NewData & "' to the list?" &
vbCrLf & vbCrLf
strMsg = strMsg & vbCrLf & vbCrLf & "Click Yes to link or No to re-type
it."

If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new name?") = vbNo Then
Response = acDataErrContinue
Else
Set db = CurrentDb
Set rsSaved = CurrentDb.OpenRecordset("tlblInstallations",
dbOpenSnapshot)
Set rs = db.OpenRecordset("tlblFITs", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!fldFIT_Name = NewData
rs.Update

If Err Then
MsgBox "An error occurred. Please try again."
Response = acDataErrContinue
Else
Response = acDataErrAdded
End If
End If

rs.Close
Set db = CurrentDb
Set rs = rsSaved
rs.Update

End Sub
'*********** Code End **************
 
Comments in-line.

Charles W. Stricklin said:
Everything I have works perfectly except:

1. If I click "No" (I don't want to add the financial institution name to
the list) the alert "An error occurred. Please try again." appears.
2. If I click "Yes" (I do) the record currently being added increments. For
example, I create a new record number 6 (of 6), I type in something new and
click okay; whatever I type correctly appears in record number 6, but
suddenly I'm editing record 7 of 7.

Any suggestions?

Here's what I have currently:

'************ Code Start **********
' This code was originally written by Dev Ashish.
' It is not to be altered or distributed,
' except as part of an application.
' You are free to use it in any application,
' provided the copyright notice is left unchanged.
'
' Code Courtesy of
' Dev Ashish
'
Private Sub cboFIT_Name_NotInList(NewData As String, Response As Integer)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Click to expand...

**Comments affecting the following object variable (rsSaved) appear later.
Dim rsSaved As DAO.Recordset
Dim strMsg As String

strMsg = "There is not currently a financial institution named '" &
vbCrLf & NewData & "' available in this list. "
strMsg = strMsg & "Do you want to add '" & NewData & "' to the list?" &
vbCrLf & vbCrLf
strMsg = strMsg & vbCrLf & vbCrLf & "Click Yes to link or No to re-type
it."

If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new name?") = vbNo Then
Response = acDataErrContinue
Else
Set db = CurrentDb
Set rsSaved = CurrentDb.OpenRecordset("tlblInstallations",
dbOpenSnapshot)
Click to expand...

**What is the purpose of the line above? You don't actually do anything with it.
Set rs = db.OpenRecordset("tlblFITs", dbOpenDynaset)
On Error Resume Next
Click to expand...

**Try commenting out the line above so you can see what, and where, your errors
are (you should really use *real* error handling - see
http://users.bigpond.net.au/abrowne1/ser-23a.html for help with that).
rs.AddNew
rs!fldFIT_Name = NewData
rs.Update

If Err Then
MsgBox "An error occurred. Please try again."
Response = acDataErrContinue
Else
Response = acDataErrAdded
End If
End If
Click to expand...


**This part makes no sense to me and will generate additional errors (but your
error handling approach - or lack thereof - hides this fact).
rs.Close
Set db = CurrentDb
Set rs = rsSaved
rs.Update
Click to expand...

**What, exactly, are you trying to achieve here? It should read ...

'Now ignore any errors as the object variables may not have been
' created due to user response or error
On Error Resume Next
'Clean up object references
rs.Close
Set rs = Nothing
Set db = Nothing

.... PERIOD! These should be placed in the exit part of your error handling so
that these resources are released even when an error occurs. Try again (and lose
the snapshot and its variable, "rsSaved").

:-)
 
Okay, that took care of the error when clicking "No" part, but the record
being modified part still increments. Why? How do I make it stop?

Here's the correction:


'************ Code Start **********
' This code was originally written by Dev Ashish.
' It is not to be altered or distributed,
' except as part of an application.
' You are free to use it in any application,
' provided the copyright notice is left unchanged.
'
' Code Courtesy of
' Dev Ashish
'
Private Sub cboFIT_Name_NotInList(NewData As String, Response As Integer)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strMsg As String

strMsg = "There is not currently a financial institution named '" &
vbCrLf & NewData & "' available in this list. "
strMsg = strMsg & "Do you want to add '" & NewData & "' to the list?" &
vbCrLf & vbCrLf
strMsg = strMsg & vbCrLf & vbCrLf & "Click Yes to link or No to re-type
it."

If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new name?") = vbNo Then
Response = acDataErrContinue
Else
Set db = CurrentDb
Set rs = db.OpenRecordset("tlblFITs", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!fldFIT_Name = NewData
rs.Update

If Err Then
MsgBox "An error occurred. Please try again."
Response = acDataErrContinue
Else
Response = acDataErrAdded
End If
End If

'Now ignore any errors as the object variables may not have been
' created due to user response or error
On Error Resume Next
'Clean up object references
rs.Close
Set rs = Nothing
Set db = Nothing
End Sub
'*********** Code End **************
 
* Lightbulb going on overhead *

Ohhhhhhhh! I see now!

If cboFIT_Name is the only field in my form, and press the tab key, it
automatically begins editing a new record if it's at the end or moves to the
next record if it isn't.

Well, that kinda blows. Any way to stop *that*?

Charles
-------
 
* Lightbulb going on overhead *
Ohhhhhhhh! I see now!

If cboFIT_Name is the only field in my form, and press the tab key, it
automatically begins editing a new record if it's at the end or moves to the
next record if it isn't.

Well, that kinda blows. Any way to stop *that*?
Click to expand...

Charles:

Please clarify one (or more) thing(s). What table is in the recordsource of *the
form* and what table is in the rowsource of the combo box? I am just a little
confused by this last revelation that there is only *one* field in your form.
What kind of records do you keep that simply require one text field ... with the
field's value being looked up in yet another table(?) with a single text field
(if that's what's happening here)? Your code seems to be adequate to add a
record to "tblFITs", as long as you aren't getting any errors (and you haven't
yet implemented proper error handling, nor have you disabled the "On Error
Resume Next" line to allow you to see where any errors are occurring) and I
don't see where this code has anything to do with any other problems you seem to
be having, unless you are adding a new record to the same table that the form is
bound to.
 
Bruce,

I was just setting up a simple test database to try out the things we
discussed here, using only 1 field. I have a form (frmInstallations) that
contains a combo box (frmFIT_Name) that is bound to a field (fldFIT_Name) in
a table (tlblFITs). In other words: SELECT [tlblFITs].[fldFIT_Name] FROM
tlblFITs;

When I create a new record in tlblInstallations, then choose the name of a
financial institution already contained in tlblFITs.fldFIT_Name, that name
is entered into that record's tlblInstallations.fldFIT_Name. If instead, I
type a new financial institution's name, and click "Yes", that name is
entered both into tlblInstallations.fldFIT_Name AND tlblFITs.fldFIT_Name,
and the combo box reflects the change.

If I press the tab key, and my focus is the last field on the form, in my
small test db the only field WAS the last field, then Access begins editing
a new record.

Sorry for all the confusion. If you like I can post the db somewhere or post
pictures here or upload the db here, whatever. Either way, I'm happy now.
Thanks for all the input and help.

Charles
 
If I press the tab key, and my focus is the last field on the form, in my
small test db the only field WAS the last field, then Access begins editing
a new record.
Click to expand...

Actually, the focus moves to a new record, but no editing has begun unless a key
has been pressed or a value has been assigned by code.
Sorry for all the confusion. If you like I can post the db somewhere or post
pictures here or upload the db here, whatever. Either way, I'm happy now.
Thanks for all the input and help.
Click to expand...

No problem. If you're happy, I'm happy. <g>
 
Back
Top