linked tables

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am a newbie at Access programming. I just finished an application that
works perfectly as a standalone .mdb file but now I would like to split the
database into a back end and a front end.

I used the database splitter wizard in Access 2003. It worked well, but now
when I run the front end some things don't work quite as they should.

The first issue I encountered appears to be related to 'requery' commands on
my forms: in certain cases, when the underlying data is changed I issue a
requery command to update the data shown in the form. In the mdb application
the form is updated perfectly, but in the fornt end it is not. If I close the
form/application and reopen it, the updated data is shown correctly. Thus, it
would appear that the 'problem' relates to how quickly the back-end tables
are updated. Is there a way to force the back end tables to update, so that
when I issue my requery command the form will indeed show updated data?
 
Here is one example where the form is not being updated correctly. The parent
form shows details for a given 'lead' and has a continuous subform showing a
list of scheduled activities for that lead that are not yet completed. For
each activity there is a checkbox to mark it as completed. Since the
recordsource for the subform includes only completed activities, I issue a
requery command and the completed activity disappears from the list. At least
that is what happened before the split. Now the requery seems to have no
effect, and the activity remains on the list until I close and reopen the
form.

Here is the code that runs when the user clicks the checkbox. What I think I
am missing is some command just before the 'me.requery' to force the linked
tables to update. Is this correct, or is the problem elsewhere?

Private Sub finished_Click()

Dim rstActivities As Recordset
Dim rstHistory As Recordset

'get the EventID of the activity being modified and the LeadId of the lead
that owns this activity
CurrentActivity = Me.EventID ' points to the record we are modifying
CurrentLead = Me.leadID ' points to the record in Leads that owns
this activity

' open the history and activities tables
Set rstActivities = CodeDb.OpenRecordset("Activities", dbOpenTable)
Set rstHistory = CodeDb.OpenRecordset("History", dbOpenTable)

'Find the activity type for the event being modified
rstHistory.index = "EventID"
rstHistory.Seek "=", CurrentActivity
CurrentType = rstHistory!ActivityType

' now change the date to today's date, to reflect the date of completion
rstHistory.Edit
rstHistory!duedate = date
rstHistory.Update

'open the Activities table and find the status and activity that must take
place next
rstActivities.index = "ActivityID"
rstActivities.Seek "=", CurrentType
newActivity = rstActivities!NextActivity
newstatus = rstActivities!NextStatus

'if there is a next activity to be scheduled, do it
If newActivity <> 0 Then
'set the values for the new activity
With rstHistory
.AddNew
!ActivityType = newActivity
!leadID = CurrentLead
!finished = False
.Update
End With
End If

'if the status needs to be updated, do it
If newstatus <> 0 Then
Me.Parent.status.value = newstatus
End If

rstHistory.Close
rstActivities.Close
Set rstHistory = Nothing
Set rstActivities = Nothing
Me.Requery
Me.Parent.Requery
End Sub

Any thoughts?

TIA!
 
Back
Top