continuous form with query (1 Viewer)

exaccess · Apr 6, 2014

I have the following code that is intended to use the three fields obtained from a record on a continuous form and display the details of the record on a single form. I have not used the findfirst method because I am having enormous problems with it. Instead I am defining a query based on the recordsource of the continuous form. This sql query gives one record as result and that record is to be used as recordsource by the single form. The code below is in a module.

Code:

Public Sub SboxSelectForm(ForName, ParName, QryName)
On Error GoTo NotFound
    Debug.Print "SelectForm ForName="; ForName; " ParName="; ParName; " QryName="; QryName
    Dim ResultQy As String, F As Form, P As Form
    Dim db As DAO.Database
    Dim Prsc As DAO.Recordset, Frsc As DAO.Recordset
    Dim apost As String, repl As String
    Dim UA1 As String, UAE1 As String, UA2 As String, UAE2 As String, UA3 As String
    Dim matched As String, SQLRecordSource As String
    MsgBox "test"
    
    apost = "'"
    repl = "''"
    UA1 = Nz(Forms(ParName).NOM, " ")
    UAE1 = Replace(UA1, apost, repl)
    UA2 = Nz(Forms(ParName).PRENOM, " ")
    UAE2 = Replace(UA2, apost, repl)
    UA3 = Nz(Forms(ParName).CARTE, "00000")
    Debug.Print "UAE1="; UAE1; " UAE2="; UAE2; " UA3="; UA3
    SQLRecordSource = "SELECT * FROM [ActiveMembersQy]" & _
        " WHERE [ActiveMembersQy].[NOM] = '" & "UAE1" & "' And " & _
           " [ActiveMembersQy].[PRENOM] = '" & "UAE2" & "' And " & _
            " [ActiveMembersQy].[CARTE] = '" & "UA3" & "';"
    
    Set Prsc = Forms(ParName).Recordset        'query
    
    DoCmd.OpenForm ForName, , , , , acHidden
    
    Forms(ForName).RecordSource = SQLRecordSource
    Set Frsc = Forms(ForName).Recordset
    
    Debug.Print "Prsc.RecordCount="; Prsc.RecordCount; "Frsc.RecordCount="; Frsc.RecordCount

    If UAE1 = " " And UAE2 = " " And UA3 = "00000" Then
        MsgBox "Please press Close to leave this screen", , GC_Title
        Exit Sub
    End If
   
   ' Forms(ForName).Recordset.FindFirst "[NOM] = '" & UAE1 & "'" & " And " & _
   '                    "[PRENOM] = '" & UAE2 & "'" & " And " & _
   '                     "[CARTE] = '" & UA3 & "'"
   ' Frsc.FindFirst "[NOM] = '" & UAE1 & "'" & " And " & _
   '                    "[PRENOM] = '" & UAE2 & "'" & " And " & _
   '                     "[CARTE] = '" & UA3 & "'"
   ' If matched = "No" Then GoTo NotFound
Found:
    MsgBox "found it"
    DoCmd.OpenForm ForName, acNormal, , , acFormReadOnly
    Forms(ForName).SetFocus
    Exit Sub
NotFound:
    MsgBox "Not found"
    MsgBox "Call support"
    
err_handler:
    MsgBox "Record cannot be found - Error Number=" & Err.Number & _
        "Error message=" & Err.Description
    Exit Sub
      
 End Sub

Having said all that I am getting nothing as a result. There should be one record but I can not get it. Could somebody help please. Thanks.

JHB · Apr 6, 2014

To me it seems as if you go across the river for water, a mix of unnecessary code.
Why do not you use the DoCmd with Criteria to open the form, it can be done with one line of code?

exaccess · Apr 6, 2014

JHB said:
To me it seems as if you go across the river for water, a mix of unnecessary code.
Why do not you use the DoCmd with Criteria to open the form, it can be done with one line of code?

DoCmd with which criteria? If you are going to use the Where phrase as filter of the DoCmd you are going to write the whole phrase there. Plus this does not change the main problem in that there is a glitch somewhere in the SQL statement for which I am looking for help.

JHB · Apr 6, 2014

exaccess said:
DoCmd with which criteria? If you are going to use the Where phrase as filter of the DoCmd you are going to write the whole phrase there. Plus this does not change the main problem in that there is a glitch somewhere in the SQL statement for which I am looking for help.

Maybe it could be easier for you to see where the problem lays without all this code, you treat your variable as final text value, take away the " around them!

Code:

  SQLRecordSource = "SELECT * FROM [ActiveMembersQy]" & _
  " WHERE [ActiveMembersQy].[NOM] = '" & [B][COLOR=Red]"[/COLOR][/B]UAE1[COLOR=Red][B]"[/B][/COLOR] & "' And " & _
  " [ActiveMembersQy].[PRENOM] = '" & [B][COLOR=Red]"[/COLOR][/B]UAE2[B][COLOR=Red]"[/COLOR][/B] & "' And " & _
  " [ActiveMembersQy].[CARTE] = '" & [B][COLOR=Red]"[/COLOR][/B]UA3[B][COLOR=Red]"[/COLOR][/B] & "';"

exaccess · Apr 6, 2014

Thank you. It worked like a charm.

JHB · Apr 7, 2014

exaccess said:
Thank you. It worked like a charm.

You're welcome, luck with your project - with or without a lot of code.

continuous form with query (1 Viewer)

exaccess

Registered User.

JHB

Have been here a while

exaccess

Registered User.

JHB

Have been here a while

exaccess

Registered User.

JHB

Have been here a while

Similar threads

Users who are viewing this thread