DoCmd OpenForm - with WHERE clause not working ? (1 Viewer)

NauticalGent

Ignore List Poster Boy
Local time
Today, 17:29
Joined
Apr 27, 2015
Messages
6,286
Perhaps

DoCmd.OpenForm "Processor Form", , , "processor='ALEX' And status Is Null"

You seem to be getting a lot of mileage from this thread, Paul. This particular post and the OpenArgs examples on your Web site have really made life easier for me.

Grazie mille, mi fratello!
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 14:29
Joined
Aug 30, 2003
Messages
36,118
Prego amico! (sorry for the poor Italian :()
 

MickCun1

Registered User.
Local time
Today, 17:29
Joined
May 21, 2018
Messages
31
Hi,

I am a new access developer. I am trying to have several links in my database with Job Names. Once the user clicks each he can be directed to each jobs information.

My VBA code is this in a procedure that accepts the JobID as a varArg ;

DoCmd.OpenForm strFormName, acNormal, , "[JobID] = " & Me.JobID, acFormReadOnly, acWindowNormal, varArgs

But I get an error message stating 'Invalid use of Me keyword. Any ideas on how to solve this?

Thanks! :)

Michael
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 14:29
Joined
Aug 30, 2003
Messages
36,118
Sounds like you've got the code in a standard module. "Me" refers to the object containing the code (ie form or report), so is invalid in a standard module. You could pass the ID as an argument if you wanted to be able to call this from different forms.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 14:29
Joined
Aug 30, 2003
Messages
36,118
Oh, and if I'm reading that you already have it as an input argument:

DoCmd.OpenForm strFormName, acNormal, , "[JobID] = " & VariableName, acFormReadOnly, acWindowNormal, varArgs
 

MickCun1

Registered User.
Local time
Today, 17:29
Joined
May 21, 2018
Messages
31
Paul, thanks for the quick reply!!

Perhaps I wasn't as informative as I could have been. I have a sub that calls my procedure;

Private Sub cboJobID_Click()
Call OpenJobOverviewPopUp(Me.Job_ID)
End Sub

(Passing the Me.Job_ID as the argument)

Then;

Public Sub OpenJobOverviewPopUp(varArgs As Variant)

Dim strFormName As String

' procedure

'strFormName = "frmJobOverview"
strFormName = "frmFinancialReport"
If IsOpen(strFormName) Then
DoCmd.Close acForm, strFormName
End If
DoCmd.OpenForm strFormName, acNormal, , "[JobID] = " & varArgs, acFormReadOnly, acWindowNormal, varArgs



' /procedure

ErrEx.CatchAll
ErrEx.CallGlobalErrorHandler
ErrEx.DoFinally
Resume

ErrEx.Finally
End Sub


When I placed varArgs in there, it worked yes but it opened a blank form with absolutely no data. I know I am not far away with this but it is just a bit tricky! Ha. Thank you very much for your help!

Michael :)
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 14:29
Joined
Aug 30, 2003
Messages
36,118
The data type of the ID field is numeric? Either set a breakpoint or use this to see what the value is within the function:

http://www.baldyweb.com/ImmediateWindow.htm

Like

Debug.Print varArgs

Plus make sure the Data Entry property of the form being opened isn't Yes.
 

MickCun1

Registered User.
Local time
Today, 17:29
Joined
May 21, 2018
Messages
31
Yea the data field is numeric! Its a 3 digit numeric number.

Thanks that debug property has certainly helped!

The data is being correctly passed it is just coming back as a blank form which is frustrating. Do you know why this could be?
 

MickCun1

Registered User.
Local time
Today, 17:29
Joined
May 21, 2018
Messages
31
I am consistently debugging here it seems to only be the where condition which makes the form open with absolutely no data when I click a jobID in access.
 

Mark_

Longboard on the internet
Local time
Today, 14:29
Joined
Sep 12, 2017
Messages
2,111
You can replace
Code:
"[JobID] = " & varArgs
in your call.

I'd have
Code:
Dim asWhere as String
and use

Code:
asWhere = "[JobID] = " & varArgs
MsgBox "For DEBUG purposes, WHERE is >" & asWhere & "<"
DoCmd.OpenForm strFormName, acNormal, , asWhere, acFormReadOnly, acWindowNormal, varArgs

just to make SURE you are really calling the JobID you think you are calling.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 14:29
Joined
Aug 30, 2003
Messages
36,118
Did you check the data entry property of the form being opened? Regarding the data, what's important is the data type for the field, not the contents.
 

MickCun1

Registered User.
Local time
Today, 17:29
Joined
May 21, 2018
Messages
31
Hi Mark,

Thank you. The message box is displaying the jobID I want. But again unless the form is previously loaded it opens with no data at all. Its very strange how it is not loading the correct data. I am going to trouble shoot all events where when clicked what exact procedure does it follow.

Do you have any other recommendations on how to best debug this issue?

All help is much appreciated.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 14:29
Joined
Aug 30, 2003
Messages
36,118
Again, what is the Data Entry property of the form being opened? What is the data type of the JobID field? Can you attach the db here?
 

MickCun1

Registered User.
Local time
Today, 17:29
Joined
May 21, 2018
Messages
31
Paul,

The data entry property was set to No as you advised.

I figured out a way to display the data. I have a jobOverview tab which displays each job in a list. When you select a job from the list it opens the financial report. I had my code as this then;

strFormNameJob = "frmJobOverview"
strFormName = "frmFinancialReport"
If IsOpen(strFormName) Then
DoCmd.Close acForm, strFormName
End If

Debug.Print varArgs
asWhere = "[JobID] = " & varArgs
MsgBox "For DEBUG purposes, WHERE is >" & asWhere & "<"
DoCmd.OpenForm strFormNameJob, acNormal, , asWhere, acFormReadOnly, acWindowNormal, varArgs
DoCmd.OpenForm strFormName, acNormal, , asWhere, acFormReadOnly, acWindowNormal, varArgs
DoCmd.Close acForm, strFormNameJob, acSaveNo


So it opened the overview, then opened the financial report and close the overview. The time process was a little delayed but it solved the problem for the time being. Do you think this is good practice? I am trying to learn as much of access as possible.

Michael :)
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 14:29
Joined
Aug 30, 2003
Messages
36,118
That shouldn't be necessary. Is it possible the job isn't saved when the code first runs? If so, try this first:

If Me.Dirty Then Me.Dirty = False
 

MickCun1

Registered User.
Local time
Today, 17:29
Joined
May 21, 2018
Messages
31
That me.dirty is again giving me an error stating 'invalid use of the me keyword'.

This whole thing was for when a user logged in to the database. He could click and load his own financial reports. Some background;

- hoping to have about 10 users
- all data is in tblJobCosts
- can then be viewed in the reports

That is why I had read only access when opening them forms.

I am thinking now would it be better to have read access yes, but to filter the data for when a user logs in. At the minute in time I have everything displayed (all users data) but would it be easy to have a select query working in VBA that filtered all data based on the userID when the user logs in? Is this easily done?

E.g. here is my thoughts in some pseudocode (I have a java background! Ha) ;

if(admin(me) logs in)
SELECT * FROM tblJobCosts
else If (user logs in)
SELECT * FROM tblJobCosts WHERE user = userID(users have unique ID's)
End If

I know its more complicated than that but would that be done easily enough in VBA?
 

Mark_

Longboard on the internet
Local time
Today, 14:29
Joined
Sep 12, 2017
Messages
2,111
Is your report referencing any controls on your job overview? If not, you should not need to open it first.

As to your call,
Code:
DoCmd.OpenForm strFormNameJob, acNormal, , asWhere, acFormReadOnly, acWindowNormal, varArgs

What are you doing with varArgs in your form? I gather it is ONLY the jobID? If so, you can omit it from your command as it is optional.
 

MickCun1

Registered User.
Local time
Today, 17:29
Joined
May 21, 2018
Messages
31
Hi Mark

No the report references nothing from the jobOverview. the reports are accessed form the Job Overview. then you just close the report once done with it.

I know the varArgs is optional I just included it for best coding practice.

Do you think my last comment of changing the data depending on what user logs in is a viable avenue to explore?
 

Mark_

Longboard on the internet
Local time
Today, 14:29
Joined
Sep 12, 2017
Messages
2,111
OK, so varArgs is just based on your style.

Have you tried using DLookup to verify the record exists prior to opening the report? Much like Paul, my guess would be the record either does not exist OR you are not passing it the right value.

When you display your where clause, are you verifying you don't have extra characters in there? I like putting values into a string FIRST before handing them over to SQL to make sure I'm not mixing variables incorrectly. If JobID is defined as a STRING you will need to enclose it in quotes.

For filtering all of your data based on user, there are a lot of ways to go about it. Totally doable (if you have user info in the tables you are dealing with) and you can do some searches on here for how others have implemented security do so this.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 14:29
Joined
Aug 30, 2003
Messages
36,118
The line to save would be in the form code before calling the function, not in the function.
 

Users who are viewing this thread

Top Bottom