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.
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.
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