Requerying a listbox on a Form after adding a record from another form?

[soulkid555]

I have an Access application which I just started to develop.

It has an Administration screen with a list of Users and a list of Groups. (zip attached of my database)

I have two problems:

1 I have forms that allow users to add new users or groups. When the user closes the form, I requery the list on the opening form so that the listbox is updated for users and groups. This works sometimes and sometimes it does not work in that I do not see the new record in the listbox until I add a another record and then two pop into the listbox.
I am requerying my listbox from the Form_Unload on the form where the user adds the new record.

2 The other problem I have is that when users delete entries from the list box on frmAdministration the record sometimes displays #Deleted and stay there until the user deletes another record.

If anyone can help me with these two issues I would greatly appreciate it. I have spent hours looking at these problems.



Access database is attached as a zip. ActivityTracker.zip

Thanks in Advance.

[June7]

1. "I requery the list on the opening form" - what form is opening - you mean you requery the list on the already open frmAdminstration? That code is in the frmAddUser. Have you step debugged? Under what condition does the code not execute? You could move the code to the AddUser button event following the OpenForm line. Since you use acDialog, the button event code execution will suspend until frmAddUser closes.
Me.lstUsers.Requery

2. Again, step debug, under what condition does the requery method not happen? Consider this code (it works in my test):

Private Sub cmdDeleteUser_Click()
    CurrentDb.Execute "DELETE FROM Users WHERE Id = " & Me.lstUsers
    'Requery the Users List
    Me.lstUsers.Requery
End Sub

[soulkid555]

1. "I requery the list on the opening form" - what form is opening - you mean you requery the list on the already open frmAdminstration? That code is in the frmAddUser. Have you step debugged? Under what condition does the code not execute? You could move the code to the AddUser button event following the OpenForm line. Since you use acDialog, the button event code execution will suspend until frmAddUser closes.
Me.lstUsers.Requery

2. Again, step debug, under what condition does the requery method not happen? Consider this code (it works in my test):

Private Sub cmdDeleteUser_Click()
    CurrentDb.Execute "DELETE FROM Users WHERE Id = " & Me.lstUsers
    'Requery the Users List
    Me.lstUsers.Requery
End Sub

Thanks for your quick reply.

My problem in part 1 works fine now.

When I use your code in part it works also. I am not sure why my code does not work.

You are using CurrentDb.Execute...is this using ADO?
Eventually my users will just have the forms running on their machine but the Access MDB will be on a server. I assume I cannoyt use CurrentDb.Execute where the database is not on the same machine as the forms?

[June7]

AFAIK, CurrentDb.Execute is not using ADO nor DAO. It has nothing to do with recordsets. It is a VBA command to run an SQL action that acts directly on table or an updatable query.

You are talking about a split db design? Backend of data tables on server and frontend running on each user's workstation? Frontend has links to backend tables? Yes, CurrentDb.Execute will still work through the table links.

[soulkid555]

Thanks for your quick reply.

My problem in part 1 works fine now.

When I use your code in part it works also. I am not sure why my code does not work.

You are using CurrentDb.Execute...is this using ADO?
Eventually my users will just have the forms running on their machine but the Access MDB will be on a server. I assume I cannoyt use CurrentDb.Execute where the database is not on the same machine as the forms?

I am miffed as to what is wrong.
The form frmAddUser allows the user to add a user.
When they close this form (either by pressing X on the Window or pressing Cancel) I call the following code in my cmdAddGroup_Click eventon frmAdminstration.

Private Sub cmdAddUser_Click()
'Display Add User Form
DoCmd.OpenForm "frmAddUser", , , , , acDialog
Me.lstUsers.RowSource = "SELECT Users.Id, Users.Name AS ['Username'], GROUPS.Name AS ['Group Name'] FROM Users INNER JOIN GROUPS ON Users.GroupId=GROUPS.Id"
Me.lstUsers.Requery
End Sub
My listbox lstUsers is not refreshed always. When I add user 1 it appears in lstUsers. When I add user 2 it does not. When I add user 3, both user 2 and 3 now appear in the list.
Any ideas why this is happening?

[soulkid555]

Found out what my problem is.
If I use the ADO code below I have the problem.
' Dim conn As New ADODB.connection
' Dim sql As String
' sql = "INSERT INTO Users (Name, UserNumber, GroupId, UserPassword, Admin, Collections, Workout) VALUES ('" + txtName + "'," + txtNumber + "," + cboGroup.Value + ",'" + txtPassword + "'," + isAdmin + "," + isCollections + "," + isWorkout + ")"
' conn.Open CurrentProject.connection
'
' conn.Execute sql
' conn.Close
'
' Set conn = Nothing

If I use CurrentDb.Execute below I get no problem. Why is this the case?

CurrentDb.Execute "INSERT INTO Users (Name, UserNumber, GroupId, UserPassword, Admin, Collections, Workout) VALUES ('" + txtName + "'," + txtNumber + "," + cboGroup.Value + ",'" + txtPassword + "'," + isAdmin + "," + isCollections + "," + isWorkout + ")"

[June7]

Try setting the connection variable instead of the Open method.

Dim conn As ADODB.Connection
Set conn = CurrentProject.Connection