Replies

You need to tell Access all the possible values in WeeksNotice; so Access can effectively reserve a column for them, even if there is no data. Change your SQL to: “…PIVOT WeeksNotice IN (1,2,3,4,5)”

THIS IS GREAT! Thank you, after reading your suggestion to Christopher, I tried it, and also found out that you can also accomplish the same by putting values, separated by commas, in the field properties for the column heading grouping, in the “column heading” property while in the design grid rather than SQL window — magical!
Pat

Thanks, John. I agree with you that it is visually easier to store long message string to a variable, which I would definitely do if it were my code. Thanks for your note on the efficiency & of your preferred style.

Pat

On your toggle button — or, perhaps, from a dropdown combo selector — check the value of the toggle or the dropdown, and change the record source of your subform:

Forms![YourParentForm]![YourSubformName].Form.RecordSource = “Select * from YourOriginalQuery where ToggleField = ” & YourJust-SelectedToggleValue
…. and then re-query the subform’s recordsource: Forms![YourParentForm]![YourSubformName].Form.Requery

If this isn’t what you’re looking for, why don’t you post a bit of your code.

Pat

What does the “xxxx” stand in place of?
I don’t remember this, as I usually re-learn it only when I have to, but can you paint the highlight via report code in the “on format” or “on print” events?
Pat

Well, just for starters, I wouldn’t put it in an “on current” module, because — won’t you want someone to be able to make a selection, first? Unless I’m not understanding what you are trying to do.

So — have you put a breakpoint before the line: “sTemp = Nz(Me!mySelections.Value, ” “)” to make sure it’s choking there (and, is this the multi-select list field which will probably be null in the “on-current” event?)

I would start by moving the code to a button, and then just double-check the name you gave the field.

Pat

Hi, Judy,
If it were me, I’d skip the intervening field declarations, and just stick the fields in directly — does anybody have a comment on this, and if it is more efficient?

Instead of:
DoCmd.SendObject acSendReport, “rptEmailAllSchoolsWithProf”, acFormatSNP, Employee, , , “Attached ESOL Roster for ” & [Location] .. etc
I’d put:
DoCmd.SendObject acSendReport, “rptEmailAllSchoolsWithProf”, acFormatSNP, rs!EmailAdd, “Attached for ” & rs!SchName .. etc

Actually, I would probably be wrapping quotes around the fields, as in ” & chr(34) & rs!EmailAdd & chr(34) & “Attached … but I see (after looking at the docmd.sendobject) that the parameters it receives are variants, so I don’t know if that’s necessary or more efficient.

Does someone know if it is better to replace declared fields for real fields in the above report? Would my method work? (I am assuming so..)
Also, Judy — I don’t see anything happening with assigning something to “gstrEmployeeName,” possibly that is included in the sendobject somewhere?

thx
Pat

I do something (originally stolen from MSAccess help files) commonly on a form I create originally with all the field names. Then, I create the field names in a fixed format (prefaced by “qbf_” for instance). Then I remove the datasource — but the fields are all still named, and drop-down selections are still there, and other things are in place.

You will have to get fancier to look at the field type in building your code, but, basically, to scroll through all the fields in a particular form:

Dim actualFldName As String
Dim frm As Form, ctl As Control, intI As Integer

Set frm = Forms!WhateverYourFormNameIs
For intI = 0 To frm.Count – 1
Set ctl = frm(intI)
If Left$(ctl.Name, 3) = “qbf” Then
If IsNull(ctl) Then
Else
actualFldName = Mid$(ctl.Name, 4)
… and then you build your criteria string using the actualFldName and the value, and whether you will use the “like” keyword or “=” or — I think in your case, you are just looking for if some information exists. Then you close all this out and set your objects to nothing, of course.

Hope this helps!
Pat

My question would be, although it’s not the likely culprit, but does this school system have occasional network issues and slowdowns? (I don’t see how that would delete the data just typed, however.)

And the problem seems to be restricted to just one user.

The reason I bring this up, is, if the user does not have the requisite permissions on the folder that contains the report, that may prevent them from deleting the old and/or creating a new file. However, if a file with the correct file name is created, then — that can’t be the issue. Another thing is if they have a full version of Access and somehow managed to change something that has fixed the filter to a certain ID and now cannot change it — I’m just grasping at straws, but since it affects only one user, it makes it less likely that there is an error in your code and more likely it’s something in the user’s environment.
Pat

Another question, is, is it happening to other users, if any other users run this module.

4) Another form in the same DB with conditional formatting (and many of the same expressions) DOES work!

Joe,

I’m not one of the experts here, but often when something like this happens (and it has happened to me), the usual thought is that the form (or database) has gotten corrupt or something.

The usual suggestions are to create a fresh database, and drag all your forms, reports, etc. to that new database, or “decompile and recompile” (just search on “decompile” in threads here, and you will find lots of info), or, sometimes it just helps to plain rebuild the form in question. I have re-created a report and form or two, and also dragged stuff into a new, fresh database (and had the issue resolved). De-compiling gets rid of code that you may not be able to see, but which just didn’t get removed during the process of editing and re-editing, etc.

Hope this helps.

thx
Pat

This problem happens (rarely, but occasionally) to us on a network, and the more I type this, the more wrong I think I must be in how it actually occurs — but let me describe what I was first going to write, although now I’m not sure it works this way:

Users A, B, and C have “modify” privs on the parent folder, where the database resides. These users have read & write privs to the database.
Someone adds user D to have read privs on the database — and also neglects to add them with “modify” privs to the parent folder (usually, giving them “read”).
That means that when a user A, B, or C opens the database, and then D access it, and then A, B, or C closes it, then when D is left, there privs don’t allow them to delete the lock file, and subsequent users, even with modify privs, only seem to get read-only access the database. — Once again, I am not sure now if the scenario I just painted is what actually occurs, but I know I solve the problem by deleting the lock file (and making sure all authorized users have “modify” privs to the folder, which makes locking down the rest of the folder a hassle, but there it is).
thx
Pat

Hello — I felt certain that I posted something about this earlier, but I can’t find it, maybe I didn’t. I found that I got this in one or two databases. I can’t remember if I remade or dragged the query or queries into a new database — and that solved the issue. In my case, I don’t think it was an issue with nulls, or anything. It was definitely a fluke — and if you search the web, you will see that there are a couple of others out there who have experienced this flukiness. — thx, Pat

. Thanks, Ken, I’m glad you got it working.
. There were really two parts to this question — the SQL connect and exec, and the actual build of the string.
. As far as the sql connect & exec, there are variations on how it can be done, and it was informative for me to see how you did it.
. As far as the string build goes, however, I think you could use some practice in just figuring out how to build that. A good, basic way to start learning that is to create code in a form which opens a report based on a large recordset, which you will limit by sending it a criteria or “where” clause in the “docmd.openreport” VBA call, based on selected information on the form (or, the current record on the form). Building a “criteria” string for your “where” clause, would have given you the knowledge to build the string that sent the parameters to the stored procedure — they are both based on the same formatting principles.
. I would urge you to search on the word, “criteria,” in Access help — but keep in mind that sending out search strings in Access are slightly different than in MSSql server — dates and text accept single quotes in SQL, and in Access, the date fields take “pound” signs and text fields take double quotes. None of the help files in Access use my formula (chr(34 or 39)), but you’ll get the picture. Once you’ve mastered sending criteria out, then, you are freer to concentrate on the other issues (like, making the connections to your sql server).
. Don’t neglect your local Access education opportunities. There is usually some place offering Advanced-level classes, and these instructors are often available to help on specific issues you might have.
Pat

Ken — it’s possible that the query window is more forgiving. I know from experience here, that sometimes happens.

I am not that much of a virtuoso with connection code, and like so many things, there are a lot of ways to do it. I don’t know if there are other issues with your code, but will just focus on the string you send to your stored procedure.

Here is a stored procedure, and what the procedure expects to get:
CREATE PROCEDURE PatsProc @StartDate as datetime, @EndDate as Datetime

Here’s a modification of what I already sent you, to send information to that stored proc. I have already created the connection (PatCn), and want to return a recordset: Set PatSet = PatCn.Execute(“EXEC ” & Chr$(34) & WhatProc & Chr$(34) & ” ” & Chr$(39) & StartDt & Chr$(39) & “, ” & Chr$(39) & EndDt & Chr$(39))

You are including proc variables in the string in the form of “@StartDate =”, which I am sure is a good way to do it, I am just sending the variables serially.

(All of a sudden, I don’t see the stored procedure ‘SiteDyncorpUpdate ‘ you are calling in the code below — that might be a big part of it … )
Since you have the actual name of the procedure, you don’t need a string wrap for that:

I recommend changing your strSQL from “EXEC @SID = ‘Ken 777’ , @S_Pkey = 1” To:
“EXEC SiteDyncorpUpdate @SID = ” & chr$(39) & “Ken 777” & chr$(39) & “, @S_Pkey = 1” (if S_Pkey is a number) or
“EXEC SiteDyncorpUpdate @SID = ” & chr$(39) & “Ken 777” & chr$(39) & “, @S_Pkey = ” & chr$(39) & “1” & chr$(39) (if S_Pkey is a string)

Or, you could simply try “EXEC SiteDyncorpUpdate ” & chr$(39) & “Ken 777” & chr$(39) & “, 1”

You’ve got to explicitly show strings. Instead of the “chr 34 or 39” that I like to use, you could pile on the quotes ( “”‘ or “”” — or ” ” ‘ or ” ” ” ) — but I have never opted to go that way, so I do not know if I got that correct. Also, you can probably drop the “$” sign, it’s “voodoo” on my part. Also, not important for your question, but I also usually wrap the date fields with a “cdate” function.