Listbox Requery Missing New Record

[markgrizzle]

My form allows the user to do this...

Select a record from listbox 2
Click link button to...
1. add listbox 2 record to link table
2. requery listbox 1 to include new link table record
3. requery listbox 2 to exclude new link table record

What happens is...
The requery doesn't include the new record on listbox 1, nor does it remove it from listbox 2.

I tried to solve this using... listbox.rowsource=listbox.rowsource
listbox.requery
listbox.refresh
adding a requery button to requery the listboxes

Comments...
My form and listboxes are unbound
The requery button works on the 2nd or 3rd attempt
The requery button and link button call the same code
Putting a 3 second loop before the requery code works, but is too slow for my users.

Has anyone faced this before? And more importantly, can anyone suggest a solution?

Thanks in advance,
Mark

ps - I've read the excellent faq on listbox events, though the author's name escapes me.

 

[OnTheFly]

How are you adding the record to the Link Table?

Hope this helps.

OnTheFly

 

[markgrizzle]

Thanks Fly,

cnn.BeginTrans
Insert new record into tblObjects, get PK ObjectID
Insert new record into tblInfo, and get PK InfoID
Insert new record into tblObjectInfo using both keys
cnn.CommitTrans

Inserts are done with ado rs.addnew
If any insert failed, rollback and notify

 

[OnTheFly]

Hmmm

It sounds like it could be a record locking issue where the new record is not being immediately released. I have not had this problem myself but the only suggestion I would venture would be to try one of the other Lock Types on the ADO recordset used to add the record. I Generally us the adLockOptimistic procedure when adding records.

Sorry I am not more help.

OnTheFly

 

[TheAceMan1]

Haaaaaaaaay markgrizzle . . . . .

Need to pin this down a little more. You say:

[blue][purple]Select a record[/purple] from listbox 2
Click link button to...
1. add listbox 2 record to link table[/blue]

Is this record within the listbox itself, or is [blue]the listbox looking-up the record[/blue] (that is the listbox takes you to the record on the form)?

Post the [blue]RecordSource[/blue] for listbox 1 & 2!

See Ya! . . . . . .

 

[markgrizzle]

Thanks everyone:

Fly:
that's what I thought, but shouldn't commitTrans handle that?

Ace:
The record's in the list, I grab the bound column, etc.

Here we go...

listbox1
--------
Table

SELECT tblRelations.ParentID,
tblRelations.ObjectID,
tblObjectTypes.ObjectType,
tblObjects.Object

FROM (tblObjectTypes
INNER JOIN tblObjects
ON tblObjectTypes.ObjectTypeID = tblObjects.ObjectTypeID) INNER JOIN tblRelations
ON tblObjects.ObjectID = tblRelations.ObjectID

WHERE (((tblRelations.ParentID)=[forms]![frmMenu]![lstObjects]));

UNION ALL Table

SELECT tblRelations.ParentID,
tblRelations.ObjectID,
tblObjectTypes.ObjectType,
tblObjects.Object

FROM tblObjectTypes
INNER JOIN (tblRelations
INNER JOIN tblObjects
ON tblRelations.ParentID = tblObjects.ObjectID)
ON tblObjectTypes.ObjectTypeID = tblObjects.ObjectTypeID

WHERE (((tblRelations.ObjectID)=[forms]![frmMenu]![lstObjects]))

ORDER BY ObjectType, Object;

listbox2
--------
SELECT tblObjects.ObjectID,
tblObjectTypes.ObjectType,
tblObjects.Object

FROM tblObjectTypes
INNER JOIN tblObjects
ON tblObjectTypes.ObjectTypeID = tblObjects.ObjectTypeID

WHERE (((tblObjects.ObjectID)
NOT IN (SELECT ParentID
FROM tblRelations
WHERE ObjectID = forms!frmMenu!lstObjects
AND (tblObjects.ObjectID)
NOT IN (SELECT ObjectID
FROM tblRelations
WHERE ParentID=forms!frmMenu!lstObjects
AND (tblObjects.ObjectID)<>
[forms]![frmMenu]![lstObjects]))

ORDER BY tblObjectTypes.ObjectType,
tblObjects.Object;