Form Load Event Question???

Ok, this one is unusual but there's got to be an answer for why this is happening.

I have an Access 2k Front-End that's connected to a MySQL Back-End database.  In the front-end there's a property form that list various details about each property.  There is a sub form that's used to basically create a dynamic product price list based on the selected account which is made up of the standard price list table joined to the account contract priced items if any.  Therefore, the final unit price will be the standard price from the price list table if no contract priced items exist for the account.  

When the account form loads that event will set the Record Source of the price list sub form because the account number is a parameter in this record source.

Private Sub Form_Load()
Me.ProductPrice_Subform.Form.RecordSource = "QryProductPrice4"
End Sub

Then the on current event of the account form will requery this sub form so that when the user navigates between accounts the price list sub-from will be generated for that property.

Private Sub Form_Current()
Me.ProductPrice_Subform.Requery
End Sub

This all works perfectly and as expected in my test environment connected to a MySQL database.  I can also connect to the client's MySQL database using the front-end and everything still works.  

The problem is when the front-end is installed the client's machines and the user tries to open the account form,  VB is throwing the following error when the following line of code is executed.

Me.ProductPrice_Subform.Form.RecordSource = "QryProductPrice4"

ERROR:
3146 - ODBC Call Failed

Any Ideas will be appreciated ...

ET

LVL 19
Eric ShermanAccountant/DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Malik1947Commented:
This generally means that your table links are not defined correctly. Open the Table Section of your Front End and make sure the tables are linked properly. use the link manager to update the links to the correct dataset that the tables are supposed to link to.
0
Eric ShermanAccountant/DeveloperAuthor Commented:
Thanks for the quick response Malik1947 ....

But the tables are linked correctly and I can run the front end against their MySQL database and it works.

ET
0
Malik1947Commented:
Each of the machines has to have the dataset defined under the ODBC connections.

to do that you go to Control Panel>Administrative Tools>Data Sources (ODBC). And you add a link to the MySQL.

I may have misunderstood your response.
I have run into this problem before and checking the data sources on the machine in question usually delivers the culprit.




0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

ldunscombeCommented:
Another couple of points.

1: if you make sure that your connection is a "System DSN" as opposed to a user DSN then it will be available to all users of that machine.

2: Make sure your MySQL database user rights are relevant for the logged on user.

Leigh
0
Eric ShermanAccountant/DeveloperAuthor Commented:
Malik1947 and ldunscombe ...

Thanks again for both responses and I understand what you are saying about the DSN's but trust me that's not the problem and the DSN's already exist on all the machines (have been for the last 8 months).  The main account form has several other sub-forms for the account (deliveries, tech calls, tech readings, parts used, etc.) and these use table relationships and they don't generate the ODBC-Call Failed error.  

The price list sub-form is dynamic in that it gets created as the user navigates between accounts.  The account parameter form the main form is passed to the query record source of the sub-form and subsequently re-queried on the form's on current event.  That's why the sub-form is loaded "without" a record-source because the main form "when opening" is not going to pass the account parameter to the sub-form in time to prevent an error in the sub-form's SQL.  Therefore, in the main form's Load Event I set the sub forms Record-Source then Requery the sub-form on the main form's On Current Event.

This all works exactly like it should except when running it on the client's actual machine.  The only difference is I think they are using a different (probably later version) of the  MySQL ODBC Connector and it does't like the sub-form's record source being set in VB while the main form is opening.  

I can make the sub-form be a separate form and just open it with a command button after the main account form has opened but it just seems strange that the current approach that I have will work in one environment and not the other.

ET



0
Malik1947Commented:
I would suggest taking he query that is not working and then building a new query using that information. Pass parameters that should return the data that you are expecting.

If the query runs normally then the issue is somewhere else. If it doesn't run fix the query first then insert it into where it was supposed to run.

Also take a look at this article by microsoft about this issue.

http://support.microsoft.com/kb/161288
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Eric ShermanAccountant/DeveloperAuthor Commented:
Thanks Malik1947 ....

Your last comments jogged my memory and set me on the correct path.  Actually, the problem was not in the form's load event and when I manually ran the queries I remembered that one of queries will pull pricing information out of the client's accounting system that will be used on the price list.  This accounting system uses Pervasive as the database manager and they are using an older version that I have here in my development office.  In the later version of Pervasive you can pass parameters to the query such as ACCOUNT_NUMBER = FORMS!CUSTOMERACCT!TxtACCTNUM  and it will work.  The earlier versions of Pervasive cannot handle the parameter comming directly from a form object.  Therefore, you have to open a QueryDef and write the SQL the has the Account Number already filled in such as ACCOUNT_NUMBER = '02INT100'.

Thanks for your help and I will award you the points since your comments jogged my memory on the subject.

ET
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.