Help!! Problems with updating table fields through my form

[StefanScheidegger]

I have a form with linked boxes named "January", "February", "March", ... and "December". I enter numbers into the fields. If I enter let's say 10 into "January", the number 10 should already be reflected in the source table. However, this is not the case. The source table is only updated when I go to the next records (that means when I filled in the numbers from "January" - "December&quot.

How can I achieve, that the source table takes immediately the values reflected in the forms before a new record is started?

Thanks very much in advance. regards,

Stefan

 

[mrf1xa]

Hi Stefan

What you describe is default behaviour for Access- it waits for you to input all the information relating to a particular record before updating it to the underlying table. This gives you the chance to validate data, check that all compulsory fields have been completed etc before the table is populated.

There no doubt ways around this- but first you should ask yourself why you want it to behave differently from the norm. Is there a particular reason why the record must be updated one field at a time? Nigel
Didn't someone say work is supposed to be fun? They didn't have computers then I guess....

 

[StefanScheidegger]

Hi Nigel

Thanks for your response. Unfortunately, I need to do what I described above. My form shall disply the calculated value according to the query field (which is quite a complicated DSUM Function).

Do you have any ideas. Since this is quite urgent, I would appreciate a soon answer.

Thank you, Nigel.

Stefan

 

[JSchumacher]

Stefan,

How about checking each text box as you leave it and writing the data to the tables if it is valid. Something like this:

Private Sub txtJanaury_Exit(Cancel As Integer)
If Dirty = True Then
If VerifyData = True Then
Dirty = False
Else
MsgBox "The data you entered......"
Cancel = True
End If
End If
End Sub

Judie

 

[StefanScheidegger]

Dear Judie

I thank you for this idea. One more question. Where do I have to add this code?

Thanks and best regards,

STefan

 

[JSchumacher]

Open your form in Design view and open the properties sheet. Click once on your text box to select it. On the On Exit line use the drop down box to choose [Event Procedure] or click to the right of the drop down box on the Builder Button (...) and choose Code Builder. It will place the first and last line of the subroutine in the code for you.

Copy or type in the remaining code I listed. The part "If VerifyData = True" assumes you have a function (named VerifyData) that checks the data you entered and returns a "True" value if it is valid for your application. You can modify that line to suit you needs.

 

[StefanScheidegger]

Hello

If I don't have the line "verify data....", how would your code then look like?

Stefan

 

[JSchumacher]

Then all you need is one line to save the changes:

If Dirty = True Then Dirty = False

However, if what the user entered isn't valid for that field (ex. it's a number field - what happens if they enter text?), they will get an error.

 

[StefanScheidegger]

Dear Judie

You are really a computer expert. Perfect, Judie. The code works beautifully (I have not yet added the verify line).

However, I agree with your expert opinion. I should have a function to verify the data. Can you tell me how I manage to add such a verification? The data to be entered are figures (no other conditions).

Thank you again for your very appreciated help.

Stefan

 

[StefanScheidegger]

I forgot to add something to my last question to you. One more condition for valid data is the following:

when entering data into the form field (e.g. in January), a specially created text box in the form (FYI: this is a text box which shows the result of the calculation in the query) must not be a negative (the value in the text box changes with every time another value is entered into the January field, thanks to your excellent code).

Do you have any ideas how to achieve that?

Stefan

 

[JSchumacher]

Stefan,

You need to create a function like:

Private Function VerifyData as Boolean

If txtJanuary >1 and txtJanaury <15 then VerifyData=True

Exit Function

You would modify the If..Then to check for your desired information - including weather or not the value results in your reliant text box being negative. If the value doesn't pass the test, the return value is False and you can then display a MsgBox with further instructions for the User.

 

[StefanScheidegger]

Thank you Judie

I do not quite understand where to add the above mentioned code (If txtJanuary >1 and txtJanaury <15 then VerifyData=True) which you stated. Must it be entered in the form field January or in the text box (which gets its value from the query calculation)?

And please note: The text box (query calculation) uses not only the field January, but also the data entries in February, March, etc. Does this change your proposed code?

Sorry, but I am not yet experienced in VBA as you probably noticed. But everybody was a beginner once.

Stefan

 

[StefanScheidegger]

Dear Judie

It would be just great if you could give me a feedback this Friday so that I could work on it a bit during the weekend.

I really appreciate your help very much.

regards,

Stefan

 

[JSchumacher]

Stefan,

If this calculation will be used by more than one form, you should create a Module for it. Click on the Modules Tab (next to the Macros Tab), and click on New. If this will OLNY be used in this form, you can place the code in the module that belongs to this form.

If you create a new Module and type in the code there, you will need to change the word Private to Public so your form can call it.

As far as the logic to make your validation test, it will all depend on what your query text box is doing. Generally what you want to do is duplicate the math your query text box does and check the answer for a negative.

You may want to take a look at the Before Update, After Update and On Exit events to compare them and decide where to place your Validate code. I wasn't able to determine that exactly, but wanted to post a reply before the weekend.

Judie

 

[StefanScheidegger]

Dear Judie

Thanks for answering so quickly. Unfortunately, I did not explain something clear enough.

The form fields &quot;January&quot;, &quot;February&quot;, &quot;March&quot;, .... &quot;December&quot; are all in the same form. Furthermore, the form includes this text box I mentioned (the text box shows a value in a calculated query).

When entering a value in the form fields (e.g. January), the value in the text box reduces (however it reduces by another value). The same happens with the other form fields. The user should be able to enter values in the form fields (January, February,...December) so that the text box will be close to zero but not negative.

Is it then possible for you to state any appropriate code?