Replies

Christopher,

OK, a multi-field link is rarely a good idea, but putting that to one side…..

On your subform, add three controls, one for each missing data element. Bind these controls to the underlying data. Set the default value for each to the relevant data from the main form.

When you add a new record, the defaults should be picked up and saved. The controls do not need to be visible, I would tend to make them visible while debugging.

Hope this helps,

Jules

Gary,

I’m not sure of the exact scenario you describe. Simple answer, an after update would open a query to find the team responsible for the item. If a team is found, the message box can be opened (or a control on screen can display the name of the person/team – less annoying for the data entry person). Is this enough help?

More complicated answer – are you trying to prevent the data processing team from working on a record at the same time as another team? If so….

Firstly, you need to be able to identify that the record is being edited. A simple locking mechanism would write the name/ID of the editor, plus the date and time, into the record that is being edited. Clicking on Save or Cancel then deletes the locking data. One of the first actions under the Edit button would be to check the locking fields, and only allow editing if the field is empty.

An Admin screen to unlock these records is often also necessary, to cope with power failures or Ctl-Alt-Del leaving the record in a locked state.

Now, the system knows if the record is in use. As you say, an After Update event could check the record and bring up the name of the person who locked it.

Hope this is of use.

Regards,

Jules

Ben,

You need to decide if you are referencing a value in a control on the ‘main form’, or whether you are referencing a value in the forn’s underlying recordsource. Either way will work. Use =Forms!FrmMain!txtID (assuming standard naming convention for the control), or =Forms!FrmMain![ID]. There are other methods, such as passing the ID to the new form as an argument (try the OpenForm wizard for an example, found by adding a new command button to the form). Another way is to store the ID in a global variable.

Then you should look at what you are doing with the ID value. It would help if we had the full error message, but it sounds as if you are trying to tinker with an Autonumber/seed value instead of filtering on /searching for the number. Have a good look at this area, and if you still see an error make a note of the error number and message. The error message will probably pinpoint the problem.

Regards,

Jules

Lee,

here’s a link to an article that highlights how Excel occasionally cannot add 2 plus 2 to make 4.

Excel additions errors

It’s all to do with Excel not using real numbers to add up with (or something like that!). As you say, rounding should fix the problem.

Jules

Lee,

another example of Excel not doing what you expect. I have not checked all your formulas, but I did change the formatting on cell C3 to Number, squintillion decimal places. This showed that Excel was calculating a value of n.050000001. So, this did not match your target of n.0500000000.

Ok?

Jules

Hi Christopher.

You posted: When the button on form 2 is clicked it should alter the source object of the subform on form 1, and then requery it.

Your code uses ‘Me’ which only refers to the current form. You need to explicitly refer to the first form, as an object in the Forms collection. Try using forms!formname!frmsubresult.sourceobject

Regards,

Jules

Andy,

it sounds as if your database falls foul of compatibility issues. We get so used to new versions of Office opening earlier versions seamlessly, that it is easy to forget it is not always the case.

Have a look at Compatibilty to see Microsoft’s own list of issues.

If this is the cause of the problem, I don’t envy you the task of stripping the modules or worksheets out one at a time, but hey, somebody’s gotta do it!

Regards,

Jules

At first glance, that’s a weird one!

Is the form Single View or Continuous View? If the latter, then yes, it will give the symptoms you describe. List boxes do not work in anything like the same way as combo boxes. I’d suggest you consider changing to a combo box. Much easier to control in these circumstances.

Regards,

Jules

Paul,

two aspects. First may just be a matter of finding the right words. Do you really mean that you want to set values in a query, or are you trying to limit the records shown on the report, by applying criteria? I’ll assume the latter.

So, If you are trying to apply a Where clause to the report, then you need an extra comma before your clause. It’s currently in the named filter position.

Then, you need to get the syntax right. Easiest way is to prepare a Select query using the normal query designer, then copy the part of the SQL Statement that you need. In this case, it is more likely to look something like “([Applydate] Is Not Null) And ([Appwithdrawal] Is Null)”. You will of course need both fields in the recordsource of the report, even if they are not shown in the printed report.

Hope this helps put you on the right track.

Regards,

Jules

Have a look at this article:

Excel statistical functions

I found it very helpful.

Cheers,

Try putting a stop on the code in your function. This might give you a clue as to what is happening. Gut feeling is that there is a messagebox open but hiding behind something.

I use docmd.close acform, me.name

Regards,

Judy,

Not my area of expertise, but it looks as if you are trying to use a DOS command within VBA. When I’ve had to copy files in the past, I’ve set a variable to a FileSystemObject, e.g. Dim objFSO as FileSystemObject. Then, the methods such as objFSO.CopyFile become available.

Hope this helps,

Tom,

Helen Feddema has an add-in that might help you. It is available at:

http://www.helenfeddema.com/CodeSamples.htm

Try download no. 20.

Regards,

I have a frmReservationEntry whose data source is qryCustomerSorted. Among the fields on the form are CustomerStatus, CustomerID, ReservationNumber (alphanumeric field), ResBegDate, ResEndDate. There are 7 CustomerStatus codes.
When entering data, I want to perform some validation tests.
1. If CustomerStatus is any of the 1st five fields, I want to prevent any entry into the ReservationNumber, ResBegDate, and ResEndDate fields
2. If CustomerStatus is 6, I want to insure that the ReservationNumber is 11 characters long.
3. If CustomerStatus is 7, I want to make ReservationNumber equal to the CustomerID number
4. If ReservationNumber is populated, then dates must be entered into ResBegDate and ResEndDate fields

Any suggestions as to the best place to perform these tests and the syntax would be greatly appreciated.

Tom

Tom,

1. After Update event of CustomerStatus control. Syntax should be very simple, to disable the other fields. You may also need an On Current event.
2. Before Update event of ReservationNumber control. If cmbCustomerStatus = 6 then…….If nz(LEN(txtReservationNumber))11 then…
3. As 1.
4. Form’s Before Update event is probably the best place.

Regards,

Patt,

I would guess you do not want to lose the ID from Access because other data is linked to it. You may need to add an extra column to your SQL Server table for ‘old ID’, and import the Autonumber data into this field. Then, run update queries on your linked tables to replace the old ID with the new ID. A query that includes the two tables, with a link between the foreign key field in your ‘slave’ table and the ‘old ID’ field in the master table, is one approach. Have the query update the foreign key field to the new SQL Server Identity value.

A bit longwinded, and must be repeated for every linked table, but it works.