TAG value problem/SET Tag value

M

mark r

i get an error message: method or data member not found:


Assessment is a combo box and field name in which the user is storing a
diagnosis test description.

maybe the problem is the AFTERupdate part of the code:



Private Sub Combo_assessmt_AfterUpdate()


Dim db As Database
Dim rs As DAO.Recordset
Dim strSQL As String

Set db = CurrentDb

strSQL = "SELECT diagcode " & _
"FROM diagnosis " & _
"WHERE diagname = '" & Me!assessment & "'"

Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
Me.assessment.Tag = Nz(rs!diagcode, "")

rs.Close
Set rs = Nothing
Set db = Nothing

Rem I am trying to store the diagnosis code associated with the diagnosis
name Rem in the tag value
End Sub

Private Sub Combo_assessmt_LostFocus()

If (Me.assessment.Tag = "36500") Then GoTo doMSG2027F


GoTo exitrout
doMSG2027F: MsgBox ("remember to .......")
GoTo exitrout
exitrout:
End Sub

There does exist a table that relates the assessment and the
diagcode :

Table Diagnosis:
diagcode text input mask 999.99
assessment text
category text


The source query does have both the assessment (or diagname) and the
diagcode available:

SELECT Diagnosis.diagname, Diagnosis_category, Diagnosis.code
FROM Tablecat_category INNER JOIN Diagnosis ON
Tablecat_category.category_code = Diagnosis.dg_category
ORDER BY Tablecat_category.category_number;

where Tablecat is yet another table of diagnosis categories
I am storing diagname in "assessment" but am looking to get diagcode into
the TAG value for each stored assessment so that I can run the appropriate
msgbox for particular diagcodes/diagnames


Would you suggest a different approach?
 
A

Albert D. Kallal

Here is the question, what table is the combo box based on?

can I assume 3 collums for the combo box?
The source query does have both the assessment (or diagname) and the
diagcode available:

SELECT diagname, Diagnosis_category, Diagnosis.code
FROM Tablecat_category INNER JOIN Diagnosis ON
Tablecat_category.category_code = Diagnosis.dg_category
ORDER BY Tablecat_category.category_number;
Click to expand...



2nd question:
Why not put the message box code in the after update event?

(lost focus is not very good, bucause the user can move to aother
roecrd..and keep the focus the SAME on the combo...

Fourthermore, you likey want to fire this code ONLY when the combo box
chanbes..and lost focus *always* fires...even when the combo box is NOT
changed. (it makes no sense to fire code as the user tabs around an exsiting
form.

Given all of the above, I woud stuggest you base teh combo box ont the three
(DiagName, Catalogr, Diagcode) and somply go:

Private Sub Combo_assessmt_AfterUpdate()

if isnull(me.combo_assmt) = false then

strDiagCode = me.combo_assmt.collumn(2)

select case strDiagCode

case "36500"
msgbox "rembmber to..."

end slect
end if


Also, It not really very good to "hard" code messages in the software. It
means developers have to go into the code to change messages for particular
diag codes. Many company will consider that design dishonest. Can you
imagine if you had to call Microsoft to change your Excel sheet each time
you needed a new diag code message?. As it stands now, you placing data
related messages in code, and those messages should be part of the
data...not the code.

I would suggest that you consider adding a "warning", or message text box
column to the table, and then simply use code to fetch out that value. (that
way, you eliminate my above case select, or your hard coded if..then
else...to display the information message....

In summary:

use after update...as it ONLY fires when you change the value.....lost
focus *always* fires...even when not needed!!!

Include the Diagcode in the combo box columns (and use the column() function
to pull this value...it is a TON less code to write.
 
M

mark r

thanks albert,
your suggestions helped alot

I like this idea but I have some questions on it:

I would suggest that you consider adding a "warning", or message text box
column to the table, and then simply use code to fetch out that value. (that
way, you eliminate my above case select, or your hard coded if..then
else...to display the information message....

1. if the "message field " was column 4
would I do

If IsNull(Me.Combo_assessmt) = False Then
strDiagCode = Me.Combo_assessmt.Column(3)
MESSAGEstring = Me.Combo_assessmt.Column(4)

Select Case strDiagCode
Case "36500"
MsgBox ("MESSAGEstring ")
End Select
End If

or would I use SET command to get MsgBox to display that string? or what? I
am confused.

2. could I use this code as well, as I know it works already in conjunction
with your idea:

Dim db As Database
Dim rs As DAO.Recordset
Dim strSQL As String
Set db = CurrentDb
strSQL = "SELECT diagcode ALBERTSfield" & "FROM diagnosis " & "WHERE
diagname = '" & Me!assessment & "'"

Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
Me.Combo_assessmt.Tag = Nz(rs!diagcode, "")
rs.Close
Set rs = Nothing
Set db = Nothing

If (Me.Combo_assessmt.Tag = "36500") Then GoTo doMSG2027F Else
If (Me.Combo_assessmt.Tag = "36614") Then GoTo doMSG1055F Else

GoTo exitrout


doMSG2027F:

albert could I do:

Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
Me.Combo_assessmt.Tag = Nz(rs!ALBERTSfield, "")
rs.Close
Set rs = Nothing
Set db = Nothing


MsgBox ("Me.Combo_assessmt.Tag") <---------but Albert, this wouldn't work,
would it?
GoTo exitrout


doMSG1055F: MsgBox ("remember to ...")
GoTo exitrout
exitrout:
End Sub
 
A

Albert D. Kallal

mark r said:
thanks albert,
your suggestions helped alot

I like this idea but I have some questions on it:

I would suggest that you consider adding a "warning", or message text box
column to the table, and then simply use code to fetch out that value.
(that
way, you eliminate my above case select, or your hard coded if..then
else...to display the information message....

1. if the "message field " was column 4
would I do

If IsNull(Me.Combo_assessmt) = False Then
strDiagCode = Me.Combo_assessmt.Column(3)
MESSAGEstring = Me.Combo_assessmt.Column(4)
Click to expand...

Yes, the above is exactly the road I am suggesting (good stuff on your
part!!).
Select Case strDiagCode
Case "36500"
MsgBox ("MESSAGEstring ")
End Select
End If
Click to expand...

If the code does NOT have a message, then the MESSAGEstring will be blank.
That way, we can simply add new error messages over time, and new error
codes without having to change/modify the actual code.

change:
MESSAGEstring = Me.Combo_assessmt.Column(4)
Click to expand...
to:

MESSAGEstring = nz(Me.Combo_assessmt.Column(4),"")

(above allows for a null/blank value when no error message is needed)

Also, the column() function is zero based (it starts at 0, so 0 = 1st
column, so, a value of 3 = 4th column).

In place of if's, or my select cast, you simply go:

if len(LESSAGEstring) > 0 then
' must be a error message...display it
msgbox MESSAGEstring
end if
2. could I use this code as well, as I know it works already in
conjunction
with your idea:
Click to expand...

Sure, you could likely continue to use that code (but if you add the column
to the combo box, you not need all that code anyway).

Furthermore, since we ONLY using the after update event, you not need to set
the tag value anyway. Also, the tag value is not such a good value to try
and set at runtime anyway. (your code should work, but perhaps it zero
length string, or something else that not allowing this).

Furthermore, to look up that value, we can dump a ton of sql code, and go:

Me.Combo_assessmt.Tag = dlookup("diagcode","diagnosis","diagname = '" &
Me!assessment & "'")

MsgBox ("Me.Combo_assessmt.Tag") <---------but Albert, this wouldn't
work,
would it?
Click to expand...

It should work (you don't want the quotes in the above...). eg:

MsgBox Me.Combo_assessmt.Tag

(don't use brackets in the above, nor quotes as you have).
GoTo exitrout
Click to expand...

The goto's do jump around a bit...makes the code hard to follow. It still a
good idea to try and eliminate branching, goto's, and the hard coded parts
of this when it not too much work!! The tricky part is when to decide if the
extra work is worth it.
 
Top