Language settings interfering with CurrentDb.OpenRecordset command

[Tompen]

I would really appreciate some help on this one!

I have database that include two linked subforms. When one form is updated, related fields on the other form are updated with calculated values that comes form a CurrentDb.OpenRecordset (pulling relevont recordset form the database) combined with a RecordCount that counts related fields and finally adds them to the right fields.

This works perfectly in my English environment, but for some mysterious reason it does not work for users with a Swedish environment.

The database will also be used by other nationalities where some of them can be expected to have other local Language Settings.

I'm not sure exactly what the problem is and I am obviously quite far away from figuring out a solution. Help would be extremely appreciated!

Thanks!

 

[spikepl]

Read my signature and provide proper description of the symptoms to get proper help.

Language settings and SQL normally have to do with decimal point, in that SQL demands "." as separator for decimal numbers, so in environments where "," is requred, variables need to be wrapped in the function Str.

 

[namliam]

Also date formats MM/DD/YYYY is required in SQL where a lot of european countries use DD/MM/YYYY instead. If you do not enforce the format in your (vba) sql this can cause issues.

 

[spikepl]

@Namliam this is exactly why I absolutely hate "it doesn't work" - the poster provides no clues but just wails

 

[Tompen]

Thanks for your support. Let me try to be clearer. Here is my function:

------------
Public Function MilestoneStatus(MyMilestoneStatus As Boolean, MyMS As Long, MyProjectID As Long) As Integer

Dim CntRecYes As Integer
Dim CntRecNo As Integer
Dim CntRecAll As Integer
Dim rs As Recordset

Set rs = CurrentDb.OpenRecordset("Select * FROM tblProjectMSCheck WHERE tblProjectMSCheck.[ProjectID] = " & MyProjectID & " AND tblProjectMSCheck.[Milestone] = " & MyMS & " AND tblProjectMSCheck.[CompleteCheck] = " & MyMilestoneStatus & "")

If rs.RecordCount > 0 Then
rs.MoveLast
rs.MoveFirst
CntRec = rs.RecordCount
End If

MilestoneStatus = CntRec
End Function
----------------------

I don't really have any dates in any of the parameters that are passed to the function so I don't thinkt that is the thing. I'm not sure though about the decimal comma. Would that look different if the different language versions?

I think I am not able to specify the problem more narrow than this code working fine for English environment but not for Swedish environment.

Thanks again for your support!

 

[namliam]

- Please use code tags ( see my signature for details)

- Disambiguate! Not "recordset" but "DAO.Recordset" (Could very well be your problem?)

- Dont splash SQL, readable code is maintainable code

Set rs = CurrentDb.OpenRecordset("Select * " & _
                                 " FROM tblProjectMSCheck  " & _
                                 " WHERE tblProjectMSCheck.[ProjectID] = " & MyProjectID & " " & _
                                 "   AND tblProjectMSCheck.[Milestone] = " & MyMS & "  " & _
                                 "   AND tblProjectMSCheck.[CompleteCheck] = " & MyMilestoneStatus & "")

Back to the basic question though, WHAT isnt working?
Compile or run error?
Wrong count returned?

Also instead of rs.movelast and all that, why not "simply" do
Select Count(*) from ....

That will always return 0,1,2, or what ever number of records is there.

 

[Tompen]

Thanks for your quick reply.

More specifically the problem can occur in different ways; In the English environment it doesn't occur at all. In another Swedish environment it will cause a runtime error "3061 too few parameters, expcted 1". Yet in another (also Swedish) environment it will not cause a runtime error but the result of the code will just not show in the form. If I change the language settings on the "trouble making machines" to English the problem is solved (in both cases).

I will try the DAO. as you propose. I would have already have done it if I only had a "trouble making machine" at hand.

If you have other ideas they are of course very welcome - otherwise I will go with the DAO. on Monday when I am back in the troublesome environment...

Thanks!

 

[namliam]

Parameters error Amy have something to do with reserved words?

I would to debug this creata a table and store the variabelen in it, or write the full SQL into a text file and ask THE user to mail me THE file