Reference a Field's Value in a Table

[Proaccesspro]

How would I reference the value of a field in a table?? I have a table named ACTIONS and a field in that table named ACTION TYPE. Would this work??

if [Action].[Action Type].Value = " Received By Inventory" Then
MsgBox ("What a LOVELY day!")

End If

[Stewart Ross]

How would I reference the value of a field in a table?? I have a table named ACTIONS and a field in that table named ACTION TYPE. Would this work??

if [Action].[Action Type].Value = " Received By Inventory" Then
MsgBox ("What a LOVELY day!")

End If

Hi Proaccesspro. No, what you propose would not work. You have not actually opened the table, nor selected any records, so you can't access any field values.

You could use the DoCmd.OpenRecordset method to open the Action table then process the records within a loop. There are many posts in this forum which show examples of doing exactly that, as you will find if you search accordingly.

-Stewart

[Proaccesspro]

Hi Proaccesspro. No, what you propose would not work. You have not actually opened the table, nor selected any records, so you can't access any field values.

You could use the DoCmd.OpenRecordset method to open the Action table then process the records within a loop. There are many posts in this forum which show examples of doing exactly that, as you will find if you search accordingly.

-Stewart

Stewart,

I jumped the gun...I have a form that has a drop down box of values. The values of that drop down box are captured in the field ACTION TYPE in the table ACTIONS. What I am trying to do is set the value of the field EMHS received to YES if the value selected = received by emhs. Would I still need to do it the way you describe above? Also, I am checking for this with the AFTER UPDATE event.

[Stewart Ross]

...I have a form that has a drop down box of values. The values of that drop down box are captured in the field ACTION TYPE in the table ACTIONS. What I am trying to do is set the value of the field EMHS received to YES if the value selected = received by emhs. Would I still need to do it the way you describe above? Also, I am checking for this with the AFTER UPDATE event.

Hi Proaccesspro. I think I begin to understand what you need to do, though I'm not certain. If the [ACTION TYPE] value you want to test is included in your combo box list you can access that value from the combo box directly without opening another recordset by referring to the Column property of the combo box.

If the [ACTION TYPE] value is, say, the third column in your combo you could refer to it from within the after update event code like this:

If Me![name of your combo].Column(2) = somevalue then
    ...

Columns are numbered from 0, with the 0 column the first one, column 1 the second, and so on. A column does not have to be visible - it can have a width of zero and still be returned using the Column property. Including additional fields in a drop-down which are not visible to the user but are accessible using the Column property is a really useful way to access subsidiary information from a reference table.

-Stewart

[Proaccesspro]

Hi Proaccesspro. I think I begin to understand what you need to do, though I'm not certain. If the [ACTION TYPE] value you want to test is included in your combo box list you can access that value from the combo box directly without opening another recordset by referring to the Column property of the combo box.

If the [ACTION TYPE] value is, say, the third column in your combo you could refer to it from within the after update event code like this:

If Me![name of your combo].Column(2) = somevalue then
    ...

Columns are numbered from 0, with the 0 column the first one, column 1 the second, and so on. A column does not have to be visible - it can have a width of zero and still be returned using the Column property. Including additional fields in a drop-down which are not visible to the user but are accessible using the Column property is a really useful way to access subsidiary information from a reference table.

-Stewart

Stewart,

Exactly what I had in mind....Now the last piece of the puzzle is how to update the EMHS received field in the CASE table. This is where I get confused...Both tables (Actions and CASE) are linked with a case #.
Could it be something like I stated above:

If Me![Combo12].Column(0) = " Received By EMHS" Then
[Case].[EMHS received].vaue = 1 <-------(is 1 the value of YES?)
EndIf

[Stewart Ross]

...Now the last piece of the puzzle is how to update the EMHS received field in the CASE table. This is where I get confused...Both tables (Actions and CASE) are linked with a case #.
Could it be something like I stated above:

If Me![Combo12].Column(0) = " Received By EMHS" Then
[Case].[EMHS received].vaue = 1 <-------(is 1 the value of YES?)
EndIf

Hi again Proaccesspro. It is not possible to refer to your CASE table directly, unless your form (or a subform within it) is already bound to it, or you have another form open which is bound to that table. Even if your form is bound to the CASE table, you would still have to go to the correct record before updating (perhaps by filtering the record).

The alternative is to use an SQL statement to update that value for you, using something like this in code:

Dim strSQL as String
strSQL = "UPDATE [Case] SET [Case].[EMHS received] = True WHERE [Case].[case #] = " & Me![Case #]
DoCmd.Setwarnings False
DoCmd.RunSQL strSQL
DoCmd.Setwarnings True

This assumes that (1) it is the Actions table to which your form is bound, (2) there is a current record on your form matching a current case #, (3) that the current case # is the one you want to select in your Case table, (4) that there is no other selection criteria required to select the record you want to update, and (5) that the case # is a number. If it is actually a string you would need single quotes on either side of the reference to it in the WHERE clause:

WHERE [Case].[case #] = '" & Me![Case #] & "'"

By the way, a Yes/No value is a boolean value. True (Yes) is stored as -1, False (No) as 0.

-Stewart

ps you don't need to use the column property when referring to the first column (column(0)) of a combo box. The two statements below would give identical results:

Me![combo name]
Me![combo name].column(0)

[Proaccesspro]

Hi again Proaccesspro. It is not possible to refer to your CASE table directly, unless your form (or a subform within it) is already bound to it, or you have another form open which is bound to that table. Even if your form is bound to the CASE table, you would still have to go to the correct record before updating (perhaps by filtering the record).

The alternative is to use an SQL statement to update that value for you, using something like this in code:

Dim strSQL as String
strSQL = "UPDATE [Case] SET [Case].[EMHS received] = True WHERE [Case].[case #] = " & Me![Case #]
DoCmd.Setwarnings False
DoCmd.RunSQL strSQL
DoCmd.Setwarnings True

This assumes that (1) it is the Actions table to which your form is bound, (2) there is a current record on your form matching a current case #, (3) that the current case # is the one you want to select in your Case table, (4) that there is no other selection criteria required to select the record you want to update, and (5) that the case # is a number. If it is actually a string you would need single quotes on either side of the reference to it in the WHERE clause:

WHERE [Case].[case #] = '" & Me![Case #] & "'"

By the way, a Yes/No value is a boolean value. True (Yes) is stored as -1, False (No) as 0.

-Stewart

ps you don't need to use the column property when referring to the first column (column(0)) of a combo box. The two statements below would give identical results:

Me![combo name]
Me![combo name].column(0)

Stewart,
ALL IS WELL.....Thanks so much for the help!!