Requery instruction doesn't refresh data in list box in AC2K

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

HI everyone,
I am facing a problem with ms access 2000. I have an unbound form name
"Employees" with a listbox showing 5 columns of information:
EmployeeID, FirstName, Last Name, Date of birth, Country of origin. The
listbox is based on the table "tblEmployees". On that form there is also a
button labeled "Entry" which opens up a form name "Employees data entry". The
form "Employees data entry" is unbound also. The data entered in the text
boxes are inserted in the table tblEmployees via a routine name
"InsertEmployees". The last instruction in this routine is:
Forms!Employees!EmployeeList.Requery, which is supposed to refresh the list
box on the form "Employees" to show the new inserted employee. But nothing
like that happened. To show the new inserted employee, I have to close the
form "Employees" and open it again which insane. I need to know if the
problem is with ms access 2000 (which I suspect). It just doesn't make sense
for microsoft to fool around with a developer's mind like that. Is microsoft
access 2000 a reliable product or not? Please HELP! DOES ANYONE KNOW SOMEBODY
AT MICROSOFT I CAN BRING UP THIS ISSUE TO. BECAUSE MY CUSTOMER CAN'T WAIT ANY
LONGER.
 
On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "ListBoxSubform.mdb" which does something like this. You
might want to download it and see how it differs from yours. One thing I
notice is that I put the requery on the OnCurrent and OnActivate events of
the form with the listbox.
--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
 
What is the Row Source for the listbox? If it's not a query that puts the
entries in order, are there enough rows of data that you may miss the item?
It may not necessarily be at the bottom of the list.

Since closing the form and reopening it shows the new data, it is apparent
that the data is being written to the table. However, it may not be there
prior to the requery. Try the following command before the requery and see
if it helps:

DBEngine.Idle dbRefreshCache
 
Zadi Friedrich said:
HI everyone,
I am facing a problem with ms access 2000. I have an unbound form name
"Employees" with a listbox showing 5 columns of information:
EmployeeID, FirstName, Last Name, Date of birth, Country of origin.
The listbox is based on the table "tblEmployees". On that form there
is also a button labeled "Entry" which opens up a form name
"Employees data entry". The form "Employees data entry" is unbound
also. The data entered in the text boxes are inserted in the table
tblEmployees via a routine name "InsertEmployees". The last
instruction in this routine is: Forms!Employees!EmployeeList.Requery,
which is supposed to refresh the list box on the form "Employees" to
show the new inserted employee. But nothing like that happened. To
show the new inserted employee, I have to close the form "Employees"
and open it again which insane. I need to know if the problem is with
ms access 2000 (which I suspect). It just doesn't make sense for
microsoft to fool around with a developer's mind like that. Is
microsoft access 2000 a reliable product or not? Please HELP! DOES
ANYONE KNOW SOMEBODY AT MICROSOFT I CAN BRING UP THIS ISSUE TO.
BECAUSE MY CUSTOMER CAN'T WAIT ANY LONGER.
Click to expand...

Please post the code of the InsertEmployees routine. It could be that
you are (mistakenly) opening a separate connection to the database to
perform your update, in which case there would likely be a timing issue
involved. I strongly doubt that your problem is due to a bug in Access.
 
Hi Dirk, please see below the code that insert the new employee:

Sub InsertEmployee(strName As String, lngBrigadeID As Long, _
lngRankID As Long, FirstAid As String, Sex As String, _
YearOfBirth As String, Assignment As String)
'this routine saves a new personal in the database
On Error GoTo err_Handler
Dim rstPersonal As New ADODB.Recordset
Set Cnn = New ADODB.Connection
If Cnn.State = adStateOpen Then
Cnn.Close
End If
Cnn.Open CurrentProject.Connection

With rstPersonal
.ActiveConnection = Cnn
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockOptimistic
.Source = "tblPersonels"
.Open
.AddNew
!Name = strName
!BrigadeID = lngBrigadeID
!RankID = lngRankID
!FirstAid = FirstAid
!Sex = Sex
!YearOfBirth = YearOfBirth
!Assignment = Assignment
.Update

.Close
End With
Cnn.Close
Forms!Employees!EmployeeList.Requery
err_Handler:
Error_Handler
End Sub

I hope that will help you see my problem
 
Zadi Friedrich said:
Hi Dirk, please see below the code that insert the new employee:

Sub InsertEmployee(strName As String, lngBrigadeID As Long, _
lngRankID As Long, FirstAid As String, Sex As String,
_ YearOfBirth As String, Assignment As String)
'this routine saves a new personal in the database
On Error GoTo err_Handler
Dim rstPersonal As New ADODB.Recordset
Set Cnn = New ADODB.Connection
If Cnn.State = adStateOpen Then
Cnn.Close
End If
Cnn.Open CurrentProject.Connection

With rstPersonal
.ActiveConnection = Cnn
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockOptimistic
.Source = "tblPersonels"
.Open
.AddNew
!Name = strName
!BrigadeID = lngBrigadeID
!RankID = lngRankID
!FirstAid = FirstAid
!Sex = Sex
!YearOfBirth = YearOfBirth
!Assignment = Assignment
.Update

.Close
End With
Cnn.Close
Forms!Employees!EmployeeList.Requery
err_Handler:
Error_Handler
End Sub

I hope that will help you see my problem
Click to expand...

I was right; you are opening a separate connection to the database to
perform your update, though you may not be aware of it. Try this
instead:

'----- start of revised code -----
Sub InsertEmployee(strName As String, lngBrigadeID As Long, _
lngRankID As Long, FirstAid As String, Sex As String, _
YearOfBirth As String, Assignment As String)

'this routine saves a new personal in the database

On Error GoTo err_Handler

Dim rstPersonal As New ADODB.Recordset

With rstPersonal

Set .ActiveConnection = CurrentProject.Connection
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockOptimistic
.Source = "tblPersonels"
.Open

.AddNew
!Name = strName
!BrigadeID = lngBrigadeID
!RankID = lngRankID
!FirstAid = FirstAid
!Sex = Sex
!YearOfBirth = YearOfBirth
!Assignment = Assignment
.Update

.Close

End With

Forms!Employees!EmployeeList.Requery

Exit Sub

err_Handler:
Error_Handler
End Sub
'----- end of revised code -----
 
Hi Dirk

I have a similar issue using an A2002 FE/BE, but not using linked tables. Is
your revised code compatible only with a stand-alone database? The reason I
ask is that I tried using this to solve my 'timing' problem. Only I got an
unexpected 'table or query not found' error.
On examining the 'currentproject.connection' 'data source' property, I find
it is the path of the project file, not the back end. I had assumed that the
primary form - having a listbox whose rowsource is an SQL statement
containing the 'back-end' database path (programmatically assigned) - would
maintain a 'connection' as long as it was open.

What am I missing?

Any help greatly appreciated.

Ali Kwok
 
AliKwok said:
Hi Dirk

I have a similar issue using an A2002 FE/BE, but not using linked
tables.
Click to expand...

I don't understand what you mean. How is it that you have a front-end
and a back-end, but are not using linked tables? These are .mdb files
we're talking about, right?
Is your revised code compatible only with a stand-alone
database? The reason I ask is that I tried using this to solve my
'timing' problem. Only I got an unexpected 'table or query not found'
error.
On examining the 'currentproject.connection' 'data source' property,
I find it is the path of the project file, not the back end.
Click to expand...

CurrentProject.Connection in an .mdb file is always a connection to the
front-end.
I had
assumed that the primary form - having a listbox whose rowsource is
an SQL statement containing the 'back-end' database path
(programmatically assigned) - would maintain a 'connection' as long
as it was open.
Click to expand...

Let me see if I understand you. Are you saying your listbox has a
rowsource that specifies a database path, to pull data from a table that
is neither local to the front-end, nor present in the front-end as a
linked table? Yes, I know you can do that, but I think it's going to
complicate matters.
What am I missing?
Click to expand...

I don't know yet. Please tell me more about your application, how it's
set up and why (insofar as that's relevant to the problem), what you're
trying to do, what code you're using to do it, and what's not working.
It's a lot, I know, but it will help.
 
Thank you Dirk

That clarified it and the problem is solved. I merely needed to create a
connection class, and a connection object property for any class which may
modify data underlying a listbox. Any time a listbox-containing form
instantiates such a class, it sets the property to its own connection and
thus the connection is shared.

Sorry - FE/BE is very loose. It's more like an 'unbound' FE. To cut a long
story short, the client needs unlimited 'back-ends' (.mdb), and relinking
linked tables is too slow in their multiuser environment.

Thanks for your help.

Ali
 
Back
Top