Solved

MS Access 2007 - Open form, and set rectangle visible property to true based on textbox value

Posted on 2010-08-26
19
820 Views
Last Modified: 2013-11-28
I have an assets form that contains assets information to include LocationID (txtLocationID), data range 1011 -1199. Next to it I have placed a label (lblFindIt), that I would like to open a GIU like form that displays a layout of the building, and the rooms in it.  On the GUI form I have place small red rectangle to show were each desk is within a room, I have labeled them bx1011 - bx1199, to represent all the possible desk locations.  I have set the rectangles visible property to false.

Now, the tricky part, I would like when the user clicks on lblFindIt, the GUI form (frmBldg4413Layout) opens, and the box with the corresponding LocationID of the current record for frmAssets has its visible property changed to true.
0
Comment
Question by:t_hungate
  • 10
  • 9
19 Comments
 
LVL 44

Expert Comment

by:GRayL
ID: 33535557
Private Sub lblFindIt_Click()
  DoCmd.OpenForm, "frmBldg4413Layout",,,Me!txtLocationID
End Sub

In the Open event of frmBldg4413Layout

Private Sub Form_Open
  Me!Controls("bx" & Me.OpenArgs).Visible = True
End Sub

What you haven't said is what you want to happen when the form closes.
0
 
LVL 10

Author Comment

by:t_hungate
ID: 33535688
On close I will set all desks to not visible again.

I also use the GUI form for some other operations, so I will need to be able to open the form without looking to change the visibility of any of the desk rectangles.

GRayL,

I am getting a compile error: Argument not optional

When I execute your solution.  Any ideas?

TLH
0
 
LVL 44

Expert Comment

by:GRayL
ID: 33536963
Oops - it needs six commas:

Private Sub lblFindIt_Click()
  DoCmd.OpenForm, "frmBldg4413Layout",,,,,,Me!txtLocationID
End Sub
0
 
LVL 44

Expert Comment

by:GRayL
ID: 33536969
I just tested the concept and it worked fine once I sorted out the comma wrinkle ;-)
0
 
LVL 44

Expert Comment

by:GRayL
ID: 33537028
Two events:

Private Sub Form_Open()
  If Not IsNull(Me.OpenArgs) Then  Me!Controls("bx" & Me.OpenArgs).Visible = True
End Sub

and:

Private Sub Form_Close()
  If Not IsNull(Me.OpenArgs) Then  Me!Controls("bx" & Me.OpenArgs).Visible = False
End Sub
0
 
LVL 10

Author Comment

by:t_hungate
ID: 33544654
GRayL,

I am trying to get your solution to work, here but I am running into some issues.

1. When I attempt to opem my frm4413Layout via a cmb on my main form I get a runtime error 2501: The OpenForm action was canceld.

2. The onclick event for the lblFindIt it producing a Runtime error 2498: An Expression you entered is the wrong data type for one of the arguments.

Any ideas?

I also need the frmBldg4413Layout form to check when it opens to see if the frmAssets form is calling it, then run the open args, if not then there will not be a field available for it to reference.

Thanks
0
 
LVL 10

Author Comment

by:t_hungate
ID: 33544673
Sorry the Find It lable is producing a 2501: OpenForm action was canceled.

The other error was becuse I had the form on a new record and ther was no location information.
0
 
LVL 44

Expert Comment

by:GRayL
ID: 33545008
You first said you were opening the form via a click on the label.  Now it is via a cmb (combo box - command button)?

Any way you can pare the mdb down to the minimum required to support the question, do a Compact & Repair, and upload it here using File below?  This should be straightforward.
0
 
LVL 10

Author Comment

by:t_hungate
ID: 33545591
I am posting a sample db here, I have left the asset table in place, and the two forms that I am having issues with.

Thanks.
Sample.accdb
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 44

Expert Comment

by:GRayL
ID: 33545729
forgot to say I only have Access 2003.  Sorry about that.  
0
 
LVL 10

Author Comment

by:t_hungate
ID: 33547504
Here is a 2003 version.
Sample.mdb
0
 
LVL 44

Expert Comment

by:GRayL
ID: 33601553
Sample.mdb creates an error message:  "unrecognizable format"
0
 
LVL 10

Author Comment

by:t_hungate
ID: 33614980
I will have to resubmit a sample when I get back to work on Tuesday.  Sorry about that.

Thanks for your help.

TLH
0
 
LVL 10

Author Comment

by:t_hungate
ID: 33614993
I was able to find what I needed, here is a working copy.

TLH
MySample1.mdb
0
 
LVL 44

Expert Comment

by:GRayL
ID: 33629493
>I was able to find what I needed< - Meaning you no longer need assistance?
0
 
LVL 44

Expert Comment

by:GRayL
ID: 33630439
I downloaded the mdb, had a look and have a few comments.  You make the mistake of trying to get everything working at the same time.  I suggest you works with just two rooms initially - with labels.  Accept the defaults, initially, do not complicate by changing backcolors. Reduce the table to two assets with different networks.  When you get the two working, now add the back colors according to the network.  Some controls are prefixed with a syllable indicating the control type (label as lbl), but the text boxes carry the same name as the fields to which they are bound - even though a Microsoft default - generally considered bad practice.  How do you refer to a control value versus a field value if the names are the same?  In addition I see you have name one label 1015 - purely numeric.  If you do that, all reference to the control must wrap the name in brackets ([1015]) to avoid throwing an error.  
0
 
LVL 10

Author Comment

by:t_hungate
ID: 33630624
I have all functionality working the way I need it between forms with the exception of the one for which I open this question.  I was changing the names of boxes or rectangels on the form to see if I could get the code to work.  I do normally use the Leszynski/Reddick naming convention in my applications.  If you have found some mis named contols it is most likely do to the reduction of size and complexity so that I could create the sample quickly.

I am still wresteling with this one however, and cannot get the boxes that represent desks in each office to have their visible property updated based on the asset forms locationID field.  I did not think this one would be this difficult.  I am able to pass the value from the assets form to the layout form using your openargs method.  However I am having a hard time ustilizing that information to change the box setting.  

I have been working on some other portions of this project to get it ready for deployment, and have not had much time to look back at this issue.  I was really hoping that I would get a quick have you tried this here on EE and I would be able to get it working and move on.

Thank you for your assistance.  Do you think I need to change the code to include the encapsulation with brakets?
0
 
LVL 10

Accepted Solution

by:
t_hungate earned 0 total points
ID: 33631534
I figured this one out, I finally had some time to work with it and found a way.  It is not the best, but it works, I may refine it later when I have more time.

The fix was to create an unbound txtbox on my layout form and make its rowsorce =[Forms]![frmAssets]![txtLocationID] - that successfully passes the current record location id to the layout form.

From there I was able to use a selcet case to my new txtbox txtLocationID
Code looks like this.

On form open

Thank you very much to GRayL, for your help.
Selcect Case Me.txtLocationID

    Case "1011"

        Me.bx1011.Visible = True

    Case "1012"

        Me.bx1012.Visible = True

    Case ""

        Me.bx1011.Visible = False

        Me.bx1012.Visible = False

End Select

Open in new window

0
 
LVL 10

Author Closing Comment

by:t_hungate
ID: 33631542
I found the solution.

Thanks again GRayL.
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now