[AccessD] Not In List Event

Rocky:  I'd add a .Undo to the Me.cboControl for the 'Don't add' user selection and use the same acDataErrContinue.  Other than that, not much changes except you lookup the value stored in the table by reading the value from the recordset after adding it.  You could probably also use the Me.fldBaitPercent = Format(NewData, "0.0") instead of reading it from the recordset as I've typed below or Dim dblNewData as Double and then add a line dblNewData = NewData and set the combo to dblNewData.  Any one of these approaches should work, and will work better yet if you are not adding a new record and simply adding a value in the 2nd, 3rd or 4th combo for a record that didn't have the newly entered value in existing records.  The gimick is to add the new value to the table, cancel the error for not matching the format and then set the combo to the value actually added in the format it will appear.  I'd put in a bit of error handling for null, non-numeric and out of range values because users always key bizarre characters in numeric fields.
 
intReply = MsgBox("The value " & NewData & " is not in the " &
Me.fldBaitPercent.Tag _
& " percent list. Do you want to add it?", vbYesNo)
If intReply = vbNo Then
  me.fldBaitPercent.Undo
  Response = acDataErrContinue
Else
Set db = CurrentDb
Set rs = db.OpenRecordset("Select * FROM tblPercents")
rs.AddNew
Dim dblNewVal As Double
rs!fldPercentType = Me.fldBaitPercent.Tag
rs!fldPercentValue = NewData
dblNewVal = rs!fldPercentValue
rs.Update
rs.Close
Set rs = Nothing
Me.fldBaitPercent = dblNewVal
Response = acDataErrContinue
End If


Ciao Jürgen Welz Edmonton, Alberta jwelz at hotmail.com


 
> From: rockysmolin at bchacc.com
> To: accessd at databaseadvisors.com
> Date: Mon, 1 Nov 2010 18:17:34 -0700
> Subject: Re: [AccessD] Not In List Event
> 
> Jürgen:
> 
> No PK this time. There are four of these fields in the table where the user
> wants to pick values from a combo box. So there will be eventually four of
> these combo boxes, each for a different field. The bound column is actually
> the second column. But I'm not sure where to put the DLast function - after
> the Response = acDataErrAdded statement?
> 
> TIA
> 
> Rocky
> 
> 
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jurgen Welz
> Sent: Monday, November 01, 2010 5:44 PM
> To: accessd at databaseadvisors.com
> Subject: Re: [AccessD] Not In List Event
> 
> 
> If you use a PK in column(0) of the combo and bind to that column, you can
> set the value to the PK of the added record and use the dataerrcontinue
> parameter of the notinlist event after requery of the combo. That way a .4
> will show up as 0.4 after the record append. If no PK in column(0), you can
> probably use the domain aggregate DLast function to look it up and set the
> combo to that value after cancelling the error.
> 
> Ciao Jürgen Welz Edmonton, Alberta jwelz at hotmail.com
> 
> 
> 
> From: rockysmolin at bchacc.com
> To: accessd at databaseadvisors.com
> Date: Mon, 1 Nov 2010 17:22:00 -0700
> Subject: [AccessD] Not In List Event
> 
> Dear List:
> 
> I am trying to implement a NotInList event on a bound combo box. The new
> value is added OK to the table that the query that is the Row Source uses to
> populate the bound combo box.
> 
> But after it adds the value I still get the standard message as if the
> NotInList event didn't fire ("The text you entered isn't an item in the
> list...") fldPercentValue is a number - double precision and is stored as a
> decimal fraction. The format of the combo box is Percent. 
> 
> Here's the code. But it's working OK.
> 
> intReply = MsgBox("The value " & NewData & " is not in the " &
> Me.fldBaitPercent.Tag _
> & " percent list. Do you want to add it?", vbYesNo)
> If intReply = vbNo Then
> Response = acDataErrContinue
> Else
> Set db = CurrentDb
> Set rs = db.OpenRecordset("Select * FROM tblPercents")
> rs.AddNew
> rs!fldPercentType = Me.fldBaitPercent.Tag
> rs!fldPercentValue = NewData
> rs.Update
> rs.Close
> Set rs = Nothing
> Response = acDataErrAdded
> End If
> 
> 
> The values for fldPercentValue added to the table look like 0.1, 0.2, etc.
> They have the leading zero. If I key in a new value with the leading zero
> like 0.4, it works OK. But if I try to add .4 it barfs. If it was just me,
> I'd always add the 0 but the user won't like it. They'll want to enter .4
> without the leading zero.
> 
> How can I finesse this one?
> 
> MTIA,
> 
> Rocky Smolin
> 
> Beach Access Software
> 
> 858-259-4334
> 
> Skype: rocky.smolin
> 
> www.e-z-mrp.com <http://www.e-z-mrp.com/> 
> 
> www.bchacc.com <http://www.bchacc.com/>