Set object values on form when new item entered in combo box

[ritco]

Using Access 2007. I have an unbound form with 3 objects, 1 combo box and two text boxes.
The combo box uses a query as its row source. It grabs three columns from the query (Item, Lot#, and DateChanged). It has an “After Update” procedure that populates the two text boxes with the Lot# and DateChanged.
Private Sub cboItem_AfterUpdate()
Me.txtCurLot.Value = Me.cboItem.Column(1)
Me.txtDOLC.Value = Me.cboItem.Column(2)
End Sub

This all works fine if the item exists in the list. If the user starts typing in an item that doesn’t exist, I would like them to be able to type the “new item” in the combo box and then have the date text box default to current date and convert the “new item” to all upper case automatically after the new item is keyed in. I have tried the following code in the “On Not in List” event for the combo box but it doesn’t work (no errors, just doesn’t do anything)…

Private Sub cboItem_NotInList(NewData As String, Response As Integer)
Response = MsgBox("This item does not exist. Do you want to continue to add it?", vbYesNo, "NewItem")
If Response = vbYes Then
Me.txtDOLC.Value = Now()
Me.cboItem.Value = UCase$(NewData)
'Response = acDataErrAdded
End If
End Sub

There is an “Add Record” button on the form that will take care of writing the new record to the SQL table from the “On Click” event. I just want to be able to manipulate the combo and text boxes if the item doesn't already exist.

 

[sparks80]

Hi,

There doesn't appear to be a problem with your code.

What have you set for the "Limit to list" property?
Make sure this is set to true, because the "Not in list" event will only fire when this is the case.

 

[ritco]

Thanks, that worked. The "Limit to List" property was set to No. I changed it to Yes and that fired the event.
The only problem is I get an additional Access message complaining that the text I entered isn't an item in the list and to select an item from the list or enter text that matches an item in the list...I click OK and it focuses back on the combo box and opens it up like it's waiting for a selection. I can tab through it again but this may be confusing to an end user. I tried setting the "Allow Value List Edits" to Yes but it still gave me the same message. Is there a way to get Access not to complain about that?

 

[sparks80]

Hi,

Please note: code below is untested!

The problem you are having is that you need to add the new value to the table that underlies your combobox.

One possible solution is to use an append query to add the desired value into the table.

Save this query as "qryAddNewValue" and replace the LookupTableName and LookupFieldName values with the name of your own table and field. Leave the [NewValue] part exactly as it is - this allows you to pass the new value from your combobox as a parameter.

INSERT INTO LookupTableName ( LookupFieldName ) SELECT [NewValue];

I have just been learning about the NotInList event, and I think this code should prompt the user if they wish to add the new value. If yes then the record is added, if not (or the record already exists) then a message will be displayed.

By setting response = acDataErrContinue the default access error message will be suppressed. By setting response = acDataErrAdded you are informing the combobox that the new value has been entered into the underlying table or query successfully.

Private Sub [/SIZE][/FONT][FONT=Arial][SIZE=3]cboItem_NotInList(NewData As String, Response As Integer)
    Dim qdf As QueryDef
    Dim Response As Integer

[/SIZE][/FONT][FONT=Arial][SIZE=3]    On Error GoTo Error[/SIZE][/FONT][FONT=Arial][SIZE=3]cboItem_NotInList[/SIZE][/FONT][FONT=Arial][SIZE=3]
[/SIZE][/FONT] [FONT=Arial][SIZE=3]    
[/SIZE][/FONT][FONT=Arial][SIZE=3]    'Suppress default error message.[/SIZE][/FONT]
         [FONT=Arial][SIZE=3]    Response = acDataErrContinue[/SIZE][/FONT]
         [FONT=Arial][SIZE=3]    
[/SIZE][/FONT]
[FONT=Arial][SIZE=3]   If vbYes = MsgBox("This item does not exist. Do you want to add it?", vbYesNo, "New Item") Then
        Set qdf = CurrentDb.QueryDefs("[/SIZE][/FONT][FONT=Arial][SIZE=3]qryAddNewValue[/SIZE][/FONT][FONT=Arial][SIZE=3]")
        qdf.Parameters(0) = UCase(NewData)
        qdf.Execute dbFailOnError

[/SIZE][/FONT][FONT=Arial][SIZE=3]        'Inform the combo box that the desired item has been added to the list.
                 Response = acDataErrAdded
    Else
        MsgBox "The value has not been added"
    End If

[/SIZE][/FONT][FONT=Arial][SIZE=3]Exit[/SIZE][/FONT][FONT=Arial][SIZE=3]cboItem_NotInList[/SIZE][/FONT][FONT=Arial][SIZE=3]:
    Set qdf = Nothing
    Exit Sub
Error[/SIZE][/FONT][FONT=Arial][SIZE=3]cboItem_NotInList[/SIZE][/FONT][FONT=Arial][SIZE=3]:
    
    If Err.Number = 3022 Then
        MsgBox "The value you have entered already exists"
    Else
        MsgBox "An error has occurred" & vbCrLf & "Number: " & Err.Number & vbCrLf & _
            "Description: " & Err.Description
    End If
    GoTo Exit[/SIZE][/FONT][FONT=Arial][SIZE=3]cboItem_NotInList[/SIZE][/FONT][FONT=Arial][SIZE=3]
[/SIZE][/FONT][FONT=Arial][SIZE=3]End Sub
[/SIZE][/FONT]

 

[ritco]

Thanks, that makes sense. I think where I'm complicating this is that I don't want to just write back the item (which is the new value) to the look up table but also the new lot number and date.
I guess I'm trying to use the same form for an Add, Change, or Delete record for the same table that my combo box uses to lookup. If the item exists in the drop down, then the existing lot number can be "changed" and record can be updated. If it doesn't exist in the list then the user can type a new item in the combo box, tab to the blank lot number text box and type in the lot number and the date field will already be populated with the current date, then the user clicks an "Add Record" button and the record is added to the table. With what you provided I thought I might be able to make this work but the table I'm trying to udate is a linked SQL table and I can't seem to get the code right to write to a SQL table. I can do it with an Access table, no problem, but not SQL.

 

[sparks80]

What is the error you are receiving when trying to update your table?

The example I gave for updating the table uses DAO, perhaps ADO would be more successful.

Also it might help if you could post a sample of the code where you are getting the error. Without a bit more information it is hard to determine what is causing the problem.

 

[ritco]

I didn't get any errors, just clicked the button and nothing happened. When I went into debugging mode, I put a breakpoint at "If myMsg = 1 Then" line and when I stepped into it a Macros window opened up (something I've never seen before). That's all that would happen when I tried to step through, that Macros window would keep opening.

I deleted most of the code and started again with what I tried originally and this worked:

Private Sub cboAdd_Click()
Dim myMsg As Integer

Dim db As Database

Set db = CurrentDb

myMsg = MsgBox("Do you want to add this item to the database?", vbOKCancel, "AddItem")

On Error GoTo errmsg '*****************************************************************
If myMsg = 1 Then

Set rsTable = db.OpenRecordset("SCCurLotNum") 'This is the linked SQL table

With rsTable
rsTable.AddNew
rsTable!StkCode = Me.cboItem.Value
rsTable!LotNum = Me.txtCurLot.Value
rsTable!DateChanged = FormatDateTime(Me.txtDOLC.Value, vbShortDate)
rsTable.Update
End With

MsgBox ("Record Added")

rsTable.Close

Else
MsgBox ("User Cancelled")
Me.cboItem.SetFocus

End If
Exit Sub

errmsg:
If Err = 3022 Then

MsgBox ("Record already exists. Try clicking Change Item to update this item.")
Else
strErr = "Error#: " & Err.Number & " Error Description: " & Err.Description

MsgBox (strErr)
End If
End Sub

I'm not sure what my original problem was, maybe a sytax error somewhere but this seems to work. I'm going to try adding more records tomorrow to make sure it keeps working. I don't know if this is the most efficient way to add records through Access to SQL but it works for now.
Thanks for all your help!

 

[sparks80]

Hi,

You could create a new form bound to the table to add the additional records, and then you wouldn't require much code at all. You could have an "add" or "cancel" button at the bottom of the form.
The add button would just close the form once the user had made any changes
The cancel button would prevent the record from saving by setting Cancel = True in the BeforeUpdate Event of the form.

Just a thought, but I'm very glad you've got it working for now