By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,124 Members | 2,027 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,124 IT Pros & Developers. It's quick & easy.

VB and Form!Field populating (automatically)

P: 48
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
Aug 9 '07 #1
Share this Question
Share on Google+
24 Replies


puppydogbuddy
Expert 100+
P: 1,923
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
Try it like this and see if it helps:
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  & Chr(34) & * & Chr(34) & WcostCenter.Value & Chr(34) & * & Chr(34) & ')"
  10.  
  11.    OrgUnit.ControlSource = WSQL
  12.  
  13.  End Sub
Aug 9 '07 #2

P: 48
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 questions, but I would like to understand...:-)
Aug 9 '07 #3

puppydogbuddy
Expert 100+
P: 1,923
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??
Aug 9 '07 #4

P: 48
.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.
Aug 9 '07 #5

FishVal
Expert 2.5K+
P: 2,653
Ahhh...ok, thanks for the explanation. Unfortunately, it didn't work.
Neither control can have ControlSource set to SELECT query. Did you mean RowSource?
Aug 9 '07 #6

P: 48
Neither control can have ControlSource set to SELECT query. Did you mean RowSource?
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?
Aug 9 '07 #7

puppydogbuddy
Expert 100+
P: 1,923
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.
Aug 9 '07 #8

P: 48
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.
Sorry, with it didn't work I mean: the Field on the Form still gives me the Error: #Name?

Compiling in VB-Editor is fine, no errors.

I appologize and thanks again
Aug 9 '07 #9

FishVal
Expert 2.5K+
P: 2,653
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.
Aug 9 '07 #10

P: 48
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.
Aug 9 '07 #11

FishVal
Expert 2.5K+
P: 2,653
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.
Is your form bound to Table/Query? If so plz post the string in the form RecordSource property, if it is table name plz post list of table fields (names, types) as well.
One more question. Do you expect your query will always return NMT 1 record? If no how do you want to represent multiple records?
Aug 9 '07 #12

puppydogbuddy
Expert 100+
P: 1,923
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
Aug 9 '07 #13

FishVal
Expert 2.5K+
P: 2,653
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?
Aug 9 '07 #14

puppydogbuddy
Expert 100+
P: 1,923
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?
Aug 9 '07 #15

FishVal
Expert 2.5K+
P: 2,653
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.
Aug 9 '07 #16

puppydogbuddy
Expert 100+
P: 1,923
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?
Aug 9 '07 #17

P: 48
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
Aug 10 '07 #18

FishVal
Expert 2.5K+
P: 2,653
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
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.
Aug 10 '07 #19

P: 48
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"
Aug 10 '07 #20

FishVal
Expert 2.5K+
P: 2,653
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"
Ok. Once more.
  • 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)

There is no need to handle AfterUpdate event to modify OrgUnit and Region controls as soon as they automatically retrieved with the query above set as Form.RecordSource.

BTW you can paste the SQL to Query builder and check whether it is working properly (I'm pretty sure it does bkz I have a working form based on this).
Aug 10 '07 #21

P: 48
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
Aug 13 '07 #22

P: 48
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).
Aug 13 '07 #23

FishVal
Expert 2.5K+
P: 2,653
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).
Aug 13 '07 #24

P: 48
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).
Thanks Fish for this intensive help, I finally could solve the problem.

For the others: The Select-Statement works just fine, the problem in my case was just the relationships.

Regards and thanks,

Alive
Aug 15 '07 #25

Post your reply

Sign in to post your reply or Sign up for a free account.