Looking at a certain record and populate the comment field (1 Viewer)

[Jass7869]

Hello,

I have Field "BC1Chng" which requires user input. I want to be able to write a code to reference to "BC1Chng" if there is an input in that field for any record...I want to copy the Remarks into each record in the Remarks Field.

I was able to get the remarks1 field to loop through each record copying what was in that field into each record.

Now I want it to look at the BC1CHng field and only copy to remarks1 field if there is any input in that field???

 

[Frothingslosh]

The way I'd probably do this, at a high level, is to have VBA code, perhaps in the After Update event for the field, that pulls the value, plugs it into a parameter query, and then updates the table en masse.

 

[Jass7869]

Hello,

I am not good at coding as I am learning...can you please help with the coding? Learning as I go

 

[Frothingslosh]

Using the query builder, do you at least know how to create an update query? And how to refer to the value of a control in an open form?

 

[Jass7869]

Hello I do know how to do a Update Query but not how to refer the value of a control in an Open form.

 

[Frothingslosh]

Okay, basically, you create an Update query, using the same criteria you use for your form. (I normally just grab the query the form is based on, strip out everything but the criteria fields and the one(s) I'm updating, and save it as its own query.)

Next, one of the rows in the query builder is 'Update To'. In your 'Remarks' field, in the 'Update To' box, you would enter a reference using this format:

[Forms]![[B][COLOR=red]FORMNAME[/COLOR][/B]]![[B][COLOR=red]CONTROLNAME[/COLOR][/B]]

Obviously you'll have to fill in FORMNAME and CONTROLNAME with the appropriate values.

What that will do is run an update query on the selected records, changing the value of the 'Remarks' field to match the value saved in the control you selected, which in this case will be your BC1Chng conrol. Just be careful to make the criteria be the same ones in your form, or you may change records you don't mean to.

As to executing it, use a completed version of this:

CurrentDb.Execute("[B][COLOR=red]UPDATEQUERYNAME[/COLOR][/B]", dbFailOnError)

Again, you'd replace UPDATEQUERYNAME with the actual name of the update query.

You would place that code in whatever you want to run it, be it the code in a control button, the AfterUpdate event on the BC1Chng control, whatever. If you need to, scroll through the events on the form and control and see which may fit - you can always ask here for suggestions, too.

 

[Jass7869]

The only problem I see with the is when you say it matches the value...I just want it to check to see if there is a value in BC1Chng1??

 

[Frothingslosh]

If you use the AfterUpdate event, it'll only trigger when BC1Chng1 is changed.

You could also use something like this to make sure there's a value in BC1Chng1:

If Nz(Me.BC1Chng1, "") <> "" Then...

That checks the control and only runs the code if there's an actual value in it.

 

[Jass7869]

Hello,

i tried the Update query and it asking for a parameter Value to be entered. Another question..Where do I place the

If Nz(Me.BC1Chng1, "") <> ""

Like I said I am pretty new at the coding.

 

[Frothingslosh]

The if statement is basically saying that as long as there is data in BC1Chng1, then run the code between it and the following End If. https://msdn.microsoft.com/en-us/library/752y8abs.aspx

You would put that in the code you use to run/execute the query.

When you tried to run the query, did you have the form open? That bit of code I gave you to reference the control quite literally means "Look in the Forms collection at form FORMNAME and read the value contained in control CONTROLNAME." The Forms collection is the collection of all currently open forms, so if the form isn't open, the code can't find the value and asks you for it instead.