D
Dave L
I'm having a bit of a problem that I can't seem to overcome.
I have a form that has a few Textbox controls in it that allows a user to
add a record to the database. There is a listbox on this form that shows a
list of records that are currently in the database and a command button that
is used when the user wants to add/change a given record.
What is supposed to happen is that the user enters/changes the information
then clicks a command button that runs some ADO code to append/update the
data in the underlying database. Then the Lisbox control's requery method is
used to update the list in the listbox with the users changes.
The list box is bound to a query that gathers its data from a linked table.
The ADO command is run using a connection string to the mdb containing the
data on a file server. Both the linked table and the connection string point
to the same data in the same mdb file on the server.
This all used to work. When the database was a single mdb. The ADO would
run against the local table and the listbox's requery method would refresh
the list instantly. I've since split the database in to a Front end and a
Backend that resides on the server. The ADO code works fine, because I can
see the data. The problem is that the requery on the Listbox doesn't display
the data that the user added/modified.
What I've found is that the data does show up if you exit the form and
re-open it. (This form is not bound to any recordsets). I've also used the
currentdb.tabledef("table").refreshlink method. I've also tried using the
ADOX library to use the catalog and table objects to use the table.properties
method to update the link on the linked table. Both of these methods only
work some of the time.
What I think is going on is that the ADO command gets executed and starts to
run, but while it's running control is returned back to the code, which
finishes faster than ADO can write the changes to the database (via Jet). So
when the code gets to the requery method of the listbox control the data is
not yet available for the listbox to requery, hense it's not displayed right
away.
So that's what I think is causing my problem. I've been struggling now to
come up with a solution. Does anyone know of a way that I can pause the code
while ADO is updating the records and have it wait unit the write operation
is succesful on the data portion of the database?
Here's the code:
Private Sub cmdUpdateFee_Click()
Dim cmd As ADODB.Command
Dim strFeeName As String
Dim lngFeeID As Long
Dim response As Variant
Dim blnFlag As Boolean
Dim lngDelay As Long
Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table
On Error GoTo ErrHandle
'Validate the data
'if the user didn't enter a name for the fee then it's wrong
If Nz(Me.txtEditFee, "") = "" Then
response = MsgBox("You must enter a fee name before you can add
one." _
, vbExclamation + vbOKOnly, "Enter Fee Name")
Me.txtEditFee.SetFocus
GoTo ExitHere
End If
'open a connection to the database
'enables the global gcnn ado connection object.
'Function is located in modConnectToDB
If Not pbfOpenConnection() Then
response = MsgBox("the Ketchum Budget Wizard was unable to open a
connection " & _
"to the database. Contact Technical Support.", vbCritical +
vbOKOnly, _
"Error connecting to database")
GoTo ExitHere
End If
'if you made it this far you are going to edit data
'set the hourglass to true and set the edit data flag to true
DoCmd.Hourglass True
blnFlag = True
'if the button caption is update then the user is changing a
pre-existing Fee Name
If Me.cmdUpdateFee.Caption = "Update" Then
'Let the user know that this effects existing budgets, ask if they
want to continue
'if not then let them out of the change
If MsgBox("Changing the Name of a Fee effects every Budget that uses
that fee." & _
" This could cause some budget's to be wrong." & vbCrLf & vbCrLf
& _
"Are you really sure you want to do this?", vbCritical +
vbYesNo, _
"Are you sure?") = vbNo Then
blnFlag = False
GoTo ExitHere
End If
'get the change from the form
strFeeName = Me.txtEditFee
lngFeeID = CLng(Me.txtFeeID)
'update the database
Set cmd = New ADODB.Command
cmd.ActiveConnection = gcnn
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "qryUpdateFeeName"
cmd.Parameters.Append cmd.CreateParameter("prmFeeID", adInteger,
adParamInput, 255, lngFeeID)
cmd.Parameters.Append cmd.CreateParameter("prmFeeName", adVarWChar,
adParamInput, 255, strFeeName)
cmd.Execute
'clean up
Set cmd = Nothing
'reset the controls
Me.txtEditFee = ""
Me.txtFeeID = ""
Me.cmdUpdateFee.Caption = "Add"
Me.cmdCancelFeeUpdate.Enabled = False
GoTo ExitHere
Else ' or if the caption is Add the user is adding a new Fee Name
strFeeName = Me.txtEditFee
Set cmd = New ADODB.Command
cmd.ActiveConnection = gcnn
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "qryAddFeeName"
cmd.Parameters.Append cmd.CreateParameter("prmFeeName", adVarWChar,
adParamInput, 255, strFeeName)
cmd.Execute
Set cmd = Nothing
Me.txtEditFee = ""
GoTo ExitHere
End If
ExitHere:
'if the hourglass is on then the system actually updated data
If blnFlag Then
'refresh the linked table in order to make the data available
'then requery the control and turn off the hourglass
'this link doesn't use the global connection object because the linked
'tables reside in the local database
Set cat = New ADOX.Catalog
cat.ActiveConnection = CurrentProject.Connection
'cycle through the tables collection looking for the Fee's linked
Table
For Each tbl In cat.Tables
If tbl.Name = "tblFee" And tbl.Type = "link" Then
tbl.Properties("Jet OLEDB:Link Datasource") =
"P:\BudgetWiz\BudgetWiz_Data.mdb"
End If
Next
Me.lstFees.Requery
DoCmd.Hourglass False
Set tbl = Nothing
Set cat = Nothing
End If
Exit Sub
ErrHandle:
response = MsgBox("The Budget Wizard has encountered the unexpected
error: " & vbCrLf & vbCrLf & _
Err.Number & vbCrLf & Err.Description & vbCrLf & vbCrLf & "Please
write this informaton down and contact " & _
"technical support.", vbCritical + vbOKOnly, "Budget Wizard Error: "
& Err.Number & " has occured")
Resume ExitHere
End Sub
I have a form that has a few Textbox controls in it that allows a user to
add a record to the database. There is a listbox on this form that shows a
list of records that are currently in the database and a command button that
is used when the user wants to add/change a given record.
What is supposed to happen is that the user enters/changes the information
then clicks a command button that runs some ADO code to append/update the
data in the underlying database. Then the Lisbox control's requery method is
used to update the list in the listbox with the users changes.
The list box is bound to a query that gathers its data from a linked table.
The ADO command is run using a connection string to the mdb containing the
data on a file server. Both the linked table and the connection string point
to the same data in the same mdb file on the server.
This all used to work. When the database was a single mdb. The ADO would
run against the local table and the listbox's requery method would refresh
the list instantly. I've since split the database in to a Front end and a
Backend that resides on the server. The ADO code works fine, because I can
see the data. The problem is that the requery on the Listbox doesn't display
the data that the user added/modified.
What I've found is that the data does show up if you exit the form and
re-open it. (This form is not bound to any recordsets). I've also used the
currentdb.tabledef("table").refreshlink method. I've also tried using the
ADOX library to use the catalog and table objects to use the table.properties
method to update the link on the linked table. Both of these methods only
work some of the time.
What I think is going on is that the ADO command gets executed and starts to
run, but while it's running control is returned back to the code, which
finishes faster than ADO can write the changes to the database (via Jet). So
when the code gets to the requery method of the listbox control the data is
not yet available for the listbox to requery, hense it's not displayed right
away.
So that's what I think is causing my problem. I've been struggling now to
come up with a solution. Does anyone know of a way that I can pause the code
while ADO is updating the records and have it wait unit the write operation
is succesful on the data portion of the database?
Here's the code:
Private Sub cmdUpdateFee_Click()
Dim cmd As ADODB.Command
Dim strFeeName As String
Dim lngFeeID As Long
Dim response As Variant
Dim blnFlag As Boolean
Dim lngDelay As Long
Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table
On Error GoTo ErrHandle
'Validate the data
'if the user didn't enter a name for the fee then it's wrong
If Nz(Me.txtEditFee, "") = "" Then
response = MsgBox("You must enter a fee name before you can add
one." _
, vbExclamation + vbOKOnly, "Enter Fee Name")
Me.txtEditFee.SetFocus
GoTo ExitHere
End If
'open a connection to the database
'enables the global gcnn ado connection object.
'Function is located in modConnectToDB
If Not pbfOpenConnection() Then
response = MsgBox("the Ketchum Budget Wizard was unable to open a
connection " & _
"to the database. Contact Technical Support.", vbCritical +
vbOKOnly, _
"Error connecting to database")
GoTo ExitHere
End If
'if you made it this far you are going to edit data
'set the hourglass to true and set the edit data flag to true
DoCmd.Hourglass True
blnFlag = True
'if the button caption is update then the user is changing a
pre-existing Fee Name
If Me.cmdUpdateFee.Caption = "Update" Then
'Let the user know that this effects existing budgets, ask if they
want to continue
'if not then let them out of the change
If MsgBox("Changing the Name of a Fee effects every Budget that uses
that fee." & _
" This could cause some budget's to be wrong." & vbCrLf & vbCrLf
& _
"Are you really sure you want to do this?", vbCritical +
vbYesNo, _
"Are you sure?") = vbNo Then
blnFlag = False
GoTo ExitHere
End If
'get the change from the form
strFeeName = Me.txtEditFee
lngFeeID = CLng(Me.txtFeeID)
'update the database
Set cmd = New ADODB.Command
cmd.ActiveConnection = gcnn
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "qryUpdateFeeName"
cmd.Parameters.Append cmd.CreateParameter("prmFeeID", adInteger,
adParamInput, 255, lngFeeID)
cmd.Parameters.Append cmd.CreateParameter("prmFeeName", adVarWChar,
adParamInput, 255, strFeeName)
cmd.Execute
'clean up
Set cmd = Nothing
'reset the controls
Me.txtEditFee = ""
Me.txtFeeID = ""
Me.cmdUpdateFee.Caption = "Add"
Me.cmdCancelFeeUpdate.Enabled = False
GoTo ExitHere
Else ' or if the caption is Add the user is adding a new Fee Name
strFeeName = Me.txtEditFee
Set cmd = New ADODB.Command
cmd.ActiveConnection = gcnn
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "qryAddFeeName"
cmd.Parameters.Append cmd.CreateParameter("prmFeeName", adVarWChar,
adParamInput, 255, strFeeName)
cmd.Execute
Set cmd = Nothing
Me.txtEditFee = ""
GoTo ExitHere
End If
ExitHere:
'if the hourglass is on then the system actually updated data
If blnFlag Then
'refresh the linked table in order to make the data available
'then requery the control and turn off the hourglass
'this link doesn't use the global connection object because the linked
'tables reside in the local database
Set cat = New ADOX.Catalog
cat.ActiveConnection = CurrentProject.Connection
'cycle through the tables collection looking for the Fee's linked
Table
For Each tbl In cat.Tables
If tbl.Name = "tblFee" And tbl.Type = "link" Then
tbl.Properties("Jet OLEDB:Link Datasource") =
"P:\BudgetWiz\BudgetWiz_Data.mdb"
End If
Next
Me.lstFees.Requery
DoCmd.Hourglass False
Set tbl = Nothing
Set cat = Nothing
End If
Exit Sub
ErrHandle:
response = MsgBox("The Budget Wizard has encountered the unexpected
error: " & vbCrLf & vbCrLf & _
Err.Number & vbCrLf & Err.Description & vbCrLf & vbCrLf & "Please
write this informaton down and contact " & _
"technical support.", vbCritical + vbOKOnly, "Budget Wizard Error: "
& Err.Number & " has occured")
Resume ExitHere
End Sub