List Box

  • Thread starter Thread starter Marcello Lenci
  • Start date Start date
M

Marcello Lenci

I have a list box in wich dates are loaded by means of a SQL SELECT from a
query derived from a table.
By means of VBA I add a record to the table.
I would like to update the list box automacitally.
I tried tu put another SELECT at the end of the sub in wich I add the
record, but it doesn't run.
Can it because the new SELECT is called when the query isn't yet updated ?
My code:
Sub ......
......
with rst
..addnew
!.....
..update
end with
....RowSource= "SELECT..
end sub
What can I do ?
Thanks
Marcello Lenci
 
Thanks for your advice, but it doesn't run in the same sub in wich I add the
record
It works only outside called by another sub, but it isn't automatic.
I think I have to delay the Requery until the Query isn't really update, but
I don't know how.
 
I'm sorry, but you've lost me. By "doesn't run in the same sub in which I
add the record", do you mean that the sub that adds the record isn't part of
the form that the combo box is on? If so, you can still requery the combo
box from that location. Instead of using the Me keyword, you'll have to
specify the full path to the combo box.

Forms!frmMyForm!cboMyCombo.Requery
 
Thanks again
The listbox is a part of the same form, but if I put the requery inside at
the end of the code in wich I add a record, it doesn't run.
If I call the requery inside another sub called by a botton it run.
For this reason I think it a time problem.
 
Private Sub BConfirm_Click()
Dim dbs As Database
Dim rst As DAO.Recordset
Set dbs = OpenDatabase("Searching.mdb")
Set rst = dbs.OpenRecordset("Sectors")
CTSector.SetFocus
With rst
.AddNew
!Sector = CTSettore.Text
!link1 = Format(Date, "yymmdd") + Format(Time, "hhmmss")
.Update
End With
CRSectors.Requery ' THIS DOESN'T RUN
rst.Close
dbs.Close
End sub

Private Sub BUpdate_Click()
CRSectors.Requery 'THIS RUN AFTER THE PREVIOUS SUB
End Sub
 
What if we move it down a couple of lines?

rst.Close
dbs.Close
Me.CRSectors.Requery ' THIS DOESN'T RUN
End sub

You're right, it looks good which makes it appear to be a timing issue. If
it is put after you close the other database, hopefully everything will have
been written at that point so it will be there when you requery. Is the
database you are adding data to actually a second database?

Also, just for information, when you click the button the CTSector textbox
will lose its focus (if it hadn't already). When this happens it will update
and place the text in the textbox into the value property of the textbox.
What this does for you is it allows you to not have to SetFocus back to the
textbox so that you can use the Text property. You could instead use the
Value property which doesn't require the control to have the focus.

Example:
CTSector.SetFocus 'this could be removed
.....
!Sector = CTSettore 'Value is the default. I assuming either this line or
the one above it is misspelled.
 
Thanks again for your advices
I solved my problem changing the listbox with a subform.
 
Back
Top