How to control label visability property with unbound text box?

  • Thread starter Thread starter Scott
  • Start date Start date
S

Scott

Hi,

I'm trying to develope an Inventory Based database with
Access2k. I've run into a couple of snags.

1.) I need the "CurrentInventory" field to be able to
change (update) as the user adds or removes items on two
other fields. Unfortuantely, I can't get this to work.
The standard expression "=[CurrentInventory]+[Added]-
[Taken]refuses to work. So I was forced to use an unbound
text box named "Amount Left" to display the updated value.
Not really how I wanted it. But I don't know any other way
to make the "CurrentInventory" field behave properly.



2.)The second issue is that I'm using a lable
named "order" to become unhidden when the updated value
gets to a certain point using this type of VB code:

Private Sub Amount_Left_AfterUpdate()
If Me!Amount_Left <= 1 Then
order.Visible = True

Else
order.Visible = False
End If
End Sub

But it won't work with this unbound text box because it's
looking for a field to control it.
It would solve both problems if the "CurrentInventory"
field would just update off of the other Field values. But
it just isn't working.


Anyone have suggestions on how to approach this type of
database? Anyone have experience with making simple
Inventory databases that have examples I might study?

Thanks,
Scott
 
Scott,

Create the following procedure in the form's class module to solve both
problems:
Private Function UpdateInventory() As Boolean
Me!txtCurrentInventory = Me!txtCurrentInventory + Me!txtAdded -
Me!txtTaken
Me!order.Visible = (Me!txtCurrentInventory <= 1)
End Sub

Then add the following to the AfterUpdate properties (the property - not the
event code) for both txtAdded and txtTaken:
=UpdateInventory()

But of course, if you want the Order textbox to only display when
txtCurrentInventory <= 1, you need to first set its Visible property to
False (in form design view).

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
 
Thanks Graham,

I tried it but the form gives me the old "The expression
you entered has a function name that Access can't find"
error.

I'm not sure what you mean by "the form's class module".
The DB didn't have any modules so I created a new one and
placed your code in it.
Could that be why it doesn't work?
I've never worked with modules before.

-Scott
 
Scott,

The code module behind a form is called its "class module" (It's all very
technical).

1. Remove the code from the module into which you've already placed it.

2. Open the form in design view.

3. Double-click the form to display the Properties dialog.

4. Select the Events tab.

5. Click the txtAdded textbox (or whatever you've called it), then while
holding down the [Shift] key, select the txtTaken textbox (or whatever
you've called it), and type the following text (including the = sign) into
the [After Update] property.
=UpdateInventory()

6. On the toolbar at the top of your screen, click the
Code: 
 button. It's
the one that looks like a square with a shiny star in its top-left corner.
That will take you into the form's class module.

7. If you don't see a line that says "Option Explicit", add it yourself,
then do the following:
7a. From the [Tools] menu, select [Options].
7b. Select the [Editor] tab.
7c. Tick the [Require Variable Declaration] checkbox.
7d. Click [OK].

8. Now, under the line that says "Option Explicit", paste the following
code. Watch for the newsreader's line wrapping - there should only be 4
lines. The line starting with "Me!txtCurrentInventory =" should end with
"Me!txtTaken".
Public Function UpdateInventory() As Boolean
Me!txtCurrentInventory = Me!txtCurrentInventory + Me!txtAdded -
Me!txtTaken
Me!order.Visible = (Me!txtCurrentInventory <= 1)
End Sub

You might notice that I changed its declaration from "Private" to "Public".
It should have been Public in the first place - sorry.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
 
Thanks a lot kind sir,

I've done a lot of tech. support for graphics software in
my time. And I really apprieciate the way you broke this
down into simple easy to follow steps.
I think I can get it to work from here.

"End Sub" tossed out a compile error.
But It worked ok when I changed it to "End Function".


Thanks for the help,
-Scott
 
Scott,

<<"End Sub" tossed out a compile error.>>
Oops! Sorry about that. I started writing it as a sub, then changed my mind.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
 
Back
Top