Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old March 24th, 2004, 04:22 PM
Authorized User
 
Join Date: Jun 2003
Location: Neverland, FL, USA.
Posts: 98
Thanks: 0
Thanked 0 Times in 0 Posts
Default Linking forms using ADO code

I am having problems trying to convert my DAO code to ADO which is suppose to link two forms together when I click on the button. HEre is my code:

Private Sub cmdConsultant_Click()

DoCmd.OpenForm "frmAllTasks", acNormal
    AgreementNo = "'" & Forms!frmAllAgreements!AgreementNo & "'"
DoCmd.Close acForm, "frmAllAgreements", acSaveYes

End Sub

Any suggestions? Thank You:D

slypunk
__________________
slypunk
  #2 (permalink)  
Old March 24th, 2004, 10:59 PM
Authorized User
 
Join Date: Mar 2004
Location: , , .
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Using filters on forms is not good practice. You should be passing to the other form the key and writing code on the other forms open event to retrieve only the records with that key.

Something like:
DoCmd.OpenForm "frmAllTasks", acNormal, , , , , AgreementNo
DoCmd.Close acForm, "frmAllAgreements", acSaveYes

And on the other forms Open event:
Me.RecordSource = "SELECT * FROM SomeTable WHERE AgreementNo = " & Me.OpenArgs

What you are doing is using the old filter method. Which will work but is inefficient.

This will work if you are hell bent on using filters:

DoCmd.OpenForm "frmAllTasks", acNormal, , , , , AgreementNo
DoCmd.Close acForm, "frmAllAgreements", acSaveYes

And on the other forms Open event:

Me.Filter = "AgreementNo = " & Me.OpenArgs
Me.FilterOn = True
Me.Requery

BTW: I'm assuming that AgreementNo is a Integer.

Hope this helps...
  #3 (permalink)  
Old March 25th, 2004, 10:42 AM
Authorized User
 
Join Date: Jun 2003
Location: Neverland, FL, USA.
Posts: 98
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Quote:
quote:Originally posted by cco2000
 Using filters on forms is not good practice. You should be passing to the other form the key and writing code on the other forms open event to retrieve only the records with that key.

Something like:
DoCmd.OpenForm "frmAllTasks", acNormal, , , , , AgreementNo
DoCmd.Close acForm, "frmAllAgreements", acSaveYes

And on the other forms Open event:
Me.RecordSource = "SELECT * FROM SomeTable WHERE AgreementNo = " & Me.OpenArgs

What you are doing is using the old filter method. Which will work but is inefficient.

This will work if you are hell bent on using filters:

DoCmd.OpenForm "frmAllTasks", acNormal, , , , , AgreementNo
DoCmd.Close acForm, "frmAllAgreements", acSaveYes

And on the other forms Open event:

Me.Filter = "AgreementNo = " & Me.OpenArgs
Me.FilterOn = True
Me.Requery

BTW: I'm assuming that AgreementNo is a Integer.

Hope this helps...
COOL ! It worked thank you very much I spent hours and hours I think I even dreamed about it (sick I know) of how converting this dao code to ado and finally it works! Thanks for your help!

slypunk
  #4 (permalink)  
Old March 25th, 2004, 11:52 AM
Authorized User
 
Join Date: Jun 2003
Location: Neverland, FL, USA.
Posts: 98
Thanks: 0
Thanked 0 Times in 0 Posts
Default

:(Bad news...it's not working now is saying that the expression 'CARN002' you entered is invalid when what it should search for is '01CARN002' it is not fully searching and filtering the correct Agreement Number. Do you know why? THanks

slypunk
  #5 (permalink)  
Old March 25th, 2004, 12:22 PM
Authorized User
 
Join Date: Jun 2003
Location: Neverland, FL, USA.
Posts: 98
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Quote:
quote:Originally posted by sherr8
 :(Bad news...it's not working now is saying that the expression 'CARN002' you entered is invalid when what it should search for is '01CARN002' it is not fully searching and filtering the correct Agreement Number. Do you know why? THanks

slypunk
The Agreement Number field is a string I made the following change to the code:

Private Sub Form_Open(Cancel As Integer)

'will filter agreementno field

Me.Filter = "AgreementNo='" & Me.OpenArgs & "'"
Me.FilterOn = True
Me.Requery

End Sub

but it is not filtering correctly it shows a blank record when it is suppose to filter the matching record. The strange thing is I tested the code and it gives no errors it's just not filtering correctly. HELP PLEASE!! Thank you

slypunk
  #6 (permalink)  
Old March 25th, 2004, 04:48 PM
Authorized User
 
Join Date: Jun 2003
Location: Neverland, FL, USA.
Posts: 98
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Quote:
quote:Originally posted by sherr8
 
Quote:
quote:Originally posted by sherr8
Quote:
 :(Bad news...it's not working now is saying that the expression 'CARN002' you entered is invalid when what it should search for is '01CARN002' it is not fully searching and filtering the correct Agreement Number. Do you know why? THanks

slypunk
The Agreement Number field is a string I made the following change to the code:

Private Sub Form_Open(Cancel As Integer)

'will filter agreementno field

Me.Filter = "AgreementNo='" & Me.OpenArgs & "'"
Me.FilterOn = True
Me.Requery

End Sub

but it is not filtering correctly it shows a blank record when it is suppose to filter the matching record. The strange thing is I tested the code and it gives no errors it's just not filtering correctly. HELP PLEASE!! Thank you

slypunk
Good news! The filter works on both forms now! Exactly how I want it. However, (yes here it comes) there is a third form that is also filtered to those two forms but it is not working properly it does filter the records but the Run-Time Error '2486'You can't carry out this action at the present time' displays and then it freezes all the forms and even the database. Any suggestions anyone? Thank you very much for your help.

slypunk
  #7 (permalink)  
Old March 25th, 2004, 05:41 PM
Authorized User
 
Join Date: Mar 2004
Location: , , .
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Access will execute the code in the lowest level subform first, which means that the subform (and its code) is probably being executed before the main form has opened/loaded. The quick dirty way of fixing the problem would be to put an “ON ERROR Resume NEXT” statement on the open event of the sub form... not the best solution but it generally works.

If you really want to fix it correctly then you will have to step through the code with the debugger and see what is executing in what order and move things about – possibly by putting certain code on the Current event etc.

There is another method...

You can use the LinkChildFields and LinkMasterFields properties together to specify how Access links records in a form or report to records in a subform, subreport, or embedded object, such as a chart. If these properties are set, Access automatically updates the related record in the subform when you change to a new record in a main form. But, you can still run into another set of problems with using this method – check the Access help files for more.

Good luck dude – isn’t Access fun :)
  #8 (permalink)  
Old March 25th, 2004, 06:11 PM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Location: Clarksville, TN, USA.
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts
Default

If you are not using ADO and a back-end such as SQL Server or Oracle, doing a link child fields is the best way to go. With Access Jet Engine, even when you have any where clause on a form, access will bring every record from the table anyway (SQL Server and Oracle will not).



Sal
  #9 (permalink)  
Old March 25th, 2004, 09:20 PM
Authorized User
 
Join Date: Feb 2004
Location: Edmonton, Alberta, Canada.
Posts: 98
Thanks: 0
Thanked 0 Times in 0 Posts
Default

That Jet pulls entire tables is not entirely true. If there is a where clause on an indexed field, Jet will pull the index and then the required records. While this is not as efficient as SQL Sever or Oracle, it is not quite as bad as some have made it out to be, particularly when the database is designed with tables with low record counts typical of many Access databases. In addition, it may be of some advantage to pull the entire table if users want to sort and/or scroll records and niether Oracle nor SQL Server have any advantage in that scenario. In a Terminal Server or ASP based system, Access databases will often be faster (depending on table size and server resources) than SQL Server or Oracle because the same kind of processing is handled on the server and only data (or screens) is parcelled out to the user. In my experience, Access has ALWAYS been faster in a terminal services environment than a comparable SQL Server application against comparably sized data.

Ciao
Jürgen Welz
Edmonton AB Canada
jwelz@hotmail.com
  #10 (permalink)  
Old March 26th, 2004, 09:25 AM
Authorized User
 
Join Date: Jun 2003
Location: Neverland, FL, USA.
Posts: 98
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Quote:
quote:Originally posted by sal
 If you are not using ADO and a back-end such as SQL Server or Oracle, doing a link child fields is the best way to go. With Access Jet Engine, even when you have any where clause on a form, access will bring every record from the table anyway (SQL Server and Oracle will not).



Sal
Actually I am using ADO my purpose is converting the database to SQL Server and now I am still in the works of converting the DAO code to ADO.

slypunk




Similar Threads
Thread Thread Starter Forum Replies Last Post
how to translate a code in DAO to ADO itsmenow Access VBA 2 July 7th, 2005 09:19 AM
How to speed up looping ADO code? llowwelll Pro VB Databases 7 October 24th, 2004 11:12 PM
Linking Subform using ADO Skharva Access 1 January 13th, 2004 12:27 AM
Linking Forms via a drop down menu brendan82 Beginning VB 6 2 December 15th, 2003 10:11 AM
Forms - ADO.net Book KennethMungwira ADO.NET 0 June 23rd, 2003 01:38 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.