Unable to open filtered form from within a form 1

[mrliam69]

I am in the process of rewriting an Access program using SQL Server 2000 and Access adp (Access 2000).
Besides the problems of not having queries readily at hand I have this problem when trying to open a form from with in a form which is filtered to only show one record
example. I have a list of Part Numbers and if you double click on the number it would bring up a Part Number Form showing more detail for that part only. I had no problems doing this under Access 2000 mdb/mde and the jet engine but under Access adp I get an error message saying "there was a problem accessing a property or method of the OLE object".
Checked on microsoft and I basically get the above answer that to solve the problem upgrade to Access 2002/XP.

http://support.microsoft.com/default.aspx?scid=kb;en-us;301693

Surely this can't be right ?

Regards

Liam Meadows

 

[cmmrfrds]

I do the type of thing you suggest frequently, but cannot tell from your description where you are having a problem. Can you explain in a little more detail the control flow.

 

[mrliam69]

Ok the probelm is this.

I have a form that displays a list of suppliers in single form view, within that form is a subform showing what parts that supplier supplies (filtered against account number). What I wanted to do and have done before is if you double click on a partnumber in the subform it would then open up the stock form to show more detail.

ex.vb code...
Private Sub Command166_Click()
On Error GoTo Err_Command166_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "FRMstock"

stLinkCriteria = "[PartNumber]=" + Me![PartNumber]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command166_Click:
Exit Sub

Err_Command166_Click:
MsgBox Err.Description
Resume Exit_Command166_Click

End Sub

Now I have changed the & to + because I seem to recollect something about that but it still doesn't work.
I have even tried a button wizard and that fails with errors like can't find FRMstockfilter1 which I haven't got or created. Under Access mdb I never had this problem, I had loads of others but not this one.

Hope this explains

Liam

 

[BSman]

I'm assuming that the part number is a numeric field, but just in case.....

Change the criteria line to read as follows:

stLinkCriteria = "[PartNumber]='" & Me![PartNumber] & "'"

This is to surround the value of PartNumber with single quotes.

By the way, the & should be the correct syntax. An important change in syntax occurs when you are writing a view/query that will be executed by SQL Server (not in Access itself), since for SQL statements executed by SQL Server a date value must be surrounded by single quotes (just like text), rather than #.

 

[mrliam69]

Thanks a million for your help.
Only just started to rewrite the old MDB to ADP and came upon that problem nearly gave up on the whole project because of it as the program uses a lot of those calling forms from within a form.
Supid how microsofts button wizard doesn't put in the correct code.

Once again your a life saver...


Liam