References Bug

[David Brown]

I have a database that I've distributed to Access 2000 and Access 2002
users. There is one particular feature that will work just fine on my
computer (Access 2002) but not on another computer (Access 2000). I
understand that this may be a reference library issue. Upon investigation,
I've found that I have the following references on my computer:

Visual Basic for Applications

Microsoft Access 10.0 Object Library

OLE Automation

Microsoft ActiveX Data Objects 2.1 Library

Microsoft Forms 2.0 Library



My end user has the following refernce libraries checked:



Visual Basic for Applications
Microsoft Access 9.0 Object Library
OLE Automation
Microsoft ActiveX Data Objects 2.1 Library
Microsoft Forms 2.0 Library

First question, I have a newer version of the Microsoft Access Object
Library. Am I right in thinking that this may be the cause of my trouble?
Second, what is the easiest way to fix the problem?

Thanks for your help,

David

 

[Tom Wickerath]

Hi David,

Version 9 of the Microsoft Access Object Library is correct for Access 2000,
and version 10 is correct for Access 2002. See the table under the heading
"Which version" on Allen Browne's web site: http://allenbrowne.com/ser-38.html

What is this one particular feature that works fine for you, but not for
others?


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

I have a database that I've distributed to Access 2000 and Access 2002
users. There is one particular feature that will work just fine on my
computer (Access 2002) but not on another computer (Access 2000). I
understand that this may be a reference library issue. Upon investigation,
I've found that I have the following references on my computer:

Visual Basic for Applications
Microsoft Access 10.0 Object Library
OLE Automation
Microsoft ActiveX Data Objects 2.1 Library
Microsoft Forms 2.0 Library

My end user has the following refernce libraries checked:

Visual Basic for Applications
Microsoft Access 9.0 Object Library
OLE Automation
Microsoft ActiveX Data Objects 2.1 Library
Microsoft Forms 2.0 Library

First question, I have a newer version of the Microsoft Access Object
Library. Am I right in thinking that this may be the cause of my trouble?
Second, what is the easiest way to fix the problem?

Thanks for your help,

David

 

[RoyVidar]

I have a database that I've distributed to Access 2000 and Access 2002 users.
There is one particular feature that will work just fine on my computer
(Access 2002) but not on another computer (Access 2000). I understand that
this may be a reference library issue. Upon investigation, I've found that I
have the following references on my computer:

Visual Basic for Applications

Microsoft Access 10.0 Object Library

OLE Automation

Microsoft ActiveX Data Objects 2.1 Library

Microsoft Forms 2.0 Library



My end user has the following refernce libraries checked:



Visual Basic for Applications
Microsoft Access 9.0 Object Library
OLE Automation
Microsoft ActiveX Data Objects 2.1 Library
Microsoft Forms 2.0 Library

First question, I have a newer version of the Microsoft Access Object
Library. Am I right in thinking that this may be the cause of my trouble?
Second, what is the easiest way to fix the problem?

Thanks for your help,

David

The Microsoft Forms reference is a bit unusual, isn't it? Isn't that
what's needed to use UserForms in Word and Excel?

Well, anyway, it's perhaps easier to address the issue if we also knew
which feature isn't working - I mean - usually with reference issues,
nothing would work, or you'd get errors on left, mid, format i e the
most common VBA functions, or other strange stuff.

A couple of things of the top of my head, that exist in 2002 but not in
2000
- filedialog object
- .additem/.removeitem methods of native combos and list box controls
- windowmode and openargs arguements for the docmd.openreport method

 

[Tom Wickerath]

Hi Roy,

The Microsoft Forms reference is a bit unusual, isn't it?

I forgot to mention that too. I've worked on a few databases for other
people that also included this reference. The strange thing is that in both
cases Access would not allow me to deselect this reference. The only way I
was able to get rid of it was by importing all objects into a new database.
After doing so, the code compiled just fine without the need for this
reference. Very strange that Access would not allow me to remove it. I got
that same message back that one would get if they attempted to remove either
of the first two checked references.

Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

The Microsoft Forms reference is a bit unusual, isn't it? Isn't that
what's needed to use UserForms in Word and Excel?

Well, anyway, it's perhaps easier to address the issue if we also knew
which feature isn't working - I mean - usually with reference issues,
nothing would work, or you'd get errors on left, mid, format i e the
most common VBA functions, or other strange stuff.

A couple of things of the top of my head, that exist in 2002 but not in
2000
- filedialog object
- .additem/.removeitem methods of native combos and list box controls
- windowmode and openargs arguements for the docmd.openreport method

 

[David Brown]

You may have hit upon something there. My feature uses the
..additem/.removeitem methods as shown below. Is there anything I can do to
make this work for Access 2000 users?

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

 

[RoyVidar]

You may have hit upon something there. My feature uses the
.additem/.removeitem methods as shown below. Is there anything I can do to
make this work for Access 2000 users?

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

Since you're dropping all items, there's no problem, you could either
just set the .rowsource of the combo to vbNullString - or not bother
with it at all, just allow the new .rowsource to replace the old one.

To populate the combo, there's one handy ADO trick called .GetString -
but it would require a fixed column list from the recordset, not select
*.

SQLString1 = "SELECT field1, field2, field3, field4 " & _
"FROM qry_ActivePatients WHERE Pat_SiteID = " & MySite


Then after opening the recordset, you could do

Me!cboPatientName.RowSource = rst1.getstring(adclipstring,,";",";")

Note - the .RowSourceType of the combo would need to be set to Value
List, not Table/Query.

To check out how the .getstring works, and the different arguements,
take a look in the help files, it's pretty good for debuging purposes,
too.

debug.print rst.getstring
' then check the result in immediate pane (ctrl+g)