regional settings

[Jason]

Hello,

I've this code which is a workaround to fill combo- or listboxes through
an ado-connection method This is because we don't have access 2003
where you can set the recordset of a combo- or listbox.

The problem is when i use my code, it sometimes fails because of
regional settings where the separator is different than the one i use in
my code.

How can i set the separator to each different regional setting if it occurs?

Here's the codesnippet:

Public Function ListComboBox_Filler(strSource As String)

Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim strValueList As String

Set cnn = myconnection

Set rst = New ADODB.Recordset

With rst
.ActiveConnection = cnn
.Source = strSource
.Open
Do Until .EOF
strValueList = strValueList & rst.Fields("Value") & ";" & _
rst.Fields("Label") & ";"
.MoveNext
Loop
End With

rst.Close

Set rst = Nothing

cnn.Close

Set cnn = Nothing

ListComboBox_Filler = strValueList

End Function

 

[Brendan Reynolds]

There's a Windows API call that will return the separator character in use.
I haven't tested this very extensively, based on limited testing it seems to
work ...

'KPD-Team 2001
'URL: http://www.allapi.net/
'E-Mail: (e-mail address removed)

'Modifications by Brendan Reynolds
'(change the obvious)
'brenreyn at brinkster dot net

Option Compare Database
Option Explicit

Const LOCALE_USER_DEFAULT = &H400
Const LOCALE_SENGCOUNTRY = &H1002 ' English name of country
Const LOCALE_SENGLANGUAGE = &H1001 ' English name of language
Const LOCALE_SNATIVELANGNAME = &H4 ' native name of language
Const LOCALE_SNATIVECTRYNAME = &H8 ' native name of country

Public Const LOCALE_SLIST As Long = &HC 'list separator

Private Declare Function GetLocaleInfo Lib "kernel32" Alias "GetLocaleInfoA"
(ByVal Locale As Long, ByVal LCType As Long, ByVal lpLCData As String, ByVal
cchData As Long) As Long

Public Function GetInfo(ByVal lInfo As Long) As String

Dim Buffer As String, Ret As String
Buffer = String$(256, 0)
Ret = GetLocaleInfo(LOCALE_USER_DEFAULT, lInfo, Buffer, Len(Buffer))
If Ret > 0 Then
GetInfo = Left$(Buffer, Ret - 1)
Else
GetInfo = ""
End If

End Function

Private Sub Combo0_DblClick(Cancel As Integer)

Dim strSep As String

strSep = GetInfo(LOCALE_SLIST)
Me.Combo0.RowSourceType = "Value List"
Me.Combo0.RowSource = "one" & strSep & "two" & strSep & "three"

End Sub

 

[Tim Ferguson]

The problem is when i use my code, it sometimes fails because of
regional settings where the separator is different than the one i use
in my code.

Quick way to get the local separator:

MySeparatorChar = Mid$( _
Format(#2000-01-01#,"dd/mm/yy"), _
3,1)


Access will substitute the local separator in place of the "/" in the
format string.


I am a little bit suspicious, though, of why you are having problems
here.

1) using VBA to convert text dates into DateTime values should correctly
handle local settings on the PC.

2) similarly, using VBA and the general formats to covert DateTimes to
text for display to the user will use the local settings.

3) for passing values to the database engine you simply don't have any
choice (okay, very limited choice); and you can control exactly what
goes in the SQL strings by explicitly formatting all dates.

At what stage are you running into these problems?

Tim F