Replies

Hans

Thanks for the object browser tip. The book by Ken Getz is apparently out of print at the moment according to Amazon UK and WH Smith’s web site. Although I’ve managed to find all the parts of the Access 2000 and VB language set, and will be ordering them as soon as I check out the vendor(s).

One last question, does it have the sort of reference in the VBA developers section that I’m after? Reading the reviews here and elsewhere I’m going to find it useful regardless, but it would be nice if I knew my head ache cure was on its way.

Thanks

Ian

Hans

Thanks, made the suggested improvements. I had a look for redemption and found it, thanks for that.

Ian

Hans

Thanks for the comments, I’ve had another look at the code after thinking about your questions and here’s the modified version, no more error message it still makes me click the send button ‘x’ times though. Yet again my sanity is slowly returning.

Private Sub sendemail_Click()

Dim dbs As DAO.Database
Dim rstfolloup As DAO.Recordset

Set dbs = CurrentDb
???Set rstfolloup = dbs.OpenRecordset(“folloupemailqry”, dbOpenDynaset)??? not sure if this should still be there, but have left it

DoCmd.Echo False, “Access is sending your emails”

If Not IsNull(Me.quotenum) Then
With Me.Recordset
DoCmd.GoToRecord , , acFirst
Do Until .EOF
DoCmd.SendObject acSendReport, “folloupemailrepv2”, acFormatRTF, rstfolloup!custcontactemailPL, , , “Quote Follow Up”, “Hello, checking up on this enquiry”
.Movenext
Loop
End With
End If

DoCmd.Echo True, “Access has sent your emails”

End Sub

Thanks for help

Ian

Hans / Charlotte

Thanks. Looks like life is going to remain ‘simple’ and relatively lazy for me then (all that potential interaction with people had me worried )

Thanks again

Ian

Hans

I’ve set all the forms to open with pop up and modal as yes, so the user doesn’t get the option to modify the form’s design (short cut menu removed as well). This has worked OK on the maintenance database I’ve had running for the last 2 years, but the database I’m currently working on will have significantly more users, so that’s why I was worried.

As far as the DAO / ADO reference is concerned, Is it set in the database (.mdb file) itself, rather than in the Access (Access.exe) application on each PC? if I understand this correctly (it’s in the .mdb file) then I’m sorted, any new user wont have to worry about the references for DAO 3.6 in their installation of Access. Other wise it sounds like I’m going to have to learn how to write batch files (not done since DOS 6….).

I’ll keep an eye on the new database once it’s fully implemented in case we do have any corruption problems, but as it’s fairly simple (compared to what some people on here are doing) I hope it will be robust.

Thanks for the help

Ian

Charlotte

I don’t exactly ‘distribute’ the database, it’s split into a front end back end set-up on a server. In the past I’ve used this system and just arranged short cuts on the users PC to the front end (there is usually some one in each department that can set-up a short cut). The beauty of it is that when I make any modifications I only have to update the front end on the server.

The lack of ADO / DAO reference is probably due to the trouble I had getting the database to convert from 97 to 2000, the other two systems converted with very little hassle (some spurious code was ‘invented’ some how, but easily tracked down and removed). The database in question would’nt even allow me to open it, security problems, so the only way I could save the work was to import it all into a newly created Access 2000 database, I guess this is why there were no references to DAO set.

If this is ‘bad practice’ (having one copy on the server) let me know, please, as I’ve got three databases running in the company with the same set-up.

As for libraries full of DAO code, sorry, my library only has books in it….. I’m new to VBA (last few weeks) and only know what I’ve learnt from ‘the idiots guide’ and, more usefully, the very nice people on here like HansV and your good self.

Thanks for the repsonse

Ian

Hans

Thanks, this is exactly what I thought was possible but couldn’t figure out how to do.

Your comments on the ‘VBA reacting to error messages’ is also what I was on about. If it hadn’t been possible to check the files in Excel then I had hoped to use the error message number to generate a text box saying ‘shut excel please’ or similar.

So, two out of two.

Thank You very much Hans.

Ian

Hello All

Sorted it out, tried the CDbl variant on the CLng function Charlotte suggested, wrapped it around the Nz function for the two counts, query now appears to be working fine (just got to count a few thousand records to be sure ).

Here’s the final working SQL in case it explains anything to some one who’s brighter than I am.

SELECT percentwon.Month, percentwon.Year, Max(CDbl(Nz([percentlost]![CountOfwonlost],0))) AS Lost, Max(CDbl(Nz([percentwon]![countofwonlost],0))) AS Won, Max(CDbl(Nz([percentongoing]![totalongoing]))) AS [Total Ongoing], [Lost]+[Won]+[total ongoing] AS Total, [won]/[total] AS Percent_Won, [lost]/[total] AS [Percent Lost], [total ongoing]/[total] AS [Percent Ongoing]
FROM percentongoing RIGHT JOIN (percentwon LEFT JOIN percentlost ON percentwon.Month = percentlost.Month) ON percentongoing.Month = percentwon.Month
GROUP BY percentwon.Month, percentwon.Year
ORDER BY percentwon.Year, percentwon.Month;

Thanks again for the help and support.

Ian

Charlotte

Thanks for the reply, just checked my dictionary, and yes, the query is concatenating (sp??) the two values. Here’s the SQL for the query in question, it has thrown me as I already totally believed that any count would be a numeric value, from experience. To suddenly find that Access has the ability to concatenate two values, without me having to learn a lot of SQL or VBA is rather surprising

SELECT percentwon.Month, percentwon.Year, Nz([percentlost]![CountOfwonlost],0) AS Lost, Nz([percentwon]![countofwonlost],0) AS Won, [Lost]+[Won] AS Sum, [won]/[Sum] AS Percent_Won, [lost]/[Sum] AS [Percent Lost]
FROM percentwon LEFT JOIN percentlost ON percentwon.Month = percentlost.Month
ORDER BY percentwon.Month, percentwon.Year;

I’m going to have a ‘play’ with the functions you suggested, just to see what they can do for me, and find out how to make them work with the Nz function.

Is there a full list of functions available from the Access help, with a brief overview? I seem to remember seeing something like this, but while I’ve been looking for it recently can’t find it. I ask as most of my ‘problems’ have simple solutions, once some one who knows points me to a certain fuinction.

Thanks for the help so far.

Ian

Charlotte

Thanks, the advice worked fine, I now have ‘0’ showing in the fields that were previously null. Unfortunately, the calculation field is not adding the two numbers together, it’s combining them, for example won = 1 lost = 2 calculation field is set to won + lost the result is 12. Do I need to use the ‘format’ command to define the Nz fields as numeric? The original data being counted is text, but before I used the Nz function the counts added up correctly where there were two values.

I’m living up to my user name on this one….

Thanks again for the help so far.

Ian

Hans

Thanks for the reply. I didn’t make my problem very clear I’m afraid. The problem occurs where there is a month with, for example, a won value and no lost value. This will return a record for the won but a blank field for the lost, I was hoping for a zero. The problem with the blank field is that when I try to add the ‘won’ count to the ‘lost’ count I get a blank field if either ‘won’ or ‘lost’ is a blank field.

For now I’ve got around the problem by using a third value ‘ongoing’, this will work for the system I’m working on at the minute, but I’d like to know if there is a ‘proper’ solution to my difficulty. I’ve checked out the web site with the working days calculation code on it, but they don’t seem to have a solution, or I’m asking the search engine the wrong question, same as on here, loads of irrelevant answers or none at all

Again, thanks for the suggestion, I’ve learnt how to link queries in SQL so that’s a plus on this morning.

Ian

You could link the excel spread sheets to your Access database, this way they are always up to date. Then set up an append query to add the new records of your choice to the main datatable. All this is very easy to do.

HTH

Ian

OK, I didn’t explain what I was talking (typing ) about very well. What I want to do is set the string expression for the ‘to’ argument to a field in a query (more likely to use the ‘bcc’ argument though, to avoid every one knowing who else got the email). So, will it use every email address in the query field, or do I need to write some code to make Access step through all the records in the query?

Sorry about the poor original question.

Ian

Hans

Once I’d taken the time to tully understand what was happening in the code I replaced the ‘<' with a less than sign, presto it worked. In the long term it's better than just copying the code and only partially understanding what it does.

The Access on line help, is this on the Microsoft site? or are you referring to the help file off the menu in Access. If the latter, then I've read this and it makes great sense if you want to do something very simple, maybe I'm looking at the wrong part of the problem with my views of Do …. Loops and the Dummies book, my problem may be with what can be done while inside the actual loop, think I need to get a function reference from somewhere….. ://ambles off with steam coming out of ears//:

Dave

Thanks for the module, I'll have a look through it, but the code from the other Access site helped me figure out what was happening and once I'd corrected a few typos (I typed it in myself in order to try and get the methodology to sink in) it ran fine the way you told me to set it up.

Hans / Dave

Thanks, I’m ‘back from the brink’ as it were, tried the code and had a problem with the sample posted by Hans, it did not like the ‘&lt’ bit at all (Access 97??), but once I’d checked out the other samples that were available from the links I figured out a way to make the thing work, but not why it wouldn’t, what does the ‘&lt’ do in the code, I can’t really find any refernce to it in the material I have, other than & as a way of setting values (no suggestion as to what value ‘lt’ is if that’s the answer).

I now have a sample query and a sample form that will produce the number of working days between datein and dateout. I’m ignoring the holiday bit as the database is for our Sales, and international customers don’t all have the same public holidays as the UK. If I start to get ‘over confident’ I’ll replace the current code with the set that allows for the use of a holiday table and put Christmas and Boxing Day in there….

As the Do … Loop is very poorly covered in ‘Access 97 programming for dummies’, what book should I get next? (Dummies is about my current level of ability though)

I’m off to celebrate with a stiff coffee, thanks again.

Ian