Trying to understand OpenForm statement 1

[RenaG]

Hi,

I am working in Access 2007 on an Access database created by an earlier version (presumably 2003). I am an extreme newbie to Access/VBA. This database was created by another company and given to us to modify as needed. So I am trying to figure out what it does and why.

It opens with a User Identification form which only contains a label, combobox which says 'Unbound' and Ok and Cancel buttons.

The Row source for the combobox is a select statement:

SELECT  Users.ID,				
        Users.FirstName, 				
        Users.LastName				
FROM    Users				
ORDER BY Users.[FirstName], Users.[LastName];

When I look at the VB behind the Ok button:

Private Sub cmdOpen_Click()
On Error GoTo Err_cmdOpen_Click

    Dim stLinkCriteria As String

    DoCmd.OpenForm "Menu", , , stLinkCriteria
    DoCmd.Close acForm, "User Identification", acSaveNo

Exit_cmdOpen_Click:
    Exit Sub

Err_cmdOpen_Click:
    MsgBox Err.Description
    Resume Exit_cmdOpen_Click
    
End Sub

I can't figure out: Where the stLinkCriteria is filled?

I hope I have given you enough information to answer my question.

TIA
~RLG

 

[Gammachaser]

It does not appear to do anything except open the Menu form.

stLinkCriteria will always be an empty string ("") so nothing will be passed to the Menu form when it is opened. Apparently the code that sets the criteria to be passed is missing.

I now understand why it was given to you. Was the other company a competitor? ;-)

 

[RenaG]

LOL! No, they aren't a competitor. In fact we are all part of a nationside program but each of us are stand-alone.

I was thinking that maybe it is defined someplace else but I just didn't know where to look. Is there a way to display it's value at the time the Menu is opened?

~RLG

 

[Gammachaser]

Yes, you can display the value, but I guarantee it will be blank (an empty string.)

The variable is local to the Procedure and therefore is not visible outside of the procedure. There is no way for it to be set except by code that is not there.

To see the value you could put:

Debug.Print stLinkCriteria

Or

Msgbox stLinkCriteria

before

DoCmd.OpenForm "Menu", , , stLinkCriteria

But again, it will always be blank.

It looks like the criteria was to be passed as a 'Where Clause' which would limit the record source of the Menu form to whatever User was selected from the ComboBox. But, since the code is not there, I am merely speculating.

If you wanted to pass the selection from the ComboBox to the Menu form, you could use:

stLinkCriteria = [i]cboMyComboBox[/i]
DoCmd.OpenForm "Menu",,,,,,stLinkCriteria

and the value in the Bound Column of the ComboBox will be passed to the Menu form as a property called "OpenArgs" which can then be referenced from inside the form (Me.OpenArgs).

Good luck to you, Sherlock.

 

[RenaG]

The Users are associated with a particular program and so, like you, I thought the purpose of 'logging in' was to place some controls based in their program. But I wasn't seeing that happen as I ran through the different screens. I don't know the history of this db (whether it was a work-in-progress or what) but it certainly doesn't seem to be fully functioning. I think my users are going to need me to control what they see based on what program they are associated with.

When I look at the Menu form, there is a Record Source that is a select stmt.

SELECT Users.FirstName, 				
       Users.LastName, 				
       Users.ID AS UserID, 				
       ProgramUserXref.ProgramID, 				
       Program.ProgramName				
FROM   Program 				
       RIGHT JOIN (Users 				
         LEFT JOIN ProgramUserXref 			
         ON Users.ID = ProgramUserXref.UserID) 			
       ON Program.ID = ProgramUserXref.ProgramID				
WHERE (((Users.ID)=[Forms]![User Identification]![cmbUser]));

cmbUser is the Name of the unbound combo box in the User Identification form. Perhaps he was going to use the stLinkCriteria and went this route instead. Just leaving a red herring to throw me off!

~RLG

 

[Gammachaser]

That is one option, except that the User Identification form is closed after the Menu form is opened, so that ComboBox is not available to get the data from.

    DoCmd.OpenForm "Menu", , , stLinkCriteria
--> DoCmd.Close acForm, "User Identification", acSaveNo

Neither method really works. In fact, when the form opens, there should be an error message.

 

[RenaG]

Surprisingly enough, it does work. I wish I knew how to send screen shots through this form to show you that when the Menu opens, in the upper left hand corner it displays the name of the Program and in the upper right hand corner is the name of the user. You observed that the Menu opens after the User Identification form closes, so doesn't that make the values in Menu available to User Identification?

BTW - do you have any web sites you could recommend where I could read more on how forms and variables and all that stuff work? I need to figure out how to use the Program information to control what other forms I display for the user. Or if is something you could relatively quickly explain, I would really appreciate it! (My gut feeling says it is complicated but it never hurts to ask )

~RLG

 

[Gammachaser]

Well, I can see that it could work if the Menu form always stays open and never has an event that causes a requery, but I certainly don't see a good reason to do it that way. I think it would be much easier and 'safer' to set a global variable before closing the Identification form. The way it is done breaks the link between the two forms.

But, I am not the smartest or most experienced person on this forum.

I am sure there are many sources on the net for learning but I am not sure which are best. One place to start is:

http://www.techonthenet.com/access/tutorials/index.php

I do not know how it compares to others. I am sure you will get other advice.

 

[RenaG]

Sounds like good advice. To use the code you sent earlier, I assume that the 'cboMyComboBox' is the 'Name' of the combo box on the Menu form. In this case cmbUser. How do I make a variable global? And one more question, how do I reference the Me.OpenArgs in the Menu form?

Thanks for the link. I'll check it out.

Thank you too for all your help!

~RLG

 

[Gammachaser]

You declare a Global Variable in a Standard Module. How does that sound?

Disclaimer: I mostly use Access 97 and 2003 because the people I work for use them. As a matter of fact I am currently trying to drag my current work location (I am a contract employee) into the current century by upgrading them from 97 to 2003 and I am meeting some resistance. So... if you are using 2007, your results may be slightly different.

In the database window you should see a tab or button that says 'Modules' and there may be some modules there already. If there are, you can add a Global variable simply by putting it into one of the existing modules. Or, you can make a new one simply by clicking on... get ready for it... 'New'

At the top of the module there is a 'Declarations' section, which is where you should put your variable. When you click 'New' you should get something that looks like this:

Option Compare Database

That is a Declaration. Add Your variable underneath that. I also recommend you add 'Option Explicit' to require that all variables are explicitly delcared. This can prevent headaches in the future when you misspell a varible name and can't figure out why you keep getting 0 or an empty string where you know a value should be.

Taking a guess at your structure for the Users table, UserID is a Long Integer (since it is probably an autonumber field) so you should have something like this:

Option Compare Database
Option Explicit

Public lUserID as Long

Notice I put 'l' infront of UserID (some people use 'lng'.) You do not need to do this but it will help you later to identify which variables are which data types like strings, boolean (True/False) or dates (e.g. dtHired as Date, strQuantity as String.) It will help a lot when you troubleshoot.

You will also have to change the RecordSource of the form to get rid of 'WHERE' and everything after it. Then you can use the Filter property to have only the proper user information in the recordsource.

In a previous post I assumed that stLinkCriteria was being used by the program, but it does not seem that way with the additional information you have give. What is being passed is the Bound Column of the ComboBox, which is the UserID (a Long Integer), not the UserName (a String.) You would need to have

DoCmd.OpenForm "Menu",,,,,,Me.cmbUser

to pass the UserID from the User Identification form to the 'OpenArgs' property of the Menu form..

If you have lUserID variable declared then you can use the OnOpen event of the Menu form to set the variable

Private Sub Form_Open(Cancel as Integer)
If lUserID = 0 Then lUserID = Me.OpenArgs
Me.Filter = "(ID = " & lUserID & ")"
Me.FilterOn = True

'other code as needed

End Sub

This will make your lUserID available wherever it is needed and closing the Menu form will not cause the loss of the data.

Having just barely, imperceptibly scratched the surface, I wish you good luck in your learning process.

 

[RenaG]

Ouch, this makes my head hurt! (LOL!)

It will take me some time to process all of this. I am working on this project along with others (as I am sure you are familar with doing yourself). I will get back with you if I get stuck. Thank you for all your help. I can't tell you how much I appreciate you sharing your knowledge with me.

~RLG

 

[Gammachaser]

You are welcome. One of the things I do is teach (Radiation Protection, not programming) and once I get rolling I sometimes can't stop.

If you think your head hurts now, just wait... you ain't seen nothing yet. I think I have permanent dents in my desk top from banging my head. But it sure is gratifying when you get it to work!

Good luck.

 

[RenaG]

Yeah, but it's a good kind of hurt. I am looking forward to making this work (and actually understanding it). It's only my second Access project. I created the first one from scratch (with much outside help, I confess) and it wasn't a beginner project either. "Throw them in the pool. They will either sink or swim." So far, I am just about treading water - .

I am glad you wrote all that you did. Unfortunately, I just got pulled off this for a couple of days. But I'll be back!

Have a great day!

~RLG