MER Lostfocus

  • Thread starter Thread starter mark r
  • Start date Start date
M

mark r

Question: how can I modify my code to utilize the textname selected,
cross-reference it to its associated codenumber, and GOTO the MSG2027F:

Background:

On a subform, when the focus leaves the combobox assessment, I want the code
underneath to check and see if a particular assessment was selected, and if
so, display a messagebox of instructions to the user.

My problem is that assessment is being stored as a text name rather than the
five digit number code associated with that text name. If the code was being
stored inthe field assessment, then I would already have functioning code:

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

However, I am storing the textname.


I do not want to write:
If (Me.assessment = "does the textname stored match these alphas") Then
GoTo doMSG2027F


The source query does have both the textname and the codenumber available:

SELECT TableDx.diagname, TableDxdg_category, TableDx.code
FROM Tablecat_category INNER JOIN TableDx xON
Tablecat_category.category_code = TableDx.dg_category
ORDER BY Tablecat_category.category_number;


There does exist a table that relates the testname and the codenumber:

TableDX:
code text input mask 999.99
assessmentDescription text
category text


Is there somesort of lookup code I can insert so that assessment is evaluated
off of the associated code number rather than the text alphas?

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

In the form's Current event, write code to put the value you want into the
control's Tag property (you might have to use a recordset to get the value
from another table). That way, every time you go from record to record, the
value will be updated 'automatically'. Then in the LostFocus event, you can
use code very similar to what you had.

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

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
 
Thanks, this sounds like what I might need. But I have some questions.
When the user selects a value for the assessment-combobox, then the .tag
value should be set according to that drop down item selected.

The drop down list for assessment-combobox comes from a query,
so the assessmentDescription populates the assessment-combobox drop down field

I want the corresponding TableDx.code to populate the .tag value

So do I put code in the LOSTFOCUS to insert the TableDx.code into .tag ,
followed by the If..Then code I am ultimately interested in?

I don't see how putting code in the CURRENT can be done since the combobox
value has not yet been selected or the used could keep changing the value
selected.

Also, my experience with recordset is limited to the code below and I am
not even sure of how it works anymore - I did it so long ago. So if you could
sketch that idea out for me , I'd appreciate it. Thanks for the help.

Rem setdat
Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
With rs
.MoveFirst
.Edit
!xmdate_of_interest = pt_exam!pt_exam!xmdate_of_interest
.Update
.Close
Me.Bookmark = .Bookmark
End With
rs.Close
Me.Combo43.Requery
Me.Combo39.Requery
Me.Comboselectdate.Requery
' requery yet another listbox
Me.List_HX_all.Requery
Me.List_todaysrecs.Requery
End Sub
 
Mark,

In that case, you'll also need to put the same code into the combo's
AfterUpdate event.

Private Sub cboMyCombo_AfterUpdate()
Dim db As Database
Dim rs As DAO.Recordset
Dim strSQL As String

Set db = CurrentDb

'Uncomment the following line if dg_category is numeric...
'strSQL = "SELECT code " & _
"FROM TableDx " & _
"WHERE dg_category = " & Me!assessment

'Uncomment the following line if dg_category is textual...
'strSQL = "SELECT code " & _
"FROM TableDx " & _
"WHERE dg_category = '" & Me!assessment & "'"

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

rs.Close
Set rs = Nothing
Set db = Nothing
End Sub

Rem setdat
Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
With rs
.MoveFirst
.Edit
!xmdate_of_interest = pt_exam!pt_exam!xmdate_of_interest
.Update
.Close
Me.Bookmark = .Bookmark
End With
rs.Close
Me.Combo43.Requery
Me.Combo39.Requery
Me.Comboselectdate.Requery
' requery yet another listbox
Me.List_HX_all.Requery
Me.List_todaysrecs.Requery


Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
 
Thanks, this seems like the right direction. I received an error message in the
Lostfocus code:

method or data member not found

the debugger opens up at:
If (Me.assessment.Tag = "36500") Then
GoTo doMSG2027F

It doesn't like: Me.assessment.Tag
if I put Me.assessment.value

I do not get the error

why won't it accept TAG?
 
Mark,

No, use Me!assessment.Tag, not Me.assessment.value!

Make sure the following is all on one line:
If (Me.assessment.Tag = "36500") Then GoTo doMSG2027F

Also make sure the label doMSG2027F exists within the same procedure.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
 
graham,


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

End Sub

Private Sub Combo_assessmt_LostFocus()

' Make sure the following is all on one line:
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 codenumber 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;
Click to expand...
where Tablecat is yet another table of diagnosis categories
I am stoing diagname in "assessment" but am looking to get diagcode into the
TAG value for each stored assessment
 
Mark,

Send me the zipped database (including backend) with a full explanation of
where to find the problem. Set the email Subject to "MVPHelp", otherwise my
server might kill it before I can see it.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
 
I appreciate you attempt to help.
I cannot send a zipped DB for various reasons.
I assume you are suggesting that the code I sent below looks correct.
In such a case, I'd rather start a new thread and have someone else take a
stab at it.
 
Back
Top