Result of drop-down from Query puts X in 1 of 19 boxes

[spudracer]

I have a query which works perfectly fine, it's the report that I'm having issues with displaying correctly.

My report is a daily personnel accountability report that shows where everyone is for the day. Instead of having a cumbersome query like I did before, I have opted to just use the results of the selected drop-down option to move the X to the appropriate box of where so-and-so is for the day.

Using

If [marked_as] = 1 Then
Me.Morning.Value = "X"
ElseIf [marked_as] = 2 Then
Me.Afternoon.Value = "X" 
ElseIf [marked_as] = 3 Then
Me.Evenings.Value = "X"
End If

I was unable to get it to work accurately outside of showing the three dummy names under the same column, even though the three dummy names were each placed in one of the three test categories.

I have this set as a private function called when the report loads, which is based on a query that filters down to the exact department or office (depending on the user's selection). Like I said, that part works fine, it's getting the code to accurately display in the correct column.

 

[pr2-eugin]

Hello spudracer. The description you have given; to explain your scenario makes perfect sense to you, because you know what you are dealing with. However for a third person looking into this, it makes very little sense. So could you please explain with some example data of what you have and what you wish to see. Then we could think about a possible solution.

 

[spudracer]

Each row lists the person, followed by an X in the column annotating where they are at.

I explained it pretty well the first time. Basically, how do I take the result of a drop-down, and using VBA, take that numeric result that has a value anywhere from 1-19 and put an "x" in the corresponding column on the report.

 

[spudracer]

Ok, now that I've wasted an entire day trying to figure this out, I still don't have anything.

I have a drop-down with three options: 1) Days 2) Afternoons 3) Nights

This data is only displayed in drop-down format on the Form.

On the report, I have the three columns next to each other (I plan on adding more columns) with some code in the On Format box:

If MarkedAs.Value = 1 Then
Me.Days.Value = "X"
ElseIf MarkedAs.Value = 2 Then
Me.Afternoons.Value = "X"
ElseIF MarkedAs.Value = 3 Then
Me.Nights.Value = "X"

My current problem is that while the first row of my three dummy records puts an X in the appropriate box, the following two rows place an X in the first two columns. For testing, I put the bottom record as having a MarkedAs value of 4, clearly outside the code but it still registered an X in the first two columns.

I don't know of any better way to explain it, I'm just trying to keep from having 38+ columns in my query. Is my if statement messed up? Is it just impossible to run this kind of coding in a report? I don't know. I'm stuck.

 

[GinaWhipp]

I'm not sure I get it either but I *think* you can just use a Select Case statement, so...

Select Case MarkedAs
   Case 1
      Me.Days = "X"
   Case 2
      Me.Afternoons = "X"
etc...
   Case Else
      'Put something here
End Select

There might be a better way but I can't picture a *real world* scenario were this would apply so I'm drawing a blank for an easier solution.

 

[spudracer]

I'll give it a shot in the morning. Thank you!

 

[JHB]

Else show the data returned from the query and a print screen of your report + where it goes wrong and what you expect.

 

[vbaInet]

Is your combo box synchronised with the report? That is does it have a field in its Control Source?

You're better off using a subreport and link it to the report's MarkedAs field.

 

[spudracer]

I have the combo box as a field in the detail section of the report, yes.

I don't know what it is, but the first two rows work just fine, it's the third row that puts an X in the first two columns.

 

[vbaInet]

Well that doesn't really answer whether it's linked via its Control Source or not. So fyi Control Source is a property of the combo box which you can find in the Property Sheet.

Are you trying to retrieve values from columns in the first row or from different rows and columns?

 

[spudracer]

Same row. I've put more time into it than I would have liked, so for the time being I've gone back to the original plan that works (it's just a huge query).

In the meantime, I'll continue to mess around with it and see what happens.

vbaInet, were you suggesting that I put my table that stores the different results into a sub-report? The way I have it setup currently (via the query) is Name, MarkedAs (just so I can see that it is, in fact, returning the correct value), Days, Afternoons, and Evenings as unbound text boxes.

An example of the results I get are as follows:

Name MarkedAs D A E
Person 1 1 X
Person 2 2 X X
Person 3 1 X X

Regardless of whether or not I mark one as being on Evenings (3), it will never place a value under Evenings. The query data marks them correctly, but it obviously returns the text value of the drop-down, vice the numeric value. Could it be that I need to re-structure my query to return a numeric value instead of the text value, even though the numeric value is what is displaying in the report?

 

[vbaInet]

Same row.

Name MarkedAs D A E
Person 1 1 X
Person 2 2 X X
Person 3 1 X X

Same row for Person 1, same row for Person 2 and so on right? I'm guessing your report is grouped by Name and you want it to select the appropriate row based on the Name that it's currently on? It can't move to Person 2 or Person 3 if you don't tell it. The Column property has a column index and row parameter. I've noticed that in reports the two parameters are swapped around on the version I was testing this on.

vbaInet, were you suggesting that I put my table that stores the different results into a sub-report? The way I have it setup currently (via the query) is Name, MarkedAs (just so I can see that it is, in fact, returning the correct value), Days, Afternoons, and Evenings as unbound text boxes.

Yes! Build another report based on the table then drop a subreport on your main report and use this new report as the Source Object. You will then link the subreport to your main report via the Name field. And if the subreport is already displaying those values, you don't need another set of unbound textboxes.

By the way, I'm hoping Name isn't a field name in your report because it's reserved for Access. Here's a list:

http://allenbrowne.com/AppIssueBadWord.html

 

[BlueIshDan]

Is this the kind of report you're looking for. Also is it based of off records and has no direct connection to the combo box other than the combo box edits the values of the records on the form then you display the records on the report?

If so here is the outcome of what I had in a report:

By using these values in the Control Source properties of the text boxes:
=IIf([index]=1,"X","")
=IIf([index]=2,"X","")
=IIf([index]=3,"X","")
=IIf([index]=4,"X","")
=IIf([index]=5,"X","")

 

[spudracer]

vbaInet and BlueIshDan, thank you! I'll give it a shot today!

 

[vbaInet]

Is BlueIshDan's layout the sort of thing you're after then? You still haven't made it clear what the exact layout should be.

 

[spudracer]

Is BlueIshDan's layout the sort of thing you're after then? You still haven't made it clear what the exact layout should be.

Yes, that was exactly the result I was hoping would populate.

 

[vbaInet]

I see! Let us know what you come up with and we'll take it from there.