Replies

Here we go, replying to my own post.
It now works but only if:

I put a control on the form (a text box) whose control source is the ClientID in the main table (tblSites)

I still think I’m doing something a bit wierd. Here’s some more information.

Form: frmSiteInfoDataEntry
Control Source: tblSites

Name: cboClientList
Control Source: ClientID (in tblSites obviously)
Row Source Type: Table/query
Row Source: SELECT [tblClientList].[ClientName], [tblClientList].[ClientID] FROM tblClientList ORDER BY [tblClientList].[ClientName];
Bound Column: 2

Is my problem (is it a problem?) because the row source is from a different table to the control source for the control?

Thanks for all your help.
Right now I’m trying to get to grips with Not In List and will probably be making a post today – I’m beginning to realise just how complex Access is!

Darsha

Again, can’t reply to the latest post, the button seems to be missing.
The code now works but not in the way I expected.
I’m playing with some sample data containing only 3 records. The text boxes on the form default to the min and max date values in the table.
The code to run and filter the report runs from a button on the form.

I am actually usually an option group to change the field used to filter for the report. Hopefully the code below should explain it:

On Error GoTo Err_PrintReports
‘ This procedure used in Preview_Click and Print_Click Sub procedures.
‘ Preview or print report selected in the ReportToPrint option group.
‘ Then close the Print Sales Reports Dialog form.

Dim strSiteName As String
Dim strDateRange As String

strSiteName = “SiteName = Forms![frmReportsDialog]!lstSelectSite”

strDateRange = “CompletionDate Between #” & [Forms]![frmReportsDialog]![BeginningDate] & “# And #” & [Forms]![frmReportsDialog]![EndingDate] & “#”

Select Case Me!fraReportFilter
Case 1
If IsNull(Forms![frmReportsDialog]!lstSelectSite) Then
DoCmd.OpenReport “rptTier1Compliance”, PrintMode
Else
MsgBox (strSiteName)
DoCmd.OpenReport “rptTier1Compliance”, PrintMode, , strSiteName
End If
Case 2
DoCmd.OpenReport “rptTier1Compliance”, PrintMode, , strDateRange

End Select

DoCmd.Close acForm, [frmReportsDialog]

Exit_PrintReports:
Exit Sub

Err_PrintReports:
Resume Exit_PrintReports

End Sub

If I run it, leaving in these defaults I would expect to see all three records but I don’t.
The one with the earliest date is ommitted.

Is my understanding of the Between clause therefore wrong or is the code not doing what it should?

I really appreciate your help with all of this. I’m learning so much right now.

Futher food for though:
Would it also be possible change the way the report is sorted in the same block of code acording to the option chosen. i.e if all Sites are printed (case 1) sort in alphabetical order, if by date range (case range) sort by ascending date order.

Darsha

Even with my limited knowledge, your first comment is just what I was expecting to hear.
Creating fairly simple databases I am beginning to rely heavily on the features built in to Access. I couldn’t imagine trying to recreate the functionality in VB (mind you, my VB knowledge is less than basic!)

Again, the second comment refering to front and back ends was not a surprise. I have a difference of opinion with a colleague so its usefull for me to get feedback from an experienced developer (particularly when I agree with them).

Regarding the Citrix issue, my task is trying to fix a particular database which is causing problems.
My personal belief is that the problem lies in the architecture of the application. We have a number of users trying to use the thing at the same time, pre-Citrix I’m pretty certain it was never used like this.

Unfortunately, for all its perceived faults, the coding is way beyond my understanding right now.
What I do know it that data is gathered with a series of wizard style forms, compared to other tables to produce scores (or rankings) and then committed to the database. I’m sure this is twhen a conflict occurs.

If I ever get this sussed it’ll be a miracle.

Darsha

For some reason I couldn’t reply to your other post.

I have tried the code
strDateRange = “CompletionDate = Between #” & [Forms]![frmReportsDialog]![BeginningDate] & “# And #” & [Forms]![frmReportsDialog]![EndingDate] & “#”

which produces the string

CompletionDate = Between #12/02/1999# And #12/02/2002#

But the report won’t preview. Something is happening as the cursor changes to the egg timer but no report. Also the line below the DoCmd for print does not run.

Having trouble manipulating strings for the filter.

In code I have used
strSiteName = “SiteName = Forms![frmReportsDialog]!lstSelectSite”
and then
DoCmd.OpenReport “rptTier1Compliance”, PrintMode, , strSiteName
to select a record by a name in a list box. I’m happy with the sql for this but I’m getting confused when I need to get a bit more complex.

I’m trying to set the where clause for the string strDateRange in the line below
DoCmd.OpenReport “rptTier1Compliance”, PrintMode, , strDateRange

I’ve used a parameter query in the report record source criteria which is ok but I can’t seem to transfer if to code to set the value for the string strDateRange

I’ve tried strDateRange = “CompletionDate = Between [Forms]![frmReportsDialog]![BeginningDate] And [Forms]![frmReportsDialog]![EndingDate]”

but it doesn’t work and is therefore clearly wrong. I doesn’t work if I only use the value for either BeginningDate or EndDate so I’m guessing I’m way off course here.

Thanks, discovered DMin was actually the function I needed.
I have a question regarding my train of thought of this matter.
Is using text boxes to set a date range for printing the best way to do this, would combos containing actual date values from my table be better? I think the text box option is probably the right way to go but any opinion is helpful.

I figured that was what you meant
Everything is working perfectly now, thanks for your help. Time to tackle the other 101 improvements I need to make!!

Darsha

I don’t believe it! Thankyou.

I have been working a little with with databases for some time now, taking over from a colleague who has now left. I always seem to think things are more complex than they really are. I will try your suggestion tomorrow and let you know how its goes

Thanks, I will try try this but I still need to ensure the query is run only once. Using this code the query could be run again. Somehow I need to check the tables to run the query only where a site has no matching records in the review criteria table. I could use the query wizard to create a query to check for unmatched records (i.e a site with no matching records in the review criteria table). If there are no criteria continue the code and run the query, else exit to sub routine. Trouble is I can’t think how to check if the current site is part of the recordset produced by the unmatched query.

Thankyou
I can’t believe this bit was so obvious. I had the save record line in but it didn’t occur to me I needed to requery the subform.

My code now reads:
Private Sub cmdRunQuery_Click()

‘save record first before running query
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

On Error GoTo Err_cmdRunQuery_Click

Dim stDocName As String

stDocName = “qryAppendDefaults”
DoCmd.OpenQuery stDocName, acNormal, acEdit

‘requery the subform to display the new records
[sfrmSiteReviewCriteriaDataEntry].Requery

Exit_cmdRunQuery_Click:
Exit Sub

Err_cmdRunQuery_Click:
MsgBox Err.Description
Resume Exit_cmdRunQuery_Click

End Sub

Don’t suppose you have any suggestions on the real task at hand, which is to run the query a soon as a new record is created.

I have also been wondering how to do this. Does anyone have any additional solutions?

I know this is what I need to do – I just know very little about queries full stop. I’m being asked to program before I know fully about databases. I’m looking for a simple example I could learn from.

Thanks, I’ve tried tried this before.
My reason for asking the original question is I’m being asked to do some more advanced stuff and need to figure out how to build up a query based on selections the user makes from a form (i.e. they aren’t typing in values where a mistake could be made but selecting actual values that exist in the database. Any more suggestions?

Firstly, I’m working with a data entry form. Later I need to figure out how to display either text or an object in the same physical space on a report.
Secondly, I meant controls (it was a bit late late in the day and my head was a bit fuzzy).
In addition to the textual content, the report for the database I’m working with is very graphical, containing CAD drawings, photographs and extracts of excel spreadsheets (all inserted as objects).
The problem I’ve got is making it versatile enough to suit all users. Basically, in one place, there is only room on the report for text or an object – some users want to type a conclusion, others want to insert part of a spreadsheet.
I thought if I could make sure they can only enter data in one or the other on the form, it would be easier to determine which is displayed on the report.
I’ve got no problem showing or hiding the controls on the report, I just can’t figure out how to clear the content of one control if the users changes their mind and opts to use the other, i.e. they type some text, then decide to insert an object.