VB and Form!Field populating (automatically)

Hi,

I am trying to solve a problem since more than 8days, without any real progresses and I am once again begging for help from you guys.

I am trying to attempted a Form automatization, ie a user chooses a CostCenter from a dropbox and then automatically a field OrgUnit and Region should be filled with the corresponding "value". (Table Structure: the main Form is Work with a Table Work, but CostCenter there's also a CostCenter Table with OrgUnit and Region)

I think, the best way to do that is in VB, but if there's a better one, I would certainly give it a shot. So far I have this:

Expand|Select|Wrap|Line Numbers

1. Private Sub CostCenter_AfterUpdate()
2.  
3.   Dim WCostCenter As String
4.    Dim WSQL  As String
5.  
6.   WCostCenter = CostCenter
7.  
8.    WSQL = "select CostCenter.*  from CostCenter "
9.    WSQL = WSQL & "where ((CostCenter.OrgUnit = CostCenter.CostCenterID) LIKE '*" & WCostCenter & "*')"
10.  
11.    OrgUnit.ControlSource = WSQL
12.  
13.  End Sub
14.  
15.  

It should work, but it doesn't :-) I am getting the #Name? in the OrgUnit Field. So, I am thinking the problem lies in the select statement, but I don't see it.
Do you?

Thanks a lot

Expand|Select|Wrap|Line Numbers

1.  
2.    WSQL = "select CostCenter.*  from CostCenter "
3.    WSQL = WSQL & "where (CostCenter.OrgUnit = CostCenter.CostCenterID LIKE ‘“ & Chr(34) & * & Chr(34) & WcostCenter.Value & Chr(34) & * & Chr(34) & “')"
4.  
5.    OrgUnit.ControlSource = WSQL
6.  
7.  End Sub

Thanks for the input, but I don't really understand what you have changed. WCostCenter.Value, what should that be? I define WCostCenter, but whats the value?? And also, what is Chr(34).

Sorry for the answer, but I would like to understand...:-)

.Value is a typo I forgot to take out. What I have changed are the placement of the quotes and the way the wildcard syntax is presented. Chr(34) is Ascii for the double quote, which is used as a delimiter for the wildcard in a string.

Did you try it (after removing .value)? Any different results??

Ahhh...ok, thanks for the explanation. Unfortunately, it didn't work.

Neither control can have ControlSource set to SELECT query. Did you mean RowSource?

Ahhh...ok, thanks for the explanation. Unfortunately, it didn't work.

Please provide details as to what "didn't work" means. Errors? what object is highlighted? Place a code break on the line where you are setting the control source = sql....display the sql string in the immediate window, etc. Remember that I don't have the application in front of me....so I am dependent on you for info.

I have to admit, I do not know. I was thinking that ControlSource needs a task to perform, so I assigned ControlSource. However, I have just tried with RowSource, which isn't a usable "control function". Or what do you mean with RowSource?

ControlSource property is exactly what you see in form design -> field properties -> Control Source. It by its very nature never could be SQL expression.

You have OrgUnit and Region form controls (let them be "controls", bkz "fields" is generally referred to table/query fields). What are there types? I've asked you about RowSource property bkz just thought they are combos.

Thanks for the explanation. So, when I do that (OrgUnit as Controls under properties) and simply write OrgUnit = WSQL in my VB script, I receive runtime error.

CostCenter, OrgUnit and Region are all Text types. CostCenter is the only one with a dropdown list.

Fish,
I am sorry but I have to disagree with you on the Control Source. See this link:

http://office.microsoft.com/en-us/ac...327251033.aspx

PDB, I apologise, but nothing about ControlSource could be set to SQL expression. And, according to common sence, how TextBox is supposed to represent result of SELECT query?

Even if the select statement is limited so that it returns only one value at a time?

Have you tried this? I've never.
Anyway OP tries to retrieve all fields, I guess he has more than one.

P.S. I've tried to enter SELECT with aggregate function to ControlSource property. The miracle didn't happen as it had been supposed to be.

Fish,
I don't think an aggregate function is a good example...but I don't want to spend any more time on this issue. You are right that, in this case, more that one field is being returned to the textbox.

To the OP
--------------
It would be helpful if you could present one row of data to make it easier for us to see exactly what you are trying to accomplish with your sql statement. Maybe you are trying to change the recordSource of the form?

Hi Fish and Hi PDB

You guys had a good discussion and I was very interested to read it. I have to learn a lot. :-)

To my Problem: I have a table structure like that:

Expand|Select|Wrap|Line Numbers

1. Work (tbl)                             CostCenter(tbl)
2. ...
3. CostCenter ---------------------->  CostCenter (Text)
4. ....                                       OrgUnit (Text)
5.                                            Region (Text)
6.  

Values look like: 008080112 , PWM IT - NPP, NY

The Idea is now: That the User on the FORM, chooses from a DropDown Box a CostCenter, and automatically OrgUnit and Region are filled.

I hope its more clear now.

Thanks

• set Form RecordSource property
  Expand|Select|Wrap|Line Numbers

  1. SELECT Work.*, CostCenter.OrgUnit, CostCenter.Region
  2. FROM CostCenter RIGHT JOIN Work ON CostCenter.CostCenter = Work.CostCenter;
• set form controls ControlSource properties to correspondent fields
• lock controls correspondent to OrgUnit and Region fields, via setting Lock property to Yes

Ok. I would suggest you to try the following.

• set Form RecordSource property
  Expand|Select|Wrap|Line Numbers

  1. SELECT Work.*, CostCenter.OrgUnit, CostCenter.Region
  2. FROM CostCenter RIGHT JOIN Work ON CostCenter.CostCenter = Work.CostCenter;
• set form controls ControlSource properties to correspondent fields
• lock controls correspondent to OrgUnit and Region fields, via setting Lock property to Yes

It works fine in one of my db.

Thanks Fish,

So how did you do that. I am trying it as follows:

1. Set RecordSource to Work
2. CostCenter Properties I create the Event AfterUpdate as follows:

Expand|Select|Wrap|Line Numbers

1. Private Sub CostCenter_AfterUpdate()
2.  
3.   Dim WCostCenter As String
4.    Dim WSQL  As String
5.  
6.   WCostCenter = CostCenter
7.  
8.    WSQL = "SELECT Work.*, CostCenter.OrgUnit, CostCenter.Region FROM CostCenter RIGHT JOIN Work ON CostCenter.CostCenter = Work.CostCenter;"
9.  
10.  End Sub
11.  

3. I fix the Fields under Properties OrgUnit and Region with Control Source OrgUnit and Region (for each field). Additionally, I set the to Locked-YES

That's the right procedure, right? However I am still getting the #Name? Error.

I have just tried another way. Set RecordSource of the field with the SELECT statement and I removed the AfterUpdate Event for CostCenter
When I do that, I can't choose CostCenter anymore. No error message just "ping-sound"

• get rid of the CostCenter_AfterUpdate sub
  
  Make the following settings in form design view
• set Form RecordSource property
  Expand|Select|Wrap|Line Numbers

  1. SELECT Work.*, CostCenter.OrgUnit, CostCenter.Region (form properties)
  2. FROM CostCenter RIGHT JOIN Work ON CostCenter.CostCenter = Work.CostCenter;
• set form controls ControlSource properties to correspondent fields (each control properties)
• lock controls correspondent to OrgUnit and Region fields, via setting Lock property to Yes (OrgUnit and Region controls' properties)

Thanks for the further elaboration. That made a lot of things very clear. The Query you gave me is working just fine in the SQL-Builder. Unfortunately, when I enter it in the form and do the steps and save the whole thing, it won't to what it should be doing. I don't receive an error, just a "bing-sound". Additionally, I can't change the CostCenter.

I was doing exactly how you described it. Sorry, I am still bothering you with this problem.

thanks

Nope, the Query isn't running right. The fields we are trying to include in our SQL-Query are empty (CostCenter.Region and CostCenter.OrgUnit).

Hi, there.
I think I should take a look at this. Send me a copy of your db via email (I will open my vCard for download).