Changing my QUERY before opening the form (1 Viewer)

[mloucel]

Hello ALL:

I am using a form that I've been able to use thanks to @arnelgp in 2 different routines, works perfect, but I have an issue now..

I created a Query and I base the form on that Query.

SELECT PatientT.PatientID, PatientT.PLastName, PatientT.PFirstName, PatientT.PDOB, PatientT.PPhone, PatientT.PDeseaced
FROM PatientT
WHERE (((PatientT.PDeseaced)=False))
ORDER BY PatientT.PLastName;

- When the form opens with 1 of the menus, it works like a charm.
- When is opened on the second menu it works but I detected an issue.

I need to get rid of the WHERE clause, so that it displays ALL the records, no matter what, I found this thread here:
WAF Link
and followed the example @arnelgp provides in answer #3, seemed the most obvious for my specific case, so I did this:
1 Dim a variable strFilter as String
2 create the following SQL statement:

strFilter = "SELECT PatientID, PLastName, PFirstName, PDOB, PPhone, PDeseaced FROM [PatientT] ORDER BY PLastName;"

3 Apply the code:

Me.RecordSource = strFilter

All of the above from 1 to 3 is done in Form_Load
but Nothing happens..

What am I doing wrong?
Maurice..

 

[DHookom]

I would change the SQL property of a saved query using a generic function.

Function fChangeSQL(pstrQueryName As String, strSQL As String) As String
[green]'=============================================================
' basQueryDefs.ChangeSQL
'-------------------------------------------------------------
' Purpose : update the SQL property of a saved query
' Copyright: Duane Hookom
' Author : Duane Hookom
' Notes :
'-------------------------------------------------------------
' Parameters
'-----------
' pstrQueryName (String) Name of saved query
' strSQL (String)        SQL Statement
'-------------------------------------------------------------
' Returns: the previous SQL statement
'-------------------------------------------------------------
' Revision History
'-------------------------------------------------------------
' 07-09-2001 DKH:
'=============================================================
' End Code Header block[/green]
    Dim db As DAO.Database
    Dim qd As DAO.QueryDef
    Set db = CurrentDb
    Set qd = db.QueryDefs(pstrQueryName)
    fChangeSQL = qd.SQL
    qd.SQL = strSQL
    Set qd = Nothing
    Set db = Nothing
End Function

 

[arnelgp]

i think what you need is Allen Browne's multiple form instance.
see this demo that implements this method.

 

[mloucel]

i think what you need is Allen Browne's multiple form instance.
see this demo that implements this method.

So instead of using the query for the form, I have to change that to the DB and do the SQL on the open using OpenAClient, this way I control the SQL and the form will open depending on the SQL I build for that instance..
Genius..
Thanks @arnelgp

@arnelgp :
I am using a routine on Form_Load, now when the code Executes and tries to open the form I got this error, which I assume has to do because the form somehow is not open, hence the error:

Clicking debug takes me to the first line:

I removed that line [REM], but keep getting errors, this time on the GotoForm=me.OpenArgs
I actually did define OpenArgs in the function OpenAClient:

    Set frm = New Form_SearchPatientF
    With frm
        .RecordSource = strRecordSource
        ' optional Modal, set to True if Modal form
        .Modal = True
        .Visible = True
        .OpenArgs = 2
    End With

I really don't know what to do at this point.. Sorry..

 

[gemma-the-husky]

You could change the query to add a clause that switches the optional items on or off, depending. Then you wouldn't need to change the query.

Just set a public Boolean variable to say yes/no to include or suppress certain rows.

 

[mloucel]

You could change the query to add a clause that switches the optional items on or off, depending. Then you wouldn't need to change the query.

Just set a public Boolean variable to say yes/no to include or suppress certain rows.

Thanks for the idea, but I am a newbie, I really don't know how to do what you are suggesting.

 

[DHookom]

I rarely include criteria in a form or report recordsource particularly if they might be dynamic. The Where parameter is much more flexible and can be omitted entirely.
[usual web prefix] learn.microsoft.com/en-us/office/vba/api/access.docmd.openform

 

[gemma-the-husky]

Thanks for the idea, but I am a newbie, I really don't know how to do what you are suggesting.

I would have thought it was simpler than trying to dynamically change a query at run time personally.

It's much easier to test as well.
.

 

[mloucel]

I would have thought it was simpler than trying to dynamically change a query at run time personally.

It's much easier to test as well.
.

Yep, I am trying to use the same form for 2 different purposes instead of creating 2 forms which I believe at this point I will have to do.
In my menu I need the form for 2 purposes as I said:
1) be able to edit the records of PatientT regardless if the patient is marked as deceased or not.
2) be able to pick 1 patient and send the info to another form that creates what we call an AUTHORIZATION.

in my MainMenu [MenuF] there are 2 different buttons to do each action, in each button I use OpenArgs to be able to perform each action, if OpenArgs is 1 then I know is to Add an Auth or 2 is to Edit a patient:

DoCmd.OpenForm "SearchPatientF", acNormal, OpenArgs:=1 ' Add an Auth, based on the chosen Patient

DoCmd.OpenForm "SearchPatientF", acNormal, OpenArgs:=2 ' EDIT Patient Record..

then the Form_Load of the form do the following:

Private Sub Form_Load()
    ' Disabled or Enabled the ShortCut Menu
    Forms("SearchPatientF").ShortcutMenu = TempVars("SCM").value
    ' GoToForm is a Public INTEGER variable
    GotoForm = Me.OpenArgs
    
    Dim RecordN As Double
    ' Need to change this since we might no longer use the Query
    RecordN = DCount("*", "qryPatient")
    Me.RecordsNumber.Caption = "Records: " & vbCrLf & Format(RecordN, "###,###")
    
    ' Code and Ideas from Colin Reddington in various Modules
    ReSizeForm Me
    CenterMe Me
    UISetRoundRect Me, 25, False
    DoEvents
    
    ' Code by Arnelgp AWF
    If GotoForm = 1 Then
        Me.ForWhatLabel.Caption = "to add Authorization"
        Me.OpenPatientbtn.Visible = False
        Me.AddAuthButton.Visible = True
        Me.NewPatientBtn.Visible = False
    Else
        Me.ForWhatLabel.Caption = "To edit patient record"
        Me.AddAuthButton.Visible = False
        Me.OpenPatientbtn.Visible = True
    End If
End Sub

so after a long talk with my sanity, I will do 2 queries and 2 forms, attach each form to each different query, problem solved.
YES is horrible and bad practice, and I could be walking the plank for the sacrilege, but I am a newbie, one day I will solve each problem but in the meantime, like the songs says..
Life must go on..

 

[gemma-the-husky]

Personally, I think I would have two different forms, rather than try to manipulate one form to do different things. It must be easier to do it that way, especially if you are learning Access.

 

[mloucel]

Personally, I think I would have two different forms, rather than try to manipulate one form to do different things. It must be easier to do it that way, especially if you are learning Access.

Just what I am doing at this point, and I found another small mistake, some lines in my code are calling the Table instead of the query, thanks for the support, I am learning, your words are a touch of wisdom for my bag of tricks.

 

[Cotswold]

Whether I should or I shouldn't, I will often set the RecordSource in the open event of a form using IF or CASE.
In fact only yesterday I did that on a Subform and used FormName.OpenArgs as a parameter in a query. But have to admit that I do like Arnelgp's suggestion and the one from DHookom is a good reminder to bear in mind.
The nice thing about programming is that there is often more than one way to do the job,