Refreshing Form source with selection from drop-down

[EdwardMartinIII]

Hiya,

First, I'm kinda' new at this, so please -- if you don't mind -- explain a little more clearly than if I were, say, an Access wrangler.

I have built a Trouble Ticket database with lots of fields, including a field called "Tester," which is the person who submitted the original Trouble Ticket. The list of possible Testers is a different table, because different people come in and out.

I have used in previous forms the forms wizard so I can have those little buttons at the bottom of the form that allows one to page through records. This is damn handy.

I'm attempting to build a form right now that's stumping me with a particular functionality.

I'd like to make a drop-down (a combo box?) that gets its rowsource (data?) from the Testers table. I can't hardcode this because the Testers change often. The rest of the form shows various values from each of the Trouble Ticket records, such as Problem Description, Technician Notes, etc. When someone selects a specific Tester from the drop-down, then I would like the form to "repopulate" to records only having that Tester in the tester" field.

So, upon opening, the form will show all Trouble Tickets ready to review (say, 300). Then, "Edward" selects his name from the drop-down box (this is my rudimentary version of a "login&quot and there's a second or two of chugging and then the little display at the bottom of the form reports only 122 records (All the records where "Edward" is in the Tester field).

I also have a button on the form that says "Approve Resolution" and if I press that, then a field value in that record ("Status&quot is changed, the record should be saved (and wouldn't it be keen to play Handel's "Hallelujah" chorus when this happens?), and the form refreshed again (now 121 records to approve).

I would even be happy if someone could suggest a good path of exploration (and a few keywords). I'm fairly clever in a variety of things, but database technology/terminology is really new to me (my last database was Appleworks for the Apple ][e, back in 1984). Our reference books here seem to all assume a person has a good grasp of db background and just wants to know how Access does "it".

Thanks,

Edward Martin III "Do not read this sentence."

 

[hwkranger]

What you want is just a query of the records where the tester field is "Edward"

Why not just use "filter by form" and "apply" filter buttons to what you want? They are located at the top of your screen

I could be entirely incorrect but.. if you click "filter by form" it makes most fields have a "drop down box" where you can select the information to go in the field. Or you can type the information in the field. then click on "apply filter" (It looks like a beer bong sort of)

I hope this helps. If not, sorry ;-) Cruz'n and Booz'n always.
This post shows what little I do at work.

 

[Darrylles]

Hiya,

I assume that testers 'come and go' at will and you need to dynamically 'update' who is 'in / out' regularly - in a combo, but from within a form?

In any case - you MUST 'hard-code' this combo with the 'datasource'; you may however refresh this datasource at will.

You can use a timer to refresh the combo at regular intervals (whether a tester has added a record or not).

Alternatively you can also have a global variable that is updated when the tester adds a record (say a DateTime variable), you then - within your form use a timer to check the global variable. If changed then you want to update the form and combo (dependant on any current form transactions,
i.e. if you are currently editing a record - then the form can't be updated).

This is a very broad idea, but I can help with detail if this seems the solution.

Some expert may well appear with a better solution.

Regards,

Darrylle "Never argue with an idiot, he'll bring you down to his level - then beat you with experience."

 

[EdwardMartinIII]

hwkranger writes "Why not just use "filter by form" and "apply" filter buttons to what you want? They are located at the top of your screen "

I know this functionality.

Four reasons:

1. I only want "Edward" to see Trouble Tickets associated with Edward. I don't want him to even see tickets associated with Chuck (although, truth be told, Edward could easily see Chuck's Tickets by choosing "Chuck" from the combo box -- I did say my "login" procedure was rudimentary, right?). Think of the resulting list as being Edward's "to-do" list.
2. On an ordinary form made from the wizard, if I pick "Edward" in the combo box, it'll set that field in the record to "Edward". Sure, I can probably figure out how to get the action of the combo box to choose a record such that Tester field contains "Edward" (hint...?). However, in order to do a filter, I have to select a record with the desired value. If no record contains "Edward" in the Tester field, then I want to see no records visible.
3. I want the selection done programatically because not all testers have a lot of training on databases (although one could respond that the db monkey in this situation hasn't a lot of training on db's, either...)
4. Crap, I forgot this reason.

You have, however, got me sniffing at the form's "filter" property. I'm going to explore using it and changing it programatically...

Cheers,

Edward "Do not read this sentence."

 

[EdwardMartinIII]

Darrylle writes "I assume that testers 'come and go' at will and you need to dynamically 'update' who is 'in / out' regularly - in a combo, but from within a form? In any case - you MUST 'hard-code' this combo with the 'datasource'; you may however refresh this datasource at will."

Ah, I wasn't sufficiently clear. You seem to be thinking that the form stays open all the time. Nah, when they complete their approvals, they shut the whole thing down.

The list of Testers is a table of all employees. In that table is a bunch of Boolean fields, such as "Manager", "Programmer", "Tester", "Relatively useless in the sack", etc. I've written a query that returns all employee names from fields where the "Tester" Boolean is set. Think of it as randomly selecting five testers for each week.

The Tester looks over their "to-do" list, approves the items they can test, skips the ones they can't test, and when they're done, they close the form, exit Access and go home, or whatever.

Next day, there are changes made to the Employee database. Then one of the five new testers sits down, and starts up Access and goes right to their little form, which looks up on the other table who are the new testers today.

If my example sounds dumb, I'm sorry -- it's still smarter than the reason we need a separate table of Testers. 8)

"You can use a timer to refresh the combo at regular intervals (whether a tester has added a record or not)."

This is what I would do: I would ask you to explain how I would process the timer event such that the form is reloaded only with records whose "Tester" fields contain the contents of the combo box. Then I would hook that code to the onchange event of the combo box (I'm weak in db concepts, but I've been programming other things for a little while). That would save me unnecessary hits to the database.

Does that help, or did I just muddy it even more?

Cheers,

Edward "Do not read this sentence."

 

[EdwardMartinIII]

This is what I'm testing:

Private Sub Tester_BeforeUpdate(Cancel As Integer)
  ' Update form records to reflect chosen Tester
    ' Build Filter String
      Dim FilterString As String
      FilterString = "Tester='" + Tester + "'"
    ' Assign to form's "filter" property
      Me.Filter = FilterString
      MsgBox (Me.Filter)
      Me.FilterOn = True
End Sub

And when I try to change the combo box, I receive an error that reads:

Run-time error '2115':
The macro or function set to the BeforeUpdate or ValidationRule property for this field is preventing Microsoft Access from saving the data in the field.

Any ideas?

Cheers,

Edward "Do not read this sentence."

 

[Darrylles]

Hi Ed,

My misunderstanding from your initial question was that I could only assume that the 'testers' were on a 'real-time' client-server database - and you wanted to know when they had updated changes - real-time, from a form that needs this information NOW, and by a seperate ADMIN person - at the same time that the TESTERS were doing their job.

OK - is this a good description of your scenario?:....

You have multiple testers.
Testers are reponsible for their own tests.
A Tester sits at the comp - selects his/her name from the combo and all of THEIR personal test notes should be displayed ONLY.
-------------------------------------
If this is right, then from what you say, you must have a TESTER table with a PK primary key (autonumber preferably).
i.e. A list of Testers in a single table - all uniquely identified by a number (PK).
E.g tblTester
Tester_pk Auto
Name Text

The TESTER combo will load this table.

You then have a NOTES table with a TESTER_FK (foreign key) of type LONG) which points to the TESTER table PK.
E.g. tblNotes
Tester_fk Long
Notes Memo

When you ever select records from the NOTES table where PK is equal to the PK of a TESTER, then you will only ever get notes for that TESTER.
--------------------------------------

I may have your requirement wrong, but I'm on email notification and will call back.

Regards,

Darrylle




"Never argue with an idiot, he'll bring you down to his level - then beat you with experience."

 

[EdwardMartinIII]

Hi Darrylle,

Pretty close.

Each record in the Trouble Tickets table has only a single memo field for Tester notes, because only one Tester works that record until it is closed. It has other memo fields for Admin Notes, Dev Notes, Tech Notes, etc., but those aren't particularly relevant here (besides, the correct path for one will be the correct path I'll take for all the viewing forms).

I thought of a great way to explain this to someone who knows LabVIEW (just in case): This is a form full of indicators and two controls, getting its display data from a table called "Trouble Tickets". One control is "Select Tester" (which happens to be fed by a separate table) and the other control is a button cluster called "Change Status of Record", which interprets a particular button push and changes the values in that record's Status field.

When the form is created, the "Select Tester" does what it's supposed to do -- goes out and gets the right values from the table. The form in general populates with all records where the Status field is "Requires Retesting".

Hm... I wonder if I can tell the form to populate itself with records such that (Status = &quot;Requires Retesting&quot; and Tester = <value of tester control>). That would be interesting on the initial condition. Also, I wonder how to tell the form (or do I have to?) to refresh itself whenever I change the value of the combo box Tester.

Thoughts?

Cheers,

Edward &quot;Do not read this sentence.&quot;

 

[EdwardMartinIII]

This is what I ended up doing:

I made an ordinary wizard-born form (would that be called a &quot;Harry Potter form&quot;?), using a query that looks for all records which have a Status = &quot;Retesting Required&quot; and sorts by Tester.

Usually, this is only half a dozen records or so every day.

Then I made all the controls enabled, but locked.

Then I told the Testers to use the honor system and only monkey with their own records.

It does the job, I guess. If I want a real &quot;bulletproof&quot; application, it seems smarter to just write one in VB that looks at all the tables, rather than trying to finagle VBA in Access to do it.

Never did figure out how to solve the refresh-records-on-the-fly thing, though. To see the revised recordset, one must close the form and reopen it.

I'll learn better on the next one...

Thanks for the good suggestions, though!

Cheers,

Edward &quot;Do not read this sentence.&quot;