I'm not entirely sure how to summarize this quickly and correctly in the Title box, since I'm not sure where the solution lies as yet...ultimately, all I need to do is get a number and it's associated location name passed to a report headers. So here's my problem: I have inherited an Access database (I think it was created with 2007; my machine was just upgraded with 2013). There is a large "Documents" table with records of company documents. The ones I'm concerned with are all related to various Workstations, and the database connects them via a "Workstation Number" field which lists each workstation in which the document should be found. Each workstation has a 3-digit code (sometimes with a letter qualifier), and a location name. So it could be "001 | Maintenance", it could be "025B | Misc". The field is formatted so that the values are separated by a comma and a space, so it could look like: "001" or "001, 002, 025B"...the user is expected to maintain the formatting correctly, it's an open short text field. That's a fix for another time.
What I am trying to do is create a report...basically, the current DB has individual, hard-coded reports & queries for each of about 190 Workstations, I want to get this down to one of each, with a form for the user to select which Workstation they want to run the report for. So I have a form (frmIndexReport) with a combo box (cmbIndexReport); the combo box is a lookup to a table named "Workstation Manuals"; when the user clicks the combo box, they see a list of all the Workstation 1)Manual Numbers and 2)Locations, so "001 | Maintenance". They click the one they want, and now the combo box displays just the Manual Number, the bound column. The Location disappears...I would really like this to remain visible, this is secondary, but possibly related to my primary concern.
So the user clicks a button, and the report loads, calling the Index Report Query, which takes the number obtained from the combo box and searches the Workstation Manual field from the Documents table for any instance of it...I use:
Like "*" & [Forms]![frmIndexReport]![cmbIndexReport] & "*"
This will return a match that occurs anywhere; I need to be able to grab "001, 002" and not just "001". Well, this works fine as far as the data is concerned, and the report is almost exactly what I want, my only problem is that I cannot seem to bring in the Location name correctly. In the header, I need the Workstation Number on top, with the Location name beneath, but I can't seem to get the Location name to be passed correctly. It only works right if the first record returned contains an exact match, otherwise the text box will be blank. I know there has to be something painfully obvious that I'm missing, please help!
What I am trying to do is create a report...basically, the current DB has individual, hard-coded reports & queries for each of about 190 Workstations, I want to get this down to one of each, with a form for the user to select which Workstation they want to run the report for. So I have a form (frmIndexReport) with a combo box (cmbIndexReport); the combo box is a lookup to a table named "Workstation Manuals"; when the user clicks the combo box, they see a list of all the Workstation 1)Manual Numbers and 2)Locations, so "001 | Maintenance". They click the one they want, and now the combo box displays just the Manual Number, the bound column. The Location disappears...I would really like this to remain visible, this is secondary, but possibly related to my primary concern.
So the user clicks a button, and the report loads, calling the Index Report Query, which takes the number obtained from the combo box and searches the Workstation Manual field from the Documents table for any instance of it...I use:
Like "*" & [Forms]![frmIndexReport]![cmbIndexReport] & "*"
This will return a match that occurs anywhere; I need to be able to grab "001, 002" and not just "001". Well, this works fine as far as the data is concerned, and the report is almost exactly what I want, my only problem is that I cannot seem to bring in the Location name correctly. In the header, I need the Workstation Number on top, with the Location name beneath, but I can't seem to get the Location name to be passed correctly. It only works right if the first record returned contains an exact match, otherwise the text box will be blank. I know there has to be something painfully obvious that I'm missing, please help!
