Need help adding a box to display multiple hyperlinks per record. (Beginner)

[MvDRelentless00]

Hello all,

First off, I am extremely inexperienced in Access, having had no formal training at all and am also not in an IT or Computing based job. My boss has asked my to update a database that somebody created for him about 6 years ago which is designed to house records pertaining to standard practices for laboratory experiments. The database worked perfectly before, but was very basic and health and safety has now moved on so it needs updating.



The issue I am facing is that I have a table which contains a number of records, each with a load of different columns. However, the new way of doing H&S splits Risk Assessments into Short and Long COSHH forms. Each experiment only has one Short form, but can have from 0-20 (or more Long). I need a way of adding a box to the form that will return all the associated Long forms for that particular record. I have tried using List Boxes and Combo boxes as well as creating a separate table for the Long Forms, but it isn't doing what I had envisioned.

List boxes appear to do what I want, however they just dump all of the results from the selected columns into the table. I only want the data for the specific record in question. For example, my first record has 6 Long forms, but a list box basically just pusts a snapshot of the table in there rather than the specific results.

All the Short and Long Forms have to be hyperlinked to the associated doccuments. I have succesfully done this for the Short.

Sorry if this is vague, I am a little out of my depth,

M

[AnandKhinvasara]

Hi
You would need to simplify and clarify what exactly you are looking for. If possible provide object names like Table1, Combobox1 etc, and talk in context of object names like need to add contents of Table1 to Comboxbox1 List ... Currently its difficult to understand your problem. And how are we supposed to understand what "H&S splits Risk Assessments", "COSHH Forms" are... Hope you got my point..

[MvDRelentless00]

Hi,

Yes, ok I get your point. Sorry as this is something I have inherited without any prior knowledge of Access I am having trouble explaining what I want to do. I have tried to attach the database, but it won't upload, I will try to find another way to do it.

Basically I have Table1 which contains a number of different records ( approximately 20) each of these have different fields such as name, number, date created and comments. Additionally it used to have one that was for Risk Assessment files, which was a single hyperlink that opened a document that had all the relevant information for that specific record. I have removed this and in its place have one column for my "Short forms" which act the same way (hyperlink to a single doccument) and this works. Now I am trying to add another field that will be populated by all the "Long forms" that are associated with that specific record. This is where I get completely stuck. Hope you can see what I mena from the database.

Thanks,

M

[MvDRelentless00]

It seems that the maximum filesize I can upload is 500Kb and my database is significantly larger than that. Any other way for me to upload it?

[AnandKhinvasara]

Please email me khinvasara_anand@yahoo.com. would need to have a look.

[MvDRelentless00]

Sent it across now.

Thanks,

M

[June7]

Run compact & repair then zip the file. 2MB zip attachment allowed.

[MvDRelentless00]

Run compact & repair then zip the file. 2MB zip attachment allowed.

Tried that, still leaves me with a 4.1Mb Zip file.

[recyan]

Do not know if below is what you are saying. All the same :


Let us say,
you have an Experiments table :


tblExperiments
ExperimentID - PK
ExperimentName,
Etc


Then you have another Forms table :


tblForms
FormID - PK
FormName - Hyperlink
FormType - ( Values - Short || Long )
ExperimentID - FK


Note : If you are very sure that any given time in the future, you are going to have only one Short Form per Experiment, then you can very well have it as a field in tblExperiments. But I would advice having it in tblForms, even if sure.


Now, can we have tblExperiments as Main Form & tblForms as Sub-Form.


Thanks

[MvDRelentless00]

Actually I only have one table, Table1, which contains all the information for each experiment. This includes name, author, date created, keywords etc. Now what I want to do is add another two fields "Short COSHH" which will have a single hyperlink to the associated file. Additionally I want to add "Long forms", but the number per experiment can vary from 0-20 or so.

I have tried adding 10 columns on Table1 which can store Long COSHH hyperlinks, but now I need a user-friendly way of displaying them on a form. Either that or a better way of including them, be it as a separate table or whatever.

[June7]

Can upload file to a fileshare site such as Box.com and post link to file.

Or make copy and delete stuff until file is small enough. Leave just enough records to test with. Be sure to run Compact & Repair.

What do you mean by 'user friendly' - don't want to show the long URL just a short description? A true hyperlink has that feature built in: http://www.allenbrowne.com/casu-09.html

Might also be of interest http://allenbrowne.com/func-GoHyperlink.html

[recyan]

Might have got your question wrong again, but below attached is the solution ( at a basic level ) that I was talking about ( uses an additional table ).

Edit : Open Form Table1 & take a look.
Also take a look at the Relationships Window.

Thanks

[AnandKhinvasara]

Hi, I saw your database.
From that and your post above, what I understand..
1. You have introduced addtional fields like "Short COSHH", "Long COSHH1","Long COSHH2" in table1.
2. Old form was "SOP Number Form". There was a field "Risk Assement File".
3. You can copied this form as "Form1", and replaced this "Risk Assesment File" by "Short COSHH".
4. Now you want to add all the Long COSHH forms to this form "Form1"

Suprisingly your "complicated" question seems to have a very simple answer...
As you have added "Short COSHH" field onto this form, add a textbox for each "Long COSHH" field, and then bind its controlsource to respective field in "Table1".

I am sure you knew this solution. Are you trying to make it more aesthetic ? so that you can avoid putting in 20 fields for Long fields together onto a form ?

Clarity is required from your side, else answer seems pretty easy to me.

[AnandKhinvasara]

4. Now you want to add all the Long COSHH forms to this form "Form1"

Sorry I meant

4. Now you want to add all the Long COSHH FIELDs to this form "Form1"