How do I prompt the user for a value to insert into...

[lccrews]

Hi,


I'm using the below code currently to insert the value from the cbobox into tbl_BulkItems.[Item]. I'd like to know if there's a way to prompt the user to enter a description to insert into the same record's [descr] field. Basically the info in the cbobox automatically inserts, but I cannot figure out how to prompt the user for the description. Thanks for any help.

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

    strSQL = "INSERT INTO tbl_BulkItems([Item]) " & _
             "VALUES ('" & NewData & "');"
    DoCmd.SetWarnings False
    DoCmd.RunSQL strSQL
    DoCmd.SetWarnings True
    Response = acDataErrAdded


End Sub

[Micron]

You will have to modify the sql to include both fields, and trap for when the control for fld2 is not filled in correctly.
Maybe look at something like http://www.databasedev.co.uk/multi-f...t-in-list.html

[lccrews]

Thanks for the guidance Micron. I'm able to understand the code from databasedev.co but for some reason or another my edits cause the code to concatenate. The data is inserted into one field instead of 3. I'm still working on it, but if you see something in my version of the code that catches your attention, please let me know.

Private Sub RawMaterial_NotInList(NewData As String, Response As Integer)    
' This procedure allows us to create a new value, using the NotInList event
' It passes multiple field values to the table.


    Dim strSQL
    Dim NewData1 As String
    Dim NewData2 As String
    Dim SpacePosition As Integer
    Dim lngNextID As Long


    ' Find the highest Actor ID in the Actors table and add 1
    lngNextID = DMax("[BID]", "tbl_BulkItems") + 1
    NewID = lngNextID


    ' Find the space in the two fields
    ' to allow us to split the entry into two fields.
    SpacePosition = InStr(NewData, "-")


    ' Trim the data into first and last name using the space position.
    NewData1 = Trim(Left(NewData, SpacePosition - 1))
    NewData2 = Trim(Mid(NewData, SpacePosition + 1))


    ' If new entry includes First and Last Names create new Actor record
    ' if the new entry does not exist.
    strSQL = "Insert Into tbl_BulkItems ([BID], [Item], [Descr]) " & _
             "values ('" & NewID & "','" & NewData1 & _
             "','" & NewData2 & "');"
    CurrentDb.Execute strSQL, dbFailOnError
    Response = acDataErrAdded
    
End Sub

[Micron]

the example code was for concatenated fields, I think. You don't want to do that.
As noted, you need a spot for the 2nd value. Textbox?
You first validate that entry, which could mean one or several checks, such as being there at all, only being a number, whatever.
If it doesn't validate, you don't write. If it passes, your sql needs to be written to append to both fields.
If you're going to turn off warnings, use error trapping and ensure it gets turned back on after, otherwise you may find they stay off, or use the .Execute method to append the record.

[orange]

You may get some ideas from this free youtube video. Edit ListItems
It may not be directly what you're asking, but it is a feature you should be aware of.

Good luck.

[lccrews]

I got it working correctly. Thanks again Micron.

Orange, I was aware of this feature. Thank you for the tip!