Listbox.requery

[Freshman]

Hi all,
I searched for posts on the above topic but could not find an answer specific to my issue.

I use currentdb.execute to insert a value into a listbox and then update the listbox using me.notelist.rowsource = me.notelist.rowsource

Me.NoteList.Requery
If IsNull(Me.CurNote) = True Then Exit Sub
Me.CurNote = Replace(Me.CurNote, "'", "")
If IsNull(Me.NoteList.Column(2)) = True Then
    CurrentDb.Execute "INSERT INTO CommentLog (LoanNumber, LogComment) VALUES('" & Forms!LoggerMaxInvRpt.InvLU & "','" & Me.CurNote & "')"
Else
    CurrentDb.Execute "UPDATE CommentLog SET LogComment = '" & Me.CurNote & "' WHERE [ID] = " & Me.NoteList.Column(2)
End If
Forms!LoggerMaxInvRpt!cmdTitleNotes.Caption = "Title Notes (" & DCount("*", "CommentLog", "[LoanNumber] = '" & Forms!LoggerMaxInvRpt!InvLU & "'") & ")"
Me.CurNote = Null
Me.NoteList = Null
Me.NoteList.RowSource = Me.NoteList.RowSource
Me.Recalc
Me.CurNote.SetFocus

I read somewhere that me.notelist.rowsource = me.notelist.rowsource is a sure w tay to requery a listbox instead of me.noteliste.requery.

Anyway - almost all methods I used worked perfectly when I'm in the db (having shift held down when opening the db).
But as soon as I run the db app 'normally (not holding shift down) the listbox does not update consistently.
I added a save button to update and requery and only when I click it a few time (sometimes up to X10) I see my new record in the listbox.

Is there anything I can do to solve the above issue when I open the db 'normally'.

Thanks
FM

 

[MajP]

First guess is that CurNote is null and never gets to that code..
Debug to see if that is the case.

 

[Freshman]

No, no - as I said everything works perfectly when I have the db "open normally"

 

[Gasman]

No, no - as I said everything works perfectly when I have the db "open normally"

That is the opposite of what you have said?

 

[Freshman]

Oh my word. Can we stop arguing about side-issues. When I open the db using the shift button everything works 100% but not if I open it wiout the shift button like the User would do it. If you read that in my OP then you would not have asked about the code not being run...so let's just try and stick with what I said orignally ok

 

[sonic8]

But as soon as I run the db app 'normally (not holding shift down) the listbox does not update consistently.

Did you verify that updating the data in the list box is the actual problem and not inserting/updating the table?

 

[Freshman]

So if everything works 100 and 10% when then db is is open using the shift button. Then I mean EVERYTHING is working 100 and 10%. The issue here is why is it not working consistently when I don't hold down shift.
Am I not clear enough.
I even added a loop to requery the listbox 100 times or a continuous loop side function that checks the number of rows in the listbox compared to the DCount of the query. All work 100% when I had the shift-open but not without.

 

[The_Doc_Man]

I'm with MajP on this one. Put a breakpoint at this line at the top of your presentation:

Me.NoteList.Requery

Then use the F8 key to single-step past the breakpoint. One step at a time, see what happens.

As to your post #5 - you may claim it is a side-issue, but if we cannot get a consistent description, why would you expect a consistent answer? We cannot dissect your file like I did (to a frog) in biology class. You just posted a very small part of it. A part with significant overkill, I might add.

If you use the shift key, you bypass the opening form or opening macro. So... what do either of these do with respect to the table on which your listbox depends? You can't do anything to the listbox itself in that mode because no forms are opened automatically during a SHIFT startup.

 

[sonic8]

Am I not clear enough.

You're not.
I asked whether you explicitly checked if the problem is writing the data to the table or displaying the updated data in the list box.
Even your "check" with the loop and DCount does not really answer this question.

 

[Freshman]

I'm with MajP on this one. Put a breakpoint at this line at the top of your presentation:

Me.NoteList.Requery

Then use the F8 key to single-step past the breakpoint. One step at a time, see what happens.

As to your post #5 - you may claim it is a side-issue, but if we cannot get a consistent description, why would you expect a consistent answer? We cannot dissect your file like I did (to a frog) in biology class. You just posted a very small part of it. A part with significant overkill, I might add.

If you use the shift key, you bypass the opening form or opening macro. So... what do either of these do with respect to the table on which your listbox depends? You can't do anything to the listbox itself in that mode because no forms are opened automatically during a SHIFT startup.

Hi Doc,

So in my attempt to upload a copy of my db app for you to check, I had to strip all sensitive data and covert all linked tables to local tables. Then to my amazement all work 100% both ways. So I thought the cause must be a delay due to the linked tables (although they all reside on a local BE database). So I relinked all tables the way they were and now everything is still working 100% both ways.
This is weird. I did not change my code at all. I merely "unlinked" the tables and found everything to work as it should and now that they linked again it still works. I hate issues like this as it makes you waste lost of time on non-existing errors in the code.
BTW - I know my code was a overkill but that is because I was loading it with extra duplication to refresh the data. I will now slowly strip the code and test. Still don't know what Access did this. I compact and repair a lot of times so it can't be due to bloating. Anyways - thanks for your input

 

[The_Doc_Man]

Can you be a LITTLE more specific with regard to that local BE? Because "local" can EITHER mean same machine, just different file for the FE and BE components... OR it can mean that the "local BE" is on another machine that happens to be a part of the local INTRANET on the same sub-net. This is an issue in nomenclature that is unfortunately not completely uniform, because it depends on LAN (local AREA network) which CAN be called "local". So which flavor of local do you have?

The idea of your problem being due to some kind of internal delay doesn't work if the BE file is a native Access DB, though it MIGHT make a little more sense if the BE file is an SQL-based but local file. Access is synchronous with respect to native databases so would just wait for the data and NOTHING would proceed until it was done. Displays will hesitate if dealing with a remote BE file on a slow network segment. I've seen it more often than I care to remember and it IS no fun when you are stuck with that situation.

Another possible "gotcha" requires you to check whether you had a recent network-oriented Windows patch about the time that your problem showed up, because that has been known to cause apparent interruptions. Not totally likely, but possible, so I'll mention it.

The last thing I'll toss your way is, if we are talking "local" to be interpreted as "local INTRANET" then the first time you try to open the FE when the BE machine is down, your network links become suspect (i.e. broken, untrusted) and might require refreshing for that reason alone. I've seen it on older versions of Access and I don't recall that you mentioned your Access version number.

Therefore, at least for the moment, I can think of a few mundane ways to hose those links bad enough to need relinking.

 

[Freshman]

Hi Doc, local as in same hard-drive
Since the problem was fixed I did not spend all that much time on it, but the it suddenly came back after I ran un update on the backend from the frontend. So then I realized the code that makes the changes to the backend is to blame. Funny thing is that the changes are not made to the table the listbox is based on. It is totally unrelated tables but I guess the code influances something that impacts on all tables.
So I'm going to paste the code below and you could tell me if you see something wrong:

Public Function UpdateBE()
On Error GoTo Err_UBE

''Remote Table Updates
Dim rec As Recordset
Dim tbl As TableDef
Dim fld As Field
Dim DataPath As String
Dim FilesPath As String
FilesPath = "C:/DocProbe/Data/AdhocData.accdb"
Set db = OpenDatabase(FilesPath, False, False)

''SETTINGS TABLE
    Set tbl = db.TableDefs("Settings")

        Set fld = tbl.CreateField("UserTel", dbText)
        On Error Resume Next
        tbl.Fields.Append fld

''BulkNotes TABLE (Create new table and one field)
    Set tbl = db.CreateTableDef("BulkNotes")
        Set fld = tbl.CreateField("Client", dbText)
        On Error Resume Next
        tbl.Fields.Append fld
        On Error Resume Next
        db.TableDefs.Append tbl
        Set tdfLinked = CurrentDb.CreateTableDef("BulkNotes")
        tdfLinked.Connect = ";DATABASE=" & FilesPath
        tdfLinked.SourceTableName = "BulkNotes"
        On Error Resume Next
        CurrentDb.TableDefs.Append tdfLinked

''BulkNotes TABLE
    Set tbl = db.TableDefs("BulkNotes")
    
        Set fld = tbl.CreateField("LoanId", dbDouble)
        On Error Resume Next
        tbl.Fields.Append fld

        Set fld = tbl.CreateField("AppNumber", dbText)
        On Error Resume Next
        tbl.Fields.Append fld

        Set fld = tbl.CreateField("AddNote", dbText)
        On Error Resume Next
        tbl.Fields.Append fld

Application.RefreshDatabaseWindow
Exit Function

Err_UBE:
    MsgBox "Update error", vbInformation, "Notice"
    Application.Quit acQuitSaveAll
End Function

 

[Minty]

If you change a backend tables properties and don't refresh the link to it you can get all sorts of odd problems.
I have to ask why you would be programmatically creating tables on a regular basis, this sounds a bit suspect in the great scheme of things?

 

[The_Doc_Man]

Changing table contents from FE to BE is no biggie. But changing table STRUCTURE from FE to BE potentially causes a table's internal ID number to change (as recorded in the hidden system tables.) There are two sets of table definitions in this case. The FE has a COPY of the BE tables but the FE has a "connect" string to point to the BE, something that the BE doesn't have - because to the BE, the BE tables are local, not connected.

If you modify the BE structure in a way that is inconsistent with the FE, the FE and BE detect a difference when the FE gets launched... at which time the FE stops trusting the BE. But now I'm confused just a bit. Do you run this code often? And therefore, do you have to drop the tables before recreating them? This will eventually lead to DB bloat, depending on the size of those tables.

You have a serious problem in error reporting. "On Error Resume Next" is repeated several times in your code. Once will suffice because once you block error reporting, it is blocked. It doesn't revert after a single instruction. BUT what is REALLY going on is that it appears you have no error management in that routine. For debugging purposes I would take out ALL of those blocking statements and see if you get any errors in the creation of those tables. If you have already done this, well and good, but you are getting errors in the results so maybe it is time to consider that something IS going wrong. Time to review the errors - if any.

 

[Freshman]

Hi Doc, thanks for the advice. So is the On Error resume next not just skipping the following line. In otherwords if the field already exisits it will bring up an error which the on error resume line will skip. So this will cause the field to be added just once (the initial time) and never again.
Else I will have to check if the field exists and if not, to only add it then?

 

[The_Doc_Man]

The way you are asking that question, you are looking for approval/feedback on the actions implied here.

If in fact the table has not been deleted and therefore already exists (and the fields also exist) because you didn't remove them beforehand, that code should throw errors for each action that tries to add a table or add a field. The "On Error Resume Next" blocks error reporting and avoids the dreaded "error trapping" issues. The .Createxxx statements will fail in some way. Don't recall the exact error, but it would be in effect "Can't do that, it exists already." As to what other effect that would have, I would have to say that it probably wouldn't do what you really wanted it to do. But that is because I can't tell what you really wanted to do.

Were you trying to establish a "virgin" table or something? What is the purpose of running this code more than once? What was your REAL goal? Because whatever it might be, that is probably not the way to achieve it. Without that understanding, I can't tell you the right way to do this.

 

[Freshman]

I want to create a new table in the BE that does not exist already. And in the other cases I want to add some fields to existing tables.

 

[The_Doc_Man]

Creating a new table? Not so bad. Add fields to existing tables? That will potentially change the internal table ID number (because of rollback issues) and that will get you with that disconnect.

But that still doesn't tell us WHY you want to add fields to extant tables. This is beginning to smell like a normalization issue.

 

[Freshman]

OK say I have a BE table and suddenly due to some request I need to add an extra field to that table to make provision for additional data to be captured. I sure don't want to have to go to each User and add the field to their BE table one User at a time. So I normally just add the field that I need via the front-end. How else do you suggest I add a field that was never on the original spec to an existing table while still retaining the User's data?

 

[The_Doc_Man]

The USUAL way we do that is we have a developer's copy of the FE that is never actually distributed. (We make copies...) Then, when we need to add a field, we set aside a day when we will update the BE and the FE (including linking of tables using UNC mapping, usually). Then we have everyone pick up a new copy of the FE that has already been updated. What I did with the Navy was add a version number to the BE and have the FE look at the version number it was built to handle. If they mismatched, the user would know to download a new FE.

The more common way to do that involves what we call the automatic updater script. Then you don't CARE - the script just automatically picks up the master FE and copies it to the user's working folder. So if you needed to add a new field somewhere, take the BE offline for a while, update it. Update the master FE. Put them both back online. And have everyone use the auto-updater to launch the app that now has the changes in it.

However, what you describe still has an air of non-normalized tables. What kind of field would you ever have to add?