Problem adding to combo box list (1 Viewer)

bcomb · Jan 20, 2017

Hi, all

I'm a relative newbie with Access, only about 4 months.

I'm having a problem with the Not_In_List event for a combo box. I need to be able to add new items here and it is not working.

The code I'm using is as follows:

Code:

[SIZE=3][FONT=Calibri]Private Sub cboClause_NotInList(NewData As String, Response As Integer)
[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]Dim intAnswer As Integer[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]Dim strsql As String[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]Dim strP As String[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]Dim strQ As String[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]Dim strR As String[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]Dim strS As String[/FONT][/SIZE]
 
[SIZE=3][FONT=Calibri]intAnswer = MsgBox("The Clause Code, " & Chr(34) & NewData & _[/FONT][/SIZE]
 [FONT=Calibri][SIZE=3]Chr(34) & ", is not currently listed." & vbCrLf & _[/SIZE][/FONT]
 [FONT=Calibri][SIZE=3]"Would you like to add it to the list now?" _[/SIZE][/FONT]
 [FONT=Calibri][SIZE=3], vbQuestion + vbYesNo, "Add Clause Code")[/SIZE][/FONT]
[SIZE=3][FONT=Calibri]If intAnswer = vbYes Then[/FONT][/SIZE]
 
[FONT=Calibri][SIZE=3]strP = NewData + " - P Not applicable to Planning"[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]strQ = NewData + " - Q Not applicable to Quality"[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]strR = NewData + " - R Not applicable to Contract Review"[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]strS = NewData + " - S Not applicable to Shipping + Receiving"[/SIZE][/FONT]
 
[FONT=Calibri][SIZE=3]strsql = "INSERT INTO tblClause([ClauseCode],[ClauseP],[ClauseQ],[ClauseR],[ClauseS],[ClauseType]) " & _[/SIZE][/FONT]
  [FONT=Calibri]            "[/FONT][FONT=Calibri][SIZE=3]VALUES ('" & NewData & "','" & strP & "','" & strQ & "','" & strR & "','" & strS & "','" & strType & "');"[/SIZE][/FONT]
 
[FONT=Calibri][SIZE=3]DoCmd.SetWarnings False[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]DoCmd.RunSQL strsql[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]DoCmd.SetWarnings True[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]Response = acDataErrAdded[/SIZE][/FONT]
 
[FONT=Calibri][SIZE=3]MsgBox "The new Clause Code has been added to the list." _[/SIZE][/FONT]
[FONT=Calibri][SIZE=3], vbInformation, "Customer Added"[/SIZE][/FONT]
[SIZE=3][FONT=Calibri]Else[/FONT][/SIZE]
 [FONT=Calibri]    [SIZE=3]MsgBox "Please choose a Clause Code from the list." _[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]         , vbInformation, "Continue?"[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]Response = acDataErrContinue[/SIZE][/FONT]
[SIZE=3][FONT=Calibri]End If[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]cboCustomer_NotInList_Exit:[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]Exit Sub[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]cboCustomer_NotInList_Err:[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]MsgBox Err.Description, vbCritical, "Error"[/FONT][/SIZE]
[FONT=Calibri][SIZE=3]Resume cboCustomer_NotInList_Exit[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]End Sub[/SIZE][/FONT]

The problem is that the new record is added, but after adding, the new field values displayed by the form are from the first record in the query. This is confusing to users.

The combo box refreshes properly, but I cannot display the new record without closing the form and reopening it. When I tried adding in code for this,

Code:

DoCmd.Close acForm, Me.Name
DoCmd.OpenForm "frmClauseEntry", acNormal

it puts in a loop, adding multiple records, and I get an error after the loop:

Run-time error '2501':
The Close action was cancelled.

What am I doing wrong?

Using Access 2016
Form Record Source is a query
Recordset Type is Dynaset
Combo box is unbound
Row Source is a query
Row Source type is Table/Query

Thanks for any help you can provide.

sneuberg · Jan 21, 2017

I don't see how this code is affecting what record is displayed in the form. Could you upload you database so that we could see what's going on.

bcomb · Jan 23, 2017

sneuberg,

Thanks for taking a look. I have attached a copy of the DB in question.

The form is frmClauseEntry, but there is some data passed into it from frmClauseReview.

Sometimes it will add the new Clause record properly, but sometimes it goes into a loop and adds multiple records, all with the same name. I also am not able to get it to display the new record without exiting the form and reopening it.

Thanks again for any help you can provide.

static · Jan 24, 2017

You appear to have normalisation and indexing issues.

I don't see a loop that could add multiple records but ClauseCode + ClauseType should be primary keys on tblClause to prevent any duplicates.

You've created a field for each subtype (P,Q,R,S)
- you've then used union queries to split them into rows. Not only is this inefficient, it also means you had to create another screen to allow the data to be edited.
- Each of these subtypes is also a memo field. You should do everything you can to avoid memo fields because they are the likeliest thing to become corrupt in a multi user database.
If any field (of any type) becomes corrupt you lose the entire record. By splitting the worst offenders off into their own (normalised) tables you would only lose one field's worth of data.

bcomb · Jan 24, 2017

Static,

Thanks for your comments. I will look into making your suggested changes.

Unfortunately, the P, Q, R, and S subtypes need to be memo fields. They are detailed instructions pertaining to manufacturing products. However, I may be able to move them into their own table to reduce the risk.

Thanks again for your comments.

Problem adding to combo box list (1 Viewer)

bcomb

New member

sneuberg

AWF VIP

bcomb

New member

Attachments

static

Registered User.

bcomb

New member

Similar threads

Users who are viewing this thread