Refreshing a listbox

[PatrickCairns]

I have a listbox that is populated from a recordset based on entries filled out in form which is working fine. Everytime someone changes the parameters the recordset is deleted and recreated which repopulates the listbox. I need to do another query when the user clicks on an item in the listbox. However it requires 2 clicks on the listbox otherwise the data is wrong. So for example on the first click on the listbox.value may = 75 which has been deleted but it corrects itself on the second click where listbox.value = 89 which would be correct. It is requery'ed during the event that populates it

strSQL = "SELECT * FROM tbl_tempCXlist WHERE tbl_Customers.ExcelCxId = " & lbOther.Value & ";"


Set db = CurrentDb
Set qdf = db.QueryDefs("qry_DeleteTempCxTransfer") 'clears all data in the table to be populated
qdf.Execute


Set cust = db.OpenRecordset(strSQL) 'error occurs here on first click, but second click works

[PatrickCairns]

Also This is in the Mousedown event.

[ssanfu]

Welcome to the forum, Patrick.

I am confused as to what you are trying to do, but here are my thoughts:
----
The Mousedown event is the probably the wrong event to use. I would a click event or after update event (depending on what you are trying to do).

----

strSQL = "SELECT * FROM tbl_tempCXlist WHERE tbl_Customers.ExcelCxId = " & lbOther.Value & ";"

This is confusing because there is no relationship between the tables "tbl_tempCXlist" and "tbl_Customers".

----
To delete all records in a table, it is easier to use:

Currentdb.Execute "DELETE * FROM TempCxTransfer"

---
What is "cust"? The form or a listbox?

[PatrickCairns]

Thanks for the reply.

cust is the recordset (Dim cust as dao.recordset)

What is happening is that the table linked to the listbox is having it's content deleted and changed when a button is being clicked. The listbox shows the new information displayed but when clicked it references the previous data.

So for example the original data shown in the listbox is

1 John Smith
2 Mike Smith


When it is requeried the listbox displays

3. Patrick Cairns
4. Jack Cairns

But when the user clicks on Patrick Cairns the lb.Value shows a value of 1 instead of 3. However on the second click of the same item it will show the correct listing of 3.

[ssanfu]

So there is more code?
Not sure how the temp table is getting populated, but somewhere you need to requery the list box, after the temp table is populated.

[PatrickCairns]

Sorry if the code is not that pretty, however I am still learning...

So the user is entering a last name in a textbox and clicking a button to populate

Private Sub cmdLN_Click()
Dim db As DAO.Database
Dim cust As DAO.Recordset
Dim tempcust As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim i As Integer
Dim strSQL As String




Set qdf = CurrentDb.QueryDefs("qry_DeleteTempCxList")
qdf.Execute


strSQL = "SELECT * FROM tbl_Customers WHERE tbl_Customers.LastName = '" & Me.txtLastName & "';"


Set db = CurrentDb


Set cust = db.OpenRecordset(strSQL)
Set tempcust = db.OpenRecordset("tbl_tempCXlist")


Do While Not cust.EOF
    tempcust.AddNew
    tempcust![LastName] = cust("LastName").Value
    tempcust![FirstName] = cust("FirstName").Value
    tempcust![Company] = cust("Company").Value
    tempcust![Phone1] = cust("Phone1").Value
    tempcust![PhysicalAddress1] = cust("PhysicalAddress1").Value
    tempcust![PhysicalAddressCity] = cust("PhysicalAddressCity").Value
    tempcust![TransferID] = cust("ExcelCxId").Value
    tempcust.Update
    cust.MoveNext
Loop


Me.lbOther.Requery


End Sub

[ssanfu]

Doesn't look so bad.
I did (couldn't resist) make a couple of changes:

Private Sub cmdLN_Click()
   Dim db As DAO.Database
   Dim cust As DAO.Recordset
   Dim tempcust As DAO.Recordset
   Dim qdf As DAO.QueryDef
   Dim i As Integer
   Dim strSQL As String

   Set db = CurrentDb

   '   Set qdf = CurrentDb.QueryDefs("qry_DeleteTempCxList")
   '   qdf.Execute
   db.Execute "DELETE * FROM tbl_tempCXlist"

   strSQL = "SELECT * FROM tbl_Customers WHERE tbl_Customers.LastName = '" & Me.txtLastName & "';"
   Set cust = db.OpenRecordset(strSQL)

   'check to see if there are records in the cust recordset
   If Not cust.BOF And Not cust.EOF Then
      Set tempcust = db.OpenRecordset("tbl_tempCXlist")
      
      cust.MoveFirst
      Do While Not cust.EOF
         tempcust.AddNew
         tempcust![LastName] = cust("LastName")
         tempcust![FirstName] = cust("FirstName")
         tempcust![Company] = cust("Company")
         tempcust![Phone1] = cust("Phone1")
         tempcust![PhysicalAddress1] = cust("PhysicalAddress1")
         tempcust![PhysicalAddressCity] = cust("PhysicalAddressCity")
         tempcust![TransferID] = cust("ExcelCxId")
         tempcust.Update
         cust.MoveNext
      Loop

   End If

   Me.lbOther.Requery

End Sub

Does it do what you want?

I do have another question. You have a customer table and a list box. Why can't you set the list box row source to

SELECT * FROM tbl_Customers WHERE tbl_Customers.LastName = '" & Me.txtLastName & "';

Enter a name in the text box and click the button. The only code you would need would be

Me.lbOther.Requery

Why use the temp table?

[PatrickCairns]

Okay so I cleaned up a bit using that . Thanks

Private Sub cmdLN_Click()
Dim db As DAO.Database
Dim strSQL As String


Set db = CurrentDb
db.Execute "DELETE * FROM tbl_tempCXlist;"


strSQL = "SELECT * FROM tbl_Customers WHERE tbl_Customers.LastName = '" & Me.txtLastName & "';"
lbOther.RowSource = strSQL


Me.lbOther.Requery


End Sub

Now I am still running into the same issue with the listbox where on the first click it shows the previous selection rather then the current selection (This occurs without changing the rowsource as well, it feels the lb.value is being updated after the mousedown event.

Private Sub lbOther_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
Dim db As DAO.Database
Dim strSQL As String


Set db = CurrentDb


strSQL = "SELECT * FROM tbl_Customers WHERE tbl_Customers.ExcelCxId = " & lbOther.Value & ";"
Debug.Print ("Selection: " & lbOther.Value)


End Sub

[PatrickCairns]

Okay got it. It has to be in the Afterupdate event not mousedown....

[ssanfu]

Excellent!