Here's a question I feel stupid asking...

[option]

Hey guys, I'm trying to requery a list box after a command button click. I tried "List72.requery" (list 72 is the name obviously), Me!List72.requery, etc etc...Here is the code I'm working with, since I am updating a record source.

Private Sub Command71_Click()
  Dim strSQL        As String
  Dim db            As DAO.Database
  Dim rs            As DAO.Recordset
  Dim ctl           As Control
  Dim varItem       As Variant

  On Error GoTo ErrorHandler

  Set db = CurrentDb()
  Set rs = db.OpenRecordset("tbl_ReportsTEMP", dbOpenDynaset, dbAppendOnly)

  'make sure a selection has been made
  If Me.adds.ItemsSelected.Count = 0 Then
    MsgBox "Must select at least 1 email address"
    Exit Sub
  End If

 
'  If Me.cbx_Rptz = Null Then
 '   MsgBox "Must select a report"
  '  Exit Sub
 ' End If

  'add selected value(s) to table
  Set ctl = Me.adds
  For Each varItem In ctl.ItemsSelected
    rs.AddNew
    rs!eID = ctl.ItemData(varItem)
    rs!proc_Rpt = Me.Text60
    rs.Update
  Next varItem

DoCmd.SetWarnings False
DoCmd.OpenQuery "addList"
DoCmd.OpenQuery "delRptTEMP"
DoCmd.SetWarnings True
List72.Requery
MsgBox ("List Updated")


ExitHandler:
  Set rs = Nothing
  Set db = Nothing
  Exit Sub

ErrorHandler:
  Select Case Err
    Case Else
      MsgBox Err.Description
      DoCmd.Hourglass False
      Resume ExitHandler
  End Select
  
End Sub

Thought the code might help in getting this silly issue resolved. Thanks!!

 

[lala]

there's no me next to list72
i thought it had to be there

 

[option]

Nah, List72.Requery works fine everywhere else. Plus, I already tried putting Me in front lol. Thanks for the quick response though!!

 

[lala]

i don't get it, what's the problem then if it works

 

[lala]

are you getting an error message, or it just doesn't do it
what's happening

 

[option]

It just doesn't do it. I tried putting the requery just about everywhere :/

 

[lala]

can u post your db?

 

[option]

It's not much more than the above code, but here goes.

 

[lala]

give me a minute

lol, i understand that it's not much more than your code, but i'm much better when i can touch things, as opposed to just reading

 

[option]

Oh I know how that goes! If I don't feel like building out my problems, I usually grab the whiteboard and get to drawing it out lol. Thanks again!

 

[lala]

first of all, question
are you also getting a OBJECT REQUIRED error message right after your message box LIST UPDATED?

 

[option]

Yeah, what that is is on the "add recipient" button, there's a line "frm_EditRpt.Requery". Just remove that and the error should fade away. I shouldv'e remembered to remove that for you, sorry!

 

[lala]

few more minutes

 

[lala]

wait a second, after clicking ADD RECIPIENT you're running a query that's adding that email to the REPORTS table or something like that
it's not adding it to the TEMP table, and that's where the records come from on that list box

this has nothing to do with requery, requery is working, you're not seeing the records because they're not in that table

you understand what i'm saying?

 

[option]

List72 is being populated by the above combo box and it's records are coming from tbl_ReportList. The query addList is what you are talking about, correct? It's appending the information to tbl_Reports. The temp table is to collect the data the user selects and then get the corresponding info (ie: eID) and place it into tbl_Reports. But since the combo box is being controlled by tbl_ReportList and not tbl_Reports, think that could be why the requery isnt working?

 

[lala]

the list box, which is called List72 gets the data from a table Called EditTEMP, or something like that, right?

so after you select report number, it queries the emails already there and puts it in the EDIT TEMP table and shows it in LIST72

once you're adding another email, it's not adding it to EDIT TEMP table, it's going straight to REPORTS table, and when you requery LIST72, the new email doesn't show up, because it's not in the TEMP table

am i confusing you?

i knew right away this was not a REQUERY problem, that's why i asked you to post the DB

 

[lala]

The temp table is to collect the data the user selects and then get the corresponding info (ie: eID) and place it into tbl_Reports.

yes, and the email user selects never makes it to the temp table, it goes straight to the main table
and since LIST72's data comes from TEMP table, the new email doesn't show up until you select that report again, then the macro runs, selects all the emails from the main table (including the new one) and that's why it shows up after selecting the report and not after REQUERY

 

[option]

It shouldnt be getting its data from the temp table. Check the after update on the combo box above List72. It should update List72 with the corresponding records to the selected report from the report selected. If it were getting its records from the temp table, there would never be anything in there to display since i clear it out after the update. Try this: close the DB then open it. Select a report from the combo box. Does anything appear in List72? If so, then its not getting anything from the temp table (which it shouldnt be)

 

[lala]

go into properties of LIST72 and look at the RowSource

it's tbl_editTmp

 

[lala]

It shouldnt be getting its data from the temp table. Check the after update on the combo box above List72. It should update List72 with the corresponding records to the selected report from the report selected. If it were getting its records from the temp table, there would never be anything in there to display since i clear it out after the update.

you didn't write this code?

after update it deletes everything from the EditTemp table and adds the current report's email to the temp table

then it makes LIST72 visible, and LIST72's RowSource is that EditTemp table