Replies

using the criteria when you open the form is the best way to do this, so stick with what you have. I assume [State] is a long integer? Just a tip but to make your code easier to understand you should be a bit more descriptive with your Field names eg State_ID

in the code you use to add the record add this line –

Code:

RunCommand acCmdRecordsGoToNew
RunCommand acCmdSaveRecord
Forms("frmclientsummary").Requery

That should refresh the form to show the new record

or you can just open the query in code to run it but set warnings to false first to suppress the message.

Code:

DoCmd.SetWarnings False
DoCmd.OpenQuery "qryAppendStuff"
DoCmd.SetWarnings True

You could try using the substitue command to create a Z drive. I’ve done this in the past and it works fine.

create a batch file on your desktop

Code:

subst z: "c:my Documents"

When you run it you will now have a z drive that is actually pointing to the folder on your machine

You can copy the sql from the append queries you created in the QBE, wrap them in quotes and use them like this –

Code:

Dim strSQL As String

    strSQL = "INSERT INTO Travellers SELECT People.* FROM People"
    CurrentDb.Execute strSQL
    

without raising any warnings.

Now the labels wizard not being enabled – most likely it wasn’t installed when you installed office. Try rerunning the install using the customise option and make sure that all the wizards in the Access section are marked to ‘Install on this Machine’. In fact make sure that everything in the Access section is marked to install.

What about using a continuous form with combo boxes in the header for filtering and command buttons in the header above each column to sort. You can then track the current filters and send them to the report in the criteria. You can use the open arg to send the sort order to the open event of the report

Code:

strArg = "Batch_Date DESC"    
strCriteria = "Batch_Date >= #" & dteFrom & "# AND Batch_Date <= #" & dteTo & "#"
DoCmd.OpenReport "rptBatch", acNormal, , strCriteria, , strArg

First you need to understand that dates are just numbers. In fact they are more or less double, so the time now is 40438.6782638889

Anything to the left of the decimal point is the day, the decimal part is the time.

So if you store just the date, today is 40438.0

To calculate differences you must ensure that you are not being messed around by the time.

Now down to business – This is the kind of thing where Excel is actually better than access. In Excel you can use a formula that compare the value in a cell with the value in a cell in the previous row.

To do the same thing in Access I would use a report with some code in the detail’s format event to set a module level variable and then for each record compare the start date to the variable from the last record then set the variable again.

You can see a simple example in the attached database.

Controlling interface flow can be challenging and there are many different approaches.

There are so many wasys to control interface flow so here are a couple of tips.

Have you made the forms Modal at design time?

A much better way, is to open them as modal.

Code:

DoCmd.OpenForm "frmModal", , , , , acDialog
'do something when frmModal closes

When you do this the code stops and passes to the opened form. The next line won’t execute until the opened form closes.

I only ever use this when there is a specific action required by the user before the process on the parent form can be completed.

Opening reports in preview from a modal form presents considerable challenges but I can’t think of a situation where a user must read a report before they can continue work.

In a situation like, for example where the data doesn’t balance, it’s better to handle that with a form.

When I ever end up in a process like yours, where I have more than one form modal at a time, I take a relook at the workflow and invariable come up with a better process.

By the Way – popup is something I only ever use if I want a little form to float outside of the Access window. You might find you don’t need to set the form’s popup property to true.

sweet.

No the existing parent/child links are fine, but you need the month in addition.

Nice to chat Bob,

Do try out MZ tools – and remember – the best things in life are free, but please don’t tell my clients…

Kent

Bob,

here my comments on your comments and rant.

1) surely you could write and add in to give this functionality even if someone hasn’t already. I did a quick search but came up empty handed.

In any case I’m much happier using MZ tools to do all this for me.

2) never tried to use it, I’ve never (well not in 15 years) had any macros to convert.

3) Very true, not to mention the impementation of Access Services in 2010. 2010 macros are designed to convert to Java script when they are uploaded to the web. With no interface for a serious developer to modify them.

My add to your rant –

I’ll admit that in my early days I did use the wizards to create code and this did help me go in the directions of code rather than macros. These days I often come across code generated by others less experienced using those wizards, say yuk and clean it up while thinking back to when I did the same.

I love that Access is designed for the information worker, I get most of my work from people like this whos application has grown to be so big and important to their business that they need to get in a real developer. While 95% of the apps started in house will die, there are those few that become mission critical. ANd as long as some of those come to me to cleaup and build on then I have a business.

Access is the best tool for a real developer to create a desktop application but as you point out Microsoft’s commitment to this is sometimes questionable.

It’s OK that information workers use macros, when they are getting an application off the ground, but at some stage the limitations will be reached and VBA is the only answer. (the converter

The fact that Access Services does not have a coding interface eliminates it from serious consideration as a web application. Even if I do manage to create a useable application using macros, at some point, my client will request a feature that is simply not possible using macros. Unhappy client.

Access 2007 was a considerable improvement for the developer trying to distribute an application. It almost got there. Pity MS’s focus was distracted by the sparkly lights of the web when they started on 2010!

SageKey’s installer does fill in all the holes but it’s very expensive. MZ Tools however is free. Here is an example of what two button clicks can insert into your procedure –

Code:

On Error GoTo Error_Handler
Dim strFormName As String, strCriteria As String, strArg As String

    strFormName = ""
    strCriteria = ""
    strArg = ""
    DoCmd.OpenForm strFormName, , , strCriteria, , acWindowNormal, strArg

Exit_Procedure:
    On Error Resume Next
    Exit Sub
Error_Handler:
    Select Case Err.Number
        Case 0

        Case Else
            MsgBox "Error " & Err.Number & " (" & Err.Description & ") in Procedure: mzTest in Module: ModTest", vbCritical, DBEngine(0)(0).Properties("AppTitle")
    End Select
    Resume Exit_Procedure
    Resume ' For Debugging

Now isn’t that better than the old wizards?

I had a look at the database you posted, you might like to clean it up a bit and repost to make it easier to work on. Stuff like the naming of fields and controls so that they make sense.

eg fields that relate to each other should have the same name like Team_Command

don’t use ‘Month’ as a field name because it’s a reserved word, use a name like Transaction_Month. I always use at least two words separated by an underscore. That way I can’t possible use a reserved word by mistake.

then name the combo cboTransaction_Month. It’s really hard to follow code that refers to controls with names like ‘combo38’

OK now a couple of pointers.

You can use the Link Master Fields / Link Child Fields to limit the data in a subform as you have done. Unfortunately you can only do this for one field.

You can however use a filter in the Record Source of the subform.

Code:

SELECT DSAllocation.*, * FROM DSAllocation WHERE (((DSAllocation.Transaction_Month)=[Forms]![DSMainFrm]![cboTransaction_Month]));

You can do this easily enough using the query builder but you can just paste the above string into the Record Source of DSProductionFrm and it should work, just so long as you rename the combo box to cboTransaction_Month

you can then just requery the subform in the after update of the combo

any chance you can post a stripped down database with the forms and just enough data to test with and I’ll check it out.

The code created by the wizards was junk anyway, mostly because of the lousy error handling. Except for maybe the find record in the combo box wizard.

I’ve started using MZ Tools, a free utility for the VBE. Easy to install, let’s you setup error handler code the way you want and inserts it in a procedure with one click even if there is already code in it.

Also lets you customise Procedure headers that insert in a click, code templates and more.

Way better than the code generating wizards.

And certainly better than using macros.

My Mother taught me that if I ever used SendKeys, I would surely go straight to hell.

I’ve recently had to do something similar and here’s what I did.

Create module level variables for the fields you want to carry over.

Set them in the after update event of the form

in the on insert event, populate the controls from the module level variables.