TAG value problem/SET Tag value

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?


 
0
Utf
1/3/2008 4:04:00 AM
access.formscoding 7493 articles. 0 followers. Follow

3 Replies
1155 Views

Similar Articles

[PageSpeed] 29

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;



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.


-- 
Albert D. Kallal    (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@msn.com 


0
Albert
1/3/2008 4:46:30 AM
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



"Albert D. Kallal" wrote:

> 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;
> 
> 
> 
> 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.
> 
> 
> -- 
> Albert D. Kallal    (Access MVP)
> Edmonton, Alberta Canada
> pleaseNOOSpamKallal@msn.com 
> 
> 
> 
0
Utf
1/3/2008 7:56:01 AM
"mark r" <markr@discussions.microsoft.com> wrote in message 
news:4E403242-3D55-431B-B06C-94DE84835657@microsoft.com...
> 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)

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

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)
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:

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?

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

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.


-- 
Albert D. Kallal    (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@msn.com 


0
Albert
1/3/2008 9:57:28 AM
Reply:

Similar Artilces:

sum values from range of cells if cells correcponding have the sam
hiya, i have a small project and i've spent too much time on this already - can't figure it out myself. What i need to do is to create a formula which will find the same values in column A and will sum the corresponding to them values from columns B:E. this is a sort of result i want to get: A B C D E F RESULT: 1 A1 1 5 2 3 A1 31 2 B5 2 6 3 3 A2 19 3 C1 3 7 4 3 B1 22 4 A1 4 8 5 3 B5 37 5 B5 5 9 6 3 C1 33 6 C2 6 1 6 3 C2 16 7 A2 7 2 7 3 8 B1 8 3 8 3 9 C1 9 4 0 3 I would appreciate any ideas how to do this. Thanks and regards, Assume in G1 down are the unique item...

How do I search for a string, without risking the "#value!" error?
Hi How do I test out whether a cell has any given string of characters in it, *without* returning the "#value!" error?! I've tried using =SEARCH() and =FIND() functions but both produce the "#value!" error if the cell in question doesnt contain the string being searched for. (And the problem with the #value! error is that you then cant count or total that column without getting more #value! errors!) Any suggestions? Ship Shiperton Henethe Try : =IF(ISERROR(yourfunction),"",yourfunction) -- Rgds Max xl 97 --------------------------------------------...

Including records with null values in report
I'm buliding an access database for a small business. I have a parent table which is related to 4 child tables. The four child tables are subforms in my form design. I created a query which pulls data from all 5 tables (parent and children included) using the Q wizard and used this for my report design. Here I realized that if all the tables have data in them, the report works fine. However, if one of the child tables contain null in it's primary key field (i.e no data was entered in that table at all) the whole of that record including data from the parent and child ta...

ntbackup
I'm still testing my Exchange Server environment and todays testing was single store and whole storage group restores from an NT backup file. Backup was created on 23/03/2007 - Whole storage group was selected for backup. tests: - delete the contents of my mailbox - delete the contents from OWA's recover deleted items - dismount mailbox store - run an eseutil /d to defragment and compact priv1 files - remount store to check everything is still ok and items are gone - dismount store - tick "this database can be overwritten by a restore" - start nt backup, choose medial file,...

MFC 64-bit working, but problems... please help!
Hi folks I have MFC compiling 64-bit under VS6 IDE now, without errors reported, but I always get a dialog with this error when I run the exe: "The procedure entry point ??2@YAPAX_K@Z could not be located in the dynamic link library msvcrt.dll" The program compiles, links and runs fine in a 32-bit build. I'm 99% sure my 64-bit stuff is all pointing to the correct places. I do end up with a 64-bit MFC executable app, it just won't execute! At least not past the dialog and error. Any ideas? Thanks "goblin" <goblin@spooks.net> wrote in message news:Xns9...

Problem
Publisher 2003 beta with the latest upgrade: When I turn to a page (with grafics) in a document, the grafics are blinking - twinkling (I don?t knew the right word) twice, three times, sometimes ten times or more, and I can?t stop it. I also have the latest video driver. Has anybody else this problem? Berndt Hi Berndt, Is this in all .PUB files or only one? Was this publication created in this version or an earlier one? Do you get the same thing if you start Publisher 2003 in Office safe mode (hold ctrl key whey clicking the Publisher shortcut/icon)? ======= <<"Berndt H&q...

Outlook and Reading Pane problem
Hello All, Fired up Outlook this morning to find the reading pane gone. Checked under the veiw menu, and the reading pane option is completley greyed out. Makes it kinda touch to turn on LOL. If anyone has any ideas on why its greyed out, or how to fix it please post. version of outlook? is a message selected? -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Need Help with Common Tasks? http://www.outlook-tips.net/beginner/ Outlook 2007: http://www.slipstick.com/outlook/ol2007/ Outlook Tips by email: dailytips-subscribe-request@lists.outlooktips.net ...

Tagging and routing mail destined for specific domains
Due to a change in NDA's I need to be able to tag, and copy mail that is being routed to and from specific domains. I am using exchange 2003 std (sp2) on 2003 server. As an example if a user were to send an email to microsoft.com I need to add a legal disclosure footer and keep a copy of that email locally. The same would be needed on inbound mail from microsoft.com to be copied to an additional location internally. Is this something that exchange can handle or do I need to route this through a second system to get this kind of tracking, etc..? On 7 Dec 2006 15:10:57 -0800, natedizzy@...

Exchange Server Overriding Outlook Client "Mail Format" Setting?
Hello. Can anyone tell me if it's possible that a setting on the Exchange Server could be overriding my client's "mail format" setting? Up until about two weeks ago when our sys admins migrated our Exchange Servers, I had no problem sending MS Outlook Objects to myself at home (I am using MS Outlook 2003 both at work and at home ... Calendar entries, Contacts, Tasks, Notes, etc... I sent all e-mail to myself in Outlook RTF format, and I received them all in RTF format ... I would just drag and drop the objects into my local client to duplicate contacts, tasks, etc. on my sy...

use same email address to set up new microsoft outlook email accou
I would like to keep my same email address (with RoadRunner) to open a Microsoft Outlook email account and not have to change it. Is that possible? Also, if it is, cannot figure out what to type in POP3, SMTP to set up that account. Is it because I am using another ISP? thanks. desperate to find out! ...

text to tags
I want to know if it is possible to convert plain text to XML tags using MACROS. If so pls tell me how to go abt this process. What do you want to convert them to? -- Hope this helps, Doug Robbins - Word MVP Please reply only to the newsgroups unless you wish to obtain my services on a paid professional basis. "Sahana" <shalmalibarki.admin@gmail.com> wrote in message news:be15bd60-dad2-4a71-b89b-f2ba90fe8bbe@u36g2000prn.googlegroups.com... >I want to know if it is possible to convert plain text to XML tags > using MACROS. If so pls tell me how to go a...

Value Axis compressed
In Excel 2003, I have several worksheets that include charts. These are all standard charts created by "Insert / Chart" and formatted with Excel's chart tools. In some of them, the value axis is highly compressed when the chart is viewed in print preview or is printed. When viewed in normal mode, the chart is OK. These are line graphs. For example, even though the values are OK, the spacing of the grid in the plot area is vertically compressed to an extreme, perhaps to as little as one-third of its normal height. The plot area is also raised to the top of the graph ...

TAG value problem/SET Tag value
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!d...

windows media player transferring media info id3 tags
I need to know how to preserve some of the media information I am inputting on my Windows Media LIbrary. When I copy the files to another computer only the basic info about the songs shows up. None of the "custom" info or the "mood" or the "keywords" are transferred. how can I avoid losing this info while still using WMP. thanks. -- clp253 Not all the info you enter is stored in tags so in the audio file. It is also dependent on the file format used, e.g. in WAV almost none of the info is stored and certainly not any edits. The only think you ...

New page based on value
How can I force a new page return in a report based on the value of [Owner]? [Owner] is from 1 to 22. What I would like to do is make sure the owner only gets his information, so when [Owner] changes I need to issue a new page return so the next owner starts at the top of a new page instead of being mixed with the previous owner's information. Thanks. Rod wrote: >How can I force a new page return in a report based on the value of [Owner]? >[Owner] is from 1 to 22. What I would like to do is make sure the owner only >gets his information, so when [Owner] changes I need...

Strip HTML tags
is there any simple mean of removing simple HTML tags(b, u, p) from a string. In Java or .Net I would do something like String text = html.replaceAll("<[^>]*>", ""); and In MFC ? I am not using &amp and this kind of tags so I need a simple solution. mosfet a �crit : > is there any simple mean of removing simple HTML tags(b, u, p) from a > string. > In Java or .Net I would do something like > String text = html.replaceAll("<[^>]*>", ""); > > and In MFC ? > > I am not using &amp and this kind of tags...

Problem Opening Password Protected Workbook/Worksheet
After I entered the correct password for my password protected worksheet, I receive message that file not found and worksheet is not opened. What went wrong???? Hi have you tried this from within Excel (using File - Open)? -- Regards Frank Kabel Frankfurt, Germany "excelman" <anonymous@discussions.microsoft.com> schrieb im Newsbeitrag news:057001c4aab6$029b2bb0$a601280a@phx.gbl... > After I entered the correct password for my password > protected worksheet, I receive message that file not found > and worksheet is not opened. > What went wrong???? > > Hi...

Problem with Mercury Gift Card Balance Function in POS
I was wondering if anyone had come across this issue using POS. The function (a button that is set up in POS) is used so that a cashier can swipe the Mercury Gift Card after being prompted to do so, and the card's balance is returned. The problem we are having is that nothing comes back from Mercury, even though it appears it is running. If the card number is manually entered, then a balance is returned. We haven't had any problems with using the swiper for credit card and gift card sales, so I'm thinking that there is something wrong with the function itself. (I have already ...

copying value between subform and form?
Hi there, How do I transfer a value from a sub-form to the main form? To be more precise: I've created a button on the sub-form and I would like that by clicking it a value from a certain field on the sub-form will then be copied to a field on the main form. I tried a few things but I keep getting error messages. Any help is appreciated! Thanks a lot! Jerome forms![mainform]![txtControlName].Value = forms![mainform]![subform]![txtsubformcontrolname].value -- --- The glass is neither half empty nor half full. It is simply twice the size it needs to be. "Jerome" w...

find largest value in a row
I have a spreadsheet with bridge component ratings in three columns. I need to find the largest of the three and have it put into another column. I've tried to use INDEX and MATCH, but can't quite get it to work. All the examples I've found have had slightly different end goals, and I'm not adjusting the formula correctly. Can someone help? (I tried to include a sample table, but how do you get it to line up correctly?) Do you mean to find the largest of 3 values and put that into the adjacent column ? Try MAX(B2:D2). Bas Hartkamp. <vicki.wells@modot.mo.gov> sc...

HOw to print sale dates on labels/ shelf tags
Anyone knows of a way to print sale dates on labels/ tags ? I do not see an Sale date in any of the label filed options Robert Hi Robert, Are you interested in custom solution ?. If so we can discuss Regards, manick manick.m@hotmail.com "Robert" wrote: > > > Anyone knows of a way to print sale dates on labels/ tags ? > I do not see an Sale date in any of the label filed options > Robert > Robert, In answer to both of your posts, we have a solution called RITE Label that allows additional fields to be included in a label format. Sale Dates are amon...

Problem: Non-modality of modal dialogs
Hi All, I have a dialog-based application in MFC (main window is CDialog1). I have a separate thread (class derived from CWinThread) in the application. The main window (m_pmainWnd) of the thread is a hidden modeless dialog (this is CDialog2). I have created the thread in InitInstance() of my application class using AfxBeginThread (). I PostMessage() to the "hidden modeless dialog" on a button-click of the application dialog. The PostMessage() handler displays a modal dialog (this is CDialog3). Button-click on 'Dialog3' displays another modal dialog (this is...

problem with primary key in schema
Hello all, I have a schema which includes the primary key for the table. The primary key was added by the VS XML Designer tool, but when I generate my typed dataset from it there is no findby method using the key. Any advice gratefully received.. An abbreviated version of the xsd follows: <?xml version="1.0" encoding="utf-8" ?> <xs:schema id="TideStations" elementFormDefault="qualified" xmlns="http://tempuri.org/XMLSchema1.xsd" xmlns:mstns="http://tempuri.org/XMLSchema1.xsd" xmlns:xs="http://www.w3.org/2001/XM...

Time conversion problem
I am trying to write some data analysis macro code and am having problems with how Excel is and isn't storing the times. During a study a researcher enters, for example 4:39. That needs to be 4 minutes and 39 seconds. However, Excel formats it as 4 hours and 39 minutes - as "h:mm". I really want it formatted as "hh:mm:ss" so it would be 00:04:39. If I go in and change the cell format to "hh:mm:ss" it simply writes the value out as 4:39:00. I've tried switching back and forth between various date, general, and text formats to no avail. For a number of reas...

Email problem with hostname
I got this error when sending email to my distributor in Brazil. ERROR: The message was undeliverable because the recipient specified in the recipient postal address was not known at this address. 450 Client host rejected: cannot find your hostname, [my ip address]. I have Exchange 5.5 sp4 and I checked with my ISP and DNS, everything correct and PTR and Reverse DNS are all checked out. Thanks in advance for any assistance. "toby one" <support@re.com> wrote: >I got this error when sending email to my distributor in Brazil. ERROR: The >message was undeliverable be...