how to reference a text box in a separate form?

[dcfi6052]

Hello all,

I have an easy to solve problem which I can't figure out.

I have a movie database I'm creating. I have a form to nicely display movies from a main table, complete with movie covers.

Currently the movie covers are saved on a hard drive and not embedded. here is part of my code right now:

box.Picture = "J:\Media\Database\Movie Database\Movie Covers\default.jpg"

box.picture= the picture box where the movie covers will be displayed

So I made an options menu (form connected to a table) with a text box which displays the current file directory above. I want to be able to change the directory in that textbox and have the covers be displayed from the new directory, for example:

box.Picture = Form![options_menu]![movie_covers_textbox] & "\default.jpg"

I have tried different styles such as:

Form_options_menu.Movie_Covers.Value & "\default.jpg"
Form.options_menu.Movie_Covers.Value & "\default.jpg"
Form.options_menu.Movie_Covers.Text & "\default.jpg"
Form.options_menu.Movie_Covers.Text & "\default.jpg"

can anyone tell me the proper referencing style to use in this case? I just keep getting errors.


Thank you!

 

[KenHigg]

I think you just need to tell it to look in the forms collection of objects with something like:

forms!Form_options_menu.Movie_Covers.Value & "\default.jpg"

 

[spikepl]

1. Add a text box to your form
2. Open the textbox's properties
3. In Control Source , click the ... and chose the Expression Builder
4. Navigate to the control on another open form whose value you want
5. Click Paste
6. Close

Now you have the reference you can copy (and remember to delete the now useless textbox)

 

[dcfi6052]

Thanks for the quick reply ken,

I tried your code and I get the following error:

runtime error 2450
access can not find the referenced form

so I took out the "form_" looks like this:

box.Picture = Forms!options_menu.Movie_Covers.Value & "\default.jpg"

and got the error:

runtime error 2186
This property isn't available in design view

even though I'm in the form view. any thoughts?

 

[dcfi6052]

1. Add a text box to your form
2. Open the textbox's properties
3. In Control Source , click the ... and chose the Expression Builder
4. Navigate to the control on another open form whose value you want
5. Click Paste
6. Close

So I put a text box in my movie display form I opened the expression builder (which is blank at the moment), I go to the options form with the textbox I want and what am I copying? I opened the expression builder for its control and all it says is "movie covers"...which is the name of the textbox. If I paste that into the control for the other form I get an invalid syntax error.

 

[KenHigg]

I'm confused as to how you have your forms set up. Can you explain again from scratch and maybe provide a screen shot or two?

 

[dcfi6052]

I'm confused as to how you have your forms set up. Can you explain again from scratch and maybe provide a screen shot or two?

-have a main switch board with a button that opens an options menu (form)

-in that options menu I have a text box with a file directory in it "J:\Media\Database\Movie Database\Movie Covers" (see attached pic "options menu")

-this text box is connected to a separate table, so if the user changes the directory in the text box it saves in the table and therefore the textbox has been updated with a new directory.

-I then have a form to nicely display the movies from the main data table (see attached pic "main movie form") the movie cover you see is the "box.picture" in the code I provided.

-so when the user decides to save the movie covers to another location on their computer they only have to go to the options menu and change what the text box says instead of changing the actual code each time.

-Just need to find out how to reference the text box in the options menu in the main movie form.

does that help any??

 

[KenHigg]

So the image path could change from movie to movie? If so it sounds like a. you need to save the path with the movie and b. You need use the the path the user selects from the setting menu as the default path.

Sound right?

Edit:

-or-

You don't store the path with the record, Then if the user changes the path they have to manually move all images to the new image folder?

 

[dcfi6052]

So the image path could change from movie to movie?

yes and no. if the directory says:

J:\Media\Database\Movie Database\Movie Covers

It obviously looks in that folder for the movie cover that matches the movie name. the second part of my code is :


If Me.Use_Alt_Name = True Then
box.Picture = "J:\Media\Database\Movie Database\Movie Covers\" & (Me.Alt_Name) & ".jpg"
End If

If Me.Use_Alt_Name = False Then
box.Picture = "J:\Media\Database\Movie Database\Movie Covers\" & (Me.film_name_box) & ".jpg"
End If

-Use_Alt_Name is a yes/no box.
-Alt_Name is a text box. if the yes/no box is true it reads the alternate name. (I us this if the movie name has a format that confuses access or window (!,$,@ etc.)
-film_name_box is the text box that displays the movie name in the main movie form.

I just want to have the correct format for referencing the text box and paste it in this code over the directory text. As this code does currently work fine, I just want the user to be able to change the directory with ease.

 

[KenHigg]

Lets stick with how the user decides where to store the image first.

So when the the system is new and the user sets up the folder where they want to store images, they put that in the settings form. No problem. Then they add movie records and put the images in the designated folder. Then to display the image, the system combines the folder path with the movie name and adds the .jpg to the end.

Right so far?

 

[dcfi6052]

So when the the system is new and the user sets up the folder where they want to store images, they put that in the settings form. No problem. Then they add movie records and put the images in the designated folder. Then to display the image, the system combines the folder path with the movie name and adds the .jpg to the end.

Correct!

 

[KenHigg]

Does the settings for stay open when the movie form is open?

Where is the alt name checkbox?

 

[dcfi6052]

Where is the alt name checkbox?

the use_alt_name check box and the alt_name text box is in the main movie form NOT VISIBLE (.visible=false) since I couldn't figure out how to reference in a different form...like what I'm trying to figure out now. It works fine and doesn't bother me to have it on the main form not visible.

Does the settings for stay open when the movie form is open?

you mean "does the settings menu stay open when the movie form is open?"

if so then, no. you can have the main movie form open looking through records and have the different movie covers popping up seamlessly without the settings menu open. But the VBA code currently has the movie cover path typed in, not as a a textbox reference as I'm trying to do. So I'm not sure how it would effect it....but I would like to be able to have the main movie form open and not have the settings menu open. The settings menu should save the file path preferences that the user specifies. Then the VBA code should be able to plug it in when the user changes records with different movie covers.

I appreciate all your help!

 

[KenHigg]

See if this clears things up; You have to have a form open before you can reference it (or any controls on it). I think you can tackle want you need by either using a domain function like dlookup() to get the path when you need it or I would just create a public variable and assign the path to it.

 

[dcfi6052]

I think you can tackle want you need by either using a domain function like dlookup() to get the path when you need it

So with this method I wouldn't need to have the form open?

or I would just create a public variable and assign the path to it.

I still learning VBA and have never heard of public variable or domain function. When I look up definitions online I find it hard to figure out the connection to what I want to use it for. What do you think the most practical method would be? a dlookup or public function?

the options form (with the file path text boxs) is connected to a small data table where the file paths are saved. would it be easier and more practical to reference the table cell then the textbox in the form?

 

[KenHigg]

The domain function looks in the table and is not dependant on a form

I suggest you try the domain function dlookup first and see how it works. It will be useful in other places and may be a bit easier for you to get your app working.

 

[dcfi6052]

okay that sounds good. I have never used the dlookup() function before so would I use the following?

DLookup("FieldName" , "TableName" , "Criteria= 'string'")

fieldname= Movie Covers ' this is the column name in the table right?
tablename= options_menu
criteria= I'm guessing I need to define a string?

DLookup("Movie Covers" , "options_menu" , "Criteria= "?")

just one line of code?? there must be a return function or something right?

 

[KenHigg]

What is the table and field names where you store the image path?

 

[dcfi6052]

What is the table and field names where you store the image path?

table name is "options_menu_folder_directories"

field name is "movie covers"

*see attachment

 

[KenHigg]

Oh, all in one record. That was confusing me. Just leave out the criteria part.

To test it, create a new button and in the click event put:

msgbox DLookup("Movie Covers" , "options_menu")

Whoops, meant this:

msgbox DLookup("[Movie Covers]" , "options_menu_folders_directories")