if else condition in access vba (if there is a date in this box, put this 8 digit code)

[akino25]

Hi,



I made a very simple macro see below:


Private Sub Command27_Click()
If Date_HOP = Date Then
Texte19.Value = txt_BC
End If
End Sub

it is working as intended, but it only does that for 1 record.

the thing is whenever I put a date on a record, we always need to put that same 8 digit in the comment field.
so I created a text field called "txt_BC" where we write our 8 digit say (12345678), and with the click of a button, id like that 8 digit to be in every record where the date box contains a date.

the macro upthere works as intended but only for 1 record. anyway to make it for every record that contain a date in that field and put that 8 digit in this comment box ?

[RuralGuy]

This is only for "Today's" date right? Use the AfterUpdate event of the [Date_HOP] control and it will catch it right after you enter the date.

[akino25]

It is for today`s date yes,

actually, there is another code in vba, as soon as you click on the date box, "today`s date will be put there automatically, see image below ?



See that it works when i click on the test button which contain the vba code for 1 record, but no the other

[RuralGuy]

I'm not quite sure what I am looking at in your picture. It looks like it might be a SubForm in datasheet view, but I can't tell.

[orange]

You are telling us HOW you have done something that isn't working. We need to know WHAT.
What are you trying to do in simple terms?
What is the meaning of your form in post #3? Who uses it? For what purpose?

You must realize we know nothing of you or your business. So telling us about yourself, your business, the issue and your approach will be helpful to people reading your post.

[akino25]

it s a simple form linked to a table,

you write the 8 digit in the No BC box and there`s a button called "Test" near it and when pressed, what you wrote in the No BC will paste it all in the No BC box where it contains a date.

my macro works but only for one record as shown in the image it did not do for all the rest (where it contains a date)

*I write abcde for test pruposes but it will be an 8 digit there

[RuralGuy]

What code did you put in the AfterUpdate event of the [Date_HOP] control> Please copy and paste it into your next post.

[Bulzie]

On the button Test code, add update code. Something like:

Docmd.RunSQL "UPDATE YourTable SET YourTable.texte19 = [Forms]![YourForm]![txt_BC]
WHERE (((YourTable.Date_HOP) Is Not Null));"

[akino25]

On the button Test code, add update code. Something like:

Docmd.RunSQL "UPDATE YourTable SET YourTable.texte19 = [Forms]![YourForm]![txt_BC]
WHERE (((YourTable.Date_HOP) Is Not Null));"

Not working =/,

Let me explain again if this time make more sense:

what i`m trying to do is simply by cliking the test button,
what was written in No BC (see pic below, it is 12345678),
Id like that to be paste on every row where it contains a date. (where the blue arrows are)

[akino25]

What code did you put in the AfterUpdate event of the [Date_HOP] control> Please copy and paste it into your next post.

There`s nothing in the afterUpdate event of [Date_HOP], I did not put any code there

[Bulzie]

When you enter a record and put a date, can you place that number at that time on that 1 record? Or for whatever reason you need to add records with dates, then at some point need to enter the number for all records with a Date and no number already?

[RuralGuy]

On the button Test code, add update code. Something like:

Docmd.RunSQL "UPDATE YourTable SET YourTable.texte19 = [Forms]![YourForm]![txt_BC]
WHERE (((YourTable.Date_HOP) Is Not Null));"

Could you copy and paste the code you put behind the Test button so we can see it? It looks like it would work to me.

[Bulzie]

When you say Not working =/,, what does that mean. You get an error message or it just does not update the records? Does it update the records but not show immediately on the form? If that is the case then add a Me.Requery after the Update code line.

[akino25]

Could you copy and paste the code you put behind the Test button so we can see it? It looks like it would work to me.

The thing is this code will paste the 12345678 through all my records in the Table, even on existing one, and that`s not what I wanted.


(from my understanding) The code you provide will modify the table directly right? since UPDATE YourTable SET YourTable.texte19 = The form you see in this pic, will insert the record into the table when I close it using the me.dirty, but as mentionned, just trying to apply the 8 digit into the form where it contains a date.
tomorrow will be a new 8 digit.

[Bulzie]

You need to also limit it to just those without values already so your Where clause would be:

WHERE (((No_HOP_BNC.Date_HOP) Is Not Null) AND ((No_HOP_BNC.Texte19) Is Null));"

Also do aRequery at the end to have it display on the form after you hit the button and do the Update.

Forms![No_HOP_BNC].Requery