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

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

:(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

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 :)

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

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
[email protected]

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