cont'd. How to save a user response,

[kjy]

Ok, so I got the combo box I was working on to accept new values. When the user types in an entry not in the original table, I can prompt the user to see if he/she wants to add this entry to the list, and if he/she says yes, a box pops up and asks them to enter the entry again. Now, I was wondering, is there a way to make it so that the user only has to type in the word once, and have it save and automatically update the table or is this the only way?

 

[grnzbra]

set the input textbox in the data input for to the text property of the combo box.

NewInputField = Forms!OriginalForm!ComboBox.text

 

[kjy]

Where would it go in this structure?

If intReply = vbYes Then
DoCmd.OpenForm "PackManuf", , , , acFormAdd, acDialog, NewData
Response = acDataErrAdded
Else
MsgBox "Please Select a Pack Manufacturer from the List!", vbExclamation, "Error"
Response = acDataErrContinue
End If

 

[nabiS2K]

Try this after you Dim rst as a recordest:

If intReply = vbYes Then
        Response = acDataErrAdded
        Set rst = CurrentDb.CreateQueryDef("","SELECT * FROM MyTable;").OpenRecordSet
        rst.Addnew
        rst("MyFieldName") = NewData
        rst.Update
    Else
        MsgBox "Please Select a Pack Manufacturer from the List!", vbExclamation, "Error"
        Response = acDataErrContinue
    End If

God Bless
Mike ;-)

 

[nberryman]

This is one I used some time ago. It opens a form that allows the entry to be edited if there is more than appears on the Combo box

In the NotInList event of the combo box:

Private Sub cboSiteIDAll_NotInList(NewData As String, Response As Integer)
Dim cbo As ComboBox, strMsg As String
Set cbo = cboSiteIDAll
strMsg = "This site is not in the list. Would you like to add it?"
If MsgBox(strMsg, vbOKCancel) = vbOK Then
Response = acDataErrAdded
DoCmd.OpenForm "Frm_Sites", , , , acFormAdd, acDialog, NewData

Else
cbo.Undo
Response = acDataErrContinue
End If
End Sub

In the On Load event of the form that is being opened use the following
Private Sub Form_Load()
Dim VarX As String
With Me
.NavigationButtons = False
.RecordSelectors = False
!St_Description = Me.OpenArgs
End With
VarX = DLookup("[CL_Client_ID]", "Tbl_Clients", "[CL_Client_ID]= '" _
& Forms![Frm_Movements_Single]![cboMov_Client_ID] & "'&quot
Me.St_Client_ID = VarX
End Sub


Delete out the VarX bit, as you can see it fills in a client ID in another
Text Box
Neil Berryman
IT Trainer
neil_berryman@btopenworld.com

 

[kjy]

Hey Nabi, where your code uses "MyFieldName" where am I supposed to find that name in my program? Thanks for your help

 

[nabiS2K]

My code is just a example.

MyFieldName is the name of the field in your table in which combo box text will be added. MyTable is the name of the table itself. You could use the code if you change these two items. God Bless
Mike ;-)

 

[krsherm]

nabiS2k,

If I may, I think you would be better off with using SQL rather than a recordset... I've taken the liberty of modifying your code:

Dim strSQL as String

If intReply = vbYes Then
Response = acDataErrAdded
strSQL = "INSERT INTO MyTable(MyFieldName) " _
& "SELECT " & NewData
CurrentDb.Execute(strSQL)

Else
MsgBox "Please Select a Pack Manufacturer from the List!", vbExclamation, "Error"
Response = acDataErrContinue
End If