Multivalue field problem

[Skatti]

Firstly, I am a medic and not a programmer. I use access to create simple databases for my workplace. So please bear with me if my question is stupid or does not make any sense.

I have a single table data base (I know. My use of access is that basic) with a single column multivalue field 'Immunostains' in it. There is a form feeding into this table with two command buttons that save the record and print a relevant report (one for each command button - Extrawork report and Herceptest1). Now, I want to get rid of one of the command buttons and use just the one that can print both reports or only one depending on the values in the 'Immunostains' field. So if the immunostains field contained the value 'Her2' amongst others, I want both reports printed out. If this value is absent, then just the Extrawork report. I hope what I am trying to say is not complete twaddle.

Here is the code I wrote for it. It prints both reports if the value in the field is only Her2. But if there are other values with Her2, it prints only the Extrawork report. what am I doing wrong? I am sure it is do with how multivalue fields work - but it is beyond me. Any help will be greatly appreciated.

Private Sub Command33_Click()
If Me.Dirty = True Then Me.Dirty = False

If Immunostains.Value.column1 Like "*Her2*" Then

strReport = "Extrawork report"
strWhere = "[Id]=" & Me![ID]
DoCmd.OpenReport strReport, acViewNormal, , strWhere
strReport = "Herceptest1"
strWhere = "[Id]=" & Me![ID]
DoCmd.OpenReport strReport, acViewNormal, , strWhere
DoCmd.GoToRecord , , acNewRec

Else
strReport = "Extrawork report"
strWhere = "[Id]=" & Me![ID]
DoCmd.OpenReport strReport, acViewNormal, , strWhere
DoCmd.GoToRecord , , acNewRec
End If
End Sub

 

[Old Man Devin]

I've never actually used a multivalued field, but looking at your code I suspect the line:

If Immunostains.Value.column1 Like "*Her2*" Then

Needs to be more like:

If Immunostains.Column(1) Like "*Her2*" Then

This may help!

 

[Skatti]

Thank you very much for your response old man Devin.

I have tried changing that line to both

If Immunostains.Column(1) Like "*Her2*" Then

and

If me.Immunostains.Column(1) like "*Her2*" Then

The problem still persists. I guess I will have to stick with two command buttons.

 

[Old Man Devin]

One other thing to check is that column 1 (which is the second column) is visible in the row source of the box and that it is set to show in the combo box. So you need to have the combo box have 2 as its number of visible columns, but if you don't actually want to see the 2nd one when using it, set the list of column widths to be like:
3cm;0cm
so the second column won't be there.

If that isn't the problem then I'm not sure quite sure what is. If you can live with keeping both buttons, maybe adding in some extra notes to explain when each one should be used so no one presses the wrong one by mistake, then you should still get what you need.

 

[Skatti]

Tried what you suggested. Still the same result.

I have also tried using the IN operator as follows:

If me.Immunostains.column (1) In (*,"Her2",*) Then

But it gives a compile error for that line. (Expected Then or Go to)

 

[Old Man Devin]

I don't think the IN route is what you want here, as having IN then a list that contains * will just mean anything can satisfy your if condition.

It's strange that Like "*Her2*" doesn't work, so I can only guess it really is something strange to do with how multi-valued fields are read in code as you originally suggested.

One good idea might be to put a break in the code on the line in question, then run it. Then mouseover the Me.Immunostains.Column(1) bit and a tooltip shoudl appear telling you the value of this statement. That might give you an idea of how the code actually perceives your multi-valued field and thus be a clue to what condition your if line should have.

 

[Skatti]

I did as you suggested. The tooltip message is

Me.Immunostains.Column(1)=Null

Does that help in any way? Sorry, I am an absolute novice and thanks again for taking the trouble to reply.

On a positive note I learnt how to introduce a break in a code!

 

[Old Man Devin]

That would suggest that the column either doesn't exist, or isn't visible for that combo box.

By the way, don't know if you're aware but in access the first column is column 0, so .column(1) is looking for information in the SECOND column of the box you're looking at. So that might be the problem if you didn't know this.

Otherwise try looking at the row source for your box to make sure that the second column always has something in it, and is populating you expect.

Breaks are always good to know about, I use them constantly, really helpful! Another tip is that when you code has been broken, tapping F8 on the keyboard makes single lines of code run. The highlighted line then changes to the next line without actually executing it. using this you can see how the values of variables in your code change line by line, which can be really helpful for working out why something is behaving strangely.

 

[Skatti]

I remember reading about the columns in these fora. The second column does get populated.

The code works just fine when there is only Her2 in the Immunostains field. But when there are other values along with Her2 it runs just the else part of the code. So I suppose it is either the operator or the "*Her2*" bit that needs changing.