Form Code will not run on some systems

[David Brown]

I have a block of code (below) that populates a combo box using the after
update event of another combo box. The code runs fine on my system but on
some machines it won't run. I am using Access 2002, the machine in question
is using Access 2000. Both machines run XP. Please take a look at my code
and fill me in on any possible problems. Also, could this be some sort of
setting or security issue in Access or XP? Finally, if there is another
group I need to post this sort of thing to, please let me know.

Thanks,

David Brown

Private Sub cboSiteID_AfterUpdate()
'populate the patient field based on site ID
Dim MyTempCount As Integer
Dim cnn1 As New ADODB.Connection
Dim cmd1 As New ADODB.Command
Dim SQLString1 As String
Dim rst1 As ADODB.Recordset
Dim MySite As Integer
Dim FirstTime As Integer
MySite = Me.cboSiteID
'Remove values from the current list
MyTempPat = Me.cboPatientName.ListCount - 1
If MyTempPat > -1 Then
For x = MyTempPat To 0 Step -1
Me.cboPatientName.RemoveItem (x)
Next
End If

'Open a connection to the current project
Set cnn1 = CurrentProject.Connection
'Create query strings
SQLString1 = "SELECT * FROM qry_ActivePatients WHERE Pat_SiteID = " & MySite
Set cmd1 = New ADODB.Command
With cmd1
.ActiveConnection = cnn1
.CommandText = SQLString1
.Execute
End With
Set rst1 = New ADODB.Recordset
rst1.Open cmd1
While Not rst1.EOF
MyName = rst1(0) & ";" & rst1(1) & ";" & rst1(2) & ";" & rst1(3)
Me.cboPatientName.AddItem Item:=MyName
rst1.MoveNext
Wend
Me.cboPatientName.Enabled = True
Me.lblTip.Visible = True
Me.lblTipContent.Visible = True
End Sub

 

[John Vinson]

I have a block of code (below) that populates a combo box using the after
update event of another combo box. The code runs fine on my system but on
some machines it won't run.

This appears to be the very common References bug. On each of the
machines that's failing, open any module in design view, or open the
VBA editor by typing Ctrl-G. Select Tools... References from the menu.
One of the .DLL files required by Access will probably be marked
MISSING. Uncheck it, recheck it, close and open Access.

If none are MISSING, check any reference; close and open Access; then
uncheck it again. This will force Access to relink the libraries.

Also be sure that all machines have been upgraded to the SAME service
pack release.

John W. Vinson[MVP]

 

[David Brown]

Thanks for the info. I've got just a few follow up questions for my own
information. First, does the user need to be in my database when they run
the fix you described? And two, will the same service pack fix or avoid the
problem or is just a good idea always?

Thanks again for your help,

David

 

[RoyVidar]

I have a block of code (below) that populates a combo box using the after
update event of another combo box. The code runs fine on my system but on
some machines it won't run. I am using Access 2002, the machine in question
is using Access 2000. Both machines run XP. Please take a look at my code and
fill me in on any possible problems. Also, could this be some sort of setting
or security issue in Access or XP? Finally, if there is another group I need
to post this sort of thing to, please let me know.

Thanks,

David Brown

Private Sub cboSiteID_AfterUpdate()
'populate the patient field based on site ID
Dim MyTempCount As Integer
Dim cnn1 As New ADODB.Connection
Dim cmd1 As New ADODB.Command
Dim SQLString1 As String
Dim rst1 As ADODB.Recordset
Dim MySite As Integer
Dim FirstTime As Integer
MySite = Me.cboSiteID
'Remove values from the current list
MyTempPat = Me.cboPatientName.ListCount - 1
If MyTempPat > -1 Then
For x = MyTempPat To 0 Step -1
Me.cboPatientName.RemoveItem (x)
Next
End If

'Open a connection to the current project
Set cnn1 = CurrentProject.Connection
'Create query strings
SQLString1 = "SELECT * FROM qry_ActivePatients WHERE Pat_SiteID = " & MySite
Set cmd1 = New ADODB.Command
With cmd1
.ActiveConnection = cnn1
.CommandText = SQLString1
.Execute
End With
Set rst1 = New ADODB.Recordset
rst1.Open cmd1
While Not rst1.EOF
MyName = rst1(0) & ";" & rst1(1) & ";" & rst1(2) & ";" & rst1(3)
Me.cboPatientName.AddItem Item:=MyName
rst1.MoveNext
Wend
Me.cboPatientName.Enabled = True
Me.lblTip.Visible = True
Me.lblTipContent.Visible = True
End Sub

If you are using a native Access combo, then the .AddItem and
..RemoveItem methods became available in the 2002 version - which means
you'll have to recode those parts for it to work on the 2000 version.

 

[John Vinson]

Thanks for the info. I've got just a few follow up questions for my own
information. First, does the user need to be in my database when they run
the fix you described? And two, will the same service pack fix or avoid the
problem or is just a good idea always?

It shouldn't matter who is in the database, provided they have full
read/write/create/delete privileges to the folder containing the
database. I'd recommend always - at the very least - keeping all
Access users at the SAME service pack level if they're sharing a
database; it's probably best that that be the latest level, since the
service packs are released to fix genuine, known problems.

John W. Vinson[MVP]