Newdata Causes Problem

  • Thread starter Thread starter Merwin12
  • Start date Start date
M

Merwin12

Hi all:

I have this code for a combo box when a new entry in noton
a list:
Dim DB As Database
Dim rs As Recordset
Dim msg As String
Dim CR As String
CR = Chr(13)

'Exit if cboWTClocation is cleared
If NewData = "" Then Exit Sub
'Confirm
msg = " ' " & NewData & "' is not on the Training
Location list." & CR
msg = msg & "@ Do you want to add it?@"

If MsgBox(msg, vbQuestion + vbYesNo) = vbNo Then
Response = acDataErrContinue
MsgBox "Please try again......", vbInformation, "IPMS -
WTC Survey"

Else
'user wants to add a new record
Set DB = DAO.DBEngine.Workspaces(0).Databases(0)
Set rs = DB.OpenRecordset("TlkpWTCTrainLocation",
dbOpenDynaset, dbSeeChanges)
On Error Resume Next
'Add a new record
rs.AddNew
rs![Train_loc_desc] = NewData
rs.Update

If Err Then
Response = acDataErrContinue
MsgBox Error$ & CR & CR & "Please try again.",
vbExclamation, "IPMS - WTC Survey"
Else
Response = acDataErrAdded
End If
End If
End Sub

on the after update event:
Me.Refresh
Me.requery

Problem:
After the update for the combo box, it goes from the
current record to the very first record if a user is
either adding to the list on the combo box or just editing
a record.
Please advise on how I can resolve this problem.
 
The line:
Me.Requery
causes Access to reload the form. Naturally enough, you will find the form
at the first record after that.

Is there really a need to requery the form?

If so, you must save the primary key value of the current record before
requerying the form, and the FindFirst in the RecordSourceClone of the form.
 
Response = acDataErrAdded


If you're talking about using the NotInList event (which
is the only spot I can think of where NewData is passed as
an argument), then if you added the entry, set the
Response to acDataErrAdded.

Response = acDataErrAdded tells MS Access that you added
an entry to the controlsource of the combobox. MS Access
automatically will requery the control and attempt to re-
select the entry that was keyed.

Response = acDataErrContinue tells MS Access to ignore the
error (but of course can't allow the record to be saved),
as it assumes you told the brain surgeon using your
application that it doesn't exist in the list.

--------------------

I DO NOT RECOMMEND PERFORMING ADMINISTRATION THE WAY YOU
HAVE IT LISTED.

I recommend centralizing administration components that
support the following:

1) Relational Delete And Replace
2) On Cascade Update Null
3) On Cascade Delete
4) Dynamic on-the-fly table administration effected by
double-clicking a drop-down list (where options 1-3 are
availlable)
5) Not-In-List handling for simple look-up tables.

I have a version for both MS Access and SQL Server 7/2000
that perform these functions.

If you are interested in this, email me at
(e-mail address removed), and I'll send you the appropriate
version.


David Atkins, MCP
 
GreySky picked up that you were using acDataErrAdded instead of
acDataErrAdded.

That should solve the issue without a requery/refresh.


--
Allen Browne - Microsoft MVP. Perth, Western Australia.


Merwin12 said:
I took out the Me.Requery line (leaving just Me.Refresh)
since I really don't need to requery it but now the
problem is - if I created a new record or edit an existing
one and added a new item on the combo list - the record
moves from the current one to a new blank record or to the
next record if I'm just editing.
Scrolling back to the record that I want to work with,
I did not lose the data but I don't want it acting this
way. Please help!!!!!!!


-----Original Message-----
The line:
Me.Requery
causes Access to reload the form. Naturally enough, you will find the form
at the first record after that.

Is there really a need to requery the form?

If so, you must save the primary key value of the current record before
requerying the form, and the FindFirst in the RecordSourceClone of the form.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.


Merwin12 said:
Hi all:

I have this code for a combo box when a new entry in noton
a list:
Dim DB As Database
Dim rs As Recordset
Dim msg As String
Dim CR As String
CR = Chr(13)

'Exit if cboWTClocation is cleared
If NewData = "" Then Exit Sub
'Confirm
msg = " ' " & NewData & "' is not on the Training
Location list." & CR
msg = msg & "@ Do you want to add it?@"

If MsgBox(msg, vbQuestion + vbYesNo) = vbNo Then
Response = acDataErrContinue
MsgBox "Please try again......", vbInformation, "IPMS -
WTC Survey"

Else
'user wants to add a new record
Set DB = DAO.DBEngine.Workspaces(0).Databases(0)
Set rs = DB.OpenRecordset("TlkpWTCTrainLocation",
dbOpenDynaset, dbSeeChanges)
On Error Resume Next
'Add a new record
rs.AddNew
rs![Train_loc_desc] = NewData
rs.Update

If Err Then
Response = acDataErrContinue
MsgBox Error$ & CR & CR & "Please try again.",
vbExclamation, "IPMS - WTC Survey"
Else
Response = acDataErrAdded
End If
End If
End Sub

on the after update event:
Me.Refresh
Me.requery

Problem:
After the update for the combo box, it goes from the
current record to the very first record if a user is
either adding to the list on the combo box or just editing
a record.
Please advise on how I can resolve this problem.
Click to expand...


.
Click to expand...
Click to expand...
 
Don't use Me.Requery. I suspect you really wanted to use
Me.cboWTlocation.requery
 
Back
Top