Showing details for one specific record / Drag and drop files to get a link to them

[Timbo]

Hi there,

I’m not used to working in VB and I think this situation calls for excactly that. I use Access 97 SR-2.

My first table is a table containing all the Tickets I got. The field ”Ticket” is simply a ticket-number.

I’ve made a form in which I am to choose an existing Ticket and write a description for it pluss other things. The description to these Tickets are stored in another table called ”LogBook”. I then made a query on all of those Tickets which have not yet been described in ”LogBook”, and which is shown in the same form, so that I can quickly pick one Ticket out which lacks description.

The information in the two tables; ”Tickets” and ”LogBook”; is linked through the ticket-number. And that is the only information which is the same.

What I want is to show details such as date, time and price from the table ”Tickets” when I have chosen a number to be stored in ”LogBook”. Making a query where the number in ”Tickets” is the same as in ”LogBook” only results in showing all the records for every ticket recorded in ”LogBook”. So, specifically, I want to show details for just that ONE specific record that I am currently viewing in my form. And I have absolutely no idea of how to do that. I am pretty sure it involves VB, but how can I: chose a ticket, and when the ticket is chose, query upon that exact ticket and show it’s details from another table in the form.

The other problem that I have is far too advanced for me. I want to aquire a link or an address to a chosen file without typing it manually (not a specific and static filename). I collect pictures in one folder and I link them to a table in the database (ie. ”Pictures”), and for some of these pictures, I link them to the ”Tickets”. I did find out how to show the picture in the form for every record it existed. Now I would like to know if there was another way of getting the link of the files to the tables instead of typing it. So specifically, I want to know if I can drag and drop the file from ie. my desktop to a field in a form which then translates the object into an address and stores it as a link (as text and not an OLE-object; not hyperlink either as I then can’t get Access 97 to show me the pictures for every record).

The third is just a curiosity: I use a rating on a scale from 1 to 10; ie. I rate the pictures. I have one table in which I store the scale and the meaning of the numbers. Is it possible to have a ”Rating”-form in which there are 10 checkmarkers or buttons; where one marker only represents one of the ratings?

This is just for apperance sake. I am currently using a list which is dragged all the way down showing all the 10 numbers.


I really hope someone out there will be willing to help me out. You don’t have to answear each and every question posted here.

Kind regards,

jg

[comteck]

The first problem you mentioned, I don't think I quite understande what you are asking. I'm going to give what I think might be solutions, but I'm not sure if it will help because like I said, I don't quite understand the problem.

Problem 1 - I'm not sure if you want to limit the number of fields you want to show on your form, or if you want to actually filter the records (by "filtering" I mean, selecting a record from a list of tickets, and displaying just that ticket with all of the fields you selected in your query). If you just want to limit the number of fields, it's just a matter of limiting them in your query, form, or report (i.e only drag the fields that you need).Only problem with that is if you don't drag a field in your query, you won't be able to use it on your form.
If it's filtering you want to do, then let me know. It's a little more complex, and I won't type it all out if that is not what you are looking for.Let me know.

Problem 2 - For problem 2, I can see what you're asking... I'm just not sure how it's done (or if it even can be done). The best I can tell you at this point is to set up a field in your table as an "OLE Object" datatype (give the field a name, say "image1"). Then, on your form, create a Bound Oject Frame, and the control source will be the OLE Object (i.e image1). You can then drag your images onto the Object Frame, and they will be displayed as the image name on the form. The only thing is this is not a link. The image is stored in the database itself. I'll keep looking.

Problem 3 - Add another field to your table (call it "Rating"). On your main form, have 10 checkboxes, and a textbox (call the textbox "Rating". Its control source would be "Rating" from the table). For appearances, you might want to put a rectangle around the 10 checkboxes, or include them in an option group. Then on each checkbox, create an On-Click Event. (While in design form, right click on a checkbox, and select "Build Event". Then choose Code Builder). The following code will appear:

Sub checkboxname _Click

End Sub

Between the "Sub" and "End Sub", type in the following line.

Me.Rating = 1

The final code should be:

Sub checkboxname _Click
Me.Rating = 1
End Sub

Do the same thing for the other nine checkboxes, making the rating equal to 2, 3, and so on. Now when you run the form, depending on which checkbox you select, the rating should appear in the "Rating" textbox.

Don't know how much I helped. Let me know.
comteck

[Timbo]

Problem 1

The solution with using a filter does not work. My problem is that the form has to show both input as well as output at the same time. But I want the output to change depending on the input.


Comteck: ”If it's filtering you want to do, then let me know. It's a little more complex, and I won't type it all out if that is not what you are looking for.”

I will try clarifying things with an example. Hope I don’t end up like Columbus (knowing only where I started from).

Two tables are created: Ticket.* and LogBook.*
Ticket is the table where I import all my tickets into.
LogBook is the table where I make descriptions, rate, and link up with a couple of pictures.

One of the fields in the Ticket table holds detailed information about date and time; information in full-text format as in ”July 24 2006 10:36 AM”. I do not know how to re-format this information into something usefull so I made a query which breaks this field up into smaller parts using the InStr-function. I then get one field for each information (July; 24; 2006; 10:36; AM) which I then collect once more using DateSerial and TimeSerial.

At last, I make one more query (let’s just call in Final_Query) in which I collect all the details I want to know for each ticket-record; details that help me writing a description for the tickets.

So, I made a couple of queries just to make life easier.

Then there is the description. I make a form with fields from LogBook.*. Some of the fields lookup the information in other fields (which works fine). Unfortunately, the tickets are rather nummerous or will be sometime in the future, and I would like to be able to only chose the tickets which are not previously included in the LogBook.Ticket. So I make another query where I include all Tickets except from those already in LogBook – this works as expected and I can only chose the tickets which have not already been described and used prior*. When I chose one ticket from this query in the form, then the LogBook.Ticket field gets updated by this ticket-number. But I would like to display the other necessary details (date, time etc.) mentioned in the other query, but only for a matching ticket and not all of them at the same time (which is what I get – I can’t figure out how to say ’show only the records in the query for the matching ticket in the form’).

(* But I have not found out how to update the form so that the tickets are excluded immediatly from the form; ie. ticket 1000 will be shown until I’ve closed and opened the form again. I put this down here so it doesn’t confuse too much.)


Problem 2

I found an article about dragging and dropping: http://www.mvps.org/access/api/api0032.htm
But to use it you have to have this as well: Access/Office and AddressOf Operator: http://www.mvps.org/access/api/api0031.htm

I’m not a coder so I don’t know what it does specifically. But is there no way to just get the link and not the object itself? My pictures are typically 100-250 kb and there are quite a few of them. Making OLE-objects out of them would make the database pretty huge – although it could be possible to make an OLE-base and then link them together… would that improve performance?


Problem 3

This works as expected – thank you. The only caveat is that pushing one button activates it, but pushing another button does not deactivate the first button, so now there are two buttons pushed – although the number is correct since: me!buttonx.rati ng = number.


I hope I did not confuse you with Problem 1. I actually did end up like Columbus. Well, almost… I knew where I came from and was going… but I didn’t know where I was.


Kind regards,

jg