Add data entered to combo box 3

[tj007]

Hi

I have created a table with the contact information.I have a form with a combo box name contact and there is a dropdown list with the names fro which the user can select. Sometimes a new contact is manually type in the form field. How do I populated the contact table with the new contact and keep the current contact list?

 

[SgtJarrow]

So.....

You have a combobox, set with the rowsource of some table. You have set the limittolist property and when the user tries to enter a value not in the list, you get an error. Using the notinlist event, place the following code:

    Dim rs As Recordset
    
    Set rs = New ADODB.Recordset
    
    With rs
        .Open "tablename", CurrentProject.Connection, adOpenKeyset, adLockOptimistic, adCmdTable
        .AddNew
        .Fields("fieldname") = NewData
        .Update
        .Close
    End With
    
    Response = acDataErrAdded

The above code basically opens a recordset to your table, adds the value the user has typed into the combobox, and then tells the combobox it added it, and allows you to continue.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+
w: rljohnso@stewart.com
h: wildmage@tampabay.rr.com

 

[mph1]

Look at the following:

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

HTH

An investment in knowledge always pays the best dividends.
by Benjamin Franklin
Autonumber Description - FAQ702-5106

 

[tj007]

Thanks mstrmage1768

It worked fine. How can I add a message to inform the user that value they have entered is not in the list "do you want to add the value?"

 

[TheAceMan1]

How are ya tj007 . . . .

In the [blue]On Not In List Event[/blue] of your combobox add the following code (be sure to set the [purple]Limit To List Property[/purple] to Yes!):

[blue]   Dim db As DAO.Database, rst As DAO.Recordset
   Dim Msg As String, Style As Integer, Title As String
   
   Msg = "Value is not in list. Add it?"
   Style = vbInformation + vbOKCancel
   Title = "Not In List Error"
   
   If MsgBox(Msg, Style, Title) = vbOK Then
      Set db = CurrentDb()
      Set rst = db.OpenRecordset("[purple][b]YourTableName?[/b][/purple]", dbOpenDynaset)
      
      With rst
         .AddNew
         ![purple][b]YourFieldName?[/b][/purple] = Me![purple][b]YourComboboxName?[/b][/purple].Text
         .Update
      End With

      Response = acDataErrAdded
   Else
      Me![purple][b]YourComboboxName?[/b][/purple].Undo
      Response = acDataErrContinue
   End If[/blue]

See Ya! . . . . . .

 

[tj007]

mstrmage1768 & TheAceMan1

Both replies are helpful and working fine. Sorry it took so long for me to answer. Thank both of you.

 

[kjv1611]

TheAceMan1,
I gave you a star there for that code. That idea/code was most helpful to me as well. I made some minor changes for style and such.

One question:
Do you (or anyone for that matter) know how that would be changed to use for multiple controls? I mean, what would be the format if I wanted to just use this as a module, then call the Sub or Function from each control in it's _NotInList() event? I would assume just having a variable for the table name, one for the field looked at in the particular table, and a one for the current control. As far as that part, I would imagine like this:

Dim strTable as String
Dim strField as String
Dim ctrl as Control

If that would work, I am not sure as to how to make the format work in the remainder of the code... any help here?

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[PHV]

Something like this ?
If MsgBox(Msg, Style, Title) = vbOK Then
Set db = CurrentDb()
Set rst = db.OpenRecordset(strTable, dbOpenDynaset)
With rst
.AddNew
rst(strField) = Me(strComboname).Text
.Update
End With
Response = acDataErrAdded
Else
Me(strComboname).Undo
Response = acDataErrContinue
End If
If you want a more general procedure, you have to replace the Me shorthand by the complete forms reference syntax, like this:
Forms(strFormname)(strComboname).Undo

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[TheAceMan1]

[blue]Do you (or anyone for that matter) know how that would be changed to use for multiple controls?[/blue]

I've always wanted to do that and havn't had a chance until now. The only hard part is Objectivity of the forms. If the user wants to cancel, the textbox portion should be nulled, and the combobox could be on a subform 3 levels deep!. Have to be able to detect just what subform level the combobox is on. When I get done I'll post back here.

See Ya! . . . . . .

 

[TheAceMan1]

To All . . . . .

Finished the Global routine for handling the [blue]NotInList[/blue] event. You can call it from anywhere in the database.

The routine prompts the user with a msgbox. Since I always like things [purple]lookin good[/purple], I always use the old 97 style (1st line bold) display format. If this is not perferred, you'll have to recode the msgbox portion & variables. So, in a module in the module window, add the following to the declarations section:

[blue]Public Msg As String, Style As Integer, Title As String
Public Const NL As String = vbNewLine
Public Const DL As String = NL & NL[/blue]

Add the following MsgBox handler routine to the same module(if ya like you can use it for all msgboxes. Just assign proper to the Msg, Style, & Title variables. Then call the uMsg function. It returns the same values as if you used the MsgBox function.):

[blue]Public Function uMsg() As Integer
   Beep
   uMsg = Eval("MsgBox(" & DQ & Msg & DQ & "," & Style & "," & DQ & Title & DQ & ")")
End Function[/blue]

Add the following AddToList routine to the same module ([purple]this is the global routine[/purple]):

[blue]Public Function AddToList(Data, tblName As String, fldName As String) As Boolean
   Dim db As DAO.Database, rst As DAO.Recordset
   
   Msg = "'" & Data & "' is not in the ComboBox List!" & _
         "@Click 'Yes' to add it." & _
         "@Click 'No' to abort."
   Style = vbInformation + vbYesNo
   Title = "Not In List Warning!"
   
   If uMsg() = vbYes Then
      Set db = CurrentDb()
      Set rst = db.OpenRecordset(tblName, dbOpenDynaset)
      
      rst.AddNew
         If rst(fldName).Type = dbText Then
            rst(fldName) = Data
         Else
            rst(fldName) = Val(Data)
         End If
      rst.Update
      
      AddToList = True
   End If
   
End Function[/blue]

Now . . . . in the [blue]NotInList[/blue] event of each combobox, add the following code. You have to prescribe names in [purple]purple[/purple]:

[blue]   [green]'tblName: the table name to add the NewData[/green]
   [green'fldName: the field name in the table that receives the NewData[/green]
   
   If AddToList(NewData, "[purple][b]tblName[/b][/purple]", "[purple][b]fldName[/b][/purple]") Then
      Response = acDataErrAdded
   Else
      Response = acDataErrContinue
   End If[/blue]

Thats it! Give it a whirl . . . . .

See Ya! . . . . . .

 

[kjv1611]

Hmm, I have it running (with a good bit of code on each control, but I'll try this to see how it runs and post back.

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[TheAceMan1]

To All . . . . .

Forgot to include [purple]DQ[/purple], required for the Eval function. The following line in [purple]purple[/purple] is added to the Declarations section:

[blue]Public Msg As String, Style As Integer, Title As String
Public Const NL As String = vbNewLine
Public Const DL As String = NL & NL
[purple][b]Public Const DQ As String = """"[/b][/purple][/blue]

See Ya! . . . . . .

 

[eburg]

TheAceMan1,
Re: your post of 5/22.
I have a cbx with 3 columns, ID, FN, and LN. ID is hidden (0 width). Can this be done to update both FN and LN?
Thanks in advance,
EB

 

[TheAceMan1]

How are ya eburg . . . . .

Yes it can be done! . . . . .

Change:
With rst
.AddNew
!YourFieldName? = Me!YourComboboxName?.Text
.Update
End With
To:
With rst
.AddNew
!FN_Name? = Me!YourComboboxName?.Column(2)
!LN_Name = Me!YourComboboxName?.Column(3)
.Update
End With

See Ya! . . . . . .

 

[TheAceMan1]

To All . . . . .

See faq702-5205

See Ya! . . . . . .

 

[PHV]

I guess TheAceMan meant:
!FN_Name? = Me!YourComboboxName?.Column([highlight]1[/highlight])
!LN_Name = Me!YourComboboxName?.Column([highlight]2[/highlight])
as Column is option base 0.


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[TheAceMan1]

Hay PHV . . . . .

Always a good thing to have ya stop by . . . .

[blue]To All:[/blue]

[purple]PHV[/purple] [blue]is full of good things![/blue]

See Ya! . . . . . .

 

[PHV]

Thanks Ace for the nice words.

 

[TheAceMan1]

PHV . . . . .

I was taught by my grandparents (before they all passed) to always honor those who are worthy. So . . . . . believe it!, I honor every post I see in your name . . . . . Sorry! . . . . can't help it!

See Ya! . . . . . .

 

[TheAceMan1]

PHV . . . . .

Very interested in a critigue from you here:

faq702-5205

See Ya! . . . . . .