Chg data in a field based on another ComboBox chg

[JSimoncelli]

Hello all, first off I am a self taught Access 2007 enthusiast. Which basically means, I can get myself into trouble and maybe get myself out.

Here is my issue; I have a table “tbl_CMS-Master”. This table has several fields the issue surrounds the “Status” and “Container” fields. Both of these fields are Combo Boxes that draw from a select query in the respective tables “tbl_Status” and “tbl_Container”.

Here is what I am trying to do: Using a Form with the respective Combo Boxes I want to change the data “text” in the “Container” field to “N/A” when either “Destroyed” or “Sent” is selected in the “Status” Combo Box. Destroyed and Sent are two of five possible selections from the Combo Box.

I have tried using an Update Query, and this works. However I find a couple of issues. First, the Update Query updates the entire database not just the one record, this is ok as long as the database does not get to large. Second, I cannot get the Update Query to launch from the OnChange Event.

I would prefer to use Code Builder to make the change on the one record. I have had no luck in finding a sample/example Code that I can “barrow”. Any help would be appreciated.

 

[pbaldy]

If I understand correctly:

If Me.ComboName = "Destroyed" Or Me.ComboName = "Sent" then
  Me.TextboxName = "N/A"
End If

 

[JSimoncelli]

Oh poo if it is that simple I will shoot myself in the foot…
May I assume the Me.ComboNome will be the name of the combo box I.E. Me.Combo56.
Also the field I want to update/change is the Container field, it is not a text box. Is it treated as a text box for the purpose of the data entry? I.E. to change it from XXX to N/A.
John

 

[JSimoncelli]

So will the following work:

If Me.Status = "Destroyed" Or Me.ComboName = "Sent" then
Me.Container = "N/A"
End If

The Me.Container being the name of the Container combo box...

John

 

[pbaldy]

Did you test it?

You'd want to change ComboName to the correct name.

 

[JSimoncelli]

I did, and nothing happens, no errors and nothing changes..

Here is what i am running

Private Sub Status_Change()
If Me.Status = "Destroyed" Or Me.Status = "Sent" Then
Me.Container = "N/A"

End If
End Sub

 

[pbaldy]

Is any code running (is the db in a trusted location or has code been enabled)? I'd use the after update event, but that should still work. The change event fires with every keystroke, which you don't need.

 

[JSimoncelli]

Let me clarify something, it may make a difference. As I stated earlier, both the Status and Container and combo boxes that pull from Select Queries in their respective tables, in the Format tab the Column Widths is set to 0”;1”.
Having said that, should the “Destroyed” be set to the corresponding ID number in the case of the Destroyed is would be 3?
John

 

[pbaldy]

That was going to be my next question. Yes, you would want to reference the ID value. Alternatively:

If Me.Status.Column(1) = "Destroyed" Or Me.Status.Column(1) = "Sent" Then

 

[pbaldy]

By the way, you'd also want to set the ID value of container.

 

[JSimoncelli]

Ok how about this:

If Me.Status(1) = "3" Or Me.Status(1) = "2" Then
Me.Container(1) = "1"

The numbers "X" are the corresponding ID number in the respective tables and represent Desrtoyed Sent and N/A..

 

[JSimoncelli]

The code above did not work, however after removing the (1) it did...

If Me.Status = "3" Or Me.Status = "2" Then
Me.Container = "1"

so the fact that they were based on select queries made a differance.... I.E. using the ID numbers corresponding to the desired text Destroyed, Sent or N/A...

So thanks for your help....

 

[pbaldy]

Glad you got it sorted out.