updating dropdown lists

  • Thread starter Thread starter my-wings
  • Start date Start date
M

my-wings

I have a form with drop-down lists. I want to be able to start typing
something in the list and if it is "not in list" I would like to be asked to
add it to the list. I'm sure this has to come up all the time, so I googled
for some code and found the message below, but I think it might be from an
older version of Access (date posted: 2-21-99) because the debugger stops on
a few things. I am hoping that you can help me get this working, because I
would use it all the time!

Here is the complete google reference:
http://groups.google.com/[email protected]

Here is the message and code I copied and tried to use. (Questions follow):

~~~~~~~~~~~~~BEGIN COPYING~~~~~~~~~~
Here's some code to make your life easier:
Add the Sub call to the Item Not In List Event on your form. For example, if
the
Terms of Payment field on a vendor form is a List Box, add this code to the
event:
ThingNotInList "tTerms", "Terms", "Terms", NewData, Response

Where "tTerms" is the table name that contains the different payment terms
that
are currently on file
"Terms" is the name of the field that contains the description of
the
payment terms
"Terms" is the description of the field that will appear in the
prompt to save / abandon the new data
NewData is the new terms data that the user entered in the list
box
Response is the users decision to add or abandon the new payment
terms

Here is the structure of the Terms Table (tTerms)
TermsID AutoNumber
Terms Text 50
The primary key is TermsID

' Generic subroutine to add an entry to a table.
============================================================================
Sub ThingNotInList(pThingTableName As String, pThingFieldName As String,
pThingDesc As String, pNewThingName As String, pResponse As Integer)
' Arguments:
' pThingTableName => The name of the table into which the new record
will
be inserted
' pThingFieldName => The name of the field in pThingTableName into
which
the new data will be inserted
' pThingDesc => A text description of the data that is being handled:
"Employee Name", "Author", "Date of Birth", etc. This text will appear in
the
message box that prompts the user to add pThingFieldName to pThingTableName
' pNewThingName => The new data that will be inserted into the
pThingFieldName field. It's the info that the user typed into the list box
onthe
form.
' pResponse => The user's decision: Add the data or not:
DATA_ERRADDED
or DATA_ERRCONTINUE

' Bill Nicholson: (e-mail address removed)

Dim status As Integer
Dim mydb As Database
Dim thingRecordset As Recordset
Dim thingField As Field
Dim thingTblDef As TableDef
Dim thingSize As Long

status = MsgBox("The " + pThingDesc + " that you entered is not on file." +
vbNewLine + " Do you want to add it?", 4 + 32, "")

If status = 6 Then ' User clicked "Yes" - he wants to add the Thing

Set mydb = CurrentDb
' Look up the field size
Set thingTblDef = mydb.TableDefs(pThingTableName)
With thingTblDef
For Each thingField In .Fields
If thingField.Name = pThingFieldName Then
thingSize = thingField.Size
Exit For
End If
Next thingField
End With
Set thingRecordset = mydb.OpenRecordset(pThingTableName)
thingRecordset.AddNew
thingRecordset(pThingFieldName) = Mid$(pNewThingName, 1, thingSize)
thingRecordset.Update

thingRecordset.Move 0, thingRecordset.LastModified ' Go to new record

thingRecordset.Close
mydb.Close

pResponse = DATA_ERRADDED ' Access will add the data to the
combo
box
Else
pResponse = DATA_ERRCONTINUE ' User clicked "No" - he does not
want
to add the Thing
End If

End Sub

~~~~~~~~~~~~END COPYING~~~~~~~~~~~

So far, my debugger stopped on:

"thingSize = thingField.Size" as it didn't recognize ".Size" I changed that
to ".ActualSize" as my guess for the closest thing on the available list.

Next, it stopped on:

thingRecordset.Move 0, thingRecordset.LastModified ' Go to new record

It didn't recognize ".LastModified" I couldn't find any likely equivalent
for that. (I was sort of hoping it would just "work" since I'm pretty new at
this.) There is an example of ".LastModified" in the help file, but it
doesn't show up as an option in that drop down list that appears when you
type the "dot" after the word "thingRecordset"

Any help would be greatly appreciated.

Alice
 
Ok...replying to my own message since no one else did, lol....I figured out
a lot of things, but I'm still having some problems.

This is a generic subroutine to add a new item to a table in a drop down
list.

The OnNotInList event calls
=====================================================
Private Sub PubCityID_NotInList(NewData As String, Response As Integer)
ThingNotInList "tblPublisherCities", "PubCity", "Publisher City", NewData,
Response
End Sub
========================================================
The ThingNotInList subroutine is shown below my comments.

My current trouble is in the With block:"thingField" isn't defined.

I copied this code, and I'm trying to figure it out, but I'm not sure what
the original author was trying to do. I know the name of the actual field I
want to update. It's "PubCity". It looks as if the original author wants to
test the maximum size of the field and shorten the entered string, if
necessary, to fit. But I'm not sure what the point of
If thingField.Name = pThingFieldName Then
is, and I don't know how to set thingField.

Can anyone enlighten me?

the whole subroutine is below.
==========================================
Sub ThingNotInList(pThingTableName As String, pThingFieldName As String, _
pThingDesc As String, pNewThingName As String, pResponse As Integer)

Dim status As Integer
Dim mydb As Database
Dim thingRecordset As DAO.Recordset
Dim thingField As Field
Dim thingTblDef As TableDef
Dim thingSize As Long

status = MsgBox("The " + pThingDesc + " that you entered is not on file." +
_
vbNewLine + " Do you want to add it?", 4 + 32, "")

If status = 6 Then ' User clicked "Yes" - he wants to add the Thing

Set mydb = CurrentDb
' Look up the field size
Set thingTblDef = mydb.TableDefs(pThingTableName)

With thingTblDef
For Each thingField In .Fields
If thingField.Name = pThingFieldName Then
thingSize = thingField.DefinedSize
Exit For
End If
Next thingField
End With
Set thingRecordset = mydb.OpenRecordset(pThingTableName)
thingRecordset.AddNew
thingRecordset(pThingFieldName) = Mid$(pNewThingName, 1, thingSize)
thingRecordset.Update

thingRecordset.Move 0, thingRecordset.LastModified ' Go to new record

thingRecordset.Close
mydb.Close

pResponse = DATA_ERRADDED ' Access will add the data to the combo box
Else
pResponse = DATA_ERRCONTINUE ' User clicked "No" - he does not want
to add the Thing
End If

End Sub
==========================================
 
What you have is old. Let's see if we can simplify it a little.

Here is an example. It uses DAO, so if you don't have a Reference set for DAO you will
have to add one. To check References, open a code window (Alt+F11) and go to
Tools|References. If there isn't a check next to Microsoft DAO 3.6 Object Library, add
one.


Private Sub cboNamePrefix_NotInList(NewData As String, Response As Integer)

Dim db As DAO.Database, rst As DAO.Recordset

msg = "You have entered a value that doesn't exist." & vbCrLf & "Do you want to add it?"
If MsgBox(msg, vbYesNo + vbQuestion, "Not in List") = vbYes Then
Set db = CurrentDb
'Open the recordset that has the RowSource data
Set rst = db.OpenRecordset("tblNamePrefix")
'Add the new data to the recordset
With rst
.AddNew
![Name Prefix] = NewData 'Add data.
.Update 'Save changes.
.Close
End With
'Tells Access you added a new item
Response = acDataErrAdded
Else
'If No was chosen in the MsgBox then tell
'Access you didn't want the new item
Response = acDataErrContinue
Me.cboNamePrefix.Undo
End If
'Clean up after yourself
Set rst = Nothing
Set db = Nothing

End Sub
 
Try changing the declaration of the thingField variable to Dim thingField As
DAO.Field, as you've already done with the declaration of the Recordset
object. As with 'Recordset', both the DAO and ADODB object libraries include
classes named 'Field'.

There's an alternative example of how to add an item to a combo box list in
the 'Forms' section at www.mvps.org/access
 
Thank you! This did the trick!

Alice


Wayne Morgan said:
What you have is old. Let's see if we can simplify it a little.

Here is an example. It uses DAO, so if you don't have a Reference set for DAO you will
have to add one. To check References, open a code window (Alt+F11) and go to
Tools|References. If there isn't a check next to Microsoft DAO 3.6 Object Library, add
one.


Private Sub cboNamePrefix_NotInList(NewData As String, Response As Integer)

Dim db As DAO.Database, rst As DAO.Recordset

msg = "You have entered a value that doesn't exist." & vbCrLf & "Do you want to add it?"
If MsgBox(msg, vbYesNo + vbQuestion, "Not in List") = vbYes Then
Set db = CurrentDb
'Open the recordset that has the RowSource data
Set rst = db.OpenRecordset("tblNamePrefix")
'Add the new data to the recordset
With rst
.AddNew
![Name Prefix] = NewData 'Add data.
.Update 'Save changes.
.Close
End With
'Tells Access you added a new item
Response = acDataErrAdded
Else
'If No was chosen in the MsgBox then tell
'Access you didn't want the new item
Response = acDataErrContinue
Me.cboNamePrefix.Undo
End If
'Clean up after yourself
Set rst = Nothing
Set db = Nothing

End Sub


--
Wayne Morgan
Microsoft Access MVP


my-wings said:
Ok...replying to my own message since no one else did, lol....I figured out
a lot of things, but I'm still having some problems.

This is a generic subroutine to add a new item to a table in a drop down
list.

The OnNotInList event calls
=====================================================
Private Sub PubCityID_NotInList(NewData As String, Response As Integer)
ThingNotInList "tblPublisherCities", "PubCity", "Publisher City", NewData,
Response
End Sub
========================================================
The ThingNotInList subroutine is shown below my comments.

My current trouble is in the With block:"thingField" isn't defined.

I copied this code, and I'm trying to figure it out, but I'm not sure what
the original author was trying to do. I know the name of the actual field I
want to update. It's "PubCity". It looks as if the original author wants to
test the maximum size of the field and shorten the entered string, if
necessary, to fit. But I'm not sure what the point of
If thingField.Name = pThingFieldName Then
is, and I don't know how to set thingField.

Can anyone enlighten me?

the whole subroutine is below.
==========================================
Sub ThingNotInList(pThingTableName As String, pThingFieldName As String, _
pThingDesc As String, pNewThingName As String, pResponse As Integer)

Dim status As Integer
Dim mydb As Database
Dim thingRecordset As DAO.Recordset
Dim thingField As Field
Dim thingTblDef As TableDef
Dim thingSize As Long

status = MsgBox("The " + pThingDesc + " that you entered is not on file." +
_
vbNewLine + " Do you want to add it?", 4 + 32, "")

If status = 6 Then ' User clicked "Yes" - he wants to add the Thing

Set mydb = CurrentDb
' Look up the field size
Set thingTblDef = mydb.TableDefs(pThingTableName)

With thingTblDef
For Each thingField In .Fields
If thingField.Name = pThingFieldName Then
thingSize = thingField.DefinedSize
Exit For
End If
Next thingField
End With
Set thingRecordset = mydb.OpenRecordset(pThingTableName)
thingRecordset.AddNew
thingRecordset(pThingFieldName) = Mid$(pNewThingName, 1, thingSize)
thingRecordset.Update

thingRecordset.Move 0, thingRecordset.LastModified ' Go to new record

thingRecordset.Close
mydb.Close

pResponse = DATA_ERRADDED ' Access will add the data to the combo box
Else
pResponse = DATA_ERRCONTINUE ' User clicked "No" - he does not want
to add the Thing
End If

End Sub
==========================================
Click to expand...
Click to expand...
 
Brendan Reynolds (MVP) said:
Try changing the declaration of the thingField variable to Dim thingField As
DAO.Field, as you've already done with the declaration of the Recordset
object. As with 'Recordset', both the DAO and ADODB object libraries include
classes named 'Field'.

There's an alternative example of how to add an item to a combo box list in
the 'Forms' section at www.mvps.org/access
Click to expand...

Thanks! That website is a great resource. I'm bookmarking it.

alice
 
Back
Top