Docmd.openform Problem

[Belimisimus]

Hi!

On load event of "tasks" form I have code

If gbl_GrupID = 2 Then
cbo_AssignedTo.Enabled = False
Form.RecordSource = "SELECT Tasks.* FROM Tasks, WHERE AssignedTo = " & gbl_ContaktID & ";" 
End If
End Sub

gbl_GrupID is the global variable which holds the Current Group and gbl_ContaktID is the global variable which holds the Current User who is logged on.


On some other form I have open command near every name of task with following code:

Private Sub cbo_Open_Click()
DoCmd.OpenForm "Tasks", , , "TaskID = " & Me.TaskID, , acDialog
End Sub

Everything is working fine untill somebody from gbl_GrupID = 2 tries to open task via that button. It opens form but not that task.
For some reason something is happened in Form.RecordSource code on load event. If I delete that row everything is working fine!
Is there a simple solution for that?

[NeoPa]

Remove the comma (,) from before the WHERE clause in the SQL. This tells SQL to treat the word WHERE, and all that follows it, as another record source.

[Belimisimus]

Thanks for reply! I was away this days, so sorry it took so long to answer.


That was my spelling mistake, there is no comma before the where clause. Do you have any other suggestion?

[NeoPa]

When posting any code on here please :

1. Ensure you have Option Explicit set (See Require Variable Declaration).
2. Try to compile it. If it doesn't compile for any reason please explain that clearly - including the error message and which line of your code it appears on. Compilation is done from the Visual Basic Editor menu - Debug \ Compile Project (Where Project is the actual name of your project).
3. Copy your code (using the Clipboard - Cut / Copy / Paste) from your project directly into your post. Typing in code is not appreciated as it is likely to introduce typos which cause members to waste their time unnecessarily.
4. Ensure that the code in your post is enveloped within CODE tags (For more on this see BB Code List). The hash (#) button in the posting page helps with this. Simply select your code and click on the hash button to have it enveloped automatically.

If all these points are covered then all members will be better able to understand, and therefore attempt to answer, your question.

[Belimisimus]

Sorry for the misunderstanding

I tried to find solution but in vain. All I find out is that when on load event you specify Form.RecordSource the value of TaskID for whatever reason become empty.

I have even try to make a new simple database with one table (TaskID, Task name) and two forms. In first form (Task1) I add on load event code:

Private Sub Form_Load()
    Form.RecordSource = "SELECT Tasks.* FROM Tasks;"
End Sub

And in second form (Task2) I add on "Task name" field dbl click event code :

Private Sub Task_Name_DblClick(Cancel As Integer)
    DoCmd.OpenForm "Tasks1", , , "TaskID = " & Me.TaskID, , acDialog
End Sub

So if I dbl click task name in second form, this task should be open in the first form. But it doesn't. Obviously something is missing and I can't figure what is it. I'll marry anyone who suggest the solution :)

[NeoPa]

@Belimisimus
That sort of enterprising effort is always appreciated.
@Belimisimus
Threats! We can't allow that sort of thing here :D

Now, as you've clearly made some effort here I'll certainly have another look and see what I can come up with. To be honest, I struggled to come up with a solution, but I saw it eventually. I'm afraid it may only be the problem in your new database, but if the old one is designed in a similar way then it would be a problem there too.

Having the RecordSource set in the OnOpen event procedure will override any criteria set in the call to open the form. Thus, your WhereCondition criteria would be ignored, or more correctly removed as soon as it had been applied. The criteria is set first, then the whole RecordSource is changed to the SQL without any criteria. Does that make sense?

The solution is simply to design the form in the first place to use [Tasks] as its RecordSource. No need to change it when it is opened. No need to marry me (I'm married already, and old, and a man too. Not marriage material).

[Belimisimus]

The solution is simply to design the form in the first place to use [Tasks] as its RecordSource. No need to change it when it is opened. No need to marry me (I'm married already, and old, and a man too. Not marriage material).

But that way how can I change record source depend on user? I have global variable which recognize every user and all users should be able to open form with different record source. See code: (this time without editing :) )

Private Sub Form_Load()
        If gbl_GrupaID = 2 Then 'Grafika
            cbo_AssignedTo.Enabled = False
            Command_AssignedTo.Visible = True
            cboGoToContact.RowSource = "SELECT Poslovi.PosloviID, Poslovi.[Ime posla] FROM Poslovi WHERE AssignedTo = " & gbl_KontaktID & " ORDER BY Poslovi.[Ime posla];"
            Form.RecordSource = "SELECT Poslovi.*, tbluVrstaizdanja.[Vrsta izdanja], tbluObrazovanje.Obrazovanje, Izdanja.Razred, tbluPredmet.Subject, Izdanja.Autori, Izdanja.IzdanjeID, Kontakti.ContactID FROM tbluPredmet INNER JOIN (tbluObrazovanje INNER JOIN ((tbluVrstaizdanja INNER JOIN (Izdanja INNER JOIN Poslovi ON Izdanja.IzdanjeID=Poslovi.[Vezano uz izdanje]) ON tbluVrstaizdanja.[Vrsta izdanjaID]=Izdanja.[Vrsta izdanja]) INNER JOIN Kontakti ON Poslovi.AssignedFrom=Kontakti.ContactID) ON tbluObrazovanje.ObrazovanjeID=Izdanja.Obrazovanje) ON tbluPredmet.SubjectID=Izdanja.Predmet WHERE AssignedTo = " & gbl_KontaktID & ";"
        ElseIf gbl_GrupaID = 3 Then 'Uredništvo
            cbo_AssignedFrom.Enabled = False
            Command_AssignedFrom.Visible = True
            cbo_AssignedTo.Enabled = False
            Command_AssignedTo.Visible = True
            cboGoToContact.RowSource = "SELECT Poslovi.PosloviID, Poslovi.[Ime posla] FROM Poslovi WHERE AssignedFrom = " & gbl_KontaktID & " ORDER BY Poslovi.[Ime posla];"
            Form.RecordSource = "SELECT Poslovi.*, tbluVrstaizdanja.[Vrsta izdanja], tbluObrazovanje.Obrazovanje, Izdanja.Razred, tbluPredmet.Subject, Izdanja.Autori, Izdanja.IzdanjeID, Kontakti.ContactID FROM tbluPredmet INNER JOIN (tbluObrazovanje INNER JOIN ((tbluVrstaizdanja INNER JOIN (Izdanja INNER JOIN Poslovi ON Izdanja.IzdanjeID=Poslovi.[Vezano uz izdanje]) ON tbluVrstaizdanja.[Vrsta izdanjaID]=Izdanja.[Vrsta izdanja]) INNER JOIN Kontakti ON Poslovi.AssignedFrom=Kontakti.ContactID) ON tbluObrazovanje.ObrazovanjeID=Izdanja.Obrazovanje) ON tbluPredmet.SubjectID=Izdanja.Predmet WHERE AssignedFrom = " & gbl_KontaktID & ";"
        Else
        End If
End Sub

It is not all in english so that is why I did the editing before... to be more understandable. So there are three kind of groups define with gbl_GrupaID and for each group there is different source.

Is it maybe possible with OpenArgs statement to do something? Yesterday someone give ma a clue but frankly I don't know how to use it.
I tried to add OpenArgs:="No" at the end of dlbclick code and If Me.OpenArgs <> "No" Then at beginning of code in load event. Is that a good start or I'm completely on the second track?

[NeoPa]

To change what data is displayed you would need to set the filter. There are many ways of doing this, but the simplest generally, is to pass a filter value as the WhereCondition parameter of the DoCmd.OpenForm() method. If the value(s) to filter by cannot be known for some reason outside of the form itself (I can't think of an example scenario off the top of my head), then OpenArgs is another way it can be accomplished. With OpenArgs however, it is necessary for the OnOpen() code to handle adding a filter after the form is opened. Perfectly possible, but less straightforward than the WhereCondition approach.

[Belimisimus]

@NeoPa
I really want it to do this my self but is hard without any previous knowledge to understand everything, so finally my friend helped me with this. He add the CStr function in the DoCmd.OpenForm method and with some changes in Onload code its working. Now it's look very simple...but...

Anyway thank you for helping!

[NeoPa]

It's very good to do it yourself. That was good thinking.

However, if you would like to, you can post your code in here and I will check it over for you and possibly suggest some minor alterations if sensible.

[Belimisimus]

of course...

on click event:

DoCmd.OpenForm "Poslovi - Detalji", , , , , acDialog, CStr(Me.cbo_PosloviID)

on load event:

        Dim PosloviID As String
 
        If IsNull(Me.OpenArgs) Then
            PosloviID = "0"
        Else
            PosloviID = Me.OpenArgs
        End If
 
        If gbl_GrupaID = 2 Then 'Graficka priprema
            cbo_AssignedTo.Enabled = False
            Command_AssignedTo.Visible = True
            Form.RecordSource = "SELECT Poslovi.* FROM Poslovi WHERE AssignedTo = " & gbl_KontaktID & " AND ( (" & PosloviID & " = 0) OR (PosloviID = " & PosloviID & ") );"

[NeoPa]

Try this instead :

Dim strPosloviID As String
 
strPosloviID = Nz(Me.OpenArgs, "")
Me.Filter = "([AssignedTo] = " & gbl_KontaktID & ")"
If strPosloviID > "" Then
    Me.Filter = Me.Filter & " AND ([PosloviID] = " & strPosloviID & ")"
Me.FilterOn = (Me.Filter > "")

Code where the variable has the same name as the related field is confusing. str at the front always indicates a string. This (your code or mine) will only work correctly though, if the data passed is numeric in nature. For alphanumeric data line #6 should instead be :

    Me.Filter = Me.Filter & " AND ([PosloviID] = '" & strPosloviID & "')"