?
Solved

Form Load Event Question???

Posted on 2008-01-30
7
Medium Priority
?
502 Views
Last Modified: 2012-05-05
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

0
Comment
Question by:Eric Sherman
  • 3
  • 3
7 Comments
 
LVL 5

Expert Comment

by:Malik1947
ID: 20782726
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
 
LVL 19

Author Comment

by:Eric Sherman
ID: 20782764
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
 
LVL 5

Expert Comment

by:Malik1947
ID: 20783035
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 new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
LVL 14

Expert Comment

by:ldunscombe
ID: 20783179
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
 
LVL 19

Author Comment

by:Eric Sherman
ID: 20783496
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
 
LVL 5

Accepted Solution

by:
Malik1947 earned 1500 total points
ID: 20789410
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
 
LVL 19

Author Comment

by:Eric Sherman
ID: 20800376
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

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
A quick solution showing how to control and open a POS Cash Register Drawer using VBA with MS Access.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

601 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question