Forms Event Procedure

[TheBull369]

I recently upsized an Access 2000 database to SQL server and created a ADP. I have a Form that opens up a report. The user selects criteria on the form and when they hit apply it puts a filter on the Report to only display the data they want/need.

I do this by putting coding behind the OnClick option. The problem I am having is when I created the ADP I can no longer access any of this coding (cant use it). If the OnClick field had [Event Procedure] in it it will error out. The only thing I can put in there is a Macro. Any ideas how I can get around this so I can get my form working?

Thanks

John

 

[cmmrfrds]

Paste in your code from the onclick event.

 

[TheBull369]

Thats the thing it doesnt matter what the code is. If the field says Event Procedure it will not work.

 

[cmmrfrds]

Is it just on the upsized Forms/Reports or can you create a new Report and build code in the event procedures? I have upsized several mdb's and don't remember running into that situation. Is it true on all your objects where you cannot build code behind events or just reports?

 

[TheBull369]

Yes it is true for all of the forms and Reports. It will not let me access the code behind an upsized forms database.

 

[BobJacksonNCI]

Just a guess...

Did you perform the upsize logged on as you, or were you on someone else's PC and therefore logged on as them?

If so, you have a permissions problem.

HTH,
Bob

 

[neecie]

This may be a little too obvious, but have you checked the value of the "Has Module" property of your forms/reports?

 

[TheBull369]

Yeah what I did was create a new database and imported things into it and it slowly began to work. Seems there is a problem with Access 2000 that MS knows about where if you import forms / reports, etc into a ADP sometimes it will give you a Network error message. Once you get that your done for you have to create a whole new database. No way to fix it.

 

[hoialmen]

Bull,

I have 2 things for you:

1) Re. your last post - the problem with importing Access 2000 database object from one mdb to another is a known bug. The "Network Error" that occurrs is caused by importing queries, forms or modules and then closing the mdb without compiling it. However, once you get this error, all is not lost. If you get the error and you know which object(s) was imported without being compiled, simply delete that object and then compile the mdb (or adp). This should fix the problem. Once you have done this, you should then be able to re-import the object (and then compile again).

2) Regarding your original post - your best option in the ADP/SQl world is probably to use a SQL stored procedure that passes in variables as the Record Source of your report.

First, create some global variables in your ADP that are assigned values by the (button's) OnClick event in your form.

rough example 1:

Sub Button_OnClick()

gRptVar1 = Me.txtVal1
gRptVar2 = Me.cmbVal2
gRptVal3 = Me.chkVal3

DoCmd.OpenReport "rptName1"

End Sub

Now that you have assigned your variables, you will need to pass them into your stored procedure. This is a 2-part process in an ADP. First, you will need to create some functions that can be called to pass the variables into the report.

rough example 2:

Function GetRptVal1() as String

If ISNull(gRptVal1) = False Then
GetRptVal1 = gRptVal1
Else:
GetRptVal1 = "%"
End If

End Function

note: You will want to assign the SQL wildcard (%) as the default for any non-values.


Once you have created the functions to pull your report variables, you need to set up you report's Input Parameters. To do this, open your report in design view and open the report properties window. If you scroll all the way down to the last report property, you should see "Input Parameters". This line is where you will enter the variables that you are passing into your stored procedure. In SQL Server, variables are designated using the "@" symbol and you also have to designate the data type so, for the purpose of this example, the proper format for this property will be something like:

@RptVar1 nvarchar=GetRptVar1(), @RptVar2 nvarchar=GetRptVar2(), @RptVar3 int=GetRptVar3(), etc...

Or, if you want to skip using the functions altogether, you can pull your variable values directly from your form using:

@RptVar1 nvarchar=[Forms]![frmRptCriteria].txtVal1, @RptVar2 int=[Forms]...

Finally, you need to write your stored procedure in such a way that it will pull your variables in. Again, you will use the same naming convention and data types that you assigned in your report Input Parameters. So, your stored procedure should be written something like:

rough example3:

Alter Procedure spReport1
@RptVal1 NVARCHAR(15), @RptVal2 NVARCHAR(15), @RptVal3 INT

AS

SELECT * FROM tblReportData
WHERE [Val1]= @RptVal1 AND [Val2] = @RptVal2 AND [Val3] = @RptVal3
ORDER BY [RecordID]

This should be all you have to do. I know it seems like a lot at first but you will get used to it and you will also find that, if you learn to do all your data processing on the SQL side rather than on the Access side, your ADP applications will really cruise (especially if your processing large amounts of data).

I hope this has been of some help to you. If you have any additional questions, feel free to e-mail me at: morgan_n_coke@hotmail.com

Best of luck!

Nate

 

[markphsd]

TheBull

"Seems there is a problem with Access 2000 that MS knows about where if you import forms / reports, etc into a ADP sometimes it will give you a Network error message."

"Once you get that your done for you have to create a whole new database. No way to fix it."

A. It does sound like that is the problem.

B. There is a fix for this, it's a microsoft known bug. The answer to this is posted in a FAQ some where on this site.. If i remember it correctly this is how it goes:
Follow it exactly.
1. create a blank new adp file.
2. Open it.
3. import all the objects you want from the old adp into the new one.
4. hit alt+f11.
5. compile the code
6. close the db next (it's important to not do anything, especially important DO NOT Compact and Repair the DB).
7. Re-open the db, try it out, then compact and repair.



Mark P.

Bleh

 

[hoialmen]

Mark,

I would make 1 correction to your post:

The database object (form, query or module) that has caused your original databsae file to become corrupted must not be copied over to your new file as it will corrupt your new file in the same manner. As I mentioned in my previous post, if you know which object has been imported into your original database file without being compiled (causing the error) and you delete it, your original database file can then be compiled and repaired without going through the trouble of creating a new database file. This shortcut can save you some serious time and frustration if you have an application that contains lots of db objects.

Nate

 

[markphsd]

hoialmen,

Hey, that's kool. that would shorten it up quite a bit, it can be a pain to wait for a whole import to a new db.

But also, how can you determine the culprit object if all events are causing erros? Also I've repaired some friends db's without having to know the bad object. I could do this fix without thinking, I just recognized the error and performed the solution.

The other thing is i've run into a combination errors where you can't even delete the bad object if you happen to find out what it is. So, it gets all nutty. But following the directions i said before works perfect.

I guess i was offering a more comprehensive solution. overkill in this situation perhaps.


Mark P.

Bleh