Rocky Smolin
rockysmolin at bchacc.com
Tue Nov 2 00:02:51 CDT 2010
Jurgen:
I added the dblNewVal = rs!fldPercentValue and Me.fldBaitPercent = dblNewVal
where you indicated:
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
dblNewVal = rs!fldPercentValue
rs.Update
rs.Close
Set rs = Nothing
Me.fldBaitPercent = dblNewVal
Response = acDataErrAdded
End If
But it made no difference. If I enter a new value like 0.25 is works but if
I enter just .25 it fails.
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 7:31 PM
To: accessd at databaseadvisors.com
Subject: Re: [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/>
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com