Full Version: Reference another Table in a Form
UtterAccess Forums > Microsoft® Access > Access Forms
deskjobUK
Please help ! My brain has gone numb !
How on earth do I reference a field from another table into an exisiting form.
For instance I have a form that that is tied to "Main Table" but I want to also be able to add information into a field on my Form that references the information field in "Second Table"
I thought it was something along the lines of:
!Second Table!information! = <whatever>
Please please help!
MicroE
Create a multi-table query and bind the recordsource of the form to the query.
dannyseager
or use a sub form.
epending on the relationship type a subform might be the method you need to use... if it's a 1 - many then you will need a sub form.
deskjobUK
Ok I am not sure I have explained myself very well
On my main form, I have a button that allows the user to browse for a default path name.
This path is stored in a variable.
This variable gets lost when the application is closed.
What I would like to do is store that variable in a seperate table, so that it can be called on next time the application is run again.
I was hoping to add a line in like:
[defaultpath_tbl]![OrgDefaultPath] = DefaultPathString
Is this possible?
MicroE
Use the DLookup Function.
ScottGem
I think I understand what you are asking, lets check. You have a button on a form that allows the user to select a file on a drive. You want to store the path and file selected in a table.
That you need to do is generate and run an Append query that adds the path to a table. But there are a number of issues here.
How does the selected file relate to the record being displayed in the form? Are you storing the path in a global variable or a control on your form?
deskjobUK
Folks, thanks for all the help with this one. I have managed to "work around" the issue.
That I was trying to do was to let a user set the default path that would be used when searching for a file via the form.
This was orginally done from the main form.
The default path was stored in the main form as a variable, and of course was lost when the application was closed down.
I wanted to store the default path in a seperate table so that I could reference it again.
What I ended up doing was instead of the button opening the "browse for default path" function, it now opens a new form that references the defaultpath table. This form has the "browse for default path" function and stores the result in a field in the table
Finally all I do then is from the main form use DLookup (thanks MicroE) to reference the path.
PHEW !!!
Might sound a little complicated to you experts out there ;P , but it works for me and I was chuffed to get it working.
Again my thanks
ScottGem
I'm curious about something here. Is the default path variable? does it change depending on some criteria? If so, what? If it doesn't change then you can just hard code it in. If it does change you should be able to automate its being filled in.
deskjobUK
Sorry I am not very good at explaining things, ok here goes a step by step explanation.
I have a "Browse for Document Location" button on a main form that allows the user to insert a hyperlink to a document into a field on the form called "Document Location"
This allows the user to link the document that is relevant to the rest of the information in the form.
So the user completes the form, then clicks the button, browses for the file they wish reference by hyperlink, they click on ok in the file browse dialogue and the files full path is inserted into the "Document Location" field as a hyperlink.
What I have also added is the function to allow the user to set the default path where the browse for document function first looks at. This path could change dependant on several things so I did not want to hard code it.
Originally I had it so that the user clicked another button on the main form that would open up a "Set Default Path" dialogue. The user could then browse to a folder location, click OK and set that path as the default. This location was stored as a variable within the main form.
The issue I have was that (of course) once the application was closed, then variable would be lost and therefore so would the default path.
To get round this I have changed the "Set Default Path" button on the main form to open a new Form (that references a new table). This form allows the user to browse for a default path location and this valuse is stored in the table. This means that the path is stored on a permanent basis and can be referenced at anytime.
Othen use the DLookup function to look up this default path whenever the user clicks on the "Browse for Document Location" button.
I hope this clears it up !
ScottGem
I figured you were doing something like that, but I'm still not clear on one thing. How does the Default Path relate?
For example, does each user have their own default path? Or is the default related to some department or document class? What I'm getting at here, is I'm not sure if there is much value to set a default if its not tied to anything. Granted, it might be a little quicker to select the default path from a drop down list rather than navigate through your drives. But I don't know if it would be worth the difference unless you have long paths.
On the other hand, if you could set the default path to a specific path based on user, department, document class, etc. That would be of value.
deskjobUK
Quote:
On the other hand, if you could set the default path to a specific path based on user, department, document class, etc. That would be of value.



This is exactly why I have included this function. Several users from different areas are using the form. As I am not on hand all the time I have allowed the user to set this themselves.
ScottGem
And you still haven't answered the question about how the default path relates. How do you identify which path to use in your DLookup function?
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.