Adding names to a list from a combo box

[marc]

I have a couple fields that are combo boxes. Is there a
way that I can add names to the list by just typing in the
name in the field and having automatically add it's self
to box.

Thanks

 

[Ken Snell]

See the ACCESS Web for info on this:

http://www.mvps.org/access/forms/frm0015.htm

 

[Guest]

Ken
I am new to this stuff and I am having some problems with
this. The field I am working out of is "Source". The
table I am working from for this field is "tblsource". My
database name is "Copy of Action_items". I don't need a
message box to pop up with it.
can you help.

Private Sub Source_NotInList(NewData As String, Response
As Integer)
Dim db As dAO.database
Dim rs As DAO.Recordset
Dim strMsg As String

strMsg = "'" & NewData & "is not an available AE Name"
& vbCrLf & vbCrLf
strMsg = strMsg & "Do you want to associate the new
Name to the current 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("tblsource",
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

rs.Close
Set rs = Nothing
Set db = Nothing
End Sub

 

[marc]

I forgot to add one other thing. After I typed it all in
I get an error message telling me that

Complie error
user-defined type not defined.
How do I fix this problem.

Thanks for the help

 

[Ken Snell]

Your description sounds as if you're trying to use this code in a table?
It's designed to be used in a form, as you cannot run code in an ACCESS
table.

Are you using lookup fields in the table, and you want to add new values
directly in the table?

Please provide a bit more info about how you're wanting to add new
values...what steps are you doing (what object do you open, etc.).

 

[marc]

Ken,
I am trying to place it into a form. I want the
person who is inputting into the form to be allowed to
place a name into the field. If that name is not in the
drop down list then i would like for them to be able to
have it automatically have it added in. The fields are
linked to a table which is where the information for the
list comes from.

 

[Ken Snell]

OK - let's be sure you've put the code in the correct place.

Open the form in design view. Click on the Source combo box. Open the
Properties window (icon on toolbar), and click on Event tab.

Go to the box next to Not In List. Delete anything that may be in that box
so that the box is empty. Click in the box. Click on the three-dot box at
far right of the box. Select Code Builder from the popup window.

You'll see two lines of code in the Visual Basic Editor (VBE):

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

End Sub

with the cursor on the blank line between the two.

Paste the code from the ACCESS Web site between these two lines (don't
repeat the First and Last lines of code).

Close the VBE.

In the Properties window, click on Data tab. Change the box next to Limit To
List to Yes.

Save and close the form.

You now should have what you want to work.

If you don't want the message box prompt, then delete this line of code:
If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new name?") = vbNo Then

and delete the "End If" just above the "rs.Close" step.

 

[marc]

I get an error message. It reads:
Compile Error
User-defined type not defined

I click on help and it says that I need to do type....End
Type code. I have never used such a thing. How and where
do I do this?

 

[Ken Snell]

I'm not sure I'm understanding the error message that you see, but likely
you need to set a reference to the DAO library.

Open Visual Basic Editor, click on Tools | References, and then select the
Microsoft Data Access Objects library (version 3.xx). Close the references
window.

Try it again.

 

[Guest]

First and fore most. Thanks for the help on that last
problem. I think I am close but I have some other errors
that I don't know what to do with.
Now I get 2 different errors.
1) when I hit yes I get:
run time error '424':
Object required
When I select debug it highlights the following line.
Set db = CurrentDb
Does the CurrentDb mean that I place the title of my
database there?

2) When I hit no I get:
run time error '91'
object variable or with block variable not set
When I select debug it highlights the following line.
rs.Close

 

[Guest]

I fixed problem 1 on the other reply but I still can not
figure out #2.

 

[Ken Snell]

Your code needs a slight tweak...the rs.Close statement should be inside of
the first End If block.

Private Sub Source_NotInList(NewData As String, Response
As Integer)
Dim db As dAO.database
Dim rs As DAO.Recordset
Dim strMsg As String

strMsg = "'" & NewData & "is not an available AE Name"
& vbCrLf & vbCrLf
strMsg = strMsg & "Do you want to associate the new
Name to the current 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("tblsource",
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

rs.Close
Set rs = Nothing
Set db = Nothing
End If

 

[Guest]

Thanks for all of your help. It worked perfectly.

-----Original Message-----
Your code needs a slight tweak...the rs.Close statement should be inside of
the first End If block.

Private Sub Source_NotInList(NewData As String, Response
As Integer)
Dim db As dAO.database
Dim rs As DAO.Recordset
Dim strMsg As String

strMsg = "'" & NewData & "is not an available AE Name"
& vbCrLf & vbCrLf
strMsg = strMsg & "Do you want to associate the new
Name to the current 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("tblsource",
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

rs.Close
Set rs = Nothing
Set db = Nothing
End If

--
Ken Snell
<MS ACCESS MVP>




.

 

[Ken Snell]

Glad to hear it. You're welcome.