Open a record in form based from a datasheet double click

[andy_dyer]

Hi,

I've seen a few posts around this subject but none seem to hit the spot I need..

I've seen pbaldy's link;

http://www.baldyweb.com/wherecondition.htm

And I'm trying to use it BUT...

I need to pass two parameters in...

Using the above link my code is

DoCmd.OpenForm "frmProjectFinanceYearDetail", , , "txtYear = " & Me.Year & "'"

But I get the following error;

Run-time error '3075':

Syntax error in string in query expression 'txtYear = 2011".

I'm not sure if that's because I need to pass into the equation the ProjectID as well as the Year in roder to open the correct form?

Any ideas what I've done wrong?

Many thanks

 

[John Big Booty]

You appear to be missing an apostrophe;

DoCmd.OpenForm "frmProjectFinanceYearDetail", , , "txtYear = [B][COLOR="Red"]'[/COLOR][/B]" & Me.Year & "'"

 

[pr2-eugin]

You do have a syntax error in your statement.. you have..

DoCmd.OpenForm "frmProjectFinanceYearDetail", , , "txtYear = " & Me.Year & "[COLOR=Red][B] ' [/B][/COLOR]"

Remove the single quote.. your syntax will be correct..

 

[andy_dyer]

Thanks both - I've treied both ways and it gets rid of my syntax error and now opens the form but not for the record I want - just a blank form...

I think I've followed the guidance pbaldy has in his link but can't get to the right record...

Any ideas?

 

[pr2-eugin]

Is the txtYear a String in the Table?

 

[John Big Booty]

You do have a syntax error in your statement.. you have..

DoCmd.OpenForm "frmProjectFinanceYearDetail", , , "txtYear = " & Me.Year & "[COLOR=Red][B] ' [/B][/COLOR]"

Remove the single quote.. your syntax will be correct..

Given that his field txtYear would appear to be a text field that is incorrect he would need to add a second apostrophe inside the double quotes enclosing txtYear = and to the right of the equal sign.

 

[pr2-eugin]

Yes John, I understand, but I felt as Andy is using a Year value I guessed it might be an Integer.. I might be wrong..

 

[andy_dyer]

Hi - Year is typed in as 2011,2012 etc and is currently a text field in the table (not sure if thats right or wrong...)

There is a PrimaryKey in the table FinanceYearID which is an autonumber (long integer)

I tried using that instead;

DoCmd.OpenForm "frmProjectFinanceYearDetail", , , "txtFinanceYearID = '" & Me.FinanceYearID & "'"

but still no joy...

 

[John Big Booty]

For that code you would need to use;

DoCmd.OpenForm "frmProjectFinanceYearDetail", , , "txtFinanceYearID =" & Me.FinanceYearID

 

[John Big Booty]

Are your year records actually in the format 2011,2012

 

[andy_dyer]

Are your year records actually in the format 2011,2012

Not multiple years, no...

But yes people type into a year field the year i.e. 2012..

Is that a faux pas?

 

[andy_dyer]

For that code you would need to use;

DoCmd.OpenForm "frmProjectFinanceYearDetail", , , "txtFinanceYearID =" & Me.FinanceYearID

Thanks I've changed to that and still get a blank form... :banghead:

Don't understand...

 

[John Big Booty]

Can you post a copy of your DB. '03 version for preference and certainly no newer than '07.

 

[andy_dyer]

Errmmm.... I'll need to sanitise it as it's got lots of sensitive stuff in it...

I'll do this and post it up in the next few hours... thanks for your help

 

[andy_dyer]

Can you post a copy of your DB. '03 version for preference and certainly no newer than '07.

And I'm working in Access 2010 so it's a .accb file so will you need me to convert it back?

 

[John Big Booty]

Unfortunately I don't have '10

 

[RonnieODIN]

I have a similar problem. I discovered that my old records actually are filtered but of some reason the record is not shown. Instead a new record is shown. I cannot figure out why it does so or how to solve it.

 

[John Big Booty]

I have a similar problem. I discovered that my old records actually are filtered but of some reason the record is not shown. Instead a new record is shown. I cannot figure out why it does so or how to solve it.

Que?

 

[andy_dyer]

Sorry John - there is just no way I can remove everything to clear it of identifying info... not without re-writing it...

Is there anything else I can provide that can help you help me?

 

[pr2-eugin]

Could you just put a copy of your DB? removing all sensitive information??