How to refer to a control on a subform in VBA 2

[TSimonick]

Hello,

My problem is how to refer to a control on a subform in the following situation:
I have mainform/subform, with a combo box on the subform. If you enter a name in the combo and it isn't present, then a new account form opens, the name you typed is entered into a new account record, then you add other info. When you close the new account form, a proceedure carries back the new account record number to put it into the combo box and requery the box to show the name.
If I explicitly refer to the subform and control the procedure works fine:

Forms![Mainform]![Subform]![BatchID] = varBatchID
Forms![Mainform]![Subform]![cboBatch].Requery

I want to use this procedure on several different mainforms with different names (the subform name never changes), so I need to set the mainform name to the current form name and use this as I did above. I have tried setting a variable to Screen.ActiveForm or Screen.ActiveForm.Name and substituting the variable into the above code, but Access never recognizes the form I'm trying to refer to. I have tried using the command Forms("strvariable&quot but I haven't got that right, either:

strForm = "[Screen].[ActiveForm].Name"
Forms("strForm&quot![Subform].[BatchID] = varBatchID

I would really appreciate any guidance on how to code this correctly.

Thank you!!

-Tom

 

[RottPaws]

The shortcut to refer to the current form is Me!. i.e.
Me!subform!control=variable

But in this case, I don't think you need to refer to the main form. If you have the code that opens the new account form in the NotInList event for the control on the subform (in the subform's module), I'm pretty sure you can handle it all right there.

I'd handle this by opening the New Account form as a dialogue and passign the new account as openargs. This will "pause" the code in the subform's NotInList event until the New Account form is closed. In the line that follows opening the New Account form, do a DLookup to verify that the account exists and then set Response=acDataErrAdded.

Look up the topics 'OpenForm Method' and 'NotInList Event' in Access Help and also check out 'NotInList Event — Event Procedures'. _________
Rott Paws

...It's not a bug. It's an undocumented feature!!!

 

[danvlas]

I'm not very sure what you try to accomplish and I am really tired (it's been a busy day B-(), but you have some basic mistakes:

Let's take a close look at what you've done here...
strForm = "[Screen].[ActiveForm].Name"

At this moment, strForm variable will have the value
[Screen].[ActiveForm].Name
which is NOT the name of a form

Correct syntax:
strForm = Screen.ActiveForm.Name
Now the variable will have the value of the name of whatever form has the focus


Your next statement has a mix-up of VB notation and Access notation:

Forms("strForm&quot![Subform].[BatchID] = varBatchID

Green color = VB notation (refers to the open form with the name "strForm", not to the open form that has the value of the strForm variable as name.
Blue color = Access specific notation. Works, but I think VB notation is better.
Red color: although it works, your program looks first for a property called BatchID, not for a control named BatchID.

Correct syntax would be:
Forms(strForm)("SubFormName&quot("TextBoxNameOnSubform&quot = varBatchID

or

Forms(strForm)![SubFormName].Form![TextBoxNameOnSubform] = varBatchID

Note that you must use VB notation to use a variable to pass the name of the form. Access notation does not support this. That's why I prefer the former.

Gotta go now.

Daniel Vlas
Systems Consultant
danvlas@yahoo.com

 

[TSimonick]

Thanks to both of you for educating me. I'll have to learn some more to use Rott's suggestion, but it is a much cleaner solution than what I have now. I didn't know that you can use a dialog box to populate a table

Dan, thanks for setting me straight. Your VB solution works great! Next question: What makes the VB line work, since it doesn't have the same syntax as the Access line? That is, it doesn't have "Forms ! [] . " Generally, can you substitute VB notation for Access notation?

Thanks again for your help!

 

[RottPaws]

I may not have been as clear as I should have been with regard to the dialogue.

One of the optional parameters (windowmode) of the OpenForm Method allows you to open a form as a dialogue; basically the form becomes a custom dialogue box.

DoCmd.OpenForm "frmNewAccount",,,,,acDialog

When you use this method, the code for the form that opens the dialogue is put on hold until the dialoge is closed. In this case, you already have a form that will allow you to add the new account. If you have a line in the NotInList event that opens that form as a dialogue, the NotInList Event code will pause until the new account form has done it's job and is closed. Then the NotInList Event continues with the next line of code. _________
Rott Paws

...It's not a bug. It's an undocumented feature!!!

 

[TSimonick]

Rott,

Thanks for making it very clear! One last question: I'm getting the value of the CustomerID control of the current record by setting a variable equal to it: varID = [CustomerID]. If I don't want to have the CustomerID control visible on the form, is there a way to set the variable to the value in code? That is, set the value to the last record in the table.

Thanks again for your time!

-Tom

 

[danvlas]

TSimonick: You can ALWAYS use VB notation in code. Access help says the performance is a little poorer. But it's universal syntax that may be used for all VB(A) applications.

Access will allow you to use any of the notations and any mix between them:

Forms![FormName]![ControlName]
Forms("FormName&quot("ControlName&quot
Forms(1)(5)

Last notation uses the index of objects in the collection, so it will point to the control number 5 that resides on the open form number 1
The problem is that you have to track objects by numbers, which I think is why this notation is not widely used.

I am not in the position to judge if having more options in syntax is a good or a bad thing . I just try to stick with one, and so far the VB seems much more flexible.

Hope you understand more than me from what I've said here .


Daniel Vlas
Systems Consultant
danvlas@yahoo.com

 

[RottPaws]

Sure.

You could use DLookup to get the value from the last record . . .

varID = DLookup ("Last([CustomerID])", "TableName&quot

You could also leave it on the form and just set the visible property to False and leave your code the way it is.

Making the assumption that the reason you don't want it visible is because you don't want the user to mess with it . . .

It may be better to leave it on the form and just set the Enabled property to False, or Locked to True (both is redundant). Depending on the application, it may be usefull for the user to see the customer ID and be able to copy it while not being able to modify it. The locked property will accomplish this. Setting Enabled to False will "gray-out" the field so the user can see it, but they won't be able to select/copy the field.

Does that answer what you were asking, or did I answer a totally different question? _________
Rott Paws

...It's not a bug. It's an undocumented feature!!!

 

[TSimonick]

Rott,

You understood my question perfectly. I was not sure if I should use DLookup, Move, Find or Seek. Also, I had thought that if the control was invisible, its data was not available. But I like the idea of setting Enabled to False.

Thanks again for the education.

-Tom

 

[RottPaws]

No problem. I'm pretty good w/ Access, so I often cruise those forums while I'm waiting for answers to questions I post in SQL Server or ASP forums......

It's all a matter of personal preferences and application requirements, but I generally prefer setting the Locked property to True as opposed to setting Enabled to False. The reason is that while it still protects the data, it allows the user the option to select and copy the contents.

For example, one of your users may be working in your database and sending an email related to a specific customer. They could click in the customer_id field, copy the number, and then paste it into the email rather than having to type it. I don't know about your application, but our systems use 9-digit account numbers, so copy/paste functions are handy.

Keep in mind also that these properties can be modified through code as needed . . .

Customer_ID.Visible=True
Customer_ID.Locked=True
Customer_ID.Enabled=False
_________
Rott Paws

...It's not a bug. It's an undocumented feature!!!