CODEWITHSUNDEEP
vbams-access
nick
nick

Reputation: 881

MS access check column value and change another

I have an ms access database with some yes/no columns I want to check and set the value of a third. The statement should be something like the following

if !col1 && !col2:
         col3 = no
else:
    col3= yes

I keep searching but don't really understand vba and can't find what I need .. Mostly a segment of an answer to something else that I cant make work. Currently trying to create it in the "module" section is that even right? Would be best if this could be done automatically as those columns are changed or maybe run once and do all rows. Please help me get on the right track, any help greatly appreciated.

Upvotes: 0

Views: 2773

Answers (3)

Chris
Chris

Reputation: 254

I would just create this on a calculated column in your DB table. See screen shot below:

enter image description here

Notice in the properties I set a formula to acquire the desired results based on the first 2 columns. The "Result type" is set to yes/no to mimic the yes/no field. Only difference is, the third column will display a -1 (True) or 0 (False). but when displaying this information on a form, you can have it display the information in a checkbox fashion. The calculated field is also ideal cause it will only update the third column when the target record is updated, not updating the whole table, very useful if the table size starts holding over 100k records.

If you want a VBA code, then you will need a trigger. I'm assuming its a button on the form, if not, you can always change the below code to match the event you want it triggered on. The below code is also a module that can be called/used for any trigger. The code is also assuming you have a primary key. 

Public Sub UpdateThirdColumn(ByVal RecordPK As String) 'RecordPK is a passed variable
                                                       'that is the primary key identifier to find the
                                                       'record in the table
'Set variavble name for SQL statement, gives you one place to update instead of hunting through code
Dim strSQL                          As String

'Creates the SQL string in the variable assigned
strSQL = "UPDATE {YourTableNameHere} " & _
         "SET {YourThirdFieldNAmeHere} = True " & _
         "WHERE ((({YourFirstFieldNAmeHere}) = True AND ({YourSecondFieldNAmeHere}) = True AND ({YourPrimaryKeyFieldHere}) ='" & RecordPK & "'));"
'Executes the SQL statement, dbFailOnError will fail if problem exists in SQL statement
CurrentDb.Execute strSQL, dbFailOnError
End Sub

'Use this code if you have a button to trigger event
Private Sub YourButton_Click()
Call UpdateThirdColumn(Me.YourPrimaryKeyControlName)
End Sub

'Use the bottom 2 codes if you want the update for each check box to be checked and update
Private Sub FirstFieldName_AfterUpdate()
Call UpdateThirdColumn(Me.YourPrimaryKeyControlName)
End Sub

Private Sub SecondFieldName_AfterUpdate()
Call UpdateThirdColumn(Me.YourPrimaryKeyControlName)
End Sub

Let me know if you need more assistance or explanation and I will be glad to help.

Upvotes: 0

Zev Spitz
Zev Spitz

Reputation: 15317

Let's say you have an Access table named Table1, with some Access fields:

Table1 in Table Designer, with three Yes/No fields

The following Access SQL statement will update the value of col3 based on the values of col1 and col2, for every row in Table1:

UPDATE Table1
SET col3 = NOT col1 AND NOT col2

There are a number of ways to leverage this SQL statement:

  • You can paste it into the Query Designer in SQL view, and execute it via the Access UI

  • You can run it as part of an Access macro

  • You can execute it in VBA, using ADO or DAO

  • You can execute it in VBA, using the DoCmd.RunSQL method

  • Instead of VBA, you can use another Automation-supporting programming language

Upvotes: 1

Benjamin Racette
Benjamin Racette

Reputation: 172

Here is what I would do:

1- Create a form and add at least one command button on it. Name it cmdMyButton or cmdAnythingThatYouWant (cmd is the prefix used in examples from Microsoft for command buttons)

2- in the design view, double click the command button so to pop the code window

3- In the onClick() function, write the code that opens up a recordset for your table, loop through records and for each row, verify the value of those 2 columns and update if needed. (look at the documentation for DAO.recordset)

Upvotes: 1

Related Questions