A
Alexandr Artamonov
I've come across an odd effect using a listbox. The following description is
just a test example of what's happening. Say there're two tables, Authors
(AuthID as PK, Author) and AuthAuto (AutoNum - autunumer - as PK, AuthID as
FK), linked by AuthID. I created a form with two listboxes. The rowsource of
lstAuthors is:
SELECT A.AuthId, A.Author
FROM Authors AS A LEFT JOIN AuthAuto AS AA
ON A.AuthId=AA.AuthID
WHERE AA.AuthID Is Null;
That is it's supposed to show authors without records in table AuthAuto.
The rowsource of lstAuthAuto is:
SELECT AuthAuto.AuthID, Authors.Author
FROM Authors INNER JOIN AuthAuto
ON Authors.AuthId=AuthAuto.AuthID;
It is supposed to show Authors with the records in AuthAuto.
I needed the double-click on a row in the lstAuthors to add a record in
table AuthAuto and wrote a code:
Private Sub lstAuthors_DblClick(Cancel As Integer)
Dim cnnCur As ADODB.Connection
Dim strInsert As String
strInsert = "INSERT INTO AuthAuto (AuthID) VALUES (" & Me.lstAuthors.Value &
")"
Debug.Print strInsert
If Not IsNull(Me.lstAuthors.Value) Then
Set cnnCur = CurrentProject.Connection
cnnCur.Execute strInsert
cnnCur.Close
Set cnnCur = Nothing
Me.lstAuthors.Requery
Me.lstAuthAuto.Requery
End If
End Sub
Then I added a button to delete all records from table AuthAuto - to made
the test easier.
The double-click on lstAuthors "removes" a row from the list (acutally
adding a record into AuthAuto) and "add" the author into lstAuthAuto. But
when the list is empty (the last author clicked), the double-click on the
empty row in the listbox still adds a record (the last author added) into
table AuthID! Executing the query identical to the listbox rowsource returns
empty recordset, but the listbox behaves as if there's one - not showing it.
Closing the form with the empy listbox and opening it again then
double-clicking on the empty row in list-box didn't cause anything which it
was supposed to do. But clicking on the emty row immediately after
'removing' the last row kept on adding that last row into the table. That
drove me nuts yesterday. Today I sorted it out asking the question at
sqlru.access - Russian newsgroup. Got two solutions: either change the
condition for executing the ADOB command for If Me.lstAuthors.ListIndex >=0
or adding Me.lstAuthors.Value = Null after Me.lstAuthAuto.Requery. Both
worked.
So my questions is: why Access behaves so unpredictably and 'untrustworthy'
? I may see nothing in the listbox but in fact there's some value hidden
somewhere there. The above was just a test example but I came accross that
weird effect working on a real database (mdb). Changing the test database
into adp showed the same results. Question number two: which solution is
better and what else can be done to correct that bug? Hope I made myselfe
understandable enough. Any hints are welcome.
Regards
Alexander Artamonov
just a test example of what's happening. Say there're two tables, Authors
(AuthID as PK, Author) and AuthAuto (AutoNum - autunumer - as PK, AuthID as
FK), linked by AuthID. I created a form with two listboxes. The rowsource of
lstAuthors is:
SELECT A.AuthId, A.Author
FROM Authors AS A LEFT JOIN AuthAuto AS AA
ON A.AuthId=AA.AuthID
WHERE AA.AuthID Is Null;
That is it's supposed to show authors without records in table AuthAuto.
The rowsource of lstAuthAuto is:
SELECT AuthAuto.AuthID, Authors.Author
FROM Authors INNER JOIN AuthAuto
ON Authors.AuthId=AuthAuto.AuthID;
It is supposed to show Authors with the records in AuthAuto.
I needed the double-click on a row in the lstAuthors to add a record in
table AuthAuto and wrote a code:
Private Sub lstAuthors_DblClick(Cancel As Integer)
Dim cnnCur As ADODB.Connection
Dim strInsert As String
strInsert = "INSERT INTO AuthAuto (AuthID) VALUES (" & Me.lstAuthors.Value &
")"
Debug.Print strInsert
If Not IsNull(Me.lstAuthors.Value) Then
Set cnnCur = CurrentProject.Connection
cnnCur.Execute strInsert
cnnCur.Close
Set cnnCur = Nothing
Me.lstAuthors.Requery
Me.lstAuthAuto.Requery
End If
End Sub
Then I added a button to delete all records from table AuthAuto - to made
the test easier.
The double-click on lstAuthors "removes" a row from the list (acutally
adding a record into AuthAuto) and "add" the author into lstAuthAuto. But
when the list is empty (the last author clicked), the double-click on the
empty row in the listbox still adds a record (the last author added) into
table AuthID! Executing the query identical to the listbox rowsource returns
empty recordset, but the listbox behaves as if there's one - not showing it.
Closing the form with the empy listbox and opening it again then
double-clicking on the empty row in list-box didn't cause anything which it
was supposed to do. But clicking on the emty row immediately after
'removing' the last row kept on adding that last row into the table. That
drove me nuts yesterday. Today I sorted it out asking the question at
sqlru.access - Russian newsgroup. Got two solutions: either change the
condition for executing the ADOB command for If Me.lstAuthors.ListIndex >=0
or adding Me.lstAuthors.Value = Null after Me.lstAuthAuto.Requery. Both
worked.
So my questions is: why Access behaves so unpredictably and 'untrustworthy'
? I may see nothing in the listbox but in fact there's some value hiddensomewhere there. The above was just a test example but I came accross that
weird effect working on a real database (mdb). Changing the test database
into adp showed the same results. Question number two: which solution is
better and what else can be done to correct that bug? Hope I made myselfe
understandable enough. Any hints are welcome.
Regards
Alexander Artamonov