Add new records for Combo Box

[Stapes]

Hi

I have a combo box with 3 columns that I want to allow the user to add
entries to. I tried adding an OnNotInList procedure, but it seems that
it processes my AfterUpdate procedure first.
I couldn't see how to add 3 columns of data here, so instead I open a
new form to allow the user to add the new record.

1. my calling procedure seems to carry on running. I was expecting it
to stop processing until the new form closed. This causes it to loop.
2. After adding a new record using the new form, I return and rerun my
query to find it, but it can't find the new record.

Here is my procedure: -

Private Sub Combo20_PartID_AfterUpdate()
On Error GoTo errCombo20_PartID_AfterUpdate
' set TXT_PartDesc and CUR_ItemPrice to values from TM_Parts depending
on PK_Part selected
Dim db As Database, qd As QueryDef, rs As Recordset, strSQL As String
Dim flag As Boolean
flag = False
Set db = CurrentDb

Set qd = db.QueryDefs("qryParts")
query_again:
qd.Parameters("pCO").Value = Me!Combo20_PartID
Set rs = qd.OpenRecordset
If rs.EOF And rs.BOF Then
If flag = True Then
GoTo errtrap
End If
Call NewPart
flag = True
GoTo query_again
Else
Me.TXT_PartDesc = rs!TXT_Detail
Me.CUR_ItemPrice = rs!CUR_Price
End If
Exit Sub
errtrap:
MsgBox "Loop"
Exit Sub
errCombo20_PartID_AfterUpdate:
MsgBox Err.Number & " " & Err.Description

End Sub

Private Sub NewPart()
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "TM_Parts"

stLinkCriteria = ""
DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormAdd
End Sub

Stapes

 

[Ron2006]

Hi

I have a combo box with 3 columns that I want to allow the user to add
entries to. I tried adding an OnNotInList procedure, but it seems that
it processes my AfterUpdate procedure first.
I couldn't see how to add 3 columns of data here, so instead I open a
new form to allow the user to add the new record.

1. my calling procedure seems to carry on running. I was expecting it
to stop processing until the new form closed. This causes it to loop.
2. After adding a new record using the new form, I return and rerun my
query to find it, but it can't find the new record.

Here is my procedure: -

Private Sub Combo20_PartID_AfterUpdate()
On Error GoTo errCombo20_PartID_AfterUpdate
' set TXT_PartDesc and CUR_ItemPrice to values from TM_Parts depending
on PK_Part selected
Dim db As Database, qd As QueryDef, rs As Recordset, strSQL As String
Dim flag As Boolean
flag = False
Set db = CurrentDb

Set qd = db.QueryDefs("qryParts")
query_again:
qd.Parameters("pCO").Value = Me!Combo20_PartID
Set rs = qd.OpenRecordset
If rs.EOF And rs.BOF Then
If flag = True Then
GoTo errtrap
End If
Call NewPart
flag = True
GoTo query_again
Else
Me.TXT_PartDesc = rs!TXT_Detail
Me.CUR_ItemPrice = rs!CUR_Price
End If
Exit Sub
errtrap:
MsgBox "Loop"
Exit Sub
errCombo20_PartID_AfterUpdate:
MsgBox Err.Number & " " & Err.Description

End Sub

Private Sub NewPart()
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "TM_Parts"

stLinkCriteria = ""
DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormAdd
End Sub

Stapes

Open the form as acDialogue

All logic stops until the new form is closed.

Whether this will fit in with your overall logic will have to be
determined, but that is how to stop the logic for the form.

Ron

 

[Guest]

As Ron said use the acDialog constant as the WindowMode argument of the
Openform method. You can take things further however and pass the new value
to the form as its OpenArgs property. Here's some code which does this when
adding a new city via a combo box:

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

Dim ctrl As Control
Dim strMessage As String

Set ctrl = Me.ActiveControl
strMessage = "Add " & NewData & " to list?"

If MsgBox(strMessage, vbYesNo + vbQuestion) = vbYes Then
DoCmd.OpenForm "frmCities", _
DataMode:=acFormAdd, _
WindowMode:=acDialog, _
OpenArgs:=NewData
' ensure frmCities closed
DoCmd.Close acForm, "frmCities"
' ensure city has been added
If Not IsNull(DLookup("CityID", "Cities", "City = """ & _
NewData & """")) Then
Response = acDataErrAdded
Else
strMessage = NewData & " was not added to Cities table."
MsgBox strMessage, vbInformation, "Warning"
Response = acDataErrContinue
ctrl.Undo
End If
Else
Response = acDataErrContinue
ctrl.Undo
End If

End Sub

In the frmCities form's Open event procedure the DefaultValue property of
the City control is then set to the new value with:

Private Sub Form_Open(Cancel As Integer)

If Not IsNull(Me.OpenArgs) Then
Me.City.DefaultValue = """" & Me.OpenArgs & """"
End If

End Sub

Note that when setting the DefaultValue property the value should always be
wrapped in literal quotes regardless of the data type.

Ken Sheridan
Stafford, England