none
In MIcrosoft Access, after a conversion from accdb to accde, form asks for a parameter

    Question

  • Only some pc (on my pc the problem does not occur) and for certain users, the file version "accde" Access 2007, shows the request parameters with the classic form in Italian: "Maschera! FormName! FieldName."

    This problem does not appear in the version accdb and not for all users.
     
    I always underestimated this sporadic problem applying it to different libraries loaded on the PC where the problem occurs, but after seeing it on the same computer, but with different domain user the problem does not occur, I decided to investigate, but I could not find no references to this specific problem.
     
    Thanks in advance for the help!
    A.

    Alfredo Vittoria

    Wednesday, January 23, 2013 12:09 PM

Answers

  • I see a couple of odd things in that code.  Possibly some of them are relics from several different attempts to work around the problem.  For example, your code creates a new Recordset object and sets several of its properties, but then doesn't open it; instead, it discards it completely when it executes the statement: Set rs = cn.Execute("Richieste_ele_sql_sp_1 " & strParr)

    Is the sub OpenRecordset in the form's module, or in another module?  Is "Me", in that sub, the same as Forms!Richieste_ele_sql ?

    I don't see, though, why this code would be forcing a parameter prompt.  However, since you execute the code in the form's Load event, and not its Open event, I wonder: does the form have anything in its RecordSource property?  If so, the query of that recordsource will begin before the Load event fires, and maybe that query is raising the prompt.

    Here's another possibility: maybe Access is trying to apply the form's Filter property to the dynamically assigned recordset.  Is the form's FilterOnLoad property set to Yes?  If so, try setting it to No and/or clearing the Filter altogether.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    • Marked as answer by AlfVit66 Tuesday, February 05, 2013 1:57 PM
    Monday, February 04, 2013 2:57 PM
  • Does this occur when the form's recordsource refers to a value on form?  Since you say that the parameter prompt uses the word "Maschera", I wonder if the difference among PCs might have to do with the system-selected language.  If so, maybe using the keyword "Forms" instead of "Maschera" would resolve the issue.

    Note that the above is speculation, because I haven't worked in languages other than English.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Wednesday, January 23, 2013 3:18 PM
  • Hi Dirk,
    Thanks for reply.

    The error occur on loading of form. In the event 'on load' I have setting a recordsource from e specific recordset, opened from a stored procedure into MSQL. So I don't need use the word' 'Maschera'.

    Below the command.
    'Set Forms!Richieste_ele_sql.Recordset = rs'

    All users use the italian settings.
    The error message don't is shown if i open other forms where the record source is a normal table or query.

    Apologies: I could not answer your response


    Alfredo Vittoria


    • Marked as answer by AlfVit66 Monday, February 04, 2013 1:48 PM
    • Edited by AlfVit66 Monday, February 04, 2013 1:50 PM
    Monday, February 04, 2013 1:48 PM

All replies

  • Does this occur when the form's recordsource refers to a value on form?  Since you say that the parameter prompt uses the word "Maschera", I wonder if the difference among PCs might have to do with the system-selected language.  If so, maybe using the keyword "Forms" instead of "Maschera" would resolve the issue.

    Note that the above is speculation, because I haven't worked in languages other than English.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Wednesday, January 23, 2013 3:18 PM
  • Hi Dirk,
    Thanks for reply.

    The error occur on loading of form. In the event 'on load' I have setting a recordsource from e specific recordset, opened from a stored procedure into MSQL. So I don't need use the word' 'Maschera'.

    Below the command.
    'Set Forms!Richieste_ele_sql.Recordset = rs'

    All users use the italian settings.
    The error message don't is shown if i open other forms where the record source is a normal table or query.

    Apologies: I could not answer your response


    Alfredo Vittoria


    • Marked as answer by AlfVit66 Monday, February 04, 2013 1:48 PM
    • Edited by AlfVit66 Monday, February 04, 2013 1:50 PM
    Monday, February 04, 2013 1:48 PM
  • The error occur on loading of form. In the event 'on load' I have setting a recordsource from e specific recordset, opened from a stored procedure into MSQL. So I don't need use the word' 'Maschera'.

    Below the command.
    'Set Forms!Richieste_ele_sql.Recordset = rs'

    All users use the italian settings.
    The error message don't is shown if i open other forms where the record source is a normal table or query.

    It looks like you are setting the form's recordset dynamically.  Something in the code that does that may be the cause of the problem.  Would you please post the code in form's Load event?


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Monday, February 04, 2013 2:23 PM
  • It looks like you are setting the form's recordset dynamically.  Something in the code that does that may be the cause of the problem.  Would you please post the code in form's Load event?


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Thank's a Lot

    Below the code on event on load and the Sub Open Recorset recall

    Private Sub Form_Load()
    [ODBC;Driver=SqlServer;Server=NomeServer;Database=NomeDb;Integrated_Security=Yes;].AR_Clienti"
    Dim strOperatore As String

    strOperatore = NomeUtente()

    DoCmd.Maximize

    If Me.chkRefreshAutomatico Then
        Me.TimerInterval = 300000
    Else
        Me.TimerInterval = 0
    End If

    If Nz(Me.OpenArgs, 0) > 0 Then
        Me.lblFiltrato.visible = True
        Me.Conc_flt = Me.OpenArgs
    Else
        Me.lblFiltrato.visible = False
    End If
    Me.fltCompetenze = NomeUtente()
    'Me.fltCompetenze = DLookup("[ID]", "Utenti", "Account ='" & NomeUtente() & "'")
    'Me.Requery
    Me.Totale = DCount("[IDRichiesta]", "Richieste")
    'Call Comando59_Click

    Call OpenRecordset

    End Sub

    Public Sub OpenRecordset()

    'On Error GoTo GestioneErrori

    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim cmd As ADODB.Command

    Dim varParam(5) As Variant, strParr As String
    Dim intNumRec As Variant
    Dim strFilter As String
    Dim strCnn As String

    'Legge i valori dai filtri della maschera
    varParam(1) = IIf(VarType(Me.fltIDRichiesta) < 2, "null", Me.fltIDRichiesta) '@IDRichiesta
    varParam(2) = IIf(IsNull(Me.cmbStato), "null", Me.cmbStato) 'IIf(Me.cmbStato = "null", "Null", Me.cmbStato) '@Aperto
    varParam(3) = IIf(Me.Conc_flt = "%", "'%'", "'" + Nz(Me.Conc_flt, "null") + "'") '@DomainName
    varParam(4) = IIf(Me.fltCompetenze = "%", "'%'", "'" & Me.fltCompetenze & "'") '@Assegnato
    varParam(5) = IIf(IsNull(Me.fltOggetto) Or Me.fltOggetto = "", "Null", "'" & Me.fltOggetto & "'") '@Oggetto

    strParr = varParam(1)

    For i = 2 To 5
        strParr = strParr & "," & varParam(i)
    Next

    'apre la connessione

    strCnn = "Provider='SQLOLEDB';" & _
             "Data Source='SQLDev';" & _
             "Initial Catalog='GestioneTask';" & _
             "Integrated Security='SSPI';"

    Set cn = New ADODB.Connection

    With cn
        .ConnectionString = strCnn
        .CursorLocation = adUseClient
        .CommandTimeout = 300
        .ConnectionTimeout = 300
        .Open
    End With

    'definisce le proprietà di rs

    Set rs = New ADODB.Recordset
    With rs
        .LockType = adLockReadOnly
        .CursorType = adOpenForwardOnly
        .CursorLocation = adUseClient
    End With

    'memorizza la condizione di filtro
    strFilter = Me.Form.Filter
    If Len(strFilter) > 0 Then
        strFilter = Mid(strFilter, InStr(3, strFilter, "["), Len(strFilter) - InStr(3, strFilter, "["))
        rs.Filter = Replace(strFilter, """", "'")
    End If

    Set rs = cn.Execute("Richieste_ele_sql_sp_1 " & strParr)

    Set Forms!Richieste_ele_sql.Recordset = rs

    Me.RecVis = rs.RecordCount


    Exit Sub

    Gestioneerrori:

    MsgBox Err.Number & " " & Err.Description
    On Error Resume Next

    End Sub


    Alfredo Vittoria



    • Edited by AlfVit66 Monday, February 04, 2013 2:34 PM
    Monday, February 04, 2013 2:32 PM
  • I see a couple of odd things in that code.  Possibly some of them are relics from several different attempts to work around the problem.  For example, your code creates a new Recordset object and sets several of its properties, but then doesn't open it; instead, it discards it completely when it executes the statement: Set rs = cn.Execute("Richieste_ele_sql_sp_1 " & strParr)

    Is the sub OpenRecordset in the form's module, or in another module?  Is "Me", in that sub, the same as Forms!Richieste_ele_sql ?

    I don't see, though, why this code would be forcing a parameter prompt.  However, since you execute the code in the form's Load event, and not its Open event, I wonder: does the form have anything in its RecordSource property?  If so, the query of that recordsource will begin before the Load event fires, and maybe that query is raising the prompt.

    Here's another possibility: maybe Access is trying to apply the form's Filter property to the dynamically assigned recordset.  Is the form's FilterOnLoad property set to Yes?  If so, try setting it to No and/or clearing the Filter altogether.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    • Marked as answer by AlfVit66 Tuesday, February 05, 2013 1:57 PM
    Monday, February 04, 2013 2:57 PM
  • I see a couple of odd things in that code.  Possibly some of them are relics from several different attempts to work around the problem.  For example, your code creates a new Recordset object and sets several of its properties, but then doesn't open it; instead, it discards it completely when it executes the statement: Set rs = cn.Execute("Richieste_ele_sql_sp_1 " & strParr)

    >Yes. You're right :(

    Is the sub OpenRecordset in the form's module, or in another module?  Is "Me", in that sub, the same as Forms!Richieste_ele_sql ?

    >At first I thought I would put it in a separate module for its use elsewhere, which I did not really done

    I don't see, though, why this code would be forcing a parameter prompt. 

    >The form passes the values ​​to the stored procedure invoked to limit the number of records returned to the client (Access).

    However, since you execute the code in the form's Load event, and not its Open event, I wonder: does the form have anything in its RecordSource property?  If so, the query of that recordsource will begin before the Load event fires, and maybe that query is raising the prompt.

    >No the field RecordSource is empty.

    Here's another possibility: maybe Access is trying to apply the form's Filter property to the dynamically assigned recordset.  Is the form's FilterOnLoad property set to Yes?  If so, try setting it to No and/or clearing the Filter altogether.

    >Unfortunatley not


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Really thanks for your meticulous analysis, and sorry for the code written so wildly.

    My answer start with ">" in your text.


    Alfredo Vittoria





    • Edited by AlfVit66 Monday, February 04, 2013 4:01 PM
    Monday, February 04, 2013 3:57 PM
  • I'm still puzzled, I'm afraid.  Could you please report the value of variable strParr at the time when the line:

        Set rs = cn.Execute("Richieste_ele_sql_sp_1 " & strParr)

    is executed?

    How precisely have you verified the point in the code where the error is raised?  If I understood you correctly, you said it was after the line:

        Set Forms!Richieste_ele_sql.Recordset = rs

    and that puzzles me mightily.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Monday, February 04, 2013 5:43 PM
  • I'm still puzzled, I'm afraid.  Could you please report the value of variable strParr at the time when the line:

        Set rs = cn.Execute("Richieste_ele_sql_sp_1 " & strParr)

    is executed?

    How precisely have you verified the point in the code where the error is raised?  If I understood you correctly, you said it was after the line:

        Set Forms!Richieste_ele_sql.Recordset = rs

    and that puzzles me mightily.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    The value of strParr is >strParr="null,1,'%','alfredo',Null"

    I can't say where the error occurs because it is shwn only in compiled versions (accde) and not with accdb, so I can not debug to see where the exact problem is, but I know that only occurs in 'opening of the form.


    Alfredo Vittoria




    • Edited by AlfVit66 Monday, February 04, 2013 5:58 PM
    Monday, February 04, 2013 5:55 PM
  • The value of strParr is >strParr="null,1,'%','alfredo',Null"

    I can't say where the error occurs because it is shwn only in compiled versions (accde) and not with accdb, so I can not debug to see where the exact problem is, but I know that only occurs in 'opening of the form.

    I don't see anything in strParr that would account for the error, and I must have misunderstood you about where the error is raised.

    I think it's important to determine where exactly the error is being raised.  I suggest you create a version of the .accdb just for debugging, and in it place calls to the MsgBox function at various places in the code, each time displaying a message that identifies which location is displaying the message.  Then make an .accde from this database, and run that .accde to see which message is last displayed before the parameter prompt occurs.  That should help you identify the exact line or section of code that causes the problem.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Monday, February 04, 2013 6:14 PM
  • Excellent suggestion, I'll just warn you that a colleague told me the problem occurs again. In fact, a few days for some reason can not be explained on the PC where the problem was now no longer occurs.

    I believe that the error is related to some library, but I do not know so well "Office" to find out exactly what.

    In any case, thank you very much for your kind interest and support.
    Certainly will post the outcome of the test as soon as I get a chance.


    Alfredo Vittoria

    Monday, February 04, 2013 6:21 PM
  • Excellent suggestion, I'll just warn you that a colleague told me the problem occurs again. In fact, a few days for some reason can not be explained on the PC where the problem was now no longer occurs.

    I'm not sure if you are saying the problem no longer occurs, or that it had stopped occurring but now has started again.  The language barrier is making this difficult; I wish I understood Italian.

    I believe that the error is related to some library, but I do not know so well "Office" to find out exactly what.

    In any case, thank you very much for your kind interest and support.
    Certainly will post the outcome of the test as soon as I get a chance.

    I am not convinced that it is related to a library difference, though I don't rule it out. So far, what you tell me makes it sound more like a timing problem, but I havent seen any code that accounts for that.

    Is it possible that you could send me a small database containing only the form, so that I can scan it for any peculiarities that might account for what's going on?  I would not be able to run it, since I wouldn't have your tables or your SQL Server back-end, but maybe something about the form or its code would stand out.

    Also, I see that you are setting the TimerInterval.  What code do you have in the form's Timer event?


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Monday, February 04, 2013 6:39 PM
  • Maybe I found the problem.
    Reviewing your suggestions, I realized, that the form responsible for the error, it was stored a value in the filter field, probably left during the test.
    Also transforming the file accde the error no longer occurs on the specific machine.

    However, it remains a mystery why the error was detected on some computers and not all.

    Thanks for your kind and knowledgeable support.


    Alfredo Vittoria

    Tuesday, February 05, 2013 1:36 PM
  • However, it remains a mystery why the error was detected on some computers and not all.

    Thanks for your kind and knowledgeable support.


    You're welcome.  As for the remaining mystery, I'm going to guess that it was due to a timing discrepancy and leave it at that.

    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Tuesday, February 05, 2013 5:37 PM
  • I'm going to guess that it was due to a timing discrepancy.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    What do you mean exactly?

    Alfredo Vittoria

    Tuesday, February 05, 2013 7:42 PM
  • What do you mean exactly?

    When a form is opened, various asynchronous processor threads are spun off, to take care of the business of instantiating the form, querying its recordsource, connecting the resulting recordset to the form and making the first record available, and completing the loading of the recordset.  I don't know the details of this, but observation makes it clear that some processes are asynchronous.

    On different systems, a given thread may take more or less time to complete.  I wasn't able to understand clearly what you said about the cause of the unwanted prompt, but it seems to have occurred at the time when Access attempted to apply a filter to the form's recordset.  This would be part of its normal processing thread in the opening of the form.  Meanwhile, in the form's Load event -- which fires *after* the form's recordsource has been queried (the Open event would have been better, I think) -- you are setting the form's Recordset, which may be happening in a separate processor thread.  I'm just guessing that something about the sequence in which these events happen to occur has an effect on whether or not the parameter prompt is displayed, and that sequence maye be different on different systems.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Tuesday, February 05, 2013 8:33 PM