By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,482 Members | 2,411 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,482 IT Pros & Developers. It's quick & easy.

Docmd.openform Problem

Belimisimus
P: 18
Hi!

On load event of "tasks" form I have code

Expand|Select|Wrap|Line Numbers
  1. If gbl_GrupID = 2 Then
  2. cbo_AssignedTo.Enabled = False
  3. Form.RecordSource = "SELECT Tasks.* FROM Tasks, WHERE AssignedTo = " & gbl_ContaktID & ";" 
  4. End If
  5. 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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub cbo_Open_Click()
  2. DoCmd.OpenForm "Tasks", , , "TaskID = " & Me.TaskID, , acDialog
  3. 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?
Mar 26 '10 #1
Share this Question
Share on Google+
11 Replies


NeoPa
Expert Mod 15k+
P: 31,186
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.
Mar 26 '10 #2

Belimisimus
P: 18
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?
Mar 29 '10 #3

NeoPa
Expert Mod 15k+
P: 31,186
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.
Mar 29 '10 #4

Belimisimus
P: 18
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Load()
  2.     Form.RecordSource = "SELECT Tasks.* FROM Tasks;"
  3. End Sub
And in second form (Task2) I add on "Task name" field dbl click event code :

Expand|Select|Wrap|Line Numbers
  1. Private Sub Task_Name_DblClick(Cancel As Integer)
  2.     DoCmd.OpenForm "Tasks1", , , "TaskID = " & Me.TaskID, , acDialog
  3. 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 :)
Mar 30 '10 #5

NeoPa
Expert Mod 15k+
P: 31,186
@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).
Mar 30 '10 #6

Belimisimus
P: 18
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 :) )


Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Load()
  2.         If gbl_GrupaID = 2 Then 'Grafika
  3.             cbo_AssignedTo.Enabled = False
  4.             Command_AssignedTo.Visible = True
  5.             cboGoToContact.RowSource = "SELECT Poslovi.PosloviID, Poslovi.[Ime posla] FROM Poslovi WHERE AssignedTo = " & gbl_KontaktID & " ORDER BY Poslovi.[Ime posla];"
  6.             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 & ";"
  7.         ElseIf gbl_GrupaID = 3 Then 'Uredništvo
  8.             cbo_AssignedFrom.Enabled = False
  9.             Command_AssignedFrom.Visible = True
  10.             cbo_AssignedTo.Enabled = False
  11.             Command_AssignedTo.Visible = True
  12.             cboGoToContact.RowSource = "SELECT Poslovi.PosloviID, Poslovi.[Ime posla] FROM Poslovi WHERE AssignedFrom = " & gbl_KontaktID & " ORDER BY Poslovi.[Ime posla];"
  13.             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 & ";"
  14.         Else
  15.         End If
  16. 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?
Mar 31 '10 #7

NeoPa
Expert Mod 15k+
P: 31,186
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.
Apr 8 '10 #8

Belimisimus
P: 18
@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!
Apr 14 '10 #9

NeoPa
Expert Mod 15k+
P: 31,186
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.
Apr 14 '10 #10

Belimisimus
P: 18
of course...

on click event:

Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenForm "Poslovi - Detalji", , , , , acDialog, CStr(Me.cbo_PosloviID)
on load event:

Expand|Select|Wrap|Line Numbers
  1.         Dim PosloviID As String
  2.  
  3.         If IsNull(Me.OpenArgs) Then
  4.             PosloviID = "0"
  5.         Else
  6.             PosloviID = Me.OpenArgs
  7.         End If
  8.  
  9.         If gbl_GrupaID = 2 Then 'Graficka priprema
  10.             cbo_AssignedTo.Enabled = False
  11.             Command_AssignedTo.Visible = True
  12.             Form.RecordSource = "SELECT Poslovi.* FROM Poslovi WHERE AssignedTo = " & gbl_KontaktID & " AND ( (" & PosloviID & " = 0) OR (PosloviID = " & PosloviID & ") );"
Apr 16 '10 #11

NeoPa
Expert Mod 15k+
P: 31,186
Try this instead :
Expand|Select|Wrap|Line Numbers
  1. Dim strPosloviID As String
  2.  
  3. strPosloviID = Nz(Me.OpenArgs, "")
  4. Me.Filter = "([AssignedTo] = " & gbl_KontaktID & ")"
  5. If strPosloviID > "" Then
  6.     Me.Filter = Me.Filter & " AND ([PosloviID] = " & strPosloviID & ")"
  7. 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 :
Expand|Select|Wrap|Line Numbers
  1.     Me.Filter = Me.Filter & " AND ([PosloviID] = '" & strPosloviID & "')"
Apr 16 '10 #12

Post your reply

Sign in to post your reply or Sign up for a free account.