Help implementing a neat idea

[JordanR]

I’m working to ever-improve a Help Desk-like database and I got this idea for “templates.” That is, a quick way to fill out the as much of the ticket form as possible when common issues come in.

So I’ve created a table (Templates)that mimics the structure of my ticket table (Incidents). I’ve gone into Templates and put the values I want automatically entered in each field and I’ve added a combo box and a button on my ticket screen. The combo box is populated, from Templates, with the name of each Template.

My problem is that I have no idea how to make each field on the Incident form populate with the values from the Template table.

Any help would be greatly appreciated!

 

[Gibbo]

If I read you right, then you need an Update query to populate the current record in the incident table with the held values in the template table. The combo box will need to have a unque id value as it's 'return' value. The incident table shouls also have a unique id to identify each record. If the user has already selected a record in the entry form then this is will be established and you can use something like the following SQL to update the record.

UPDATE Incidents, Templates SET Incidents.Inc_date = [temp_date], Incidents.Inc_time = [temp_time], Incidents.Inc_what = [temp_what], Incidents.Inc_why = [temp_why], Incidents.Inc_howsoon = [temp_howsoon]
WHERE (((Incidents.Inc_id)=[forms]![entry]![inc_id]) AND ((Templates.Temp_id)=[forms]![entry]![template]));

All field names are clearly spurious and, in fact, not the ones that you would have in the templates.

Alternatively, if you want to use the combo box as a 'new record' box then you can use similar code as above to Append a new record when the user selects a template. You will need to save the record after appending and then run 'gotorecord last' to select the record.


A lot of the detail depends on how you are automated the form. Are using macros. procedures (or nothing!). If you are using VBA code, then the procedure can create the SQL above but replace the references to the current incident id and the selected template id with the actual values.

Throughout the process you will need to include 'save record' commands to avoid conlicts between data entered by the user and the updates that you have run through automation.

I hope that this is of help. Cleverer people may come back with better solutions. By the way, I prefer the append method which asks the user to select a template in order to add a new record - I have used exactly this approach in databases that I have designed.

 

[Matt Greatorex]

If all values are coming from this one Templates table, set the form's Data Source to be that table (open the Properties window).

Associate each field on the form with one of the fields from the table.

Add the following code to the After Update event of the first combobox/listbox (obviosuly, you'll need to change the str_Criteria so that the value the user selects will match a value from your table):

Dim str_Criteria As String
Dim rs As Object

str_Criteria = "[fieldname] = '" & Me![name of combobox] & "'"

Set rs = Me.Recordset
rs.FindFirst str_Criteria
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

When you then select an item from the first combobox/listbox, the others should be automatically populated.

 

[JordanR]

Thanks for the assist. Gibbo, I think you're closest to what I'm looking for. I'll try to explain again and hopefully clear up some confusion.

User goes into the database and clicks a button to create a new (blank) case.
Blank form comes up. On the right side of the screen there's a combo and button.
User selects "Printer Not Working" from the drop down and pushes the button.

The autonumber Case_ID field populates with the ID (obviously).
The category field populates with the value "Printer"
The classification field populates with the value "Error(s)"
The severity field populates with the value "Severity 2".
Etc.

Right now, it's doing all that because I have a massive case statement sitting behind the tempate button that says something like:
If combobox.value = Printer Error Then
Category.value = "Printer"
Classification.Value = "Error(s)"
Etc.

I'm trying to net the same effect in a more logical and maintainable way, buy putting all the field updates into a table. A update query seems to make sense and will try it, but let me know if you see a better way to be doing this.

Thanks again.

 

[JordanR]

Thanks for the assist. Gibbo, I think you're closest to what I'm looking for. I'll try to explain again and hopefully clear up some confusion.

User goes into the database and clicks a button to create a new (blank) case.
Blank form comes up. On the right side of the screen there's a combo and button.
User selects "Printer Not Working" from the drop down and pushes the button.

The autonumber Case_ID field populates with the ID (obviously).
The category field populates with the value "Printer"
The classification field populates with the value "Error(s)"
The severity field populates with the value "Severity 2".
Etc.

Right now, it's doing all that because I have a massive case statement sitting behind the tempate button that says something like:
If combobox.value = Printer Error Then
Category.value = "Printer"
Classification.Value = "Error(s)"
Etc.

I'm trying to net the same effect in a more logical and maintainable way, buy putting all the field updates into a table. A update query seems to make sense and will try it, but let me know if you see a better way to be doing this.

Thanks again.

 

[JordanR]

Any help? Anyone?
I'm just not seeing how to do this.

 

[JordanR]

Ok, I got further thanks to reading Gibbo's explaination a few times. Thanks man, I was just being thick.
So it looks like it should work, but I keep getting an error.
When I pick an item from the combo box and open the query into datasheet view, it looks like it's going to populate perfectly. But when I actually run the query I get:
"...can't append the records in the append query.
... set 0 fields to Null due to a type conversion failure and it didn't add 0 records to the table due to key violations, 0 records due to lock violations and 1 record due to validation rule violations"

I've even trimmed the query so only one fields gets updated and I still get the error.

Any thoughts?

 

[Severin]

Have a look at solving the issue with a multi column combobox!~)