CMD Button to Update Table (1 Viewer)

[Steven19]

Hi

I have a command button on a form.

I want to add additional functionality to my database becomes slightly more dynamic.
At the moment the button assigns a date to a field that is in the form and then hides the button.

I want to add to the buttons abilities.

The form the button sits on is used to update fields in "tblCompanyDDIdentity"
I want it that the value of the country field = United Kingdom on click updates some fields of the table without them being visible in the form or even there and hidden.

The form already displays
Country in the form, called DDCountry.
A Primary Key "CompanyDDIdentityID"

I do not want lots of fields on the form.

So when the button is clicked I want to update "tblCompanyDDIdentity" field "BankUpdated" with a value Not Applicable, but I dont want to have the field added to the form and have it hidden and the button update that. Otherwise I would have to do this for a number of fields and have lots of hidden fields on the page.

I just want to click the button. Then
"tblCompanyDDIdentity",field"BankUpdated" to now have the word Not Applicable entered.

I can do it if I have the field on the form hidden as I can just do me.BankUpdated = Not Applicable, but do not know how to do this with the field not on the form.

Can you help?

 

[mike60smart]

Hi

I have a command button on a form.

I want to add additional functionality to my database becomes slightly more dynamic.
At the moment the button assigns a date to a field that is in the form and then hides the button.

I want to add to the buttons abilities.

The form the button sits on is used to update fields in "tblCompanyDDIdentity"
I want it that the value of the country field = United Kingdom on click updates some fields of the table without them being visible in the form or even there and hidden.

The form already displays
Country in the form, called DDCountry.
A Primary Key "CompanyDDIdentityID"

I do not want lots of fields on the form.

So when the button is clicked I want to update "tblCompanyDDIdentity" field "BankUpdated" with a value Not Applicable, but I dont want to have the field added to the form and have it hidden and the button update that. Otherwise I would have to do this for a number of fields and have lots of hidden fields on the page.

I just want to click the button. Then
"tblCompanyDDIdentity",field"BankUpdated" to now have the word Not Applicable entered.

I can do it if I have the field on the form hidden as I can just do me.BankUpdated = Not Applicable, but do not know how to do this with the field not on the form.

Can you help?

Hi

Something along these lines
Dim strSQL As String

strSQL = "UPDATE [tblCompanyDDIdentity] SET BankUpdated= " Not Applicable " _

& " WHERE CompanyDDIdentityID= " & Me! CompanyDDIdentityID& ";"

CurrentDb.Execute strSQL, dbFailOnError

 

[Pat Hartman]

You don't need a separate update query. In fact, it is poor practice to run an update query on the record your form is updating.

Include all the columns you want to reference in the form's RecordSource query. Then reference them as if they were bound to controls using Me. I have recently run into a problem doing this but I can't reproduce it reliably so I'll explain instead. Access takes it upon itself to rewrite a REPORT's RecordSource query to eliminate all columns which are not bound to controls on the report. That makes code break unexpectedly and for a report, the solution is to put all fields you need to reference via code but not view somewhere on the report and hide them. You can make them very tiny. For my reports, I always set the background of these necessary but hidden controls to bright yellow as a memory jog. The same solution is available if you run into the intermittent bug that I ran into. In theory, you just need the columns to be bound in the RecordSource, but in practice, Access may think you should bind them to controls on the form.